Spring Boot with CriteriaBuilder for Dynamic Search Queries:
Spring Boot with CriteriaBuilder for Dynamic Search Queries:
When you are developing an enterprise application, you have to provide a search option dynamically with “and” condition. Our grand father way to do is using stringbuilder to construct the query in java and return the result.
But hibernate has provided criteria for the same with better performance. But in spring boot you can use JPA to get the better results.
In your service class you can use persistencecontext to get the entitymanager, which is needed to get the criteriaBuilder.
Table of Contents
MyServiceClassImpl.java:
[java]
@Service
public class MyServiceClassImpl implements MyServiceInterface {
@PersistenceContext
private EntityManager em;
@Override
public List<AffiliateUrl> searchByParams(AffiliateUrl affiliateUrl) {
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<AffiliateUrl> criteriaQuery = criteriaBuilder.createQuery(AffiliateUrl.class); Root<AffiliateUrl> affiliateUrlRoot = criteriaQuery.from(AffiliateUrl.class); criteriaQuery.select(affiliateUrlRoot);
final List<Predicate> predicates = new ArrayList<Predicate>();
if (affiliateUrl.getShop_home_url() != null && !affiliateUrl.getShop_home_url().isEmpty()) {
predicates.add(criteriaBuilder.and(criteriaBuilder.equal(affiliateUrlRoot.get(“shop_home_url”), affiliateUrl.getShop_home_url())));
}
if (affiliateUrl.getAffiliate_network_name() != null && !affiliateUrl.getAffiliate_network_name().isEmpty()) { predicates.add(criteriaBuilder.and(criteriaBuilder.equal(affiliateUrlRoot.get(“affiliate_network_name”), affiliateUrl.getAffiliate_network_name())));
}
criteriaQuery.select(affiliateUrlRoot)criteriaQuery.select(affiliateUrlRoot) .where(criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]))); List<AffiliateUrl> results = em.createQuery(criteriaQuery).getResultList();
return results;
}
}
[/java]
What Am I doing here ?
- Get EntityManager using PersistenceContext annotation.
- Get CriteriaBuilder Object using the entitymanager [em.getCriteriaBuilder()].
- Get Root of your entity class using criteriaQuery from class.
- Now criteriaQuery have your entity, we have to write a predicates with and/or/like etc to get the required results.
- Have a check and add your criteria with and/or/whatever you need to predicates list.
- Finally input the predicates list constructed to criteriaBuilder.
- Use createQuery method with the constructed criteriaQuery to get the result in List<AffiliateUrl>.
here get(“affiliate_network_name”) should match with the member name of the AffiliateUrl Entity.
MyServiceInterface.java will looks like :
[java]
public interface MyServiceInterface {
public List<AffiliateUrl> searchByParams(AffiliateUrl affiliateUrl);
}
[/java]
AffiliateUrl is my entity class here, I am going to fetch the result from this class only through JPA.
The above snippets will function this way:
1. Basically I have two field named shop_home_url and affiliate_network_name
2. If I have given the values for both the field then it will construct with “and” and returns the result which matches both the criteria.
3. If I input anyone then the other condition skipped and results returned accordingly.
4. I have tested it for my requirement and it works fine.
JPA predicate can be used for “in” condition like this,
[java]
Expression<String> exp = affiliateUrlRoot.get(“affiliate_network_name”);
Predicate predicate = exp.in(tempNetworkNmLstSelected); criteriaQuery.where(predicate);
predicates.add(predicate);
[/java]
predicates has the list of predicate, where everything can be given to criteriabuilder like this and get it executed:
[java]
criteriaQuery.select(affiliateUrlRoot) .where(criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])));
[/java]
Here tempNetworkNmLstSelected is a list of strings which can be passed to the in values, something like (select * from tempTable where id in (X) etc..)
Feel free to write your query in the below comments section to help you better.