[Spring Data JPA] 3-2. JPA Query Methods (2)

4. Using @Query

• 도메인 클래스로부터 쿼리 로직 분리 (repository에 직접 선언)

public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u WHERE u.lastname = :lastname")
    List<User> findByLastname(@Param("lastname") String lastname);
}

 

@Param

• query method's named parameter (안전성)

public interface UserRepository extends JpaRepository<User, Long> {

  @Query("select u from User u where u.firstname = :firstname or u.lastname = :lastname")
  User findByLastnameOrFirstname(@Param("lastname") String lastname, @Param("firstname") String firstname);                                 
}

 

parameter name 자동 탐지 (Java 8)

•  컴파일러 플래그

tasks.withType(JavaCompile) {
    options.compilerArgs << '-parameters'
}

 

 

Applying a QueryRewriter

• 쿼리 수정 (EntityManager에 보내기 전)

public interface MyRepository extends JpaRepository<User, Long> {

  @Query(value = "select original_user_alias.* from SD_USER original_user_alias",
         nativeQuery = true, queryRewriter = MyQueryRewriter.class)
  List<User> findByNativeQuery(String param);

  @Query(value = "select original_user_alias from User original_user_alias",
         queryRewriter = MyQueryRewriter.class)
  List<User> findByNonNativeQuery(String param);
}

public class MyQueryRewriter implements QueryRewriter {

  @Override
  public String rewrite(String query, Sort sort) {
    return query.replaceAll("original_user_alias", "rewritten_user_alias");
  }
}

 

Using Advanced LIKE Expressions

public interface UserRepository extends JpaRepository<User, Long> {

  @Query("select u from User u where u.firstname like %?1")
  List<User> findByFirstnameEndsWith(String firstname);
}

 

Native Queries

• Native Query 실행 가능 (nativeQuery 속성 켜기)

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)
  User findByEmailAddress(String emailAddress);
}

 

 

Paging

• 카운트 쿼리 정의 필요

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
         countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
         nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

 

5. Using SpEL Expressions

동적 쿼리 생성

#{entityName}

• 도메인 클래스의 이름을 동적으로 쿼리에 삽입할 수 있음

@MappedSuperclass
public abstract class AbstractMappedType {
  String attribute;
}

@Entity
public class ConcreteType extends AbstractMappedType {
  // 필드와 메서드 정의
}

@NoRepositoryBean
public interface MappedTypeRepository<T extends AbstractMappedType> extends Repository<T, Long> {
  @Query("select t from #{#entityName} t where t.attribute = ?1")
  List<T> findAllByAttribute(String attribute);
}

public interface ConcreteRepository extends MappedTypeRepository<ConcreteType> {
  // 구체적인 리포지토리 메서드 정의
}

 

동적 매개변수 조작

매개변수

• 이름 or 인덱스로 접근 가능

@Query("select u from User u where u.firstname = ?1 and u.firstname = ?#{[0]} and u.emailAddress = ?#{principal.emailAddress}")
List<User> findByFirstnameAndCurrentUserWithCustomQuery(String firstname);

 

Like 조건

• 와일드카드 추가 가능

@Query("select u from User u where u.lastname like %:#{#lastname}% and u.lastname like %:lastname%")
List<User> findByLastnameWithSpelExpression(@Param("lastname") String lastname);

 

Sanitizing

• 입력값 세척 (입력 공격 방지)

@Query("select u from User u where u.firstname like %?#{escape([0])}% escape ?#{escapeCharacter()}")
List<User> findContainingEscaped(String namePart);

• escape(): '_', '%' 이스케이프 처리
• escapeCharacter(): 이스케이프할 문자 설정

 

출처

• Spring Data JPA - JPA Query Methods