jpa-and-or-criteria-predicates

Posted on: 2019-10-08 2019-11-19
Tags: JPA

Combining JPA And/Or Criteria Predicates

1. Overview

The JPA Criteria API can be used to easily 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 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 the use case where we need to find items having both:

red or blue color
AND
A or B grade

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

To begin, let’s 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 a 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, let’s 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 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

On the other hand, let’s consider the case where we need to find items having either:

red color and grade D
OR
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 tutorial, we used the JPA Criteria API to implement use cases where we needed to combine AND/OR predicates.

As usual, the complete source code used for this tutorial is over on GitHub.

getdocs

3043