JPA Criteria API with multiple parameters

Question

I need to make a search method that uses the JPA Criteria API with multiple parameters. Now the problem is that not every parameter is required. So some could be null, and they shouldn't be included in the query. I've tried this with the CriteriaBuilder but I couldn't see how to make it work.

With the Hibernate Criteria API this is fairly easy. Just create the criteria and then add Restrictions.

Criteria criteria = session.createCriteria(someClass.class);
if(someClass.getName() != null) {
   criteria.add(Restrictions.like("name", someClass.getName());
}

How could I achieve the same with JPA's Criteria API?

Answer 1

Concept is to construct array of javax.persistence.Predicate which contains only predicates we want to use:

Example entity to be queried:

@Entity
public class A {
    @Id private Long id;    
    String someAttribute;
    String someOtherAttribute;
    ...
}

Query itself:

    //some parameters to your method
    String param1 = "1";
    String paramNull = null;

    CriteriaBuilder qb = em.getCriteriaBuilder();
    CriteriaQuery cq = qb.createQuery();
    Root<A> customer = cq.from(A.class);

    //Constructing list of parameters
    List<Predicate> predicates = new ArrayList<Predicate>();

    //Adding predicates in case of parameter not being null
    if (param1 != null) {
        predicates.add(
                qb.equal(customer.get("someAttribute"), param1));
    }
    if (paramNull != null) {
        predicates.add(
                qb.equal(customer.get("someOtherAttribute"), paramNull));
    }
    //query itself
    cq.select(customer)
            .where(predicates.toArray(new Predicate[]{}));
    //execute query and do something with result
    em.createQuery(cq).getResultList();

Answer 2

Take a look at this site JPA Criteria API. There are plenty of examples.

Update: Providing a concrete example

Let's search for Accounts with a balance lower than a specific value:

SELECT a FROM Account a WHERE a.balance < :value

First create a Criteria Builder

CriteriaBuilder builder = entityManager.getCriteriaBuilder();

CriteriaQuery<Account> accountQuery = builder.createQuery(Account.class);
Root<Account> accountRoot = accountQuery.from(Account.class);
ParameterExpression<Double> value = builder.parameter(Double.class);
accountQuery.select(accountRoot).where(builder.lt(accountRoot.get("balance"), value));

To get the result set the parameter(s) and run the query:

TypedQuery<Account> query = entityManager.createQuery(accountQuery);
query.setParameter(value, 1234.5);
List<Account> results = query.getResultList();

BTW: The entityManager is injected somewhere in an EJB/Service/DAO.

Answer 3

A simple solution for Spring, using lambda expressions:

Specification<User> specification = (root, query, builder) -> {
    List<Predicate> predicates = new ArrayList<>();

    // like
    predicates.add(builder.like(root.get("name"), "%test%"));

    // equal
    predicates.add(builder.equal(root.get("parent_id"), 99L);


    // AND all predicates
    return builder.and(predicates.toArray(new Predicate[0]));
};

repository.findAll(specification);

Answer 4

Mikko's answer worked beautifully. Only change I needed to do, was to replace:

cq.select(customer).where(predicates.toArray(new Predicate[]{}));

with:

Predicate [] predicatesarr = predicates.toArray(new Predicate[predicates.size()]); 
cq.select(customer).where(predicatesarr);

Somewhere the conversion from list to array in the original did not work.

Answer 5

First, Mikko's answer got me to my answer. Upvote for that.

My scenario was I wanted to parent/child relationship and I wanted to find a match on ~any~ child.

Employee has multiple JobTitle(s).

I wanted to find an employee (where the has many job titles), but find it on ~any of the jobtitles I send in.

SQL would look like:

Select * from dbo.Employee e join dbo.JobTitle jt on e.EmployeeKey = jt.EmployeeKey WHERE ( jt.JobTitleName = 'programmer' OR jt.JobTitleName = 'badcop' )

I threw in gender and date-of-birth to complete the example (and give more "optional") criteria)

My JPA code

import org.apache.commons.lang3.StringUtils;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;

public class MyEmployeeSpecification implements Specification<MyEmployee> {

    private MyEmployee filter;

    public MyEmployeeSpecification(MyEmployee filter) {
        super();
        this.filter = filter;
    }

    public Predicate toPredicate(Root<MyEmployee> root, CriteriaQuery<?> cq,
                                 CriteriaBuilder cb) {

        Predicate returnPred = cb.disjunction();

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

        if (filter.getBirthDate() != null) {
            patientLevelPredicates.add(
                    cb.equal(root.get("birthDate"), filter.getBirthDate()));
        }

        if (filter.getBirthDate() != null) {
            patientLevelPredicates.add(
                    cb.equal(root.get("gender"), filter.getGender()));
        }

        if (null != filter.getJobTitles() && filter.getJobTitles().size() > 0) {

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

            Join<JobTitle, JobTitle> hnJoin = root.join("jobtitles");

            for (JobTitle hnw : filter.getJobTitles()) {
                if (null != hnw) {
                    if (StringUtils.isNotBlank(hnw.getJobTitleName())) {
                        jobTitleLevelPredicates.add(cb.equal(hnJoin.get("getJobTitleName"), hnw.getFamily()));
                    }
                }
            }

            patientLevelPredicates.add(cb.or(jobTitleLevelPredicates.toArray(new Predicate[]{})));
        }

        returnPred = cb.and(patientLevelPredicates.toArray(new Predicate[]{}));

        return returnPred;
    }
}

But I figured mine out because of predicates.toArray(new Predicate[]{}) , aka, the varargs trick. (Thanks Mikko)

I'm also doing the "implements Specifiction" method.

Other helpful links:

JPA Specifications by Example

JPA CriteriaBuilder conjunction criteria into a disjunction criteria