Single Column Expression in SELECT Clause
When a class is selected in the query result set, every column of that class is included in the SELECT clause so that a full object can be built and returned in the QueryResult. In some cases, only single columns are needed in a result set. When possible, single columns should be used since this is much more efficient and offers better performance. The following API supports single column expressions in the SELECT clause: appendSelect(ColumnExpression a_column, int[] a_fromIndices, boolean a_selectOnly)
The fromIndices parameter is used to associate the column expression to classes in the query, if applicable. In general, a ColumnExpression may be associated with zero or more classes in the From clause. See the following table to determine the size of the a_fromIndices array based on the type of ColumnExpression. For example, a single ClassAttribute would require one from index. A SQLFunction with two ClassAttribute arguments and a ConstantExpression argument would require two from indices. If no fromIndices are required, a null value can be passed as the argument. The selectOnly parameter controls whether the column expression should be returned as a result object. If true, the column expression is included only in the select and is not returned as a result.
The ColumnExpression parameter specifies the query column to append to the select clause. The following are concrete ColumnExpression implementations:
|
Column Expression
|
Description
|
Required From Indices
|
|
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.
|
1
|
|
SQLFunction
|
This class represents a SQL function within a SQL statement.
|
0 or more. This number is based on the sum of the required from indices of all arguments.
|
|
ConstantExpression
|
This class represents a constant in a SQL statement.
|
0
|
|
KeywordExpression
|
This class represents an expression that evaluates to a SQL keyword that can be used in a SQL statement.
|
0
|
|
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.
|
0
|
The following example builds a query for part numbers:
QuerySpec qs = new QuerySpec();
int classIndex = qs.appendClassList(wt.part.WTPart.class, false);
ClassAttribute ca = new ClassAttribute(
wt.part.WTPartclass, wt.part.WTPart.NUMBER);
qs.appendSelect(ca, new int[] { classIndex }, false);
Note that when the WTPart class is appended to the query, the selectable parameter is false. The full object is not returned; only the number column is returned.
Results are still returned in the QueryResult object. Each element of the QueryResult corresponds to a row and is an Object array (that is, Object[]). In this example, the number column is at index 0 for each element. The actual Java type for each result is based on the table column and the JDBC SQL-to-Java type mapping.
The behavior of queries for parent classes (that is, classes that have one or more persistable, concrete subclasses) is to execute SQL for each table. When only ColumnExpressions are included in the SELECT clause, all of these SQL statements are implicitly executed as a single UNION statement instead of multiple separate database queries.
Queries that include only column expressions still have Access Control applied. Internally, columns are added to the query to retrieve information needed for Access Control. This behavior is important to understand when using aggregate SQL functions. When these are used, the SELECT clause must contain only expressions with aggregate SQL functions (or the expression must be included in the GROUP BY clause. If Access Control is applied to such a statement, then it will result in invalid SQL.