Managing Criteria Query Results

For queries that return more than one result, it’s often helpful to organize those results. The CriteriaQuery interface defines the orderBy method to order query results according to attributes of an entity. The CriteriaQuery interface also defines the groupBy method to group the results of a query together according to attributes of an entity, and the having method to restrict those groups according to a condition.

Ordering Results

The order of the results of a query can be set by calling the CriteriaQuery.orderBy method and passing in an Order object. Order objects are created by calling either the CriteriaBuilder.asc or the CriteriaBuilder.desc method. The asc method is used to order the results by ascending value of the passed expression parameter. The desc method is used to order the results by descending value of the passed expression parameter. The following query shows the use of the desc method:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
cq.select(pet);
cq.orderBy(cb.desc(pet.get(Pet_.birthday));

In this query, the results will be ordered by the pet’s birthday from highest to lowest. That is, pets born in December will appear before pets born in May.

The following query shows the use of the asc method:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
Join<Owner, Address> address = cq.join(Pet_.owners).join(Owner_.address);
cq.select(pet);
cq.orderBy(cb.asc(address.get(Address_.postalCode));

In this query, the results will be ordered by the pet owner’s postal code from lowest to highest. That is, pets whose owner lives in the 10001 zip code will appear before pets whose owner lives in the 91000 zip code.

If more than one Order object is passed to orderBy, the precedence is determined by the order in which they appear in the argument list of orderBy. The first Order object has precedence.

The following code orders results by multiple criteria:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
Join<Pet, Owner> owner = cq.join(Pet_.owners);
cq.select(pet);
cq.orderBy(cb.asc(owner.get(Owner_.lastName), owner.get(Owner_.firstName));

The results of this query will be ordered alphabetically by the pet owner’s last name, then first name.

Grouping Results

The CriteriaQuery.groupBy method partitions the query results into groups. These groups are set by passing an expression to groupBy:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
cq.groupBy(pet.get(Pet_.color));

This query returns all Pet entities and groups the results by the pet’s color.

The CriteriaQuery.having method is used in conjunction with groupBy to filter over the groups. The having method takes a conditional expression as a parameter. By calling the having method, the query result is restricted according to the conditional expression:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
cq.groupBy(pet.get(Pet_.color));
cq.having(cb.in(pet.get(Pet_.color)).value("brown").value("blonde");

In this example, the query groups the returned Pet entities by color, as in the preceding example. However, the only returned groups will be Pet entities where the color attribute is set to brown or blonde. That is, no gray-colored pets will be returned in this query.