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));
}
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));
}
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 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));
}
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));
}
