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 orderBymethod 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 and 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 CriteriaBuilder.desc methods. 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.

Example 22–20 Ordering Results in Descending Order

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.

Example 22–21 Ordering Results in Ascending Order

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.

Example 22–22 Ordering Results By More than One 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.

Example 22–23 Grouping Results Using the CriteriaQuery.groupBy Method

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 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.

Example 22–24 Grouping Results Using the CriteriaQuery.groupBy and CriteriaQuery.having Methods

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 example above. However, the only returned groups will be Pet entities where the color attribute is set to brown or blonde. That is, no grey colored pets will be returned in this query.