Restricting Criteria Query Results

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 using methods defined in the Expression and CriteriaBuilder interfaces.

The Expression Interface Methods

An Expression object is used to in a query's select, where, or having clause

Table 22–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.

---

Example 22–13 Using the Expression.isNull 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(pet.get(Pet_.color).isNull());

This query finds all pets where the color attribute is null.

---

---

Example 22–14 Using the Expression.in 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(pet.get(Pet_.color).in("brown", "black");

This query finds all brown and black pets.

---

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

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

---

Example 22–15 Using 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");
...

---

---

Example 22–16 Using 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);

---

---

Example 22–17 Using 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);

---

---

Example 22–18 Using 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.

Table 22–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.

---

Example 22–19 Using 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");

---