Close

JPA Criteria API - Restricting Query Results by applying 'where' conditions

[Updated: Oct 10, 2018, Created: Oct 9, 2018]

In previous tutorials we have been using where clause in Criteria API. In this tutorial we will quickly go through the available API involving 'where' statements.

CriteriaQuery.where() methods

Where clause query restrictions are applied via following methods of CriteriaQuery interface:

package javax.persistence.criteria;
 .....
public interface CriteriaQuery<T> extends AbstractQuery<T> {
     .....
    CriteriaQuery<T> where(Expression<Boolean> restriction);//applies restriction per specified boolean expression
    CriteriaQuery<T> where(Predicate... restrictions);//applies restriction per specified predicate(s) with 'and' (conjunction)
}

As seen above the argument to the where() methods can either be an Expression<Boolean> instance or Predicate instances. Let's see how to create each one of them.

CriteriaBuilder methods which return Predicate

We can create a Predicate instance by using one of the following methods:

package javax.persistence.criteria;
 ....
public interface CriteriaBuilder {
     ..........
    //testing the existence of a subquery result
    Predicate exists(Subquery<?> subquery);

    //conjunctions/disjunction for creating compound predicate
    Predicate and(Expression<Boolean> x, Expression<Boolean> y);
    Predicate and(Predicate... restrictions);
    Predicate or(Expression<Boolean> x, Expression<Boolean> y);
    Predicate or(Predicate... restrictions);

    //negate
    Predicate not(Expression<Boolean> restriction);

    //and(conjunction)/or(disjunction) with zero conjuncts/disjuncts)
    Predicate conjunction();
    Predicate disjunction();

    //testing true/false
    Predicate isTrue(Expression<Boolean> x);
    Predicate isFalse(Expression<Boolean> x);

    //testing null
    Predicate isNull(Expression<?> x);
    Predicate isNotNull(Expression<?> x);

    //testing equality
    Predicate equal(Expression<?> x, Expression<?> y);
    Predicate equal(Expression<?> x, Object y);
    Predicate notEqual(Expression<?> x, Expression<?> y);
    Predicate notEqual(Expression<?> x, Object y);

    //greater than and less than 
    <Y extends Comparable<? super Y>> Predicate greaterThan(Expression<? extends Y> x, 
                                                                       Expression<? extends Y> y);
    <Y extends Comparable<? super Y>> Predicate greaterThan(Expression<? extends Y> x, Y y);
    <Y extends Comparable<? super Y>> Predicate greaterThanOrEqualTo(Expression<? extends Y> x, 
                                                                       Expression<? extends Y> y);
    <Y extends Comparable<? super Y>> Predicate greaterThanOrEqualTo(Expression<? extends Y> x, Y y);
    <Y extends Comparable<? super Y>> Predicate lessThan(Expression<? extends Y> x, 
                                                                       Expression<? extends Y> y);
    <Y extends Comparable<? super Y>> Predicate lessThan(Expression<? extends Y> x, Y y);
    <Y extends Comparable<? super Y>> Predicate lessThanOrEqualTo(Expression<? extends Y> x, 
                                                                       Expression<? extends Y> y);
    <Y extends Comparable<? super Y>> Predicate lessThanOrEqualTo(Expression<? extends Y> x, Y y);
    //short forms for greater than and less than 
    Predicate gt(Expression<? extends Number> x, Expression<? extends Number> y);
    Predicate gt(Expression<? extends Number> x, Number y);
    Predicate ge(Expression<? extends Number> x, Expression<? extends Number> y);
    Predicate ge(Expression<? extends Number> x, Number y);
    Predicate lt(Expression<? extends Number> x, Expression<? extends Number> y);
    Predicate lt(Expression<? extends Number> x, Number y);
    Predicate le(Expression<? extends Number> x, Expression<? extends Number> y);
    Predicate le(Expression<? extends Number> x, Number y);

   //between operator
    <Y extends Comparable<? super Y>> Predicate between(Expression<? extends Y> v, 
                                                  Expression<? extends Y> x, Expression<? extends Y> y);
    <Y extends Comparable<? super Y>> Predicate between(Expression<? extends Y> v, Y x, Y y);

    //testing whether collection is empty 
    <C extends Collection<?>> Predicate isEmpty(Expression<C> collection);
    <C extends Collection<?>> Predicate isNotEmpty(Expression<C> collection);

    //testing whether a value is an element of a collection
    <E, C extends Collection<E>> Predicate isMember(Expression<E> elem, Expression<C> collection);
    <E, C extends Collection<E>> Predicate isMember(E elem, Expression<C> collection);
    <E, C extends Collection<E>> Predicate isNotMember(Expression<E> elem, Expression<C> collection);
    <E, C extends Collection<E>> Predicate isNotMember(E elem, Expression<C> collection);

    //like operator with pattern
    Predicate like(Expression<String> x, Expression<String> pattern);
    Predicate like(Expression<String> x, String pattern);
    Predicate notLike(Expression<String> x, Expression<String> pattern);
    Predicate notLike(Expression<String> x, String pattern);
    //like operator with pattern and escape char
    Predicate like(Expression<String> x, Expression<String> pattern, Expression<Character> escapeChar);
    Predicate like(Expression<String> x, Expression<String> pattern, char escapeChar);
    Predicate like(Expression<String> x, String pattern, Expression<Character> escapeChar);
    Predicate like(Expression<String> x, String pattern, char escapeChar);
    Predicate notLike(Expression<String> x, Expression<String> pattern, Expression<Character> escapeChar);
    Predicate notLike(Expression<String> x, Expression<String> pattern, char escapeChar);
    Predicate notLike(Expression<String> x, String pattern, Expression<Character> escapeChar);
    Predicate notLike(Expression<String> x, String pattern, char escapeChar);
    .................
}

As Predicate extends Expression<Boolean>, Predicate can be passed to the above methods which takes Expression<Boolean>.

Quick Example

Following is the example of using one of the above methods (equal()) of CriteriaBuilder:

  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  CriteriaQuery<Customer> query = criteriaBuilder.createQuery(Customer.class);
  Root<Customer> customer = query.from(Customer.class);
  Predicate predicate = criteriaBuilder.equal(customer.get(Customer_.name), "Lindsey");
  query.select(customer)
       .where(predicate);
  TypedQuery<Customer> typedQuery = entityManager.createQuery(query);
  Customer c = typedQuery.getSingleResult();

Expression methods which return Predicate

package javax.persistence.criteria;
  ....
public interface Expression<T> extends Selection<T> {
    //test whether the expression is null
    Predicate isNull();
    Predicate isNotNull();

    //test whether the expression is a member of the specified values
    Predicate in(Object... values);
    Predicate in(Expression<?>... values);
    Predicate in(Collection<?> values);
    Predicate in(Expression<Collection<?>> values);
    .......
}

Quick Example

Following example shows how to use one of the above methods (isNull()):

  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  CriteriaQuery<Person> query = criteriaBuilder.createQuery(Person.class);
  Root<Person> person = query.from(Person.class);
  //in following line person.get() returns Path which is sub-interface of Expression
  Expression<Byte> ageExpression = person.get(Person_.age);
  Predicate nullAgePredicate = ageExpression.isNull();
  query.select(person)
        .where(nullAgePredicate);
  TypedQuery<Person> typedQuery = entityManager.createQuery(query);
  Person p = typedQuery.getSingleResult();

Negating Predicate

The method not() can be used to negate:

package javax.persistence.criteria;
 ........
public interface Predicate extends Expression<Boolean> {
   ..... 
    Predicate not();
   ...
}

Quick example:

  CriteriaQuery<Person> query = criteriaBuilder.createQuery(Person.class);
  Root<Person> person = query.from(Person.class);
  Predicate predicate = criteriaBuilder.equal(person.get(Person_.name), "Lindsey");
  query.select(person)
       .where(predicate.not());
  TypedQuery<Person> typedQuery = entityManager.createQuery(query);
  List<Person> p = typedQuery.getResultList();

Alternatively we can use CriteriaBuilder.not(Expression<Boolean>) method in above example:

  ....
  Predicate predicate = criteriaBuilder.equal(person.get(Person_.name), "Lindsey");
  query.select(person)
       .where(criteriaBuilder.not(predicate));
  ....

Using Expression<Boolean>

The Path interface is a sub-interface of Expression, that means a Path<Boolean> can be used in CriteriaBuilder.where(Expression<Boolean>).

Quick example:

In following example Person#registeredVoter is a boolean field:

  CriteriaQuery<Person> query = criteriaBuilder.createQuery(Person.class);
  Root<Person> person = query.from(Person.class);
 //in following line person.get() returns Path which is sub-interface of Expression
 Expression<Boolean> voterExpression = person.get(Person_.registeredVoter);
 query.select(person)
      .where(voterExpression);
 TypedQuery<Person> typedQuery = entityManager.createQuery(query);
 List<Person> p = typedQuery.getResultList(); //returns all persons having registeredVoter = true

Above boolean expression can be negated as follows:

  CriteriaQuery<Person> query = criteriaBuilder.createQuery(Person.class);
  Root<Person> person = query.from(Person.class);
  Expression<Boolean> voterExpression = person.get(Person_.registeredVoter);
  query.select(person)
       .where(criteriaBuilder.isFalse(voterExpression));
  TypedQuery<Person> typedQuery = entityManager.createQuery(query);
  List<Person> p = typedQuery.getResultList(); //returns all persons having registeredVoter = false

Or by using CriteriaBuilder.not(Expression<Boolean>) method:

  .......
  Expression<Boolean> voterExpression = person.get(Person_.registeredVoter);
  query.select(person)
       .where(criteriaBuilder.not(voterExpression));
  ....

Use of CriteriaBuilder.literal()

As stated in this tutorial, CriteriaBuilder.literal() can be used to convert a literal value to expression. This might be useful for the above CriteriaBuilder methods which requires only Expression parameter.

Expression<T> literal(T value);
Expression<T> nullLiteral(Class<T> resultClass);

Complete Example

Entity

@Entity
public class Person {
  @Id
  @GeneratedValue
  private int id;
  private String name;
  private Byte age;
  private boolean registeredVoter;
    .............
}

Applying where restrictions

public class ExampleMain {
  static EntityManagerFactory emf = Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {

      try {
          persistEntity();
          usePredicate();
          useNegatePredicate();
          useExpressionPredicate();
          useBooleanExpression();
          useNegateBooleanExpression();
      } finally {
          emf.close();
      }
  }

  private static void useBooleanExpression() {
      System.out.println("-- finding person who is registered voter --");
      EntityManager entityManager = emf.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Person> query = criteriaBuilder.createQuery(Person.class);
      Root<Person> person = query.from(Person.class);
      //in following line person.get() returns Path which is sub-interface of Expression
      Expression<Boolean> voterExpression = person.get(Person_.registeredVoter);
      query.select(person)
           .where(voterExpression);
      TypedQuery<Person> typedQuery = entityManager.createQuery(query);
      List<Person> p = typedQuery.getResultList();
      p.forEach(System.out::println);
  }

  private static void useNegateBooleanExpression() {
      System.out.println("-- finding person who is not registered voter --");
      EntityManager entityManager = emf.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Person> query = criteriaBuilder.createQuery(Person.class);
      Root<Person> person = query.from(Person.class);
      //in following line person.get() returns Path which is sub-interface of Expression
      Expression<Boolean> voterExpression = person.get(Person_.registeredVoter);
      query.select(person)
           .where(criteriaBuilder.isFalse(voterExpression));
      TypedQuery<Person> typedQuery = entityManager.createQuery(query);
      List<Person> p = typedQuery.getResultList();
      p.forEach(System.out::println);
  }

  private static void useExpressionPredicate() {
      System.out.println("-- finding person with null age --");
      EntityManager entityManager = emf.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Person> query = criteriaBuilder.createQuery(Person.class);
      Root<Person> person = query.from(Person.class);
      //in following line person.get() returns Path which is sub-interface of Expression
      Expression<Byte> ageExpression = person.get(Person_.age);
      Predicate nullAgePredicate = ageExpression.isNull();
      query.select(person)
           .where(nullAgePredicate);
      TypedQuery<Person> typedQuery = entityManager.createQuery(query);
      Person p = typedQuery.getSingleResult();
      System.out.println(p);
  }

  private static void usePredicate() {
      System.out.println("-- finding person named 'Lindsey' --");
      EntityManager entityManager = emf.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Person> query = criteriaBuilder.createQuery(Person.class);
      Root<Person> person = query.from(Person.class);
      Predicate predicate = criteriaBuilder.equal(person.get(Person_.name), "Lindsey");
      query.select(person)
           .where(predicate);
      TypedQuery<Person> typedQuery = entityManager.createQuery(query);
      Person p = typedQuery.getSingleResult();
      System.out.println(p);
  }

  private static void useNegatePredicate() {
      System.out.println("-- finding person not named 'Lindsey' --");
      EntityManager entityManager = emf.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Person> query = criteriaBuilder.createQuery(Person.class);
      Root<Person> person = query.from(Person.class);
      Predicate predicate = criteriaBuilder.equal(person.get(Person_.name), "Lindsey");
      query.select(person)
           .where(predicate.not());
      TypedQuery<Person> typedQuery = entityManager.createQuery(query);
      List<Person> p = typedQuery.getResultList();
      p.forEach(System.out::println);
  }

  private static void persistEntity() {
      System.out.println("-- Persisting entities --");
      EntityManager em = emf.createEntityManager();

      Person p1 = Person.of("Lindsey", (byte) 31, false);
      Person p2 = Person.of("Morgan", (byte) 40, true);
      Person p3 = Person.of("Mike", null, false);

      em.getTransaction().begin();
      em.persist(p1);
      em.persist(p2);
      em.persist(p3);
      em.getTransaction().commit();
      em.close();
  }
}
-- Persisting entities --
-- finding person named 'Lindsey' --
Person{id=1, name='Lindsey', age=31, registeredVoter=false}
-- finding person not named 'Lindsey' --
Person{id=2, name='Morgan', age=40, registeredVoter=true}
Person{id=3, name='Mike', age=null, registeredVoter=false}
-- finding person with null age --
Person{id=3, name='Mike', age=null, registeredVoter=false}
-- finding person who is registered voter --
Person{id=2, name='Morgan', age=40, registeredVoter=true}
-- finding person who is not registered voter --
Person{id=1, name='Lindsey', age=31, registeredVoter=false}
Person{id=3, name='Mike', age=null, registeredVoter=false}

In upcoming tutorials we will be exploring more methods of CriteriaBuilder and Expression which return Predicate.

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • hibernate-core 5.3.6.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • hibernate-jpamodelgen 5.3.6.Final: Annotation Processor to generate JPA 2 static metamodel classes.
  • JDK 1.8
  • Maven 3.5.4

JPA Criteria API Applying where clause Select All Download
  • jpa-criteria-api-restricting-query-results
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also