

Table of Contents
1. Overview
In this article we will learn, Spring JPA query IN clause example or Spring JPA IN or NOT IN query with an example. In SQL query, when we require to select multiple records which based on multiple values IN cause is used. and same ways we want to ignore multiple records based on multiple values at that NOT IN query is used, For example:
SELECT * FROM employee WHERE id IN (1,2,3...)
Above query will select all those employees whose id is 1 or 2 or 3.
While working with spring data JPA we require List
or Collection
of those records that we require to pass in IN clause. Spring data JPA support IN queries using method name, @Query annotation, or native query.
Here is a complete example of spring JPA with the database.
2. Spring JPA query IN and NOT IN query examples
2.1 Spring JPA query IN clause example
In bellow repository, we have defined three methods:
- In the first method, we have fetched records using the method name, When we define a method with a specified naming conversation then spring JPA will automatically generate the query at runtime and return the result.
- In the second method, We have used
@Query
and passednames
as a parameterList
. - In the third and last method, We have used the native query in which, nativeQuery = true means
value
contains the native query.
@Repository @Transactional public interface EmployeeDAO extends JpaRepository<Employee,Integer> { List<Employee> findByEmployeeNameIn(List<String> names); // 1. Spring JPA In cause using method name @Query("SELECT e FROM Employee e WHERE e.employeeName IN (:names)") // 2. Spring JPA In cause using @Query List<Employee> findByEmployeeNames(@Param("names")List<String> names); @Query(nativeQuery =true,value = "SELECT * FROM Employee as e WHERE e.employeeName IN (:names)") // 3. Spring JPA In cause using native query List<Employee> findByEmployeeName(@Param("names") List<String> names); }
Output:
java.util.List<String> names = new ArrayList<>(); names.add("Jone"); names.add("Harry"); List<Employee> employees = employeeDAO.findByEmployeeNameIn(names);
Query:
SELECT * FROM employee WHERE employeeName IN (? , ?)
2.2 Spring JPA query NOT IN clause example
Three different ways to write NOT IN
query in Spring JPA as below:
@Repository @Transactional public interface EmployeeDAO extends JpaRepository<Employee,Integer> { List<Employee> findByEmployeeNameNotIn(List<String> names); // Spring JPA In cause using method name @Query("SELECT e FROM Employee e WHERE e.employeeName NOT IN (:names)") // Spring JPA In cause using @Query List<Employee> findByEmployeeNamesNot(@Param("names")List<String> names); @Query(nativeQuery =true,value = "SELECT * FROM Employee as e WHERE e.employeeName NOT IN (:names)") // Spring JPA In cause using native query List<Employee> findByEmployeeNameNot(@Param("names") List<String> names); }
Output:
java.util.List<String> names = new ArrayList<>(); names.add(“Harry”); List<Employee> employees = employeeDAO.findByEmployeeNameNotIn(names);
Query:
SELECT * FROM employee WHERE employeeName NOT IN (? , ?)
2.3 Spring JPA dynamic IN Query
Here we have defined ways to write dynamic IN query, for example, if we passed List
of names then it will return result based on those names and if we pass the empty or null value in list parameter then it will fetch all the records. Here are more examples of Spring JPA dynamic query.
public List<Employee> findByInCriteria(List<String> names){ return employeeDAO.findAll(new Specification<Employee>() { @Override public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); if(names!=null && !names.isEmpty()) { predicates.add(root.get("employeeName").in(names)); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }
2.4 Spring JPA dynamic NOT IN Query
Here we have defined the way to write dynamic NOT IN
query. Means if list contains any value then it will be considered as where cause otherwise it will be ignored. Here are more examples of Spring JPA dynamic query.
public List<Employee> findByInCriteria(List<String> names){ return employeeDAO.findAll(new Specification<Employee>() { @Override public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); if(names!=null && !names.isEmpty()) { predicates.add(criteriaBuilder.not(root.get("employeeName").in(names))); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }); }
3. Conclusion
In this article, We learn that ways write IN and NOT IN query in spring JPA, We learn three ways to write JPA query like using IN and NOT IN query using method name, using @Query parameter and using native query. We also learn to write dynamic IN and NOT IN query in spring data JPA.