Handling optional search parameters in Criteria API

Question

Imagine I have optional search parameters that can be null.

Is there a more concise alternative for checking each of them explicitly before adding a jakarta.persistence.criteria.Predicate to some local List?

I mean this almost looks nice, fairly readable (though, Criteria is never really pretty):

import org.springframework.data.jpa.domain.Specification;

//...

    private static Specification<User> buildSpecification(FindUserRequestDto userRequestDto) {
        return (root, query, criteriaBuilder) -> {
            var nameBeginsWith = criteriaBuilder.like(root.get("name"), userRequestDto.getName() + "%");
            var ageGreaterThan = criteriaBuilder.greaterThan(root.get("dateOfBirth"), userRequestDto.getDateOfBirth());
            var hasEmail = criteriaBuilder.equal(root.join("emailData").get("email"), userRequestDto.getEmail());
            var hasPhone = criteriaBuilder.equal(root.join("phoneData").get("phone"), userRequestDto.getPhone());
            return criteriaBuilder.and(nameBeginsWith, ageGreaterThan, hasEmail, hasPhone);

But if I introduce an explicit null check for each optional parameter, it'll get uglier, more verbose.

// like so
    private static Specification<User> buildSpecification(FindUserRequestDto userRequestDto) {
        return (root, query, criteriaBuilder) -> {
            var nameBeginsWith = criteriaBuilder.like(root.get("name"), userRequestDto.getName() + "%");
            var bornAfter = criteriaBuilder.greaterThan(root.get("dateOfBirth"), userRequestDto.getDateOfBirth());
            var hasEmail = criteriaBuilder.equal(root.join("emailData").get("email"), userRequestDto.getEmail());
            var hasPhone = criteriaBuilder.equal(root.join("phoneData").get("phone"), userRequestDto.getPhone());

            List<jakarta.persistence.criteria.Predicate> predicates = new ArrayList<>();
            if (userRequestDto.getName() != null) predicates.add(nameBeginsWith);
            if (userRequestDto.getDateOfBirth() != null) predicates.add(bornAfter);
            if (userRequestDto.getEmail() != null) predicates.add(hasEmail);
            if (userRequestDto.getPhone() != null) predicates.add(hasPhone);
            return criteriaBuilder.and(predicates.toArray(new jakarta.persistence.criteria.Predicate[0]));
        };
    }

You could argue it's a lost cause to try and make Criteria not verbose and ugly. But since I'm not very familiar with Criteria, I may be unaware of something. I wish Jakarta's Predicates had some or() method.

Here are my (simplified) entities:

import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;

import java.util.List;

@Entity
@Getter
@Setter
@Table(name = "\"user\"")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    @OneToOne(mappedBy = "user")
    @OneToMany(mappedBy = "user", fetch = FetchType.EAGER,
            cascade = CascadeType.ALL, orphanRemoval = true)
    private List<EmailData> emailData;
}

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;

@Entity
@Getter
@Setter
@Table(name = "email_data")
public class EmailData {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;
    private String email;
}

There is no conceptually-different way of dynamic building of the Criteria. You can implement some generic utility methods that hide null checks inside, but it would be just sort of syntactic sugar — Nikolai Shevchenko
– Nikolai Shevchenko, Commented May 26 at 13:37
You have to deal with null, either in the criteria query or in the database. By pushing it to the DB, you are complicating the SQL generation and pushing more processing to the DB for the only benefit of not seeing null handling in the code. There are reasons to do that, such as having a single cacheable statement that can be more generic, but the push to not see null checks in code always perplexes me. You can use an or statement to check for null in the criteria query, but they generally use query parameters - you are passing in modified strings inline. — Chris
– Chris, Commented May 26 at 14:01

Maria · Accepted Answer · 2025-05-25 21:24:27Z

0

or() is supported by CriteriaBuilder, see the details there: https://www.baeldung.com/jpa-and-or-criteria-predicates

public List<User> searchUsers(String name, Integer age, Boolean active,
      boolean matchAny) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<User> query = cb.createQuery(User.class);
Root<User> root = query.from(User.class);

List<Predicate> predicates = new ArrayList<>();

if (name != null && !name.isEmpty()) {
    predicates.add(cb.like(cb.lower(root.get("name")), "%" + name.toLowerCase() + "%"));
}

if (age != null) {
    predicates.add(cb.equal(root.get("age"), age));
}

if (active != null) {
    predicates.add(cb.equal(root.get("active"), active));
}

if (!predicates.isEmpty()) {
    Predicate finalPredicate = matchAny
        ? cb.or(predicates.toArray(new Predicate[0]))
        : cb.and(predicates.toArray(new Predicate[0]));
    query.where(finalPredicate);
}

return entityManager.createQuery(query).getResultList();

}

answered May 25 at 21:24

Maria

4413 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sergey Zolotarev May 26 at 6:58

Your snippet still contains null checks

Maria May 26 at 15:05

okay, then I just did not get your question, I thought you're wondering about having or(), sorry @SergeyZolotarev

Collectives™ on Stack Overflow

Handling optional search parameters in Criteria API

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related