SearchCondition
A SearchCondition represents a SQL WHERE clause expression of the following form: <left side operand> <operator> <right side operand>
The following are examples:
MyTable.Column1 = 5
MyTable.Column2 LIKE "E%"
MyTable.Column3 = JoinTable.Column1
Operands can also be more complex, such as SQL functions or subselects. SearchCondition can use arbitrary RelationalExpression operands. The operands can be specified using the SearchCondition constructor or setter methods. The following are concrete ColumnExpression implementations:
|
ClassAttribute
|
This class represents a class attribute that can be used in a SQL statement. Introspection information is used to determine the associated table and column.
|
|
SQLFunction
|
This class represents a SQL function within a SQL statement.
|
|
SubSelectExpression
|
This class represents a subselect that can be used in a SQL statement. The subselect is specified via a StatementSpec attribute.
|
|
ConstantExpression
|
This class represents a constant in a SQL statement.
|
|
KeywordExpression
|
This class represents an expression that evaluates to a SQL keyword that can be used in a SQL statement.
|
|
RangeExpression
|
This class represents a range in a SQL WHERE clause.
|
|
DateExpression
|
This class represents a date constant in a SQL statement. This subclass of ConstantExpression is necessary to provide the special handling for date values.
|
|
ArrayExpression
|
This class represents an array of constants in a SQL IN clause.
|
|
TableColumn
|
This class represents a table column that can be used in a SQL statement. The exact table and column name specified are used directly in the SQL statement.
|
|
|
The following example builds a complex query to determine the WTPartMaster object with the oldest modify timestamp after a specified date cutoff. Following is the SQL for this query:
SELECT A0.*
FROM WTPartMaster A0
WHERE (A0.modifyStampA2 IN (SELECT MIN(B0.modifyStampA2)
FROM WTPartMaster B0
WHERE B0.modifyStampA2 > ’cutoff’) )
The following code constructs the query specification:
Class targetClass = wt.part.WTPartMaster.class;
QuerySpec subSelect = new QuerySpec();
subSelect.getFromClause().setAliasPrefix("B");
int subIndex = subSelect.appendClassList(targetClass, false);
int[] fromIndicies = { subIndex };
ClassAttribute subModifyStamp =
new ClassAttribute(targetClass,WTAttributeNameIfc.MODIFY_STAMP_NAME);
SQLFunction minFunction = SQLFunction.new SQLFunction(SQLFunction.
MINIMUM, subModifyStamp);
subSelect.appendSelect(minFunction, fromIndicies, false);
subSelect.appendWhere(new SearchCondition(subModifyStamp,
SearchCondition.GREATER_THAN, DateExpression.newExpression(cutoff)),
fromIndicies);
QuerySpec select = new QuerySpec();
int index = select.appendClassList(targetClass, true);
select.appendWhere(new SearchCondition(modifyStamp,SearchCondition.IN,
new SubSelectExpression(subSelect)), new int[] { index });
Compound Query
A compound query is a SQL statement that combines more than one component query into a single SQL statement via a set operator. Set operators include UNION, UNION ALL, INTERSECT, and MINUS. A compound query is composed by specifying a set operator and adding component queries. The component queries are StatementSpec objects so nesting of compound queries is also supported.
The following example builds a compound query to return a specific PartMaster number and the numbers of all of its alternates. Note that only numbers are selected, not full objects. This is necessary because, if all subclasses are considered, the compound query statement must include all subclass tables. These subclass tables may contain additional columns that would make the select list for each statement incompatible with other component statements. SQL requires that each component query in a compound statement must have the same number and corresponding type in the select list. Following is the SQL for this query:
SELECT A0.number
FROM WTPartMaster A0
WHERE (A0.name = ‘ENGINE')
UNION
SELECT A2.number
FROM WTPartMaster A0,WTPartAlternateLink A1,WTPartMaster A2
WHERE (A0.name = ‘ENGINE') AND
(A0.idA2A2 = A1.idA3A5) AND (A2.idA2A2 = A1.idA3B5)
The following code constructs the query specification. The first select constructed is for PartMasters with the name ENGINE.
QuerySpec partSelect = new QuerySpec();
int partIndex = partSelect.appendClassList(wt.part.WTPartMaster.class, false);
partSelect.appendWhere(new SearchCondition(wt.part.WTPartMaster.class,
WTPartMaster.NAME, SearchCondition.EQUAL, "ENGINE"), new int[]
{ partIndex });
The next select is constructed for returning PartMaster alternates. An alternate is represented by the WTPartAlternateLink class, which is a many-to-many association between PartMasters. A join must be specified across this association from the original part to its alternates.
QuerySpec altSelect = new QuerySpec();
partIndex = altSelect.appendClassList(wt.part.WTPartMaster.class, false);
int altIndex = altSelect.appendClassList(W wt.part.WTPartAlternateLink.class,
false);
int altPartIndex = altSelect.appendClassList(wt.part.WTPartMaster.class,
false);
altSelect.appendSelect(new ClassAttribute(
wt.part.WTPartMaster.class, wt.part.WTPartMaster.NUMBER),
new int[] { altPartIndex }, false);
altSelect.appendWhere(new
SearchCondition(wt.part.WTPartMaster.class,
WTPartMaster.NAME, SearchCondition.EQUAL, "ENGINE"), new int[]
{ partIndex });
altSelect.appendJoin(altIndex, wt.part.WTPartAlternateLink.ALTERNATES_ROLE,
partIndex);
altSelect.appendJoin(altIndex, wt.part.WTPartAlternateLink.ALTERNATE_FOR_ROLE,
altPartIndex);
Finally, the compound statement is constructed using the two previous queries and the UNION set operator.
CompoundQuerySpec compound = new CompoundQuerySpec();
compound.setSetOperator(SetOperator.UNION);
compound.addComponent(partSelect);
compound.addComponent(altSelect);
Access Control Consideration
The use of some advanced SQL APIs can bypass Access Control. These situations are detected and an AdvancedQueryAccessException will be thrown. The following advanced query uses will cause this exception:
• Sub-selects (wt.query.SubSelectExpression)
• MINUS or INTERSECT Compound Statements (wt.query.CompoundQuerySpec)
• External Tables (wt.query.ExternalTableExpression)
• Aggregate Functions (wt.query.SQLFunction)
AVERAGE
MAXIMUM
MINIMUM
SUM
COUNT
• ROWNUM keyword (wt.query.KeywordExpression)
This is done to ensure that Access Control is not bypassed unknowingly. In some cases, the use of these advanced SQL features that bypass Access Control is legitimate. For these cases, the advanced checking can be disabled at runtime. Query specification classes support an "advancedQueryEnabled" attribute that can only be set from server side code. If applicable, the attribute should be set to true on the query instance that is passed to the PersistenceManager query/find API to allow these queries to be executed without throwing an exception.
// Use advanced APIs to build query.
// Disable checking of advance features statement.setAdvancedQueryEnabled(true);
// Execute query with access control
PersistenceHelper.manager.find(statement);
The find() method executes the statement with access control. Therefore, Access Control related columns may be implicitly added to the select. For some advanced features, such as aggregate functions, INTERSECT, and MINUS, the addition of these columns can affect the expected results or cause a SQL exception. In these cases, to successfully execute the query, the server side, non-access controlled query() method should be used.
PersistenceServerHelper.manager.query(statement);
Sorting
Queries can be used to sort the result data at the database level. However, in general, database sorting should only be applied to paging queries and queries that involve only ColumnExpressions. Other types of queries may be implemented as several separate SQL statements so the sorting is only applied to the individual statements and not the complete query. Any ColumnExpression can be used as a sort column. The OrderBy item is used to pass the ColumnExpression to the StatementSpec. The OrderBy also indicates the sort order (ascending or descending) and optionally a Locale. If a Locale is specified, then any character based attributes are sorted with respect to that Locale using the database language support. For Oracle, this is the National Language Support (NLS) (see Oracle documentation for more information). Java Locale values are mapped to Oracle NLS linguistic sort names via dbservice.properties entries.
Sorting is supported for standard and compound queries via QuerySpec and CompoundQuerySpec methods.
QuerySpec.appendOrderBy(OrderBy a_orderBy, int[] a_fromIndicies)
CompoundQuerySpec.appendOrderBy(OrderBy a_orderBy)
The QuerySpec method validates the ColumnExpression contained in the OrderBy against the QuerySpec’s FROM clause. The CompoundQuerySpec method does not validate. Note that neither method handles appending the ColumnExpression to the SELECT clause of the statement. This still must be done via the appendSelect() method. In both cases, it is recommended that a column alias be set for each ColumnExpression that is contained in an OrderBy. Depending on the type of query and the number of subclasses involved, the actual SQL statements may not be valid if a column alias is not used. Note that the column alias must not be a SQL reserved word (e.g., "number").
The following example builds a compound query using sorting. The names of parts and documents are returned sorted by name. Following is the SQL for this query:
SELECT A0.bname sortName
FROM WTPart A0
UNION
SELECT A0.bname sortName
FROM WTDocument A0
ORDER BY sortName DESC
The following code constructs the query specification. The first component query is for Parts. Note the setting of the column alias.
String sortName = "sortName";
QuerySpec partQuery = new QuerySpec();
int classIndex = partQuery.appendClassList(wt.part.WTPart.class, false);
ClassAttribute partName = new ClassAttribute(wt.part.WTPart.class,
wt.part.WTPart.NAME);
partName.setColumnAlias(sortName);
partQuery.appendSelect(partName, new int[] { classIndex }, false);
This next section constructs the Document portion of the query. The same column alias is used.
QuerySpec docQuery = new QuerySpec();
classIndex = docQuery.appendClassList(wt.doc.WTDocument.class, false);
ClassAttribute docName =
new ClassAttribute(wt.doc.WTDocument.class, wt.doc.WTDocument.NAME);
docName.setColumnAlias(sortName);
docQuery.appendSelect(docName, new int[] { classIndex }, false);
Finally, the compound query is constructed using these two component queries. The OrderBy is appended to the overall query. The default locale is used to sort the names with respect to the user’s language.
CompoundQuerySpec query = new CompoundQuerySpec();
query.setSetOperator(SetOperator.UNION);
query.addComponent(partQuery);
query.addComponent(docQuery);
query.appendOrderBy(new OrderBy(partName, true
));
To enable checking advance features statement and to successfully execute the query on the server side, non-access controlled query() method must be used.
query.setAdvancedQueryEnabled(true);
QueryResult queryResult = PersistenceHelper.manager.query (statement);
|
|
The QuerySpec must be executed inside the Remote Method server stub. For more details of the example, see Access Control Consideration.
|
Join Support
Query joins are used for associating data contained in separate tables. Joins can be accomplished by using the PersistenceManager navigate methods or through adhoc WhereExpressions. The QuerySpec class also provides explicit support for appending a join to a query using link classes and roles defined in the Rose model. This offers the flexibility of the QuerySpec along with the simplicity of specifying query joins using model information. The following QuerySpec methods can be used.
appendJoin(int a_linkIndex, String a_role, Persistable a_source)
appendJoin(int a_linkIndex, String a_role, int a_targetIndex)
The following example builds a query that joins together the SubFolder and Part classes via the FolderMembership link. The query returns all folders and all of the associated parts that are contained in the folder. The following code constructs the query specification. The first section adds the classes and the attributes that should be returned. The final two lines of code join together the classes using the modeled roles for the FolderMembership link class.
QuerySpec query = new QuerySpec();
int folderIndex = query.appendClassList(wt.folder.SubFolder.class,
false);
int linkIndex = query.appendClassList(wt.folder.FolderMembership.class,
false);
int partIndex = query.appendClassList(wt.part.WTPart.class, false);
query.appendSelect(new ClassAttribute(wt.folder.SubFolder.class,
wt.folder.SubF
older.NAME),
new int[] { folderIndex } , false);
query.appendSelect(new ClassAttribute(wt.part.WTPart.class,
wt.part.WTPart.NAME),
new int[] { partIndex }, false);
query.appendJoin(linkIndex, wt.folder.FolderMembership.FOLDER_ROLE,
folderIndex);
query.appendJoin(linkIndex, wt.folder.FolderMembership.MEMBER_ROLE,
partIndex);
