JPA Criteria API with multiple parameters
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();
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.
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);