The results of a query can be restricted on the CriteriaQuery object according to conditions set by calling the CriteriaQuery.where method. Calling the where method is analogous to setting the WHERE clause in a JPQL query.
The where method evaluates instances of the Expression interface to restrict the results according to the conditions of the expressions. Expression instances are created by using methods defined in the Expression and CriteriaBuilder interfaces.
An Expression object is used in a query's SELECT, WHERE, or HAVING clause. Table 23–1 shows conditional methods you can use with Expression objects.
Table 23–1 Conditional Methods in the Expression Interface
Method |
Description |
---|---|
isNull |
Tests whether an expression is null |
isNotNull |
Tests whether an expression is not null |
in |
Tests whether an expression is within a list of values |
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); Metamodel m = em.getMetamodel(); EntityType<Pet> Pet_ = m.entity(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); Metamodel m = em.getMetamodel(); EntityType<Pet> Pet_ = m.entity(Pet.class); Root<Pet> pet = cq.from(Pet.class); cq.where(pet.get(Pet_.color).in("brown", "black");
The in method also can check whether an attribute is a member of a collection.
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 23–2 shows conditional methods you can use with CriteriaBuilder objects.
Table 23–2 Conditional Methods in the CriteriaBuilder Interface
Conditional Method |
Description |
---|---|
equal |
Tests whether two expressions are equal |
notEqual |
Tests whether two expressions are not equal |
gt |
Tests whether the first numeric expression is greater than the second numeric expression |
ge |
Tests whether the first numeric expression is greater than or equal to the second numeric expression |
lt |
Tests whether the first numeric expression is less than the second numeric expression |
le |
Tests whether the first numeric expression is less than or equal to the second numeric expression |
between |
Tests whether the first expression is between the second and third expression in value |
like |
Tests whether the expression matches a given pattern |
The following code uses the CriteriaBuilder.equal method:
CriteriaQuery<Pet> cq = cb.createQuery(Pet.class); Metamodel m = em.getMetamodel(); EntityType<Pet> Pet_ = m.entity(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); Metamodel m = em.getMetamodel(); EntityType<Pet> Pet_ = m.entity(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); Metamodel m = em.getMetamodel(); EntityType<Pet> Pet_ = m.entity(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); Metamodel m = em.getMetamodel(); EntityType<Pet> Pet_ = m.entity(Pet.class); Root<Pet> pet = cq.from(Pet.class); cq.where(cb.like(pet.get(Pet_.name)), "*do");
Multiple conditional predicates can be specified by using the compound predicate methods of the CriteriaBuilder interface, as shown in Table 23–3.
Table 23–3 Compound Predicate Methods in the CriteriaBuilder Interface
Method |
Description |
---|---|
and |
A logical conjunction of two Boolean expressions |
or |
A logical disjunction of two Boolean expressions |
not |
A logical negation of the given Boolean expression |
The following code shows the use of compound predicates in queries:
CriteriaQuery<Pet> cq = cb.createQuery(Pet.class); Metamodel m = em.getMetamodel(); EntityType<Pet> Pet_ = m.entity(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");