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.