protected Expression processPropertyTracedownTooneOptimized(
Attribute<?, ?> attr, Expression builder, List<Parameter> parameters) {
ConditionQuery subQuery = getTraceDownQuery();
Expression finalExp = null;
for (PropertyCondition subPc : subQuery.getConditions()) {
if (subPc.isReturnSimpleExpression()) {
Expression subExp = subPc.processProperty(builder.get(getProp()), parameters);
if (finalExp == null) {
finalExp = subExp;
} else {
finalExp = finalExp.and(subExp);
}
} else {
// low-performance query since an extra layer of exists added
// but this is the only way to make it work.
// this situation is rare. So we may do not need to optimize it
Expression subBuilder = new ExpressionBuilder();
Expression subExp = subPc.processProperty(subBuilder, parameters);
ReportQuery childQuery = formRelationReportQuery(attr, subBuilder);
childQuery.retrievePrimaryKeys();
childQuery.setSelectionCriteria(subBuilder.equal(builder.get(getProp())).and(subExp));
if (finalExp == null) {
finalExp = builder.exists(childQuery);
} else {
finalExp = finalExp.and(builder.exists(childQuery));
}
}
}
return finalExp;
}
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));
}
/**
* INTERNAL: this returns a single expression to represent the join from the main table to all
* child descriptor tables Only if outer joins should be printed in the where clause
*
* @return Expression
*/
public Expression getTreatCriteria() {
if (getDescriptor() == null) {
return null;
}
// need to build this using just the multiple tables on this descriptor not included in the
// parent's join expression
Expression criteria = null;
if (getSession().getPlatform().shouldPrintOuterJoinInWhereClause()) {
Vector tables = getDescriptor().getTables(); // This child's tables
ClassDescriptor parentDescriptor = this.typeExpressionBase.getDescriptor();
int tablesSize = tables.size();
if (parentDescriptor.hasInheritance()
&& parentDescriptor.getInheritancePolicy().hasMultipleTableChild()) {
// look up the joins from the parent descriptor to our tables.
for (int i = 0; i < tablesSize; i++) {
DatabaseTable table = (DatabaseTable) tables.elementAt(i);
Expression joinExpression =
parentDescriptor.getInheritancePolicy().getChildrenTablesJoinExpressions().get(table);
// Some of our tables might be the in our parent as well, so ignore the lack of a
// joinExpression
if (joinExpression != null) {
joinExpression = this.baseExpression.twist(joinExpression, this);
if (shouldUseOuterJoin()) {
joinExpression = joinExpression.convertToUseOuterJoin();
}
criteria = joinExpression.and(criteria);
}
}
}
}
return criteria;
}
/**
* INTERNAL: Return the expression to join the main table of this node to any auxiliary tables.
*/
public Expression additionalExpressionCriteria() {
if (getDescriptor() == null) {
return null;
}
Expression criteria = getDescriptor().getQueryManager().getAdditionalJoinExpression();
if (criteria != null) {
criteria = getBaseExpression().twist(criteria, this);
if (shouldUseOuterJoin() && getSession().getPlatform().shouldPrintOuterJoinInWhereClause()) {
criteria.convertToUseOuterJoin();
}
}
if (getSession().getPlatform().shouldPrintOuterJoinInWhereClause()) {
if (isUsingOuterJoinForMultitableInheritance()) {
Expression childrenCriteria =
getDescriptor().getInheritancePolicy().getChildrenJoinExpression();
childrenCriteria = getBaseExpression().twist(childrenCriteria, this);
childrenCriteria.convertToUseOuterJoin();
if (criteria == null) {
criteria = childrenCriteria;
} else {
criteria = criteria.and(childrenCriteria);
}
}
}
if ((getDescriptor() != null) && (getDescriptor().getHistoryPolicy() != null)) {
Expression historyCriteria =
getDescriptor().getHistoryPolicy().additionalHistoryExpression(this);
if (criteria != null) {
criteria = criteria.and(historyCriteria);
} else {
criteria = historyCriteria;
}
}
return criteria;
}
public void setup() {
super.setup();
ExpressionBuilder employees = new ExpressionBuilder();
expression = employees.get("firstName").equal("Charles");
expression = expression.and(employees.get("lastName").equal("Chanley"));
// ensure our employee is in one of the distributed caches
DistributedServer server =
(DistributedServer) DistributedServersModel.getDistributedServers().get(0);
Object result = server.getDistributedSession().readObject(Employee.class, expression);
((Employee) result).getManagedEmployees();
((Employee) result).getPhoneNumbers();
((Employee) result).getAddress();
((Employee) result).getManager();
((Employee) result).getProjects();
((Employee) result).getResponsibilitiesList();
}
public void testCountExpression() {
EntityManager em = createEntityManager();
ReportQuery query = new ReportQuery(Employee.class, new ExpressionBuilder());
Expression whereClause1 =
query.getExpressionBuilder().get("address").get("province").equal("QUE");
Expression whereClause2 =
query.getExpressionBuilder().get("address").get("city").equal("Montreal");
query.setSelectionCriteria(whereClause1.and(whereClause2));
query.addCount(
"areaCodeCount",
query.getExpressionBuilder().anyOf("phoneNumbers").get("areaCode"),
Long.class);
query.returnSingleResult();
Long expectedResult =
(Long) ((ReportQueryResult) getServerSession().executeQuery(query)).get("areaCodeCount");
String ejbqlString =
"SELECT COUNT(p.areaCode) FROM Employee e JOIN e.phoneNumbers p JOIN e.address a "
+ " WHERE a.province='QUE' AND a.city='Montreal'";
Long result = (Long) em.createQuery(ejbqlString).getSingleResult();
String alternateEjbqlString =
"SELECT COUNT(p) FROM Employee e JOIN e.phoneNumbers p JOIN e.address a "
+ " WHERE a.province='QUE' AND a.city='Montreal' AND p.areaCode IS NOT NULL";
Long alternateResult = (Long) em.createQuery(alternateEjbqlString).getSingleResult();
Assert.assertTrue(
"Count expression test failed: data validation error, ReportQuery returned 0",
expectedResult.intValue() > 0);
Assert.assertTrue(
"Count expression test failed: data validation error, first JPQL returned 0",
result.intValue() > 0);
Assert.assertTrue(
"Count expression test failed: data validation error, second JPQL returned 0",
alternateResult.intValue() > 0);
Assert.assertTrue(
"Count expression test failed: two equivalent ejb queries return different results",
alternateResult.equals(result));
Assert.assertEquals("Count expression test failed", expectedResult, result);
}
protected Expression processPropertyTracedownToManyNone(
Attribute<?, ?> attr, Expression builder, List<Parameter> parameters) {
ConditionQuery subQuery = getTraceDownQuery();
// subQuery.setPrefix(getQuery().getPrefix()+parameters.size()+"_");
Expression subBuilder = new ExpressionBuilder();
List<Expression> childExps = new ArrayList<Expression>(3);
for (PropertyCondition subPc : subQuery.getConditions()) {
Expression subExp = subPc.processProperty(subBuilder, parameters);
childExps.add(subExp);
}
if (childExps.isEmpty()) {
throw new InvalidQueryException("No expression from subquery.");
}
Expression finalExp = childExps.get(0);
for (int i = 1; i < childExps.size(); i++) {
finalExp = finalExp.and(childExps.get(i));
}
return builder.noneOf(getProp(), finalExp);
}
/**
* INTERNAL: Selection criteria is created with source foreign keys and target keys. This criteria
* is then used to read target records from the table.
*/
public void initializeSelectionCriteria(AbstractSession session) {
Expression selectionCriteria = null;
Expression expression;
ExpressionBuilder expBuilder = new ExpressionBuilder();
Iterator sourceKeysEnum = getSourceToTargetQueryKeyNames().keySet().iterator();
while (sourceKeysEnum.hasNext()) {
DatabaseField sourceKey = (DatabaseField) sourceKeysEnum.next();
String target = (String) this.getSourceToTargetQueryKeyNames().get(sourceKey);
expression = expBuilder.getParameter(sourceKey).equal(expBuilder.get(target));
if (selectionCriteria == null) {
selectionCriteria = expression;
} else {
selectionCriteria = expression.and(selectionCriteria);
}
}
setSelectionCriteria(selectionCriteria);
}
protected Expression processPropertyTracedownToManySomeOptimized(
Attribute<?, ?> attr, Expression builder, List<Parameter> parameters) {
ConditionQuery subQuery = getTraceDownQuery();
// subQuery.setPrefix(getQuery().getPrefix()+parameters.size()+"_");
Expression prefixExp = builder.anyOf(getProp());
List<Expression> complexExps = new ArrayList<Expression>();
for (PropertyCondition subPc : subQuery.getConditions()) {
Expression subExp = subPc.processProperty(prefixExp, parameters);
complexExps.add(subExp);
}
Expression finalExp = null;
if (complexExps.isEmpty()) {
throw new InvalidQueryException("No expression from subquery.");
}
finalExp = complexExps.get(0);
for (int i = 1; i < complexExps.size(); i++) {
finalExp = finalExp.and(complexExps.get(i));
}
return finalExp;
}
public void testComplexDeleteExpression() {
if (isOnServer()) {
// Not work on server.
return;
}
if ((JUnitTestCase.getServerSession()).getPlatform().isSymfoware()) {
getServerSession()
.logMessage(
"Test testComplexDeleteExpression skipped for this platform, "
+ "Symfoware doesn't support UpdateAll/DeleteAll on multi-table objects (see rfe 298193).");
return;
}
JpaEntityManager em = (org.eclipse.persistence.jpa.JpaEntityManager) createEntityManager();
try {
beginTransaction(em);
String orderString = "DELETE FROM OrderBean o WHERE o.customer.name ='Karen McDonald' ";
em.createQuery(orderString).executeUpdate();
orderString = "DELETE FROM OrderBean o WHERE o.billedCustomer.name ='Karen McDonald' ";
em.createQuery(orderString).executeUpdate();
String ejbqlString =
"DELETE FROM Customer c WHERE c.name='Karen McDonald' AND c.orders IS EMPTY";
int result = em.createQuery(ejbqlString).executeUpdate();
Assert.assertEquals(
"Complex Delete Expression test failed: customer to delete not found", 1, result);
em.flush();
ReadAllQuery raq = new ReadAllQuery(Customer.class, new ExpressionBuilder());
Expression whereClause1 = raq.getExpressionBuilder().get("name").equal("Karen McDonald");
Expression whereClause2 = raq.getExpressionBuilder().isEmpty("orders");
raq.setSelectionCriteria(whereClause1.and(whereClause2));
List customerFound = (List) em.getActiveSession().executeQuery(raq);
Assert.assertEquals("Complex Delete Expression test failed", 0, customerFound.size());
} finally {
rollbackTransaction(em);
}
}
/**
* INTERNAL: For CR#2456 if this is part of an objExp.equal(objExp), do not need to add additional
* expressions to normalizer both times, and the foreign key join replaces the equal expression.
*/
public Expression normalize(ExpressionNormalizer normalizer, Vector foreignKeyJoinPointer) {
if (hasBeenNormalized()) {
return this;
}
super.normalize(normalizer);
setHasBeenNormalized(true);
if ((getMapping() != null) && getMapping().isDirectToXMLTypeMapping()) {
normalizer.getStatement().setRequiresAliases(true);
}
// Check if any joins need to be added.
if (isAttribute()) {
return this;
}
// If the mapping is 'ref' or 'structure', no join needed.
if ((getMapping() != null)
&& (getMapping().isReferenceMapping() || getMapping().isStructureMapping())) {
normalizer.getStatement().setRequiresAliases(true);
return this;
}
// Compute if a distinct is required during normalization.
if (shouldQueryToManyRelationship()
&& (!normalizer.getStatement().isDistinctComputed())
&& (!normalizer.getStatement().isAggregateSelect())) {
normalizer.getStatement().useDistinct();
}
// Turn off DISTINCT if nestedTableMapping is used (not supported by Oracle 8.1.5).
if ((getMapping() != null) && getMapping().isNestedTableMapping()) {
// There are two types of nested tables, one used by clients, one used by mappings, do nothing
// in the mapping case.
if (!shouldQueryToManyRelationship()) {
return this;
}
normalizer.getStatement().dontUseDistinct();
}
Expression mappingExpression = mappingCriteria();
if (mappingExpression != null) {
mappingExpression = mappingExpression.normalize(normalizer);
}
if (mappingExpression != null) {
// If the join was an outer join we must not add the join criteria to the where clause,
// if the platform prints the join in the from clause.
if (shouldUseOuterJoin() && (getSession().getPlatform().isInformixOuterJoin())) {
normalizer.getStatement().getOuterJoinExpressions().addElement(this);
normalizer.getStatement().getOuterJoinedMappingCriteria().addElement(mappingExpression);
normalizer.addAdditionalExpression(mappingExpression.and(additionalExpressionCriteria()));
return this;
} else if ((shouldUseOuterJoin() || isUsingOuterJoinForMultitableInheritance())
&& (!getSession().getPlatform().shouldPrintOuterJoinInWhereClause())) {
if (shouldUseOuterJoin()) {
normalizer.getStatement().getOuterJoinExpressions().addElement(this);
normalizer.getStatement().getOuterJoinedMappingCriteria().addElement(mappingExpression);
normalizer
.getStatement()
.getOuterJoinedAdditionalJoinCriteria()
.addElement(additionalExpressionCriteriaMap());
normalizer.getStatement().getDescriptorsForMultitableInheritanceOnly().add(null);
return this;
} else {
if (isUsingOuterJoinForMultitableInheritance()) {
normalizer.getStatement().getOuterJoinExpressions().addElement(null);
normalizer.getStatement().getOuterJoinedMappingCriteria().addElement(null);
normalizer
.getStatement()
.getOuterJoinedAdditionalJoinCriteria()
.addElement(additionalExpressionCriteriaMap());
normalizer
.getStatement()
.getDescriptorsForMultitableInheritanceOnly()
.add(getMapping().getReferenceDescriptor());
// fall through to the main case
}
}
}
// This must be added even if outer. Actually it should be converted to use a right outer
// join, but that gets complex
// so we do not support this current which is a limitation in some cases.
if (foreignKeyJoinPointer != null) {
// If this expression is right side of an objExp.equal(objExp), one
// need not add additionalExpressionCriteria twice.
// Also the join will replace the original objExp.equal(objExp).
// For CR#2456.
foreignKeyJoinPointer.add(mappingExpression);
} else {
normalizer.addAdditionalExpression(mappingExpression.and(additionalExpressionCriteria()));
}
}
// For bug 2900974 special code for DirectCollectionMappings moved to printSQL.
return this;
}
public void testGroupByHavingExpression() {
EntityManager em = createEntityManager();
boolean testPass = true;
ReadAllQuery raq = new ReadAllQuery(Employee.class, new ExpressionBuilder());
Expression whereClause1 = raq.getExpressionBuilder().get("firstName").equal("Bob");
Expression whereClause2 = raq.getExpressionBuilder().get("lastName").equal("Smith");
Expression whereClause3 = raq.getExpressionBuilder().get("firstName").equal("John");
Expression whereClause4 = raq.getExpressionBuilder().get("lastName").equal("Way");
raq.setSelectionCriteria((whereClause1.and(whereClause2)).or(whereClause3.and(whereClause4)));
List employees = (List) getServerSession().executeQuery(raq);
int firstManagerId = ((Employee) employees.get(0)).getId();
int secondManagerId = ((Employee) employees.get(1)).getId();
int expectedEmployeesManaged =
((Employee) employees.get(0)).getManagedEmployees().size()
+ ((Employee) employees.get(1)).getManagedEmployees().size();
Vector managerVector = new Vector();
managerVector.add(firstManagerId);
managerVector.add(secondManagerId);
ReportQuery query = new ReportQuery(Employee.class, new ExpressionBuilder());
query.returnWithoutReportQueryResult();
query.addGrouping(query.getExpressionBuilder().get("manager").get("id"));
query.setHavingExpression(
query.getExpressionBuilder().get("manager").get("id").in(managerVector));
query.addAttribute("managerId", query.getExpressionBuilder().get("manager").get("id"));
query.addAverage("salary", Double.class);
query.addCount("id", Long.class);
List expectedResult = (List) getServerSession().executeQuery(query);
String ejbqlString =
"SELECT e.manager.id, avg(e.salary), count(e) FROM Employee e"
+ " GROUP BY e.manager.id HAVING e.manager.id IN ("
+ firstManagerId
+ ","
+ secondManagerId
+ ")";
List result = em.createQuery(ejbqlString).getResultList();
int employeesManaged = 0;
Iterator expectedResultIterator = expectedResult.iterator();
Iterator resultIterator = result.iterator();
if (expectedResult.size() == result.size()) {
while (resultIterator.hasNext()) {
Object objectArray[] = (Object[]) expectedResultIterator.next();
Object otherObjectArray[] = (Object[]) resultIterator.next();
testPass = testPass && objectArray[0].equals(otherObjectArray[0]);
testPass = testPass && objectArray[1].equals(otherObjectArray[1]);
testPass = testPass && objectArray[2].equals(otherObjectArray[2]);
employeesManaged = ((Long) objectArray[2]).intValue() + employeesManaged;
}
} else {
testPass = false;
}
Assert.assertEquals(
"GroupBy Having expression test failed: data validation error",
employeesManaged,
expectedEmployeesManaged);
Assert.assertTrue("GroupBy Having expression test failed", testPass);
}
public Expression normalize(
ExpressionNormalizer normalizer, Expression base, List<Expression> foreignKeyJoinPointer) {
// need to determine what type this is, as it may need to change the expression its based off
// slightly
if (this.hasBeenNormalized) {
return this;
}
this.hasBeenNormalized = true;
Expression typeExpression = getTypeClause();
typeExpression.normalize(normalizer);
if (this.baseExpression != null) { // should never be null
// First normalize the base.
setBaseExpression(this.baseExpression.normalize(normalizer));
if (getAsOfClause() == null) {
asOf(this.baseExpression.getAsOfClause());
}
}
// This class has no validation but we should still make the method call for consistency
// bug # 2956674
// validation is moved into normalize to ensure that expressions are valid before we attempt to
// work with them
validateNode();
// the following is based on QueryKey.normalize
SQLSelectStatement statement = normalizer.getStatement();
// no longer directly normalize the typeExpressionBase, or find a way to use it
this.typeExpressionBase = (ObjectExpression) this.typeExpressionBase.normalize(normalizer);
// Normalize the ON clause if present. Need to use rebuild, not twist as parameters are real
// parameters.
if (this.onClause != null) { // not sure this is needed/valid
this.onClause = this.onClause.normalize(normalizer);
}
ClassDescriptor parentDescriptor = this.typeExpressionBase.getDescriptor();
boolean isSTI = getOwnedSubTables().isEmpty();
// only really valid if it has inheritance, but better this code than skipping it into the joins
if (isSTI) {
if (foreignKeyJoinPointer != null) {
// If this expression is right side of an objExp.equal(objExp), one
// need not add additionalExpressionCriteria twice.
// Also the join will replace the original objExp.equal(objExp).
// For CR#2456.
foreignKeyJoinPointer.add(typeExpression.and(this.onClause));
} else {
// this just and's in the entire expression to the normalizer's expression.
// Need to use this for TYPE and none-outerjoin components
normalizer.addAdditionalLocalExpression(typeExpression.and(this.onClause));
}
return this;
}
// if shouldPrintOuterJoinInWhereClause is true, this is this child's tables joined together in
// one expression
Expression treatJoinTableExpressions = getTreatCriteria();
boolean parentUsingOuterJoinForMultitableInheritance =
typeExpressionBase.isUsingOuterJoinForMultitableInheritance();
if (treatJoinTableExpressions != null) {
treatJoinTableExpressions = treatJoinTableExpressions.normalize(normalizer);
}
Integer postition = typeExpressionBase.getOuterJoinExpIndex();
if (postition != null) {
if (parentUsingOuterJoinForMultitableInheritance) {
// outer join was done, so our class' tables would have been included
return this;
}
if (getSession().getPlatform().isInformixOuterJoin()) {
normalizer.addAdditionalLocalExpression(
typeExpression.and(additionalTreatExpressionCriteria()).and(this.onClause));
return this;
} else if (((!getSession().getPlatform().shouldPrintOuterJoinInWhereClause()))
|| (!getSession().getPlatform().shouldPrintInnerJoinInWhereClause())) {
// Adds the left joins from treat to the base QKE joins.
Map<DatabaseTable, Expression> map =
statement
.getOuterJoinExpressionsHolders()
.get(postition)
.outerJoinedAdditionalJoinCriteria;
if (map != null) {
map.putAll(additionalTreatExpressionCriteriaMap());
} else {
statement
.getOuterJoinExpressionsHolders()
.get(postition)
.outerJoinedAdditionalJoinCriteria =
additionalTreatExpressionCriteriaMap();
}
return this;
}
} else if (!getSession().getPlatform().shouldPrintOuterJoinInWhereClause()
|| (!getSession().getPlatform().shouldPrintInnerJoinInWhereClause())) {
// the base is not using an outer join, so we add a new one for this class' tables.
Map additionalExpMap = additionalTreatExpressionCriteriaMap();
if (additionalExpMap != null && !additionalExpMap.isEmpty()) {
statement.addOuterJoinExpressionsHolders(additionalExpMap, parentDescriptor);
}
}
typeExpression = typeExpression.normalize(normalizer);
if (foreignKeyJoinPointer != null) {
// If this expression is right side of an objExp.equal(objExp), one
// need not add additionalExpressionCriteria twice.
// Also the join will replace the original objExp.equal(objExp).
// For CR#2456.
foreignKeyJoinPointer.add(typeExpression.and(this.onClause));
} else {
// this just and's in the entire expression to the normalizer's expression. Need to use this
// for TYPE and non-outerjoin components
normalizer.addAdditionalLocalExpression(
typeExpression.and(additionalTreatExpressionCriteria()).and(this.onClause));
}
return this;
}
