Multi-parameter JPA API

Question

I need to create a search method that uses the JPA API with several parameters. Now the problem is that not every parameter is required. Thus, some may be empty, and they should not be included in the request. I tried this with CriteriaBuilder, but I couldn't figure out how to make it work.

With the Hibernate Criteria API, it's pretty simple. Just create criteria and then add constraints.

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

How can I achieve the same with the JPA API?

Answer 1

, javax.persistence.Predicate, , :

:

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

:

    //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

JPA Criteria API. .

:

:

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

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));

, :

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

: entityManager - EJB/Service/DAO.

Answer 3

. , , :

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

:

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

- .

Answer 4

-, . Upvote .

, .

JobTitle ().

( ), , .

SQL :

* dbo.Employee e dbo.JobTitle jt e.EmployeeKey = jt.EmployeeKey WHERE (jt.JobTitleName = 'programmer' jt.JobTitleName = 'badcop')

, ( "") )

JPA

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;
    }
}

. ToArray ( Predicate [] {}), , varargs. ( )

" ".

:

JPA

JPA CriteriaBuilder

Answer 5

Spring -:

Specification<User> specification = (root, query, builder) -> {
    List<Predicate> predicates = new ArrayList<>();
    if (criteria.getName() != null) {
        // like
        predicates.add(builder.like(root.get("name"), "%" + criteria.getName() + "%"));
    }
    if (criteria.getParentId() != 0) {
        // equal
        predicates.add(builder.equal(root.get("parent"), criteria.getParentId()));
    }

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

repository.findAll(specification);