public void parameterTest() {
EntityManager em = createEntityManager();
List employeeList = getServerSession().readAllObjects(Employee.class);
Employee expectedEmployee = (Employee) employeeList.get(0);
int i = 1;
while (expectedEmployee.getPhoneNumbers().size() == 0) {
expectedEmployee = (Employee) employeeList.get(i);
i++;
}
String phoneNumber = (expectedEmployee.getPhoneNumbers().iterator().next()).getNumber();
String ejbqlString;
String alternateEjbqlString;
if (usesSOP() && getServerSession().getPlatform().isOracle()) {
// distinct is incompatible with blob in selection clause on Oracle
ejbqlString = "SELECT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = ?1";
alternateEjbqlString =
"SELECT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = :number";
} else {
ejbqlString = "SELECT DISTINCT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = ?1";
alternateEjbqlString =
"SELECT DISTINCT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = :number";
}
List firstResult = em.createQuery(ejbqlString).setParameter(1, phoneNumber).getResultList();
List secondResult =
em.createQuery(alternateEjbqlString).setParameter("number", phoneNumber).getResultList();
// random test cant duplicate
Assert.assertTrue(
"Parameter test failed: two equivalent ejb queries return different results",
comparer.compareObjects(secondResult, firstResult));
Assert.assertTrue(
"Parameter test failed", comparer.compareObjects(expectedEmployee, firstResult));
}
public void getOrderForCustomer() {
EntityManager em = createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("name").equal("Jane Smith");
ReadAllQuery raq = new ReadAllQuery(Customer.class);
raq.setSelectionCriteria(whereClause);
Customer expectedCustomer = (Customer) (((List) getServerSession().executeQuery(raq)).get(0));
SalesPerson salesPerson =
((Order) (expectedCustomer.getOrders().iterator().next())).getSalesPerson();
String ejbqlString =
"SELECT DISTINCT c FROM Customer c JOIN c.orders o JOIN o.salesPerson s WHERE s.id = "
+ salesPerson.getId();
List firstResult = em.createQuery(ejbqlString).getResultList();
String alternateEjbqlString =
"SELECT DISTINCT c FROM Customer c, IN(c.orders) o WHERE o.salesPerson.id = "
+ salesPerson.getId();
List secondResuslt = em.createQuery(alternateEjbqlString).getResultList();
// only 1 order for this customer
Assert.assertEquals(
"Get order for customer test failed: data validation error", firstResult.size(), 1);
Assert.assertTrue(
"Get order for customer test failed: two equivalent ejb queries return different results",
comparer.compareObjects(secondResuslt, firstResult));
Assert.assertTrue(
"Get order for customer test failed",
comparer.compareObjects(expectedCustomer, firstResult));
}
public void findAllEmployeesWithPhoneNumbers() {
EntityManager em = createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.isEmpty("phoneNumbers").not();
ReadAllQuery raq = new ReadAllQuery(Employee.class);
raq.setSelectionCriteria(whereClause);
raq.useDistinct();
List expectedResult = (List) getServerSession().executeQuery(raq);
String ejbqlString = "SELECT DISTINCT e FROM Employee e, IN (e.phoneNumbers) l";
Query query = em.createQuery(ejbqlString);
if (usesSOP() && getServerSession().getPlatform().isOracle()) {
// distinct is incompatible with blob in selection clause on Oracle
query.setHint(QueryHints.SERIALIZED_OBJECT, "false");
}
List firstResult = query.getResultList();
String alternateEjbqlString = "SELECT e FROM Employee e WHERE e.phoneNumbers IS NOT EMPTY";
List secondResult = em.createQuery(alternateEjbqlString).getResultList();
// 14 employees returned
Assert.assertEquals(
"Ejbql statements returned different results: data validation error",
firstResult.size(),
14);
Assert.assertTrue(
"Equivalent Ejbql statements returned different results",
comparer.compareObjects(secondResult, firstResult));
Assert.assertTrue(
"Find all employees with phone numbers test failed",
comparer.compareObjects(expectedResult, firstResult));
}
public void getOrderLargerThan() {
EntityManager em = createEntityManager();
ExpressionBuilder builder1 = new ExpressionBuilder(Order.class);
ExpressionBuilder builder2 = new ExpressionBuilder(Order.class);
Expression o1Quantity = builder1.get("quantity");
Expression o2Quantity = builder2.get("quantity");
Expression quantityComparison = o1Quantity.greaterThan(o2Quantity);
Expression o2CustomerName = builder2.get("customer").get("name");
Expression nameComparison = o2CustomerName.equal("Jane Smith");
Expression whereClause = quantityComparison.and(nameComparison);
ReadAllQuery raq = new ReadAllQuery();
raq.setSelectionCriteria(whereClause);
raq.setReferenceClass(Order.class);
raq.useDistinct();
List expectedResult = (List) getServerSession().executeQuery(raq);
String ejbqlString =
"SELECT DISTINCT o1 FROM OrderBean o1, OrderBean o2 WHERE o1.quantity > o2.quantity AND"
+ " o2.customer.name = 'Jane Smith' ";
List result = em.createQuery(ejbqlString).getResultList();
// only 1 order
Assert.assertEquals(
"Get order larger than test failed: data validation error", result.size(), 1);
Assert.assertTrue(
"Get order larger than test failed", comparer.compareObjects(expectedResult, result));
}
/**
* The setup is done as a test, both to record its failure, and to allow execution in the server.
*/
public void testSetup() {
clearCache();
// get session to start setup
DatabaseSession session = JUnitTestCase.getServerSession();
// create a new EmployeePopulator
EmployeePopulator employeePopulator = new EmployeePopulator();
RelationshipsExamples relationshipExamples = new RelationshipsExamples();
new AdvancedTableCreator().replaceTables(session);
new RelationshipsTableManager().replaceTables(session);
// initialize the global comparer object
comparer = new JUnitDomainObjectComparer();
// set the session for the comparer to use
comparer.setSession((AbstractSession) session.getActiveSession());
// Populate the advanced model
employeePopulator.buildExamples();
// populate the relationships model and persist as well
relationshipExamples.buildExamples(session);
// Persist the advanced model examples in the database
employeePopulator.persistExample(session);
}
public void aggregateParameterTest() {
EntityManager em = createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
ReportQuery query =
new ReportQuery(
org.eclipse.persistence.testing.models.jpa.advanced.Employee.class, builder);
query.returnWithoutReportQueryResult();
query.addItem("employee", builder);
org.eclipse.persistence.testing.models.jpa.advanced.EmploymentPeriod period =
new EmploymentPeriod();
Calendar startCalendar = Calendar.getInstance();
Calendar endCalendar = Calendar.getInstance();
startCalendar.set(1901, 11, 31, 0, 0, 0);
endCalendar.set(1995, 0, 12, 0, 0, 0);
period.setStartDate(new java.sql.Date(startCalendar.getTime().getTime()));
period.setEndDate(new java.sql.Date(endCalendar.getTime().getTime()));
Expression exp = builder.get("period").equal(builder.getParameter("period"));
query.setSelectionCriteria(exp);
query.addArgument("period", EmploymentPeriod.class);
Vector args = new Vector();
args.add(period);
List expectedResult = (Vector) getServerSession().executeQuery(query, args);
List result =
em.createQuery("SELECT e FROM Employee e WHERE e.period = :period ")
.setParameter("period", period)
.getResultList();
Assert.assertTrue(
"aggregateParameterTest failed", comparer.compareObjects(expectedResult, result));
}
public void findEmployeeWithWorkPhone2258812() {
EntityManager em = createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause1 = builder.anyOf("phoneNumbers").get("type").equal("Work");
Expression whereClause2 = builder.anyOf("phoneNumbers").get("number").equal("2258812");
ReadAllQuery raq = new ReadAllQuery(Employee.class);
raq.setSelectionCriteria(whereClause1.and(whereClause2));
if (usesSOP() && getServerSession().getPlatform().isOracle()) {
// distinct is incompatible with blob in selection clause on Oracle
} else {
raq.useDistinct();
}
List expectedResult = (List) getServerSession().executeQuery(raq);
String ejbqlString;
if (usesSOP() && getServerSession().getPlatform().isOracle()) {
// distinct is incompatible with blob in selection clause on Oracle
ejbqlString =
"SELECT e FROM Employee e JOIN e.phoneNumbers p "
+ "WHERE p.type = 'Work' AND p.number = '2258812' ";
} else {
ejbqlString =
"SELECT DISTINCT e FROM Employee e JOIN e.phoneNumbers p "
+ "WHERE p.type = 'Work' AND p.number = '2258812' ";
}
List result = em.createQuery(ejbqlString).getResultList();
// 8 employees
Assert.assertEquals(
"Find employee with 2258812 number test failed: data validation error", result.size(), 8);
Assert.assertTrue(
"Find employee with 2258812 number test failed",
comparer.compareObjects(expectedResult, result));
}
/*
* test for gf675, using count, group by and having fails. This test is specific for a a use case
* where DISTINCT is used with Count and group by
*/
public void complexCountDistinctWithGroupByTest2() {
oracle.toplink.essentials.ejb.cmp3.EntityManager em =
(oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
// need to set the class in the expressionbuilder, as the Count(Distinct) will cause the
// query to change and be built around the Employee class instead of the Address class.
ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);
ReportQuery rq = new ReportQuery(Address.class, expbldr);
Expression exp = expbldr.anyOf("employees");
Expression exp2 = expbldr.get("city");
rq.addAttribute("city", exp2);
rq.addCount("COUNT1", exp, Long.class);
rq.addCount("COUNT2", exp.get("lastName").distinct(), Long.class);
rq.addGrouping(exp2);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
String ejbqlString3 =
"SELECT a.city, COUNT( e ), COUNT( DISTINCT e.lastName ) FROM Address a JOIN a.employees e GROUP BY a.city";
Query q = em.createQuery(ejbqlString3);
List result = (List) q.getResultList();
Assert.assertTrue(
"Complex COUNT(Distinct) with Group By test failed",
comparer.compareObjects(result, expectedResult));
}
public void findAllEmployeesWithCellPhones() {
EntityManager em = createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.anyOf("phoneNumbers").get("type").equal("Cellular");
ReadAllQuery raq = new ReadAllQuery(Employee.class);
raq.setSelectionCriteria(whereClause);
if (usesSOP() && getServerSession().getPlatform().isOracle()) {
// distinct is incompatible with blob in selection clause on Oracle
} else {
raq.useDistinct();
}
List expectedResult = (List) getServerSession().executeQuery(raq);
String ejbqlString;
if (usesSOP() && getServerSession().getPlatform().isOracle()) {
// distinct is incompatible with blob in selection clause on Oracle
ejbqlString = "SELECT e FROM Employee e JOIN e.phoneNumbers p " + "WHERE p.type = 'Cellular'";
} else {
ejbqlString =
"SELECT DISTINCT e FROM Employee e JOIN e.phoneNumbers p " + "WHERE p.type = 'Cellular'";
}
List firstResult = em.createQuery(ejbqlString).getResultList();
String alternateEjbqlString;
if (usesSOP() && getServerSession().getPlatform().isOracle()) {
// distinct is incompatible with blob in selection clause on Oracle
alternateEjbqlString =
"SELECT e FROM Employee e INNER JOIN e.phoneNumbers p " + "WHERE p.type = 'Cellular'";
} else {
alternateEjbqlString =
"SELECT DISTINCT e FROM Employee e INNER JOIN e.phoneNumbers p "
+ "WHERE p.type = 'Cellular'";
}
List secondResult = em.createQuery(alternateEjbqlString).getResultList();
// 4 employees returned
Assert.assertEquals(
"Find all employees with cellular phone numbers test failed: data validation error",
firstResult.size(),
4);
Assert.assertTrue(
"Find all employees with cellular phone numbers test failed: two equivalent ejb queries return different results",
comparer.compareObjects(secondResult, firstResult));
Assert.assertTrue(
"Find all employees with cellular phone numbers test failed",
comparer.compareObjects(expectedResult, secondResult));
}
/** Test for partial fix of GF 932. */
public void complexHavingWithAggregate() {
oracle.toplink.essentials.ejb.cmp3.EntityManager em =
(oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
// Test using the project id in COUNT, GROUP BY and HAVING
ExpressionBuilder employeeBuilder = new ExpressionBuilder(Employee.class);
ReportQuery rq = new ReportQuery(Employee.class, employeeBuilder);
Expression projects = employeeBuilder.anyOf("projects");
Expression pid = projects.get("id");
Expression count = pid.count();
rq.addAttribute("id", pid);
rq.addAttribute("COUNT", count, Long.class);
rq.addGrouping(pid);
rq.setHavingExpression(count.greaterThan(1));
rq.setShouldReturnWithoutReportQueryResult(true);
Vector expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
String jpql =
"SELECT p.id, COUNT(p.id) FROM Employee e JOIN e.projects p "
+ "GROUP BY p.id HAVING COUNT(p.id)>1";
List result = em.createQuery(jpql).getResultList();
Assert.assertTrue(
"Complex HAVING with aggregate function failed",
comparer.compareObjects(result, expectedResult));
// Test using the project itself in COUNT, GROUP BY and HAVING
employeeBuilder = new ExpressionBuilder(Employee.class);
rq = new ReportQuery(Employee.class, employeeBuilder);
projects = employeeBuilder.anyOf("projects");
count = projects.count();
rq.addAttribute("projects", projects);
rq.addAttribute("COUNT", count, Long.class);
rq.addGrouping(projects);
rq.setHavingExpression(count.greaterThan(1));
rq.setShouldReturnWithoutReportQueryResult(true);
expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
jpql = "SELECT p, COUNT(p) FROM Employee e JOIN e.projects p " + "GROUP BY p HAVING COUNT(p)>1";
result = em.createQuery(jpql).getResultList();
Assert.assertTrue(
"Complex HAVING with aggregate function failed",
comparer.compareObjects(result, expectedResult));
}
public void findAllOrders() {
EntityManager em = createEntityManager();
List expectedResult = getServerSession().readAllObjects(Order.class);
String ejbqlString = "SELECT o FROM OrderBean o";
List result = em.createQuery(ejbqlString).getResultList();
// 4 orders returned
Assert.assertEquals("Find all orders test failed: data validation error", result.size(), 4);
Assert.assertTrue(
"Find all orders test failed", comparer.compareObjects(expectedResult, result));
}
public void getSalesPersonForOrders() {
EntityManager em = createEntityManager();
List expectedResult = getServerSession().readAllObjects(SalesPerson.class);
String ejbqlString = "SELECT DISTINCT o.salesPerson FROM Customer AS c, IN(c.orders) o";
List result = em.createQuery(ejbqlString).getResultList();
// 2 sales person
Assert.assertEquals(
"Get SalesPerson for Orders test failed: data validation error", result.size(), 2);
Assert.assertTrue(
"Get SalesPerson for Orders test failed", comparer.compareObjects(expectedResult, result));
}
public void testOuterJoin() {
EntityManager em = createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause =
builder.anyOfAllowingNone("phoneNumbers").get("type").equal("Cellular");
ReadAllQuery raq = new ReadAllQuery(Employee.class);
raq.setSelectionCriteria(whereClause);
List expectedResult = (List) getServerSession().executeQuery(raq);
String ejbqlString =
"SELECT e FROM Employee e LEFT JOIN e.phoneNumbers p " + "WHERE p.type = 'Cellular'";
List firstResult = em.createQuery(ejbqlString).getResultList();
String alternateEjbqlString =
"SELECT e FROM Employee e LEFT OUTER JOIN e.phoneNumbers p " + "WHERE p.type = 'Cellular'";
List secondResult = em.createQuery(alternateEjbqlString).getResultList();
// return 4 employees with cell phones
Assert.assertEquals(
"Get SalesPerson for Orders test failed: data validation error", firstResult.size(), 4);
Assert.assertTrue(
"Get Outer Join test failed: two equivalent ejb queries return different results",
comparer.compareObjects(secondResult, firstResult));
Assert.assertTrue(
"Get Outer Join test failed", comparer.compareObjects(expectedResult, firstResult));
}
public void findEmployeesInOntario() {
EntityManager em = createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("address").get("province").equal("ONT");
List expectedResult = getServerSession().readAllObjects(Employee.class, whereClause);
String ejbqlString = "SELECT e FROM Employee e WHERE e.address.province='ONT'";
List result = em.createQuery(ejbqlString).getResultList();
// 9 employees returned
Assert.assertEquals(
"Find Employees in Ontario test failed: data validation error", result.size(), 9);
Assert.assertTrue(
"Find Employees in Ontario test failed", comparer.compareObjects(expectedResult, result));
}
public void findOrdersWithDifferentBilledCustomer() {
EntityManager em = createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.get("customer").equal(builder.get("billedCustomer")).not();
ReadAllQuery raq = new ReadAllQuery(Order.class);
raq.setSelectionCriteria(whereClause);
List expectedResult = (List) getServerSession().executeQuery(raq);
String ejbqlString = "SELECT o FROM OrderBean o WHERE o.customer <> o.billedCustomer";
List firstResult = em.createQuery(ejbqlString).getResultList();
String alternateEjbqlString =
"SELECT o FROM OrderBean o WHERE NOT o.customer.customerId = o.billedCustomer.customerId";
List secondResult = em.createQuery(alternateEjbqlString).getResultList();
// 2 orders returned
Assert.assertTrue(
"Find orders with different billed customers test failed: two equivalent ejb queries return different results",
comparer.compareObjects(secondResult, firstResult));
Assert.assertTrue(
"Find orders with different billed customers test failed",
comparer.compareObjects(expectedResult, firstResult));
}
public void testOrderByExpression() {
EntityManager em = createEntityManager();
ReadAllQuery raq = new ReadAllQuery(PhoneNumber.class, new ExpressionBuilder());
Expression whereClause =
raq.getExpressionBuilder().get("owner").get("address").get("province").equal("ONT");
raq.setSelectionCriteria(whereClause);
raq.addOrdering(raq.getExpressionBuilder().get("areaCode"));
raq.addOrdering(raq.getExpressionBuilder().get("type"));
List expectedResult = (List) getServerSession().executeQuery(raq);
String ejbqlString =
"SELECT p FROM Employee e JOIN e.phoneNumbers p JOIN e.address a WHERE a.province = 'ONT' "
+ "ORDER BY p.areaCode, p.type";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertEquals("OrderBy expression test failed: data validation error", result.size(), 13);
Assert.assertTrue(
"OrderBy expression test failed", comparer.compareObjects(expectedResult, result));
}
public void findAllEmployeesWithOutPhoneNumbers() {
EntityManager em = createEntityManager();
ExpressionBuilder builder = new ExpressionBuilder();
Expression whereClause = builder.isEmpty("phoneNumbers");
ReadAllQuery raq = new ReadAllQuery(Employee.class);
raq.setSelectionCriteria(whereClause);
raq.useDistinct();
List expectedResult = (List) getServerSession().executeQuery(raq);
String ejbqlString = "SELECT DISTINCT e FROM Employee e WHERE e.phoneNumbers IS EMPTY";
List result = em.createQuery(ejbqlString).getResultList();
// 1 employee w/o phone number returned
Assert.assertEquals(
"Find all employees WITHOUT phone numbers test failed: data validation error",
result.size(),
1);
Assert.assertTrue(
"Find all employees WITHOUT phone numbers test failed",
comparer.compareObjects(expectedResult, result));
}
