1. Overview

The JPA Criteria API can easily be used to add multiple AND/OR conditions when querying records in a database. In this tutorial, we’ll explore a quick example of JPA criteria queries that combine multiple AND/OR predicates.

If you’re not already familiar with predicates, we suggest reading about the basic JPA criteria queries first.

2. Sample Application

For our examples, we’ll consider an inventory of Item entities, each having an id, name, color, and grade:

@Entity
public class Item {

    @Id
    private Long id;
    private String color;
    private String grade;
    private String name;
    
    // standard getters and setters
}

3. Combining Two OR Predicates Using an AND Predicate

Let’s consider a scenario where we need to find items having both:

  1. red or blue color
    AND
  2. A or B grade

We can easily do this using JPA Criteria API’s and() and or() compound predicates.

To begin, we’ll set up our query:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Item> criteriaQuery = criteriaBuilder.createQuery(Item.class);
Root<Item> itemRoot = criteriaQuery.from(Item.class);

Now we’ll need to build a Predicate to find items having a blue or red color:

Predicate predicateForBlueColor
  = criteriaBuilder.equal(itemRoot.get("color"), "blue");
Predicate predicateForRedColor
  = criteriaBuilder.equal(itemRoot.get("color"), "red");
Predicate predicateForColor
  = criteriaBuilder.or(predicateForBlueColor, predicateForRedColor);

Next, we’ll build a Predicate to find items of grade A or B:

Predicate predicateForGradeA
  = criteriaBuilder.equal(itemRoot.get("grade"), "A");
Predicate predicateForGradeB
  = criteriaBuilder.equal(itemRoot.get("grade"), "B");
Predicate predicateForGrade
  = criteriaBuilder.or(predicateForGradeA, predicateForGradeB);

Finally, we’ll define the AND Predicate of these two, apply the where() method, and execute our query:

Predicate finalPredicate
  = criteriaBuilder.and(predicateForColor, predicateForGrade);
criteriaQuery.where(finalPredicate);
List<Item> items = entityManager.createQuery(criteriaQuery).getResultList();

4. Combining Two AND Predicates Using an OR Predicate

Conversely, let’s consider a scenario where we need to find items having either:

  1. red color and grade D
    OR
  2. blue color and grade B

The logic is quite similar, but here we create two AND Predicates first, and then combine them using an OR Predicate:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Item> criteriaQuery = criteriaBuilder.createQuery(Item.class);
Root<Item> itemRoot = criteriaQuery.from(Item.class);

Predicate predicateForBlueColor
  = criteriaBuilder.equal(itemRoot.get("color"), "red");
Predicate predicateForGradeA
  = criteriaBuilder.equal(itemRoot.get("grade"), "D");
Predicate predicateForBlueColorAndGradeA
  = criteriaBuilder.and(predicateForBlueColor, predicateForGradeA);

Predicate predicateForRedColor
  = criteriaBuilder.equal(itemRoot.get("color"), "blue");
Predicate predicateForGradeB
  = criteriaBuilder.equal(itemRoot.get("grade"), "B");
Predicate predicateForRedColorAndGradeB
  = criteriaBuilder.and(predicateForRedColor, predicateForGradeB);

Predicate finalPredicate
  = criteriaBuilder
  .or(predicateForBlueColorAndGradeA, predicateForRedColorAndGradeB);
criteriaQuery.where(finalPredicate);
List<Item> items = entityManager.createQuery(criteriaQuery).getResultList();

5. Conclusion

In this article, we used the JPA Criteria API to implement use cases where we needed to combine AND/OR predicates.

As usual, the complete source code for this article is available over on GitHub.