Spring Data JPA Query ( Native, Pageable, Sorting, Dynamic )

1. JPQL

@Query(**”SELECT u FROM User u WHERE u.status = 1”**)
Collection<User> findAllActiveUsers();

2. JPQL Native Query

@Query(
 value = “SELECT * FROM USERS u WHERE u.status = 1”, 
 nativeQuery = true)
Collection<User> findAllActiveUsersNative();

3. Sorting JpaReository

userRepository.findAll(new Sort(Sort.Direction.ASC, “name”));
// OR 
userRepository.findAll(new Sort(“name”));
// new Sort(“LENGTH(name)”) 지원지 않음. 
// 이름 길이 정렬을 하려면 JpaSort.unsafe() 사용 해야한다. 
userRepository.findAll(JpaSort.unsafe(“LENGTH(name)”));

4. JPQL Sorting

• @ Query를 이용한 Native Qurey 를 사용하며 Sorting 을 사용할 수 없음

@Query(value = “SELECT u FROM User u”)
List<User> findAllUsers(Sort sort);
//call method
userRepository.findAllUsers(new Sort(“name”));
//OR 
//이름 길이 별로 정럴
userRepository.findAllUsers(JpaSort.unsafe(“LENGTH(name)”));

5. Pagination JpaReository

userRepository.findAll(new Pagerable(1,20));
// OR 
userRepository.findAll(new Pagerable(1,20,Sort.Direction.ASC, “name”));
// OR 
// 정렬 원하는 필드는 여러개 추가 가능.
userRepository.findAll(new Pagerable(1,20,Sort.Direction.ASC, “name”, “zipCode”));

6. Pagination JPQL

@Query(value = “SELECT u FROM User u ORDER BY id”)
Page<User> findAllUsersWithPagination(Pageable pageable);

7. Pagination JPQL Native — Spring Data JPA 2.0.4 이하

@Query(
 value = “SELECT * FROM Users ORDER BY id”, 
 countQuery = “SELECT count(*) FROM Users”, 
 nativeQuery = true)
Page<User> findAllUsersWithPagination(Pageable pageable);

8. Pagination JPQL Native — Spring Data JPA 2.0.4 이상

@Query(
 value = “SELECT * FROM Users ORDER BY id \n — #pageable\n”,
 countQuery = “SELECT count(*) FROM Users”,
 nativeQuery = true)
Page<User> findAllUsersWithPagination(Pageable pageable);

9. Param JPQL

@Query(“SELECT u FROM User u WHERE u.status = ?1”)
User findUserByStatus(Integer status);
// 순서대로 파라미터 적용 
// 1에는 status 2에는 name
@Query(“SELECT u FROM User u WHERE u.status = ?1 and u.name = ?2”)
User findUserByStatusAndName(Integer status, String name);

10. Param JPQL Native

• JPQL 기본 쿼리와 동일.

@Query(
 value = “SELECT * FROM Users u WHERE u.status = ?1”, 
 nativeQuery = true)
User findUserByStatusNative(Integer status);

11. Param JPQL

@Query(“SELECT u FROM User u WHERE u.status = :status and u.name = :name”)
User findUserByStatusAndNameNamedParams(
 @Param(“status”) Integer status, 
 @Param(“name”) String name);
// 변수 명과 파라미터 명의 같지 않아도됨. 
@Query(“SELECT u FROM User u WHERE u.status = :status and u.name = :name”)
User findUserByUserStatusAndUserName(@Param(“status”) Integer userStatus, 
 @Param(“name”) String userName);

12. Param JQPL Native

• JPQL 기본 쿼리와 동일.

@Query(value = “SELECT * FROM Users u WHERE u.status = :status and u.name = :name”, 
 nativeQuery = true)
User findUserByStatusAndNameNamedParamsNative(
 @Param(“status”) Integer status, @Param(“name”) String name);

13. SQL ( IN, NOT IN ) JPQL

// Collection 을 지원하므로 List, HashSet 등 을 사용할수 있다. 
@Query(value = “SELECT u FROM User u WHERE u.name IN :names”)
List<User> findUserByNameList(@Param(“names”) Collection<String> names);

14. @Modifying JPQL

• insert OR update 쿼리시 사용

//Update
@Modifying
@Query(“update User u set u.status = :status where u.name = :name”)
int updateUserSetStatusForName(@Param(“status”) Integer status, 
 @Param(“name”) String name);
//Insert 
@Modifying
@Query(value = “insert into Users (name, age, email, status) values (:name, :age, :email, :status)”,
 nativeQuery = true)
void insertUser(@Param(“name”) String name, @Param(“age”) Integer age, 
 @Param(“status”) Integer status, @Param(“email”) String email);

15. @Modifying JPQL Native

• JPQL 기본 쿼리와 동일.

@Modifying
@Query(value = “update Users u set u.status = ? where u.name = ?”, 
 nativeQuery = true)
int updateUserSetStatusForNameNative(Integer status, String name);

16. Dynamic Query

• interface 생성

import java.util.Collection;
import java.util.List;
import java.util.Set;
import java.util.function.Predicate;
import com.test.spring.data.persistence.model.User;
public interface UserRepositoryCustom {
List<User> findUserByEmails(Set<String> emails);
List<User> findAllUsersByPredicates(Collection<Predicate<User>> predicates);
}

• inserface를 상속받은 클래스 생성

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import com.test.spring.data.persistence.model.User;
public class UserRepositoryCustomImpl implements UserRepositoryCustom {
@PersistenceContext
private EntityManager entityManager;
@Override
public List<User> findUserByEmails(Set<String> emails) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<User> query = cb.createQuery(User.class);
Root<User> user = query.from(User.class);
Path<String> emailPath = user.get(“email”);
List<Predicate> predicates = new ArrayList<>();
for (String email : emails) {
predicates.add(cb.like(emailPath, email));
}
query.select(user)
.where(cb.or(predicates.toArray(new Predicate[predicates.size()])));
return entityManager.createQuery(query)
.getResultList();
}
@Override
public List<User> findAllUsersByPredicates(Collection<java.util.function.Predicate<User>> predicates) {
List<User> allUsers = entityManager.createQuery(“select u from User u”, User.class).getResultList();
Stream<User> allUsersStream = allUsers.stream();
for (java.util.function.Predicate<User> predicate : predicates) {
allUsersStream = allUsersStream.filter(predicate);
}
return allUsersStream.collect(Collectors.toList());
}
}

• JpaReposictory 상속 받은 interface 에 UserRepositoryCustomImpl 를 추가

import java.time.LocalDate;
import java.util.Collection;
import java.util.List;
import java.util.stream.Stream;
import com.baeldung.spring.data.persistence.model.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
public interface UserRepository extends JpaRepository<User, Integer>, UserRepositoryCustom {
Stream<User> findAllByName(String name);
@Query(“SELECT u FROM User u WHERE u.status = 1”)
Collection<User> findAllActiveUsers();
@Query(“select u from User u where u.email like ‘%@gmail.com’”)
List<User> findUsersWithGmailAddress();
@Query(value = “SELECT * FROM Users u WHERE u.status = 1”, nativeQuery = true)
Collection<User> findAllActiveUsersNative();
@Query(“SELECT u FROM User u WHERE u.status = ?1”)
User findUserByStatus(Integer status);
@Query(value = “SELECT * FROM Users u WHERE u.status = ?1”, nativeQuery = true)
User findUserByStatusNative(Integer status);
@Query(“SELECT u FROM User u WHERE u.status = ?1 and u.name = ?2”)
User findUserByStatusAndName(Integer status, String name);
@Query(“SELECT u FROM User u WHERE u.status = :status and u.name = :name”)
User findUserByStatusAndNameNamedParams(@Param(“status”) Integer status, @Param(“name”) String name);
@Query(value = “SELECT * FROM Users u WHERE u.status = :status AND u.name = :name”, nativeQuery = true)
User findUserByStatusAndNameNamedParamsNative(@Param(“status”) Integer status, @Param(“name”) String name);
@Query(“SELECT u FROM User u WHERE u.status = :status and u.name = :name”)
User findUserByUserStatusAndUserName(@Param(“status”) Integer userStatus, @Param(“name”) String userName);
@Query(“SELECT u FROM User u WHERE u.name like ?1%”)
User findUserByNameLike(String name);
@Query(“SELECT u FROM User u WHERE u.name like :name%”)
User findUserByNameLikeNamedParam(@Param(“name”) String name);
@Query(value = “SELECT * FROM users u WHERE u.name LIKE ?1%”, nativeQuery = true)
User findUserByNameLikeNative(String name);
@Query(value = “SELECT u FROM User u”)
List<User> findAllUsers(Sort sort);
@Query(value = “SELECT u FROM User u ORDER BY id”)
Page<User> findAllUsersWithPagination(Pageable pageable);
@Query(value = “SELECT * FROM Users ORDER BY id”, countQuery = “SELECT count(*) FROM Users”, nativeQuery = true)
Page<User> findAllUsersWithPaginationNative(Pageable pageable);
@Modifying
@Query(“update User u set u.status = :status where u.name = :name”)
int updateUserSetStatusForName(@Param(“status”) Integer status, @Param(“name”) String name);
@Modifying
@Query(value = “UPDATE Users u SET u.status = ? WHERE u.name = ?”, nativeQuery = true)
int updateUserSetStatusForNameNative(Integer status, String name);
@Query(value = “INSERT INTO Users (name, age, email, status, active) VALUES (:name, :age, :email, :status, :active)”, nativeQuery = true)
@Modifying
void insertUser(@Param(“name”) String name, @Param(“age”) Integer age, @Param(“email”) String email, @Param(“status”) Integer status, @Param(“active”) boolean active);
@Modifying
@Query(value = “UPDATE Users u SET status = ? WHERE u.name = ?”, nativeQuery = true)
int updateUserSetStatusForNameNativePostgres(Integer status, String name);
@Query(value = “SELECT u FROM User u WHERE u.name IN :names”)
List<User> findUserByNameList(@Param(“names”) Collection<String> names);
void deleteAllByCreationDateAfter(LocalDate date);
@Modifying(clearAutomatically = true, flushAutomatically = true)
@Query(“update User u set u.active = false where u.lastLoginDate < :date”)
void deactivateUsersNotLoggedInSince(@Param(“date”) LocalDate date);
@Modifying(clearAutomatically = true, flushAutomatically = true)
@Query(“delete User u where u.active = false”)
int deleteDeactivatedUsers();
@Modifying(clearAutomatically = true, flushAutomatically = true)
@Query(value = “alter table USERS add column deleted int(1) not null default 0”, nativeQuery = true)
void addDeletedColumn();
}

• findUserByEmails Method Call

Set<String> emails = new HashSet<>();
userRepository.findUserByEmails(emails);