Spring JPA dynamic query example

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:

1. 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

• Spring JPA Specifications document
• Spring JPA documentation

5. Source Code

spring-boot-jpa-dynamic-query-example (54 KB)