[Spring JPA] IN절 query

 

1. Spring JPA query IN clause example

@Repository
@Transactional
public interface EmployeeDAO extends JpaERepository<Employee, Integer> {
    // 1. Spring JPA In Cause using method name
    List<Employee> findByEmployeeNameIn(List<String> names);
   
    // 2. Spring JPA In cause using @Query
    @Query("SELECT e FROM Employee e WHERE e.employeeName IN (:names)")
    List<Employee> findByEmployeeNames(@Param("names") List<String> names);
    
    // 3. Spring JPA In cause using native query
    @Query(nativeQuery = true, value = "SELECT * FROM Employee as e WHERE e.employeeName IN (:names)")
    List<Employee> findByEmployeeName(@Param("names") List<String> names);
}

 

2. Spring JPA query NOT IN clause example

@Repository
@Transactional
public interface EmployeeDAO extends JpaRepository<Employee, Integer> {
    
    // 1. Spring JPA In cause using method name
    List<Employee> findByEmployeeNameNotIn(List<String> names);
    
    // 2. Spring JPA In cause using @Query
    @Query("SELECT e FROM Employee e WHERE e.employeeName NOT IN (:names)")
    List<Employee> findByEmployeeNamesNot(@Param("names") List<String> names);
    
    // 3. Spring JPA In cause using native query
    @Query(nativeQuery = true, value = "SELECT * FROM Employee as e WHERE e.employeeName NOT IN (:names)")
    List<Employee> findByEmployeeNameNot(@Param("names") List<String> names);
}

 

3. Spring JPA dynamic IN 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.dd(root.get("employeeName").in(names));
            }
            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
        });
    }
}

 

4. Spring JPA dynamic NOT IN 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()]));
        }
    });
}

 

 

https://javadeveloperzone.com/spring/spring-jpa-query-in-clause-example/

Spring JPA query IN clause example - Java Developer Zone