40.3 Using the Criteria API and Metamodel API to Create Basic Typesafe Queries

40.3.5.1 The Expression Interface Methods

An Expression object is used in a query's SELECT, WHERE, or HAVING clause. Table 40-1 shows conditional methods you can use with Expression objects.

The following query uses the Expression.isNull method to find all pets where the color attribute is null:

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

The following query uses the Expression.in method to find all brown and black pets:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
cq.where(pet.get(Pet_.color).in("brown", "black"));

The in method can also check whether an attribute is a member of a collection.

40.3.5.2 Expression Methods in the CriteriaBuilder Interface

The CriteriaBuilder interface defines additional methods for creating expressions. These methods correspond to the arithmetic, string, date, time, and case operators and functions of JPQL. Table 40-2 shows conditional methods you can use with CriteriaBuilder objects.

The following code uses the CriteriaBuilder.equal method:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
cq.where(cb.equal(pet.get(Pet_.name), "Fido"));

The following code uses the CriteriaBuilder.gt method:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
Date someDate = new Date(...);
cq.where(cb.gt(pet.get(Pet_.birthday), date));

The following code uses the CriteriaBuilder.between method:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
Date firstDate = new Date(...);
Date secondDate = new Date(...);
cq.where(cb.between(pet.get(Pet_.birthday), firstDate, secondDate));

The following code uses the CriteriaBuilder.like method:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
cq.where(cb.like(pet.get(Pet_.name), "*do"));

To specify multiple conditional predicates, use the compound predicate methods of the CriteriaBuilder interface, as shown in Table 40-3.

The following code shows the use of compound predicates in queries:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
Root<Pet> pet = cq.from(Pet.class);
cq.where(cb.equal(pet.get(Pet_.name), "Fido")
        .and(cb.equal(pet.get(Pet_.color), "brown")));

40.3.6.1 Ordering Results

To order the results of a query, call the CriteriaQuery.orderBy method, passing in an Order object. To create an Order object, call 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 = pet.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 = pet.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.

40.3.6.2 Grouping Results

The CriteriaQuery.groupBy method partitions the query results into groups. To set these groups, pass 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.

Use the CriteriaQuery.having method in conjunction with groupBy to filter over the groups. The having method, which takes a conditional expression as a parameter, restricts the query result 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.

40.3.7.1 Single-Valued Query Results

Use the TypedQuery<T>.getSingleResult method to execute queries that return a single result:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
...
TypedQuery<Pet> q = em.createQuery(cq);
Pet result = q.getSingleResult();

40.3.7.2 Collection-Valued Query Results

Use the TypedQuery<T>.getResultList method to execute queries that return a collection of objects:

CriteriaQuery<Pet> cq = cb.createQuery(Pet.class);
...
TypedQuery<Pet> q = em.createQuery(cq);
List<Pet> results = q.getResultList();