Section C.3. SELECT Clause

C.3. SELECT Clause

As with standard SQL queries, the SELECT clause in EJB QL can specify what you want returned from the persistent store. The SELECT clause takes the form:

 SELECT [ DISTINCT ] [ <schema variable> | <aggregation expression> ] 

The clause contains a single variable reference to indicate the type of result returned by the query, or an aggregation function can be used to calculate some formula on the results. The optional DISTINCT operator specifies that the return results should be unique. The variable reference can either be to a specific variable declared in the FROM clause, or it can be a path expression that refers to a single-valued CMR field on an abstract schema type. In our previous example, we used a specific variable reference:

 SELECT OBJECT(p) FROM ProfileBean AS p WHERE p.entriesBytes IS NULL 

This SELECT clause specifies OBJECT(p) as the return value of the query. If a single query variable is being used in the SELECT clause, then it has to be qualified with the OBJECT operator. The p variable is declared in the FROM clause as a range variable with type ProfileBean, so the query results are one or more ProfileBeans that match the conditions in the WHERE clause. In our other query example:

 SELECT DISTINCT OBJECT(prof) FROM Person p, IN(p.profiles) prof WHERE ... 

the SELECT clause specifies OBJECT(prof) as the return value of the query. The prof variable is declared in the FROM clause as a collection member variable that refers to the profiles CMR field in the Person bean. This CMR field contains EJBs with the abstract schema type of ProfileBean, so the query results again are ProfileBean objects.

You can also use path expressions to specify the return type of the query. Suppose, for example, that our Person abstract schema type has a one-to-one relationship named employer with an Organization bean that represents the organization that employs the person. We can use a path expression in our SELECT clause to have the query return the Organizations of the selected Person beans:

 SELECT DISTINCT p.employer FROM Person p WHERE ... 

Note that the path expression has to be single-valued. If a CMR field is many-valued, then you need to declare a collection member variable in the FROM clause and refer to that variable in the SELECT clause. We did this in the previous example when we used the prof variable to refer to the ProfileBeans associated with the Person beans selected by the query.

If the EJB QL query is for a finder method, then the type specified in the SELECT clause has to be the abstract schema type of an EJB, since finder methods can only return EJB references. If the query is for a select method, then the SELECT clause can also specify CMP fields on abstract schema types, using a path expression. If, for example, our Person bean has a name CMP field, we could use the following query for a select method on the Person bean:

 SELECT p.name FROM Person p WHERE ... 

In this case, the return type of the select method needs to match the type of the name CMP field.

C.3.1. Aggregation expressions

EJB 2.1 introduced several aggregation functions that can be used within the SELECT clause of queries. The argument to these aggregation functions is a path expression or a variable reference. The aggregation functions supported in EJB 2.1 are:

AVG

Calculate the average of the values of the argument. The argument data must be numeric.

COUNT

Return the number of results found in the argument expression.

MAX

Return the maximum value found in the argument expression. The argument data can be any orderable types (numeric, string, character, date).

MIN

Return the minimum value found in the argument expression. The argument data can be any orderable type (numeric, string, character, date).

SUM

Return the sum of the values in the aggregate expression. The argument data must be numeric.

You can also optionally use the DISTINCT keyword before the argument expression in aggregation functions. Any duplicate values are removed from the set before the function is applied to them. NULL values are always removed from the data set before applying the aggregation function, regardless of whether you use DISTINCT or not.

The following example returns the number of people whose first names are John:

 SELECT COUNT(p) FROM Person p WHERE p.firstName = 'John' 

This example returns the number of different first names contained in the Person data:

 SELECT COUNT(DISTINCT p.firstName) FROM Person p 

This example returns the average age of all people:

 SELECT AVG(p.age) FROM Person p 

This example returns (alphabetically) the last names of all the people in the database:

 SELECT MAX(p.lastName) FROM Person p 

C.3.2. DISTINCT Queries

The DISTINCT option is used in the SELECT clause to specify that any duplicate values in the result set should be eliminated. It makes sense only when used in EJB QL queries for methods that return a Collection of some kind. If a finder or select method returns a single value, then the result set needs to be unique by definition. If a select method is declared as a java.util.Set, then the results set has to be distinct, so the EJB container has to assume the DISTINCT keyword in these cases even if it's not specified in the EJB QL for the method. For example, if we have a select method, such as the following, defined in an EJB implementation class:

 public abstract java.util.Set ejbSelectPreferredClients(...)   throws FinderException; 

and we put this entry in our ejb-jar.xml deployment descriptor to specify the EJB QL for this method:

 <ejb-ql>   <?[CDATA[SELECT OBJECT(c) FROM Customers c WHERE c.preferred IS NOT NULL]]> </ejb-ql> 

the EJB container still executes the query as if the DISTINCT clause had been included in the EJB QL.