[JPA] 네이티브 SQL 정리

| Native SQL

---

JPQL은 표준 SQL이 지원하는 대부분의 문법과 SQL 함수들을 지원하지만

특정 데이터베이스에 종속적인 기능은 지원하지 않음 (Ex.

- 특정 데이터베이스만 지원하는 함수, 문법, SQL 쿼리 힌트

- 인라인 뷰, UNION, INTERSECT

- 스토어드 프로시저)

- ...

다양한 이유로 JPQL을 사용할 수 없을 때,

JPA는 Native SQL을 통해 SQL을 직접 사용할 수 있는 기능을 제공.

- SQL을 개발자가 직접 정의

- 네이티브 SQL 사용 시 엔티티를 조회하고, JPA가 지원하는 영속성 컨텍스트의 기능을 그대로 사용 가능

  반면, JDBC API 사용 시 단순히 데이터의 나열을 조회

|| 사용

---

ㅇ 결과 타입 정의

public Query createNativeQuery(String sqlString, class resultClass);

ㅇ 결과 타입을 정의할 수 없을 경우

public Query createNativeQuery(String sqlString);

ㅇ 결과 매핑 사용

public Query createNativeQuery(String sqlString,

String resultSetMapping);

||| 엔티티 조회

---

- em.createNativeQuery(SQL, 결과 클래스) 사용

- JPQL 사용과 비슷하지만 실제 데이터베이스 SQL 사용 및 위치기반 파라미터만 지원하는 차이

* 네이티브 SQL로 SQL만 직접 사용할 뿐 나머지는 JPQL과 동일

* 조회한 엔티티도 영속성 컨텍스트에서 관리

String sql = "SELECT ID, AGE, NAME, TEAM_ID" +
             " FROM MEMBER WHERE AGE > ?";
 
Query nativeQuery = em.createNativeQuery(sql, Member.class)
                      .setParameter(1, 20);
 
List<Member> resultList = nativeQuery.getResultList();

||| 값 조회

---

ㅇ 여러 값으로 조회

String sql = "SELECT ID, AGE, NAME, TEAM_ID " +
             "FROM MEMBER WHERE AGE > ?";
 
Query nativeQuery = em.createNativeQuery(sql)
                      .setParameter(1, 10);
 
List<Object[]> resultList = nativeQuery.getResultList();
for(Object[] row : resultList) {
    System.out.println("id = " + now[0]);
    System.out.println("age = "now[1]);
    System.out.println("name = "now[2]);
    System.out.println("team_id = "now[3]);
}

||| 결과 매핑 사용

---

- 엔티티와 스칼라 값을 함께 조회하며 매핑이 복잡해지면 @SqlResultSetMapping 을 정의하여 결과 매핑을 사용

c. 결과 매핑 사용

- em.createNativeQuery()의 두 번째 파라미터에 결과 매핑 정보의 이름이 사용

String sql = 
    "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) I " +
    "ON M.ID = I.ID";
 
Query nativeQuery = em.createNativeQuery(sql, "memberWithOrderCount");
 
List<Object[]> resultList = nativeQuery.getResultList();
for(Object[] row : resultList) {
    Member member = (Member) row[0];
    BigInteger orderCount = (BigInteger) row[1];
 
    System.out.println(member);
    System.out.println(orderCount);
}

c. 결과 매핑 정의

- Member 엔티티와 ORDER_COUNT 컬럼을 매핑

@Entity
@SqlResultSetMapping(name = "memberWithOrderCount",
    entities = {@EntityResult (entityClass = Member.class)},
    columns = {@ColumnResult (name = "ORDER_COUNT")}
)
public class Member { ... }

//

c. 결과 매핑 Example

- @FieldResult를 사용하여 컬럼명과 필드명을 직접 매핑

- 단점은 @FieldResult를 한 번이라도 사용하면 전체 필드를 @FieldResult로 매핑해야 함

@SqlResultSetMapping(name = "OrderResults", // 결과 매핑 이름
    entities = { // @EntityResult를 사용해서 엔티티를 결과로 매핑 
        @EntityResult(entityClass=com.acme.Order.class, // 결과로 사용할 엔티티 클리스
            fields = { // @FieldResult를 사용해서 결과 컬럼을 필드와 매핑
                @FieldResult(name="id", // 결과를 받을 필드명
                            column="order_id"), // 결과 컬럼명
                @FieldResult(name="quantity", column="order_quantity"),
                @FieldResult(name="item", column="order_item"),
            }
    },
    columns={
        @ColumnResult(name = "item_name") // 결과 컬럼명
    }
)
 

||| 결과 매핑 어노테이션

---

@SqlResultSetMapping

> 속성

name : 결과 매핑 이름

entities : @EntityResult를 사용해서 엔티티를 결과로 매핑

columns : @ColumnResult를 사용해서 컬럼을 결과로 매핑

@EntityResult

> 속성

entityClass : 결과로 사용할 엔티티 클래스를 지정

fields : @FieldResult를 사용해서 결과 컬럼을 필드와 매핑

discriminatorColumn : 엔티티의 인스턴스 타입을 구분하는 필드(상속에서 사용)

@FieldResult

> 속성

name : 결과를 받을 필드명

column : 결과 컬럼명

@ColumnResult

> 속성

name : 결과 컬럼명

|| Named Native SQL

---

c. JPQL처럼 Named Native SQL을 사용해서 정적 SQL 작성

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

c. Named Native SQL 사용

TypedQuery<Member> nativeQuery =
    em.createNamedQuery("Member.memberSQL", Member.class)
      .setParameter(1, 20);

//

c. Named Native SQL 에서 결과 매핑 사용

@Entity
@SqlResultSetMapping( name = "memberWithOrderCount",
    entities = {@EntityResult (entityClass = Member.class)},
    columns = {@ColumnResult (name = "ORDER_COUNT")}
)
@NamedNativeQuery(
    name = "Member.memberWithOrderCount", // 네임드 쿼리 이름(필수)
    query = "SELECT M.ID, AGE, NAME, TEAM_ID, I.ORDER_COUNT" + // SQL 쿼리(필수)
            "FROM MEMBER M" +
            "LEFT JOIN " +
            "    (SELECT IM.ID, COUNT(*) AS ORDER_COUNT " +
            "    FROM ORDERS O, MEMBER IM" +
            "    WHERE O.MEMBER_ID = IM.ID) I " +
            "ON M.ID = I.ID",
    SqlResultSetMapping = "memberWithOrderCount" // 결과 매핑 사용
)
public class Member { ... }
 

c. 정의한 Named Native Query 사용

List<Object[]> resultList =
    em.createNamedQuery("Member.memberWithOrderCount")
      .getResultList();

@NamedNativeQuery

> 속성

name : 네임드 쿼리 이름(필수)

query : SQL 쿼리(필수)

hints : 벤더 종속적인 힌트 (hibernate 같은 JPA 구현체에 제공하는 힌트)

resultClass : 결과 클래스

resultSetMapping : 결과 매핑 사용

|| Native SQL XML 정의

---

--

c. Named Native Query XML 정의

- <named-native-query> 정의 후 <sql-result-set-mapping> 정의.

<entity-mappings
        xmlns="http://java.sun.com/xml/ns/persistence/orm"
        version="2.1">
 
    <named-native-query name="Member.memberWithOrderCountXml"
        result-set-mapping="memberWithOrderCountResultMap">
        <query><CDATA[
            SELECT MID, 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 I.MEMBER_ID = IM.ID)
            ON M.ID = I.ID
        ]></query>
    </named-native-query>
 
    <sql-result-set-mapping name="memberWithOrderCountResultMap">
        <entity-result entity-class="test.domainMember" />
        <column-result name="ORDER_COUNT" />
    </sql-result-set-mapping>
 
</entity-mappings>

* Native SQL은 보통 JPQL로 작성하기 어려운 복잡한 SQL 쿼리를 작성하거나 SQL을 최적화해서 데이터베이스 성능을 향상할 때 사용

  이런 쿼리들은 대체로 복잡하고 라인 수가 많으므로 Annotation보다는 XML을 사용하는 것이 편리.

|| 정리

---

--

- Native SQL도 JPQL을 사용할 때와 마찬가지로 Query, TypeQuery(Named native query의 경우에만)를 반환

- JPQL API를 그대로 사용 가능

- Native SQL을 사용해도 페이징 처리 API 적용 가능

// 11~30번 데이터 조회
String sql = "SELECT ID, AGE, NAME, TEAM_ID FROM MEMBER"
Query nativeQuery = em.createNamedQuery(sql, Member.class)
                      .setFirstResult(10)  // 조회 시작 위치
                      .setMaxResults(20) // 조회할 데이터 수

- Native SQL은 JPQL이 자동 생성하는 SQL을 수동으로 직접 정의하는 것 -> JPA가 제공하는 기능 대부분 그대로 사용 가능

- 단, Native SQL은 관리하기 쉽지 않고 자주 사용하면 이식성이 떨어짐

1. 될 수 있으면 표준 JPQL 사용

2. 그래도 안되면 hibernate 같은 JPA 구현체가 제공하는 기능 사용

3. 그래도 안되면 Native SQL 사용

4. 그래도 부족하면 MyBatis나 JdbcTemplate 같은 SQL 매퍼와 JPA를 함께 사용

|| 스토어드 프로시저

---

--

...

출처 : 자바 ORM 표준 JPA 프로그래밍