Native Query

1. Native Query

---

• 가급적 네이티브 쿼리는 사용하지 않는게 좋음, 정말 어쩔 수 없을 때 사용
• 최근에 나온 궁극의 방법 --> 스프링 데이터 Projections 활용
• Repository

@Query(value = "select * from member where userName = ?", nativeQuery = true)
Member findByNativeQuery(String userName);

• Test

Member result = memberRepository.findByNativeQuery("m1");
System.out.println("result = " + result);

• SQL

    select
        * 
    from
        member 
    where
        user_name = ?

 

2. 스프링 데이터 JPA 기반 네이티브 쿼리

• 페이징 지원
• 반환 타입
  
  • Object[]
  • Tuple
  • DTO(스프링 데이터 인터페이스 Projections 지원)
• 제약
  
  • Sort 파라미터를 통한 정렬이 정상 동작하지 않을 수 있음(믿지 말고 직접 처리)
  • JPQL처럼 애플리케이션 로딩 시점에 문법 확인 불가
  • 동적 쿼리 불가

 

3. Projections 활용

• Interface

public interface MemberProjection {

    Long getId();
    String getUserName();
    String getTeamName();

}

• Repository

 @Query(value = "select m.member_id as id, m.userName, t.name as teamName " +
            "from member m left join team t",
    countQuery = "select count(*) from member",
    nativeQuery = true)
    Page<MemberProjection> findByNativeProjection(Pageable pageable);

• Test

   @Test
    public void nativeQuery(){
        Team teamA = new Team("teamA");
        em.persist(teamA);

        Member m1 = new Member("m1", 0, teamA);
        Member m2 = new Member("m2", 0, teamA);
        em.persist(m1);
        em.persist(m2);

        em.flush();
        em.clear();

        //when
        Page<MemberProjection> result = memberRepository.findByNativeProjection(PageRequest.of(0, 10));
        List<MemberProjection> content = result.getContent();
        for (MemberProjection memberProjection : content) {
            System.out.println("memberProjection.getUserName() = " + memberProjection.getUserName());
            System.out.println("memberProjection.getTeamName() = " + memberProjection.getTeamName());
        }
    }

 

 

4. 동적 네이티브 쿼리

---

하이버네이트를 직접 활용
스프링 JdbcTemplate, myBatis, jooq같은 외부 라이브러리 사용

 

 

5.GitHub : 210901 NativeQuery

---

GitHub - bsh6463/SpringDataJpa