openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/jpql/expressions/TestEJBQLCondExpression.java - openjpa - Git at Google

 /*
  * Licensed to the Apache Software Foundation (ASF) under one
  * or more contributor license agreements.  See the NOTICE file
  * distributed with this work for additional information
  * regarding copyright ownership.  The ASF licenses this file
  * to you under the Apache License, Version 2.0 (the
  * "License"); you may not use this file except in compliance
  * with the License.  You may obtain a copy of the License at
  *
  * http://www.apache.org/licenses/LICENSE-2.0
  *
  * Unless required by applicable law or agreed to in writing,
  * software distributed under the License is distributed on an
  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  * KIND, either express or implied.  See the License for the
  * specific language governing permissions and limitations
  * under the License.
  */
 package org.apache.openjpa.persistence.jpql.expressions;

 import java.util.List;
 import javax.persistence.EntityManager;

 import org.apache.openjpa.persistence.common.apps.*;
 import org.apache.openjpa.persistence.common.utils.AbstractTestCase;

 public class TestEJBQLCondExpression extends AbstractTestCase {

     private int userid1, userid2, userid3, userid4, userid5;

     public TestEJBQLCondExpression(String name) {
         super(name, "jpqlclausescactusapp");
     }

     public void setUp() {
         deleteAll(CompUser.class);
         EntityManager em = currentEntityManager();
         startTx(em);

         Address[] add =
             new Address[]{ new Address("43 Sansome", "SF", "USA", "94104"),
                 new Address("24 Mink", "ANTIOCH", "USA", "94513"),
                 new Address("23 Ogbete", "CoalCamp", "NIGERIA", "00000"),
                 new Address("10 Wilshire", "Worcester", "CANADA", "80080"),
                 new Address("23 Bellflower", "Ogui", "NIGERIA", "02000") };

         CompUser user1 = createUser("Seetha", "MAC", add[0], 40, true);
         CompUser user2 = createUser("Shannon", "PC", add[1], 36, false);
         CompUser user3 = createUser("Ugo", "PC", add[2], 19, true);
         CompUser user4 = createUser("Jacob", "LINUX", add[3], 10, true);
         CompUser user5 = createUser("Famzy", "UNIX", add[4], 29, false);

         em.persist(user1);
         userid1 = user1.getUserid();
         em.persist(user2);
         userid2 = user2.getUserid();
         em.persist(user3);
         userid3 = user3.getUserid();
         em.persist(user4);
         userid4 = user4.getUserid();
         em.persist(user5);
         userid5 = user5.getUserid();

         endTx(em);
         endEm(em);
     }

     public void testNothing() {
         EntityManager em = currentEntityManager();
         String query = "SELECT o FROM CompUser o";

         List result = em.createQuery(query).getResultList();

         assertNotNull("the list is null", result);
         assertEquals("the size of the list is not 5", 5, result.size());

         endEm(em);
     }

     public void testBetweenExpr() {
         EntityManager em = currentEntityManager();
         String query =
             "SELECT o.name FROM CompUser o WHERE o.age BETWEEN 19 AND 40 AND o.computerName = 'PC'";

         List result = em.createQuery(query).getResultList();

         assertNotNull("the list is null", result);
         assertEquals("they are not equal", 2, result.size());
         assertTrue("result dont contain shannon", result.contains("Shannon"));
         assertTrue("result dont contain ugo", result.contains("Ugo"));

         endEm(em);
     }

     public void testNotBetweenExpr() {
         EntityManager em = currentEntityManager();
         String query =
             "SELECT o.name FROM CompUser o WHERE o.age NOT BETWEEN 19 AND 40 AND o.computerName= 'PC'";

         List result = em.createQuery(query).getResultList();

         assertNotNull("the list is null", result);
         assertEquals("they are not equal", 0, result.size());

         endEm(em);
     }

     public void testInExpr() {
         EntityManager em = currentEntityManager();
         String query =
             "SELECT o.name FROM CompUser o WHERE o.age IN (29, 40, 10)";

         List result = em.createQuery(query).getResultList();

         assertNotNull("the list is null", result);
         assertEquals(3, result.size());
         assertTrue("seetha is not in the list", result.contains("Seetha"));
         assertTrue("jacob is not in the list", result.contains("Jacob"));
         assertTrue("famzy is not in the list", result.contains("Famzy"));

         endEm(em);
     }

     public void testNotInExpr() {
         EntityManager em = currentEntityManager();
         String query =
             "SELECT o.name FROM CompUser o WHERE o.age NOT IN (29, 40, 10)";

         List result = em.createQuery(query).getResultList();

         assertNotNull(result);
         assertEquals(2, result.size());
         assertTrue(result.contains("Ugo"));
         assertTrue(result.contains("Shannon"));

         endEm(em);
     }

     public void testLikeExpr() {
         EntityManager em = currentEntityManager();

         String query =
             "SELECT o.computerName FROM CompUser o WHERE o.name LIKE 'Sha%' AND o.computerName NOT IN ('PC')";

         List result = em.createQuery(query).getResultList();

         assertNotNull(result);
         assertEquals(0, result.size());

         query =
             "SELECT o.computerName FROM CompUser o WHERE o.name LIKE 'Sha%o_' AND o.computerName NOT IN ('UNIX')";

         result = em.createQuery(query).getResultList();

         assertNotNull(result);
         assertEquals(1, result.size());

         query = "SELECT o.name FROM CompUser o WHERE o.name LIKE '_J%'";

         result = em.createQuery(query).getResultList();

         assertNotNull(result);
         assertEquals(0, result.size());

         query = "SELECT o.name FROM CompUser o WHERE o.name LIKE ?1 ESCAPE '|'";

         result = em.createQuery(query).setParameter(1, "%|_%").getResultList();

         assertNotNull(result);
         assertEquals(0, result.size());

         endEm(em);
     }

     public void testNullExpr() {
         EntityManager em = currentEntityManager();

         String query =
             "SELECT o.name FROM CompUser o WHERE o.age IS NOT NULL AND o.computerName = 'PC' ";

         List result = em.createQuery(query).getResultList();

         assertNotNull("the list is null", result);
         assertEquals("the list size is not 2", 2, result.size());
         assertTrue("the result doesnt contain ugo", result.contains("Ugo"));
         assertTrue("the result doesnt contain shannon",
             result.contains("Shannon"));

         endEm(em);
     }

     public void testNullExpr2() {
         EntityManager em = currentEntityManager();

         String query =
             "SELECT o.name FROM CompUser o WHERE o.address.country IS NULL";

         List result = em.createQuery(query).getResultList();

         assertNotNull("the list is null", result);
         assertEquals("they are not equal", 0, result.size());

         endEm(em);
     }

     public void testIsEmptyExpr() {
         EntityManager em = currentEntityManager();

         String query =
             "SELECT o.name FROM CompUser o WHERE o.nicknames IS NOT EMPTY";

         List result = em.createQuery(query).getResultList();

         assertNotNull("the list is null", result);
         assertEquals("they are not equal", 0, result.size());

         endEm(em);
     }

     public void testIsEmptyExpr2() {
         EntityManager em = currentEntityManager();

         String query =
             "SELECT o.name FROM CompUser o WHERE o.nicknames IS EMPTY";

         List result = em.createQuery(query).getResultList();

         assertNotNull("the list is null", result);
         assertEquals("they are not equal", 5, result.size());

         endEm(em);
     }

     /**
      * TO BE TESTED LATER WITH A DIFF DATABASE
      * public void testMemberOfExpr(){}
      */

     public void testExistExpr() {
         EntityManager em = currentEntityManager();

         String query = "SELECT DISTINCT o.name FROM CompUser o WHERE EXISTS" +
             " (SELECT c FROM Address c WHERE c = o.address )";

         List result = em.createQuery(query).getResultList();

         assertNotNull("the list is null", result);
         assertEquals("they are not equal", 5, result.size());
         assertTrue("Seetha is not list", result.contains("Seetha"));
         assertTrue("Shannon is not list", result.contains("Shannon"));
         assertTrue("jacob is not list", result.contains("Jacob"));
         assertTrue("ugo is not list", result.contains("Ugo"));

         endEm(em);
     }

     public void testNotExistExpr() {
         EntityManager em = currentEntityManager();

         String query =
             "SELECT DISTINCT o.name FROM CompUser o WHERE NOT EXISTS" +
                 " (SELECT s FROM CompUser s WHERE s.address.country = o.address.country)";

         List result = em.createQuery(query).getResultList();

         assertNotNull("list is null", result);
         assertEquals("they are not equal", 0, result.size());

         endEm(em);
     }

     public void testAnyExpr() {
         EntityManager em = currentEntityManager();

         String query =
             "SELECT o.name FROM CompUser o WHERE o.address.zipcode = ANY (" +
                 " SELECT s.computerName FROM CompUser s WHERE s.address.country IS NOT NULL )";

         List result = em.createQuery(query).getResultList();

         assertNotNull("list is null", result);
         assertEquals("they are not equal", 0, result.size());

         endEm(em);
     }

     public void testConstructorExpr() {
         EntityManager em = currentEntityManager();

         String query =
             "SELECT NEW org.apache.openjpa.persistence.common.apps.MaleUser(c.name, c.computerName, c.address, c.age, c.userid)" +
                 " FROM CompUser c WHERE c.name = 'Seetha'";

         MaleUser male = (MaleUser) em.createQuery(query).getSingleResult();

         assertNotNull("the list is null", male);
         assertEquals("the names dont match", "Seetha", male.getName());
         assertEquals("computer names dont match", "MAC",
             male.getComputerName());
         assertEquals("the ages dont match", 40, male.getAge());

         endEm(em);
     }

     public CompUser createUser(String name, String cName, Address add, int age,
         boolean isMale) {
         CompUser user = null;
         if (isMale) {
             user = new MaleUser();
             user.setName(name);
             user.setComputerName(cName);
             user.setAddress(add);
             user.setAge(age);
         } else {
             user = new FemaleUser();
             user.setName(name);
             user.setComputerName(cName);
             user.setAddress(add);
             user.setAge(age);
         }
         return user;
 	}
 }
	/*
	* Licensed to the Apache Software Foundation (ASF) under one
	* or more contributor license agreements. See the NOTICE file
	* distributed with this work for additional information
	* regarding copyright ownership. The ASF licenses this file
	* to you under the Apache License, Version 2.0 (the
	* "License"); you may not use this file except in compliance
	* with the License. You may obtain a copy of the License at
	*
	* http://www.apache.org/licenses/LICENSE-2.0
	*
	* Unless required by applicable law or agreed to in writing,
	* software distributed under the License is distributed on an
	* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	* KIND, either express or implied. See the License for the
	* specific language governing permissions and limitations
	* under the License.
	*/
	package org.apache.openjpa.persistence.jpql.expressions;

	import java.util.List;
	import javax.persistence.EntityManager;

	import org.apache.openjpa.persistence.common.apps.*;
	import org.apache.openjpa.persistence.common.utils.AbstractTestCase;

	public class TestEJBQLCondExpression extends AbstractTestCase {

	private int userid1, userid2, userid3, userid4, userid5;

	public TestEJBQLCondExpression(String name) {
	super(name, "jpqlclausescactusapp");
	}

	public void setUp() {
	deleteAll(CompUser.class);
	EntityManager em = currentEntityManager();
	startTx(em);

	Address[] add =
	new Address[]{ new Address("43 Sansome", "SF", "USA", "94104"),
	new Address("24 Mink", "ANTIOCH", "USA", "94513"),
	new Address("23 Ogbete", "CoalCamp", "NIGERIA", "00000"),
	new Address("10 Wilshire", "Worcester", "CANADA", "80080"),
	new Address("23 Bellflower", "Ogui", "NIGERIA", "02000") };

	CompUser user1 = createUser("Seetha", "MAC", add[0], 40, true);
	CompUser user2 = createUser("Shannon", "PC", add[1], 36, false);
	CompUser user3 = createUser("Ugo", "PC", add[2], 19, true);
	CompUser user4 = createUser("Jacob", "LINUX", add[3], 10, true);
	CompUser user5 = createUser("Famzy", "UNIX", add[4], 29, false);

	em.persist(user1);
	userid1 = user1.getUserid();
	em.persist(user2);
	userid2 = user2.getUserid();
	em.persist(user3);
	userid3 = user3.getUserid();
	em.persist(user4);
	userid4 = user4.getUserid();
	em.persist(user5);
	userid5 = user5.getUserid();

	endTx(em);
	endEm(em);
	}

	public void testNothing() {
	EntityManager em = currentEntityManager();
	String query = "SELECT o FROM CompUser o";

	List result = em.createQuery(query).getResultList();

	assertNotNull("the list is null", result);
	assertEquals("the size of the list is not 5", 5, result.size());

	endEm(em);
	}

	public void testBetweenExpr() {
	EntityManager em = currentEntityManager();
	String query =
	"SELECT o.name FROM CompUser o WHERE o.age BETWEEN 19 AND 40 AND o.computerName = 'PC'";

	List result = em.createQuery(query).getResultList();

	assertNotNull("the list is null", result);
	assertEquals("they are not equal", 2, result.size());
	assertTrue("result dont contain shannon", result.contains("Shannon"));
	assertTrue("result dont contain ugo", result.contains("Ugo"));

	endEm(em);
	}

	public void testNotBetweenExpr() {
	EntityManager em = currentEntityManager();
	String query =
	"SELECT o.name FROM CompUser o WHERE o.age NOT BETWEEN 19 AND 40 AND o.computerName= 'PC'";

	List result = em.createQuery(query).getResultList();

	assertNotNull("the list is null", result);
	assertEquals("they are not equal", 0, result.size());

	endEm(em);
	}

	public void testInExpr() {
	EntityManager em = currentEntityManager();
	String query =
	"SELECT o.name FROM CompUser o WHERE o.age IN (29, 40, 10)";

	List result = em.createQuery(query).getResultList();

	assertNotNull("the list is null", result);
	assertEquals(3, result.size());
	assertTrue("seetha is not in the list", result.contains("Seetha"));
	assertTrue("jacob is not in the list", result.contains("Jacob"));
	assertTrue("famzy is not in the list", result.contains("Famzy"));

	endEm(em);
	}

	public void testNotInExpr() {
	EntityManager em = currentEntityManager();
	String query =
	"SELECT o.name FROM CompUser o WHERE o.age NOT IN (29, 40, 10)";

	List result = em.createQuery(query).getResultList();

	assertNotNull(result);
	assertEquals(2, result.size());
	assertTrue(result.contains("Ugo"));
	assertTrue(result.contains("Shannon"));

	endEm(em);
	}

	public void testLikeExpr() {
	EntityManager em = currentEntityManager();

	String query =
	"SELECT o.computerName FROM CompUser o WHERE o.name LIKE 'Sha%' AND o.computerName NOT IN ('PC')";

	List result = em.createQuery(query).getResultList();

	assertNotNull(result);
	assertEquals(0, result.size());

	query =
	"SELECT o.computerName FROM CompUser o WHERE o.name LIKE 'Sha%o_' AND o.computerName NOT IN ('UNIX')";

	result = em.createQuery(query).getResultList();

	assertNotNull(result);
	assertEquals(1, result.size());

	query = "SELECT o.name FROM CompUser o WHERE o.name LIKE '_J%'";

	result = em.createQuery(query).getResultList();

	assertNotNull(result);
	assertEquals(0, result.size());

	query = "SELECT o.name FROM CompUser o WHERE o.name LIKE ?1 ESCAPE '\|'";

	result = em.createQuery(query).setParameter(1, "%\|_%").getResultList();

	assertNotNull(result);
	assertEquals(0, result.size());

	endEm(em);
	}

	public void testNullExpr() {
	EntityManager em = currentEntityManager();

	String query =
	"SELECT o.name FROM CompUser o WHERE o.age IS NOT NULL AND o.computerName = 'PC' ";

	List result = em.createQuery(query).getResultList();

	assertNotNull("the list is null", result);
	assertEquals("the list size is not 2", 2, result.size());
	assertTrue("the result doesnt contain ugo", result.contains("Ugo"));
	assertTrue("the result doesnt contain shannon",
	result.contains("Shannon"));

	endEm(em);
	}

	public void testNullExpr2() {
	EntityManager em = currentEntityManager();

	String query =
	"SELECT o.name FROM CompUser o WHERE o.address.country IS NULL";

	List result = em.createQuery(query).getResultList();

	assertNotNull("the list is null", result);
	assertEquals("they are not equal", 0, result.size());

	endEm(em);
	}

	public void testIsEmptyExpr() {
	EntityManager em = currentEntityManager();

	String query =
	"SELECT o.name FROM CompUser o WHERE o.nicknames IS NOT EMPTY";

	List result = em.createQuery(query).getResultList();

	assertNotNull("the list is null", result);
	assertEquals("they are not equal", 0, result.size());

	endEm(em);
	}

	public void testIsEmptyExpr2() {
	EntityManager em = currentEntityManager();

	String query =
	"SELECT o.name FROM CompUser o WHERE o.nicknames IS EMPTY";

	List result = em.createQuery(query).getResultList();

	assertNotNull("the list is null", result);
	assertEquals("they are not equal", 5, result.size());

	endEm(em);
	}

	/**
	* TO BE TESTED LATER WITH A DIFF DATABASE
	* public void testMemberOfExpr(){}
	*/

	public void testExistExpr() {
	EntityManager em = currentEntityManager();

	String query = "SELECT DISTINCT o.name FROM CompUser o WHERE EXISTS" +
	" (SELECT c FROM Address c WHERE c = o.address )";

	List result = em.createQuery(query).getResultList();

	assertNotNull("the list is null", result);
	assertEquals("they are not equal", 5, result.size());
	assertTrue("Seetha is not list", result.contains("Seetha"));
	assertTrue("Shannon is not list", result.contains("Shannon"));
	assertTrue("jacob is not list", result.contains("Jacob"));
	assertTrue("ugo is not list", result.contains("Ugo"));

	endEm(em);
	}

	public void testNotExistExpr() {
	EntityManager em = currentEntityManager();

	String query =
	"SELECT DISTINCT o.name FROM CompUser o WHERE NOT EXISTS" +
	" (SELECT s FROM CompUser s WHERE s.address.country = o.address.country)";

	List result = em.createQuery(query).getResultList();

	assertNotNull("list is null", result);
	assertEquals("they are not equal", 0, result.size());

	endEm(em);
	}

	public void testAnyExpr() {
	EntityManager em = currentEntityManager();

	String query =
	"SELECT o.name FROM CompUser o WHERE o.address.zipcode = ANY (" +
	" SELECT s.computerName FROM CompUser s WHERE s.address.country IS NOT NULL )";

	List result = em.createQuery(query).getResultList();

	assertNotNull("list is null", result);
	assertEquals("they are not equal", 0, result.size());

	endEm(em);
	}

	public void testConstructorExpr() {
	EntityManager em = currentEntityManager();

	String query =
	"SELECT NEW org.apache.openjpa.persistence.common.apps.MaleUser(c.name, c.computerName, c.address, c.age, c.userid)" +
	" FROM CompUser c WHERE c.name = 'Seetha'";

	MaleUser male = (MaleUser) em.createQuery(query).getSingleResult();

	assertNotNull("the list is null", male);
	assertEquals("the names dont match", "Seetha", male.getName());
	assertEquals("computer names dont match", "MAC",
	male.getComputerName());
	assertEquals("the ages dont match", 40, male.getAge());

	endEm(em);
	}

	public CompUser createUser(String name, String cName, Address add, int age,
	boolean isMale) {
	CompUser user = null;
	if (isMale) {
	user = new MaleUser();
	user.setName(name);
	user.setComputerName(cName);
	user.setAddress(add);
	user.setAge(age);
	} else {
	user = new FemaleUser();
	user.setName(name);
	user.setComputerName(cName);
	user.setAddress(add);
	user.setAge(age);
	}
	return user;
	}
	}