

Table of Contents
1. Overview
In this article, We will learn Spring JPA dynamic query example. As we all know that we can write Spring JPA query and fetch the data as like:
@Query("SELECT em FROM Employee em WHERE em.employeeId = :employeeId") List<Employee> getEmployeeById(@Param("employeeId") Long employeeId);
But while applying filters, It may require to skips some parameters and based on that fetching the database results, In those cases, we require to generate a dynamic query based on parameters.
In the above query, Let assume that our requirement is like if passed employeeId then fetch only that single employee or If pass a NULL value in an employeeId if then it will fetch all the employee but this scenario simple query (as above) will not work in that case we need to generate a dynamic query.
Steps to Generate Dynamic Query In Spring JPA:
- Extends
JpaSpecificationExecutor
in Repository as like:
import com.javadeveloperzone.model.Employee; import org.springframework.data.jpa.repository.JpaSpecificationExecutor; import org.springframework.data.repository.CrudRepository; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; @Repository @Transactional public interface EmployeeDAO extends CrudRepository<Employee,Long>,JpaSpecificationExecutor<Employee>{ }
2. Write Specification
for the query:
Here we have created List of Predicate, We can add criteria to predicates based on our requirements and using those predicates criteria spring JPA will generate a dynamic query. Here is employeeName will be null then we haven’t added Predicate so it will fetch all the Employees otherwise match to specific names.
public List<Employee> findByCriteria(String employeeName){ return employeeDAO.findAll(new Specification<Employee>() { @Override public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); if(employeeName!=null) { predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeName"), employeeName))); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }
2. Spring JPA dynamic query examples
2.1 JPA Dynamic Criteria with equal
public List<Employee> findByCriteria(String employeeName){ return employeeDAO.findAll(new Specification<Employee>() { @Override public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); if(employeeName!=null) { predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeName"), employeeName))); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }
employeeService.findByCriteria("Harry");
// Fetch only those whose name is Harry
SQL Query: select * from employee where employeeName=?
employeeService.findByCriteria(null);
// Fetch all employees
SQL Query: select * from employee
2.2 JPA dynamic with equal and like
public List<Employee> findByCriteria(String employeeName,String employeeRole){ return employeeDAO.findAll(new Specification<Employee>() { @Override public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); if(employeeName!=null) { predicates.add(criteriaBuilder.and(criteriaBuilder.like(root.get("employeeName"), "%"+employeeName+"%"))); } if(employeeRole!=null){ predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeRole"), employeeRole))); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }
employeeService.findByCriteria("Jo","ADMIN");
// Fetch all names like *Jo* and role is ADMIN
SQL Query: select * from employee where (employeeName like ?) and employeeRole=?
employeeService.findByCriteria(null,"ADMIN");
// Fetch employee whose role is ADMIN
SQL Query: select * from employee where employeeRole=?
employeeService.findByCriteria(null,null);
// Fetch all employees
SQL Query: select * from employee
2.3 JPA dynamic like for multiple fields
public List<Employee> findByLikeCriteria(String text){ return employeeDAO.findAll(new Specification<Employee>() { @Override public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); if(text!=null) { predicates.add(criteriaBuilder.or( criteriaBuilder.like(root.get("employeeName"), "%" + text + "%"), criteriaBuilder.like(root.get("employeeEmail"), "%" + text + "%")) ); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }
employeeService.findByLikeCriteria("info");
// Fetch all employees whole email or name contains “info”
SQL Query: select * from employee where employeeName like ? or employeeEmail like ?
employeeService.findByLikeCriteria(null);
// Fetch all employees
SQL Query: select * from employee
2.4 JPA dynamic Like and between criteria
public List<Employee> findByLikeAndBetweenCriteria(String text,int employeeIdStart, int employeeIdEnd){ return employeeDAO.findAll(new Specification<Employee>() { @Override public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); if(text!=null) { predicates.add(criteriaBuilder.or(criteriaBuilder.like(root.get("employeeName"), "%" + text + "%"), criteriaBuilder.like(root.get("employeeEmail"), "%" + text + "%"))); } if(employeeIdStart!=0 && employeeIdEnd!=0){ predicates.add(criteriaBuilder.between(root.get("employeeId"),employeeIdStart,employeeIdEnd)); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }
List<Employee> findByLikeAndBetweenCriteria(String employeeName, int employeeIdStart, int employeeIdEnd);
SQL Query: select * from employee where (employeeName like ? or employeeEmail like ?) and (employeeId between 15 and 20)
employeeService.findByLikeAndBetweenCriteria("info",0,0);
// Fetch all employees whole email or name contains “info”
SQL Query: select * from employee where employeeName like ? or employeeEmail like ?
employeeService.findByLikeAndBetweenCriteria(null,0,0);
// Fetch all employees
SQL Query: select * from employee
2.5 JPA dynamic query with Paging or Pagination
public List<Employee> findByPagingCriteria(String employeeName,Pageable pageable){ Page page = employeeDAO.findAll(new Specification<Employee>() { @Override public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); if(employeeName!=null) { predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeName"), employeeName))); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }, pageable); page.getTotalElements(); // get total elements page.getTotalPages(); // get total pages return page.getContent(); // get List of Employee }
employeeService.findByPagingCriteria("Jone", new PageRequest(0,10));
In pageRequest, First Param is page number, Second for page size
SQL Query: select * from employee where employeeName=? limit ?
2.6 JPA Dynamic Order
public List<Employee> findByCriteria(String employeeName,String fieldName){ return employeeRepository.findAll(new Specification<Employee>() { @Override public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); if(employeeName!=null) { predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("employeeName"), employeeName))); } query.orderBy(criteriaBuilder.desc(root.get(fieldName))); // for desc // query.orderBy(criteriaBuilder.asc(root.get(fieldName))); // for asc return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }
employeeService.findByCriteria("Harry","joinedDate");
SQL Query: select * from employee where employeeName=? order by joinedDate desc
3. Conclusion
We learned that how we can generate dynamic query or query based on parameters in spring JPA. We have also attached source code with spring boot + JPA Dyanimc query.
4. References
5. Source Code
1 comment. Leave new
All the examples you have mentioned here, fetch all the columns of the table. How to use Predicates and select only a few columns of the table? Also, can we perform aggregation on the selected columns along with using predicates in the where clause?