/*
* 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));
}
// FueledVehicles owned by Companies that also own NonFueledVehicles
public static void test_ownerOwnsNonFueledVehicle() {
ExpressionBuilder builder = new ExpressionBuilder();
ExpressionBuilder subBuilder = new ExpressionBuilder();
ReportQuery rq = new ReportQuery(NonFueledVehicle.class, subBuilder);
rq.addAttribute("id");
Expression subExpression = subBuilder.get("owner").equal(builder.get("owner"));
rq.setSelectionCriteria(subExpression);
Expression exp = builder.exists(rq);
deleteAllQueryInternal_Deferred_Children(FueledVehicle.class, exp);
}
public void complexDistinctCountTest() {
oracle.toplink.essentials.ejb.cmp3.EntityManager em =
(oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ExpressionBuilder expbldr = new ExpressionBuilder();
ReportQuery rq = new ReportQuery(Employee.class, expbldr);
Expression exp = expbldr.get("lastName").equal("Smith");
rq.setReferenceClass(Employee.class);
rq.setSelectionCriteria(exp);
rq.useDistinct();
rq.returnSingleAttribute();
rq.dontRetrievePrimaryKeys();
rq.addCount("COUNT", expbldr.get("lastName").distinct(), Long.class);
Vector expectedResultVector = (Vector) em.getActiveSession().executeQuery(rq);
Long expectedResult = (Long) expectedResultVector.get(0);
String ejbqlString =
"SELECT COUNT(DISTINCT emp.lastName) FROM Employee emp WHERE emp.lastName = \"Smith\"";
Long result = (Long) em.createQuery(ejbqlString).getSingleResult();
Assert.assertEquals("Complex DISTINCT COUNT test failed", expectedResult, result);
}
public void complexSumTest() {
oracle.toplink.essentials.ejb.cmp3.EntityManager em =
(oracle.toplink.essentials.ejb.cmp3.EntityManager) createEntityManager();
ExpressionBuilder expbldr = new ExpressionBuilder();
ReportQuery rq = new ReportQuery(Employee.class, expbldr);
rq.setReferenceClass(Employee.class);
rq.returnSingleAttribute();
rq.dontRetrievePrimaryKeys();
rq.addAttribute("salary", expbldr.get("salary").distinct().sum(), Long.class);
Vector expectedResultVector = (Vector) em.getActiveSession().executeQuery(rq);
Long expectedResult = (Long) expectedResultVector.get(0);
String ejbqlString = "SELECT SUM(DISTINCT emp.salary) FROM Employee emp";
Long result = (Long) em.createQuery(ejbqlString).getSingleResult();
Assert.assertEquals("Complex SUMtest failed", expectedResult, result);
}
{
reportQueryVehicles = new ReportQuery(Vehicle.class, new ExpressionBuilder());
reportQueryVehicles.setShouldRetrievePrimaryKeys(true);
reportQueryCompanies = new ReportQuery(Company.class, new ExpressionBuilder());
reportQueryCompanies.setShouldRetrievePrimaryKeys(true);
}
protected static String execute(
Session mainSession, UpdateAllQuery uq, boolean handleChildren, Class rootClass) {
String errorMsg = "";
ClassDescriptor descriptor = mainSession.getDescriptor(uq.getReferenceClass());
clearCache(mainSession);
// original objects
Vector objects = mainSession.readAllObjects(rootClass);
// first update using the original TopLink approach - one by one.
// That will be done using report query - it will use the same selection criteria
// as UpdateAllQuery and each attribute will correspond to an update item.
ReportQuery rq = new ReportQuery(uq.getReferenceClass(), uq.getExpressionBuilder());
rq.setSelectionCriteria(uq.getSelectionCriteria());
rq.setShouldRetrievePrimaryKeys(true);
// some db platforms don't allow nulls in select clause - so add the fields with null values to
// the query result.
Vector fieldsWithNullValues = new Vector();
Iterator itEntrySets = uq.getUpdateClauses().entrySet().iterator();
while (itEntrySets.hasNext()) {
Map.Entry entry = (Map.Entry) itEntrySets.next();
Expression valueExpression;
String keyString =
getQualifiedFieldNameFromKey(
entry.getKey(), rq.getReferenceClass(), descriptor, mainSession);
Object value = entry.getValue();
DatabaseMapping mapping =
descriptor.getObjectBuilder().getMappingForField(new DatabaseField(keyString));
if (mapping != null && mapping.isOneToOneMapping() && value != null) {
// Note that this only works in case the reference PK is not compound
if (((OneToOneMapping) mapping).getSourceToTargetKeyFields().size() > 1) {
errorMsg =
"Attribute "
+ mapping.getAttributeName()
+ " mapped with 1to1 mapping that has more than one targetKeyField. UpdateAllQueryTestHelper currently doesn't support that.";
}
DatabaseField targetField =
(DatabaseField)
((OneToOneMapping) mapping)
.getSourceToTargetKeyFields()
.get(new DatabaseField(keyString));
if (value instanceof Expression) {
valueExpression = ((Expression) (((Expression) value).clone())).getField(targetField);
} else {
ClassDescriptor targetDescriptor = ((OneToOneMapping) mapping).getReferenceDescriptor();
Object fieldValue =
targetDescriptor
.getObjectBuilder()
.extractValueFromObjectForField(
value,
targetField,
(oracle.toplink.essentials.internal.sessions.AbstractSession) mainSession);
valueExpression = rq.getExpressionBuilder().value(fieldValue);
}
} else {
if (value instanceof Expression) {
valueExpression = (Expression) value;
} else {
valueExpression = rq.getExpressionBuilder().value(value);
}
}
if (value == null) {
fieldsWithNullValues.add(keyString);
} else {
rq.addAttribute(keyString, valueExpression);
}
}
UnitOfWork uow = mainSession.acquireUnitOfWork();
// mainSession could be a ServerSession
Session session = uow.getParent();
// report query results contain the values to be assigned for each object to be updated.
Vector result = (Vector) session.executeQuery(rq);
Vector objectsAfterOneByOneUpdate = new Vector(objects.size());
((oracle.toplink.essentials.internal.sessions.AbstractSession) session).beginTransaction();
try {
for (int i = 0; i < result.size(); i++) {
// read through uow the object(clone) to be updated
ReportQueryResult reportResult = (ReportQueryResult) result.elementAt(i);
// hammer into the object the updated values
Object obj = reportResult.readObject(rq.getReferenceClass(), uow);
DatabaseRecord row = new DatabaseRecord();
for (int j = 0; j < reportResult.getNames().size(); j++) {
String name = (String) reportResult.getNames().elementAt(j);
DatabaseField field = new DatabaseField(name);
Object value = reportResult.getResults().elementAt(j);
row.add(field, value);
}
// some db platforms don't allow nulls in select clause - so add the fields with null values
// to the query result
for (int j = 0; j < fieldsWithNullValues.size(); j++) {
String name = (String) fieldsWithNullValues.elementAt(j);
DatabaseField field = new DatabaseField(name);
row.add(field, null);
}
rq.getDescriptor().getObjectBuilder().assignReturnRow(obj, (AbstractSession) uow, row);
}
// uow committed - objects updated.
uow.commit();
// Because the transaction will be rolled back (to return to the original state to execute
// UpdateAllQuery)
// objects are copied into another vector - later it will be compared with UpdateAllQuery
// result.
for (int i = 0; i < objects.size(); i++) {
Object original = objects.elementAt(i);
Object copy = buildCopy(descriptor, original, uow);
objectsAfterOneByOneUpdate.add(copy);
}
} finally {
// transaction rolled back - objects back to the original state in the db.
((oracle.toplink.essentials.internal.sessions.AbstractSession) session).rollbackTransaction();
}
clearCache(mainSession);
// now use UpdateAllQuery
uow = mainSession.acquireUnitOfWork();
// mainSession could be a ServerSession
session = uow.getParent();
Vector objectsAfterUpdateAll = new Vector(objects.size());
((oracle.toplink.essentials.internal.sessions.AbstractSession) session).beginTransaction();
try {
uow.executeQuery(uq);
// uow committed - objects updated.
uow.commit();
// Because the transaction will be rolled back (to return to the original state)
// objects are copied into another vector - it will be compared with update one-by-one result.
for (int i = 0; i < objects.size(); i++) {
Object original = objects.elementAt(i);
Object copy = buildCopy(descriptor, original, uow);
objectsAfterUpdateAll.add(copy);
}
} finally {
// transaction rolled back - objects back to the original state in the db.
((oracle.toplink.essentials.internal.sessions.AbstractSession) session).rollbackTransaction();
}
clearCache(mainSession);
// verify
String classErrorMsg = "";
for (int i = 0; i < objects.size(); i++) {
Object obj = objects.elementAt(i);
Object obj1 = objectsAfterOneByOneUpdate.elementAt(i);
Object obj2 = objectsAfterUpdateAll.elementAt(i);
boolean equal =
rq.getDescriptor()
.getObjectBuilder()
.compareObjects(
obj, obj2, (oracle.toplink.essentials.internal.sessions.AbstractSession) session);
if (!equal) {
classErrorMsg =
classErrorMsg
+ "Difference: original = "
+ obj.toString()
+ "; afterOneByOneUpdate = "
+ obj1.toString()
+ "; afterUpdateAll = "
+ obj2.toString()
+ ";";
}
}
if (classErrorMsg.length() > 0) {
errorMsg = errorMsg + classErrorMsg;
}
if (handleChildren) {
if (descriptor.hasInheritance() && descriptor.getInheritancePolicy().hasChildren()) {
Iterator it = descriptor.getInheritancePolicy().getChildDescriptors().iterator();
while (it.hasNext()) {
ClassDescriptor childDescriptor = (ClassDescriptor) it.next();
Class childReferenceClass = childDescriptor.getJavaClass();
UpdateAllQuery childUq = (UpdateAllQuery) uq.clone();
childUq.setReferenceClass(childReferenceClass);
childUq.setIsPrepared(false);
errorMsg += execute(mainSession, childUq, handleChildren, rootClass);
}
}
}
return errorMsg;
}
/** 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));
}
