Expression in WHERE Clause
The most common type of WHERE clause expression is a SearchCondition. However, other expressions can also be used. The following APIs support expressions in the WHERE clause:
appendWhere(WhereExpression a_expression, int[] a_fromIndicies)
appendWhere(WhereExpression a_expression, TableExpression[] a_tableExpressions, String[] a_aliases)
The following are concrete WhereExpression implementations:
|
SearchCondition
|
This class represents a search condition on a query. When appended to a QuerySpec, the values will be used in the SQL WHERE clause.
|
|
ExistsExpression
|
This class represents an EXISTS expression in a WHERE clause. A StatementSpec instance is used for the subselect.
|
|
CompositeWhereExpression
|
This class represents a number of WHERE expressions connected using a logical operator (i.e. AND/OR).
|
|
NegatedExpression
|
This class represents a negation of an expression in a WHERE clause. This class contains an aggregated WhereExpression that is preceded with a NOT when this expression is evaluated.
|
The fromIndices parameter is used to associate the WHERE expression operands with tables in the FROM clause. Similar to the appendSelect() method, the fromIndices array is based on the types of WhereExpression and ColumnExpressions used in those WhereExpressions. For example, a SearchCondition with a ClassAttribute and a ConstantExpression would require a single from index. A CompositeWhereExpression containing three SearchConditions would require fromIndices array with size equal to the sum of the size needed for each SearchCondition.
The following example demonstrates the proper usage of the fromIndices. This code queries for parts and their associated alternate parts. A composite where expression is used with several criteria: the second through fourth characters of the associated part numbers are equivalent, the part name begins with "E", or the alternate part name begins with "E". This first section of code sets up the classes in the query, the select items, and the joins between the classes.
QuerySpec qs = new QuerySpec();
int partIndex = qs.appendClassList(wt.part.WTPartMaster.class, false);
int alternatePartIndex = qs.appendClassList(wt.part.WTPartMaster.class, false);
int linkIndex = qs.appendClassList(wt.part.WTPartAlternateLink.class, false);
// Define the attributes in the query
ClassAttribute partName =
new ClassAttribute(wt.part.WTPartMaster.class,
wt.part.WTPartMaster.NAME);
ClassAttribute alternatePartName =
new ClassAttribute(wt.part.WTPartMaster.class,
wt.part.WTPartMaster.NAME);
ClassAttribute partNumber =
new ClassAttribute(wt.part.WTPartMaster.class,
wt.part.WTPartMaster.NUMBER);
ClassAttribute alternatePartNumber =
new ClassAttribute(wt.part.WTPartMaster.class,
wt.part.WTPartMaster.NUMBER);
// Define constants used in the criteria
ConstantExpression subStringStart = new ConstantExpression(new Long(2));
ConstantExpression subStringEnd = new ConstantExpression(new Long(4));
ConstantExpression wildcardExpression = new ConstantExpression("E% [ ]");
// Add items to the select and join the classes
qs.appendSelect(partName, new int[] { 0 }, false);
qs.appendSelect(alternatePartName, new int[] { 1 }, false);
qs.appendJoin(linkIndex, wt.part.WTPartAlternateLink.ALTERNATES_ROLE, partIndex);
qs.appendJoin(linkIndex, wt.part.WTPartAlternateLink.ALTERNATE_FOR_ROLE,
alternatePartIndex);
In this next section, the criteria are constructed and appended to the query. Note that the first SearchCondition uses two ClassAttribute instances. The corresponding indices must be added to the fromIndices array that is used in the appendWhere. Likewise, the second SearchCondition references the part class and the third SearchCondition references the alternate part class. Therefore, four fromIndices are required and each array element must correspond to the appropriate SearchCondition.
CompositeWhereExpression orExpression =
new CompositeWhereExpression(LogicalOperator.OR);
orExpression.append(new SearchCondition(
SQLFunction.newSQLFunction(SQLFunction.SUB_STRING,
partNumber, subStringStart, subStringEnd),
SearchCondition.EQUAL,
SQLFunction.newSQLFunction(SQLFunction.SUB_STRING,
alternatePartNumber, subStringStart, subStringEnd)));
orExpression.append(new SearchCondition(
partName, SearchCondition.LIKE, wildcardExpression));
orExpression.append(new SearchCondition(
alternatePartName, SearchCondition.LIKE, wildcardExpression));
qs.appendWhere(orExpression, new int[] {
partIndex, alternatePartIndex, partIndex, alternatePartIndex });
The last API explicitly specifies table expressions and aliases for the WHERE expression operands. This API is used for correlated subselects. When using subselects, it is common to use correlated columns (that is, a join between a column in the outer select and a column in the subselect). This is supported using the appendWhere() API in which TableExpressions and aliases are passed explicitly. For WhereExpressions that do not involve a subselect, the TableExpressions and aliases are derived implicitly using the QuerySpec FROM clause and the specified indices.
The following example builds a query using an EXISTS clause and a correlated subselect. The query will return all PartMasters for which an alternate PartMaster does not exist. An alternate is represented by the WTPartAlternateLink class, which is a many-to-many association between PartMasters. The role A of the WTPartAlternateLink class specifies the current PartMaster and the role B specifies the alternate PartMaster. Following is the SQL for this query:
SELECT A0.*
FROM WTPartMaster A0
WHERE NOT (EXISTS (SELECT B0.ida2a2
FROM WTPartAlternateLink B0
WHERE (A0.ida2a2 = B0.ida3a5)))
The following code constructs the query specification. The outer select will return PartMaster objects.
QuerySpec select = new QuerySpec();
int partIndex = select.appendClassList(wt.part.WTPartMaster.class, true);
The following code constructs the subselect. The alias prefix is changed to avoid conflicts with the outer select.
QuerySpec subSelect = new QuerySpec();
subSelect.getFromClause().setAliasPrefix("B");
int altIndex = subSelect.appendClassList(wt.part.WTPartAlternateLink.class,
false);
subSelect.appendSelect(new ClassAttribute(
wt.part.WTPartAlternateLink.class, WTAttributeNameIfc.ID_NAME),
new int[] { altIndex }, true);
The following code explicitly sets up the TableExpressions and aliases, which are passed as arrays. The join will be from the outer select to the subselect so the outer select values are placed in the arrays at index 0 and the subselect values are placed in the array at index 1. The arrays are then used to append the SearchCondition.
TableExpression[] tables = new TableExpression[2];
String[] aliases = new String[2];
tables[0] = select.getFromClause().getTableExpressionAt(partIndex);
aliases[0] = select.getFromClause().getAliasAt(partIndex);
tables[1] = subSelect.getFromClause().getTableExpressionAt(altIndex);
aliases[1] = subSelect.getFromClause().getAliasAt(altIndex);
SearchCondition correlatedJoin = new SearchCondition(
wt.part.WTPartMaster.class, WTAttributeNameIfc.ID_NAME,
wt.part.WTPartAlternateLink.class,WTAttributeNameIfc.ROLEA_OBJECT_ID);
subSelect.appendWhere(correlatedJoin, tables, aliases);
Finally, the negated EXISTS clause is appended to the outer select.
select.appendWhere(new NegatedExpression(new
ExistsExpression(subSelect)), null);