[JPA] 네이티브 SQL

1. 네이티브 SQL의 소개와 필요성

- 다양한 이유로 JPQL을 사용할 수 없는 상황에서 특정 DB에 종속적인 SQL을 직접 사용할 수 있도록 하는 기능이다.

- JPQL이 아닌 SQL을 이용해도 영속성 컨텍스트에 접근할 수 있는 방법을 제공한다.★

- 특정 DB에 종속적인 기능을 JPA가 해결하는 예로는 다음과 같은 것들이 있다.

• 특정 DB에서만 사용하는 함수 : 네이티브 SQL 함수를 사용하는 방법, Hibernate가 방언에 정의해둔 함수를 사용하는 방법, 함수를 직접 정의하는 방법이 있다.
• 특정 DB에서만 지원하는 SQL 쿼리 힌트 : Hibernate를 포함한 몇몇 JPA 구현체들이 지원한다.
• 특정 DB에서만 지원하는 문법 : 너무 종속적인 SQL 문법은 지원하지 않으므로 네이티브 SQL를 사용해야 한다.
• 인라인뷰(From절 서브쿼리), INTERSECT, UNION : Hibernate는 지원하지 않지만 일부 JPA 구현체들이 지원한다.
• 스토어드 프로시저 : JPQL에서 호출 가능하다.

- 네이티브 SQL은 상대적으로 후순위로 고려되는 기술이다.

표준 JPQL > JPA 구현체 > 네이티브 SQL > MyBatis 또는 JdbcTemplate 혼용

 

 

 

2. 네이티브 쿼리 API

- EntityManager의 createNativeQuery() 메소드를 사용한다.

- JPQL과 달리 위치 기반 파라미터만 지원한다.

※ Hibernate는 이름 기반 파라미터도 지원하기 때문에 Hibernate에서는 이름 기반 파라미터로 변경해도 동작한다.

 

1) 엔티티 조회

public Query createNativeQuery(string sql, Class resultClass);

- 첫 번째 파라미터에는 SQL문을 입력하고 두 번째 파라미터는 조회할 엔티티 클래스 타입을 지정한다. 타입 정보를 주었더라도 반환 타입은 Query다.

사용 예시

String sql = "SELECT ID, AGE, NAME, TEAM_ID FROM MEMBER WHERE AGE < ?1"; // 파라미터는 ?로 남겨둔다.
List<Member> result = em.createNativeQuery(sql, Member.class)
    .setParameter(1, 30)
    .getResultList();

 

 

2) 값 조회

public Query createNativeQuery(string sql);

- 결과 타입을 정의할 수 없는 경우에는 단순히 값으로 조회할 수 있다.

- 엔티티 클래스가 지정되지 않았기 때문에 조회한 값들은 Object[] 배열에 담긴다.

- 조회 결과가 영속성 컨텍스트에 저장되지 않는다.

사용 예시

String sql = "SELECT ID, AGE, NAME, TEAM_ID FROM MEMBER WHERE AGE < ?1"; // 파라미터는 ?로 남겨둔다.
List<Object[]> result = em.createNativeQuery(sql)
    .setParameter(1, 30)
    .getResultList();

 

 

3) 결과 매핑 사용

public Query createNativeQuery(string sql, String resultSetMapping);

- 엔티티와 값을 함께 조회하는 경우처럼 매핑이 복잡해지면 결과 매핑을 사용하는 것이 좋다.

- @SqlResultSetMapping을 정의해서 결과 매핑을 사용할 수 있다. @SqlResultMapping의 entities 속성에 매핑할 엔티티 클래스 또는 필드들을 지정하고, columns 속성에는 테이블에 존재하지 않는 프로젝션 값들을 지정할 수 있다.

 

@SqlResultSetMapping 정의 예시

@SqlResultSetMapping(
    name = "OrderResults", // 결과 매핑 이름
    entities = {
        @EntityResult(entityClass = com.acme.Order.class, fields={
            @FieldResult(name="id", column="order_id"),
            @FieldResult(name="quantity", column="order_quantity"),
            @FieldResult(name="item", column="order_item")
        })
    },
    columns = {
        @ColumnResult(name = "item_name")
    }
)

- @FieldResult는 컬럼명과 필드명을 직접 매핑하는 어노테이션이다. @FieldResult를 한 번이라고 사용하면 전체 필드를 @FieldResult로 매핑해야 하고, 여러 엔티티를 조회할 때 컬럼명이 중복될 때도 @FieldResult를 사용해야 한다.

※ @FieldResult.name : 결과를 받을 필드명

※ @FieldResult.column : 결과 컬럼명

- @ColumnResult.name : 결과 컬럼명

 

SQL문 작성 예시

Query q = em.createNativeQuery(
    "SELECT o.id AS order_id, " +
        "o.quantity AS order_quantity, " +
        "o.item AS order_item, " +
        "i.name AS item_name, " +
    "FROM Order o, Item i " +
    "WHERE (order_quantity > 25) AND (order_item = i.id)"
, "OrderResults");

 

 

 

3. Named 네이티브 SQL

- @NamedNativeQuery를 사용하여 네이티브 SQL을 등록한다.

 

1) @NamedNativeQuery 선언 예시

@Entity
@NamedNativeQuery(
    name="Member.memberAgeGt",
    query="SELECT ID, AGE, NAME, TEAM_ID "+
        "FROM MEMBER WHERE AGE > ?1",
    resultClass = Member.class
)
public class Member {...}

 

추가 옵션

- @NamedNativeQuery.resultSetMapping : 단순히 엔티티를 조회하는 대신에 결과 매핑을 사용하고 싶다면, @NamedNativeQuery.resultClass 대신에 @NamedNativeQuery.resultSetMapping을 사용하면 된다. 조회 결과를 매핑할 대상의 @SqlResultSetMapping.name을 작성하면 된다.

@Entity
@SqlResultSetMapping(
    name = "memberAgeGt",
    ...
)
@NamedNativeQuery(
    name="Member.memberAgeGt",
    query="SELECT ID, AGE, NAME, TEAM_ID "+
        "FROM MEMBER WHERE AGE > ?1",
    resultSetMapping = "memberAgeGt"
)
public class Member {...}

- @NamedNativeQuery.hints : JPA 구현체에 종속적인 힌트

 

 

2) @NamedNativeQuery 사용 방법

- @NamedNativeQuery에 지정한 name을 작성하면 된다.

List<Object[]> resultList = em.createNamedQuery("Member.memberAgeGt")
    .setParameter(1, 30)
    .getResultList();

 

 

3) 여러 @NamedNativeQuery 선언하기

- @NamedNativeQueries 안에 여러 @NamedNativeQuery들을 선언하면 된다.

@NamedNativeQueries({
    @NamedNativeQuery(...),
    @NamedNativeQuery(...)
})

 

참고) Named 쿼리 추가 예제

https://cheolhojung.github.io/posts/record/jpa-named-native-query-result-map.html

 

 

 

4. 네이티브 SQL을 XML에 정의하기

- 네이티브 SQL은 보통 복잡한 쿼리를 작성할 때 쓰이기 때문에 라인 수가 많은 경향이 있다. 게다가 자바는 멀티라인 문자열을 지원하지 않기 때문에 어노테이션 설정보다는 XML 설정이 더 유리할 수 있다.

- xml에 정의할 때는 <named-native-query>를 먼저 정의하고 <sql-result-set-mapping>을 정의해야 한다.

- 어노테이션 방식의 속성명과 XML 방식의 속성명이 유사하기 때문에 의미하는 바는 직관적으로 알 수 있을 것이다.

 

예시

<ormMember.xml>

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="htt://xmlns.jcp.org/xml/ns/persistence/orm" version="2.1">
    <named-native-query name="Member.memberWithOrderCountXml"
        result-set-mapping="memberWithOrderCountResultMap">
        <query>
            <![CDATA[
                SELECT M.ID, AGE, NAME, TEAM_ID, I.ORDER_COUNT FROM MEMBER M
                LEFT JOIN
                    (SELECT IM.ID, COUNT(*) AS ORDER_COUNT
                    FROM ORDERS O, MEMBER IM
                    WHERE O.MEMBER_ID = IM.ID)
                ON M.ID = I.ID
            ]]>
        </query>
    </named-native-query>

    <sql-result-set-mapping name="memberWithOrderCountResultMap">
        <entity-result entity-class="com.example.demo.dto.Member"/>
        <column-result name="ORDER_COUNT"/>
    </sql-result-set-mapping>

</entity-mappings>

 

 

참고로, 매핑 파일을 JPA가 인식하도록 META-INF/persistence.xml에 매핑 파일을 추가하는 것을 잊지 말자

<persistence-unit name="hello">
    <mapping-file>META-INF/ormMember.xml</mapping-file>
    ...
>

 

 

 

● 참고자료 : 자바 ORM 표준 JPA 프로그래밍