Java Enterprise in a Nutshell, Third Edition

ORDER BY Clause

EJB 2.1 introduced an ORDER BY clause, to mimic the functionality of the corresponding SQL clause. With an ORDER BY clause, you can instruct the container to sort the results of the query before returning them to the calling application. This can save programming time and potentially be more efficient than doing the sort in the application code. Note the key word, “potentially.” If the EJB container is using an underlying relational engine to presort the results, then it’s likely that this will be much faster than sorting the data yourself in Java code. But there are no guarantees about the expected performance of the container’s sort algorithms, so using ORDER BY is not only a convenience, but also a leap of faith.

Note that, if you use an ORDER BY clause in your EJB QL, then the SELECT clause must specify either a single range variable (e.g., OBJECT(p)) or a simple path expression (e.g., p.firstName).

The ORDER BY clause has the following syntax:

ORDER BY <order item> [, <order item>]*

Each order item must be an orderable field of the entity specified in the SELECT clause. For example, if we wanted to select all people with the first name Judy, and order them by their last names, then we’d use a query such as:

SELECT OBJECT(p) FROM Person p WHERE p.firstName = 'Judy'
    ORDER BY p.lastName

If we wanted to select all people, and sort them by last name and then by first names, we would use a query something like this:

SELECT OBJECT(p) FROM Person p
 ORDER BY p.lastName, ...