Simple and Clean Queries: Using Criteria API in Java

To perform database operations with Java, we can perform these operations using native query or JPQL, which is the old method. However, this will reduce the readability of the code.

The Criteria API is a query generation tool that comes with Java’s JPA(Java Persistence API), allowing you to write dynamic and type-safe queries. Instead of writing queries directly, such as SQL or JPQL (Java Persistence Query Language), the Criteria API allows you to create queries programmatically.

Especially if we are going to filter according to certain fields in a GET operation, we can use the Criteria API.

Here’s a sample guide on how you can write cleaner and more manageable queries with the Criteria API in a few steps.

Predicate

Predicate is a conditional expression used when creating queries with the Criteria API in Java. Predicate objects define the conditions of the query and each Predicate works like a filter to determine the results returned from the database.

By adding multiple Predicates to a query in the Criteria API, you can make the query dynamic and easily define complex conditions. Predicate objects can be combined with operations such as and or, so you can combine multiple conditions into a single query.

Expressions

equal

Basically checks whether the field is equal to a given value. Used for simple comparisons;

predicates.add(cb.equal(user.get("age"), 25));

notEqual

Checks that a field is not equal to a certain value;

predicates.add(cb.notEqual(user.get("city"), "Istanbul"));

like

Used to retrieve data that matches a specific pattern. It is especially useful in fields of type String. With the ‘%’ sign you can check whether there are certain characters at the beginning or end of the search:

predicates.add(cb.like(user.get("name"), "%John%"));

greaterThan and lessThan

greaterThan allows you to get results that are greater than a value; lessThan gets results that are less than a value. These methods are used for numerical and historical comparisons:

predicates.add(cb.greaterThan(user.get("age"), 18));

greaterThanOrEqualTo and lessThanOrEqualTo

These methods are similar to greaterThan and lessThan, but include the specified value:

predicates.add(cb.greaterThanOrEqualTo(user.get("age"), 18));

between

Used to check whether a field is between two values. It is especially useful with numeric or historical data:

cb.between(user.get("createDate"), startDate, endDate);

in

Checks whether a field is included in a specific list. Useful when you want to make multiple comparisons:

predicates.add(user.get("city").in("Istanbul", "Ankara", "Izmir"));

isNull and isNotNull

These methods check whether a field is null:

predicates.add(cb.isNull(user.get("phoneNumber")));

and, or

It allows you to combine multiple conditions. With Predicate objects in the Criteria API, you can define multiple conditions and write flexible queries:

query.select(user).where(cb.and(predicate1, predicate2));

If both conditions are met, the result is returned.

Example Scenario: Filtering Users by Date and City Criteria

Firstly, let’s assume that we have an entity named User. Our goal is to write a query that dynamically filters users according to age and city information.

@Entity
@Getter
@Setter
public class User {
    @Id
    private Long id;
    private String name;
    private Date createDate;
    private String city;
}

Step 0: Controller Class

In this class, we will filter GET requests to the /users endpoint using UserService. At the same time, using Spring Boot’s Pageable class, we will serve the data by limiting the data even if there is excessive data.

If we do not specify the request type in the parameter field, Spring Boot will recognize the fields in this class as @RequestParam.

@RestController
@RequestMapping("/user")
public class UserController {
 
    @Autowired
    private UserService userService;
 
    @GetMapping
    public Page<User> getByFilter(UserFilter filter, Pageable pageable) {
        return userService.findByFilter(filter, pageable);
    }
}

Step 1: Creating CriteriaBuilder and CriteriaQuery Objects

Firstly, we initialise our CriteriaBuilder and CriteriaQuery objects:

@Service
public class UserServiceImpl implements UserService {
 
    @PersistenceContext
    private EntityManager entityManager;
 
    @Override
    public Page<User> findByFilter(UserFilter filter, Pageable pageable) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = cb.createQuery(User.class);
        Root<User> user = query.from(User.class);

Step 2: Add Dynamic Conditions

You can add conditions dynamically with Predicate objects. Let’s update our conditions according to non-empty age and city parameters:

    List<Predicate> predicates = new ArrayList<>();
 
    if (Objects.nonNull(filter.getStartDate()) && Objects.nonNull(filter.getEndDate())) 
        predicates.add(cb.between(user.get("createDate"), filter.getStartDate(), filter.getEndDate()));
        
    if (Objects.nonNull(filter.getCity())) 
        predicates.add(cb.equal(user.get("city"), filter.getCity()));
 
    query.select(user).where(cb.and(predicates.toArray(new Predicate[0])));

Step 3: Get Results

Finally, we run the query and return the result:

   List<User> users = entityManager.createQuery(query)
                .setFirstResult((int) pageable.getOffset())
                .setMaxResults(pageable.getPageSize())
                .getResultList();
 
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<User> countRoot = countQuery.from(User.class);
    countQuery.select(cb.count(countRoot)).where(cb.and(predicates.toArray(new Predicate[0])));
    long totalElements = entityManager.createQuery(countQuery).getSingleResult();
 
    return new PageImpl<>(users, pageable, totalElements);
  }
}

Full code:

@Service
public class UserService {
 
    @PersistenceContext
    private EntityManager entityManager;
 
    @Override
    public Page<User> findByFilter(UserFilter filter, Pageable pageable) {      CriteriaBuilder cb = entityManager.getCriteriaBuilder();
      CriteriaQuery<User> query = cb.createQuery(User.class);
      Root<User> user = query.from(User.class);
 
      List<Predicate> predicates = new ArrayList<>();
 
      if (Objects.nonNull(filter.getStartDate()) && Objects.nonNull(filter.getEndDate())) 
          predicates.add(cb.between(user.get("createDate"), filter.getStartDate(), filter.getEndDate()));
        
      if (Objects.nonNull(filter.getCity())) 
          predicates.add(cb.equal(user.get("city"), filter.getCity()));
 
      query.select(user).where(cb.and(predicates.toArray(new Predicate[0])));
 
      List<User> users = entityManager.createQuery(query)
                .setFirstResult((int) pageable.getOffset())
                .setMaxResults(pageable.getPageSize())
                .getResultList();
 
      CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
      Root<User> countRoot = countQuery.from(User.class);
      countQuery.select(cb.count(countRoot)).where(cb.and(predicates.toArray(new Predicate[0])));
      long totalElements = entityManager.createQuery(countQuery).getSingleResult();
  
      return new PageImpl<>(users, pageable, totalElements);
    }
}

Use Case

GET /users?startDate=2024-01-01&endDate=2024-12-31&city=Istanbul&page=1&size=10&sort=createDate,desc

Return Type

{
  "content": [
    {
      "id": 1,
      "name": "John Doe",
      "city": "Istanbul",
      "createDate": "2024-01-15"
    }
  ],
  "pageable": {
    "sort": {
      "sorted": true,
      "unsorted": false,
      "empty": false
    },
    "pageNumber": 1,
    "pageSize": 10,
    "offset": 10,
    "paged": true,
    "unpaged": false
  },
  "totalPages": 5,
  "totalElements": 50,
  "last": false,
  "size": 10,
  "number": 1,
  "sort": {
    "sorted": true,
    "unsorted": false,
    "empty": false
  },
  "first": false,
  "numberOfElements": 10,
  "empty": false
}

Why Criteria API?

The Criteria API helps you make your queries reusable and dynamic. Managing your code with Predicate objects instead of complex if-else structures increases your query flexibility and I recommend using it especially if you are going to filter for multiple fields in a large database.

Why Pageable?

When we want to retrieve a large number of records from the database, retrieving all the data at once can be inconvenient in terms of performance.
For example, retrieving tens of thousands of user records with a single query will cause heavy resource consumption in the database and slow down the application. In such cases, using Pageable provides a very useful solution to retrieve data piece by piece.

Thank you for reading my article! If you have any questions, feedback or thoughts you would like to share, I would love to hear them in the comments.

You can follow me on Medium for more information on this topic and my other posts. Don’t forget to clap my posts to help them reach more people!

Thank you! 👨‍💻🚀

To follow me on LinkedIn: https://www.linkedin.com/in/tamerardal/