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 {@PersistenceContextprivate EntityManager entityManager;@Overridepublic 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();}@Overridepublic 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)@Modifyingvoid 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);