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