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 ?

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

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

 

Was this post helpful?
Let us know, if you liked the post. Only in this way, we can improve us.
Yes
No

Leave a Reply

Your email address will not be published. Required fields are marked *