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