SELECT Clause

The SELECT clause defines the types of the objects or values returned by the query.

Return Types

The return type of the SELECT clause is defined by the result types of the select expressions contained within it. If multiple expressions are used, the result of the query is an Object[], and the elements in the array correspond to the order of the expressions in the SELECT clause and in type to the result types of each expression.

A SELECT clause cannot specify a collection-valued expression. For example, the SELECT clause p.teams is invalid because teams is a collection. However, the clause in the following query is valid because the t is a single element of the teams collection:

SELECT t
FROM Player p, IN (p.teams) t

The following query is an example of a query with multiple expressions in the SELECT clause:

SELECT c.name, c.country.name
 FROM customer c
 WHERE c.lastname = 'Coss' AND c.firstname = 'Roxane'

This query returns a list of Object[] elements; the first array element is a string denoting the customer name, and the second array element is a string denoting the name of the customer’s country.

The result of a query may be the result of an aggregate function, listed in Table 22–11.

Table 22–11 Aggregate Functions in Select Statements

Name	Return Type	Description
AVG	Double	Returns the mean average of the fields
COUNT	Long	Returns the total number of results
MAX	The type of the field	Returns the highest value in the result set
MIN	The type of the field	Returns the lowest value in the result set
SUM	Long (for integral fields) Double (for floating-point fields) BigInteger (for BigInteger fields) BigDecimal (for BigDecimal fields)	Returns the sum of all the values in the result set

For select method queries with an aggregate function (AVG, COUNT, MAX, MIN, or SUM) in the SELECT clause, the following rules apply:

• The AVG, MAX, MIN, and SUM functions return null if there are no values to which the function can be applied.

• The COUNT function returns 0 if there are no values to which the function can be applied.

The following example returns the average order quantity:

SELECT AVG(o.quantity)
 FROM Order o

The following example returns the total cost of the items ordered by Roxane Coss:

SELECT SUM(l.price)
FROM Order o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname = 'Coss' AND c.firstname = 'Roxane'

The following example returns the total number of orders:

SELECT COUNT(o)
FROM Order o

The following example returns the total number of items that have prices in Hal Incandenza’s order:

SELECT COUNT(l.price)
FROM Order o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname = 'Incandenza' AND c.firstname = 'Hal'

The DISTINCT Keyword

The DISTINCT keyword eliminates duplicate return values. If a query returns a java.util.Collection, which allows duplicates, you must specify the DISTINCT keyword to eliminate duplicates.

Constructor Expressions

Constructor expressions allow you to return Java instances that store a query result element instead of an Object[].

The following query creates a CustomerDetail instance per Customer matching the WHERE clause. A CustomerDetail stores the customer name and customer’s country name. So the query returns a List of CustomerDetail instances:

SELECT NEW com.xyz.CustomerDetail(c.name, c.country.name)
 FROM customer c
WHERE c.lastname = 'Coss' AND c.firstname = 'Roxane'