Select
SELECT DISTINCT? selectedPropertiesList | ( NEW className OPEN selectedPropertiesList CLOSE );
The optional distinct keyword can be used to reduce the result set to unique results (no result is returned more than once). This can be useful when you wish to limit the results. For example, the statement select student.id from Student as student, Student as student2 returns the full product of every record in the Student table as matched with every other student record (literally, the records in the student table multiplied by the records in the student table). The distinct keyword can be used to limit these results back to the individual student records. For example, the statement select distinct student.id from Student as student, Student as student2 will return the same results as a from Student.
If one or more properties (selectedProperties) are specified, the query will return them as mapped to objects. For example, the HQL select owner.id, owner.name from com.cascadetg.ch03.Owner as owner will return a set of java.lang.Long and java.lang.String objects.
Instead of specifying that the properties should be returned as a set of different objects, you may wish to use the new className statement instead. This will return an arbitrary class, calling the constructor as the result of a query. For example, the HQL command select new java.lang.StringBuffer (owner.name) from Owner as owner returns a set of java.lang.StringBuffer objects, initialized with the owner.name property.
Selected Properties List
( path | aggregate ) ( COMMA path | aggregate )*
The properties returned by a SELECT statement can be either a set of properties or an aggregate function. For example, the HQL select max(result.score), min(result.score) from Examresult as result is valid, as is select result from Examresult as result. The statement select result, max(result.score), min(result.score) from Examresult as result, however, is NOT valid.
Aggregate
COUNT OPEN path CLOSE
SUM OPEN path CLOSE
AVG OPEN path CLOSE
MAX OPEN path CLOSE
MIN OPEN path CLOSE
COUNT OPEN STAR CLOSE
COUNT OPEN DISTINCT | ALL path CLOSE
The aggregate functions are used to return statistical data on a set of records rather than the records themselves.
avg(path) calculates the mean average value. count(*) returns the number of items in a group, including null values and duplicates. count(ALL path) returns the number of non-null values. count(DISTINCT path) returns the number of unique, non-null values. max(path) finds the maximum value. min(path) finds the minimum value. sum(path) adds the values specified.
Collection Properties
( OPEN query CLOSE ) | ( 'elements'|'indices' OPEN path CLOSE )
Collections have several special properties in addition to the obvious properties that are dependent on the type of collection. All collections can refer to the elements property, which is simply a result of all the possible objects.
For example, select student.examresults.elements.score from com.cascadetg.ch04.Student as student returns the score values from the examresult table as java.lang.Integer, where examresult.studentID is bound to a valid student.id.
As another example, select student.examresults.elements from com.cascadetg.ch04.Student as student returns the set of examresult records, bound as com.cascadetg.ch04.Examresult.
Table 8.3 shows the various collection properties.
Table 8.3. Collection PropertiesProperty | Meaning |
|---|
elements | The objects in the collection. | indices | The index values of the collection (only available if the collection is indexed; see Chapter 7). | size | The size of the collection. Useful if you wish to know the number of associated objects without fetching the entire collection. | maxIndex | The maximum index value (only available if the collection is indexed). | minIndex | The minimum index value (only available if the collection is indexed). | maxElement | The element corresponding to the maximum index value (only available if the collection is indexed). | minElement | The element corresponding to the maximum index value (only available if the collection is indexed). | index [...] | Used to find elements in an indexed collection. The expression inside the [ ] tokens is evaluated to determine the resulting elements against the index. |
You can extend the path of a collection past the elements property. For example, the statement select student.examresults.elements.score from Student as student returns the scores of the student as java.lang.Integer objects.
| 
