[자바 ORM 표준 JPA 프로그래밍] 객체지향 쿼리 언어 ⑤ - 네이티브 SQL

네이티브 SQL

• JPQL은 표준 SQL이 지원하는 대부분의 문법과 SQL 함수들을 지원하지만 특정 데이터베이스에 종속적인 기능은 지원하지 않음
  예) 특정 데이터베이스만 지원하는 함수, 문법, SQL 쿼리 힌트, 인라인 뷰, UNION, INTERSECT, 스토어드 프로시저
• 때로는 특정 데이터베이스에 종속적인 기능이 필요하며 JPA는 다양한 방법을 제공
  
  
  • 특정 데이터베이스에만 사용하는 함수
    이를 위해 JPQL에서 네이티브 SQL 함수를 호출, 하이버네이트는 정의된 종속적인 함수를 호출하거나 직접 정의
    
  • 특정 데이터베이스만 지원하는 SQL 쿼리 힌트
    하이버네이트를 포함한 몇몇 JPA 구현체들이 지원
  • 인라인 뷰 (From 절에서 사용하는 서브 쿼리), UNION, INTERSECT
    일부 JPA 구현체들이 지원
  • 스토어 프로시저
    JPQL에서 스토어드 프로시저를 호출
  • 특정 데이터베이스만 지원하는 문법
    네이티브 SQL 사용
• 다양한 이유로 JPQL을 사용할 수 없을 때 JPA는 SQL을 직접 사용할 수 있는 네이티브 SQL 기능을 제공
• JPQL을 사용하면 JPA가 SQL을 생성하는 반면, 네이티브 SQL은 SQL을 개발자가 직접 정의함
  즉, JPQL은 자동 모드, 네이티브 SQL은 수동 모드
• 네이티브 SQL을 사용하면 엔티티를 조회할 수 있고 JPA가 지원하는 영속성 컨텍스트의 기능을 사용할 수 있지만,
  JDBC API를 직접 사용하면 단순히 데이터의 나열을 조회할 뿐

네이티브 SQL 사용

• 네이티브 쿼리 API에는 3가지가 존재
  
  1) 엔티티 조회
  2) 값 조회
  3) 결과 매핑 사용

// 결과 타입 정의
public Query createNativeQuery(String sqlString, Class resultClass);

// 결과 타입을 정의할 수 없을 때
public Query createNativeQuery(String sqlString);

public Query createNativeQuery(String sqlString, String resultSetMapping); // 결과 매핑 사용

• 엔티티 조회
  
  네이티브 SQL은 em.createNativeQuery(SQL, 결과 클래스)를 사용하며,
  첫 번째 파라미터는 네이티브 SQL을 입력하고 두 번째 파라미터는 조회할 엔티티 클래스의 타입을 입력
  조회한 엔티티는 영속성 컨텍스트에서 관리됨

// 엔티티 조회 코드
// SQL 정의 : 회원의 나이가 20살 초과
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();

• 값 조회
  
  단순히 값으로만 조회

// 값 조회
// 엔티티로 조회하지 않고 단순히 값으로 조회
// SQL 정의
String sql = "SELECT ID, AGE, NAME, TEAM_ID " +
             "FROM MEMBER WHERE AGE > ?";

// 여러 값으로 조회하려면 두 번째 파라미터를 사용하지 않음
Query nativeQuery = em.createNativeQuery(sql).setPrameter(1, 10);

// JPA는 조회한 값들을 Object[]에 담아서 반환
List<Object[]> resultList = naitveQuery.getResultList();
for (Object[] row : resultList) {
    System.out.println("id = " + row[0]);
    System.out.println("age = " + row[1]);
    System.out.println("name = " + row[2]);
    System.out.println("team_id = " + row[3]);
}

• 결과 매핑 사용
  
  엔티티와 스칼라 값을 함께 조회하는 것처럼 매핑이 복잡해지면 @SqlResultSetMapping을 정의해서 결과 매핑을 사용
  이에 대한 JPA 표준 명세의 예제 코드 제공

// 결과 매핑 사용
// SQL 정의 : 회원 엔티티와 회원이 주문한 상품 수를 조회
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 ID " +
    "     WHERE O.MEMBER_ID = IM.ID) I " +
    "ON M.ID = I.ID";
    
// 두 번째 파라미터에 결과 매핑 정보의 이름이 사용됨
Query nativeQuery = em.createNativeQuery(sql, "memberWithOrdreCount");

List<Object[]> resultList = naitveQuery.getResultList();
for (Object[] row : resultList) {
    Member member = (Member) row[0];
    BigInteger orderCount = (BigInteger)row[1];
    
    System.out.println("member = " + member);
    System.out.println("orderCount = " + orderCount);
}

// 결과 매핑을 정의
// 회원 엔티티와 ORDER_COUNT 컬럼을 매핑
// 쿼리 결과에서 ID, AGE, NAME, TEAM_ID는 Member 엔티티와 매핑하고 ORDER_COUTN는 단순히 값으로 매핑
@Entity
@SqlResultSetMapping(name = "memberWithOrderCount",
    entities = {@EntityResult(entityClass = Member.class)}, // 여러 엔티티와 컬럼 매핑 가능
    columns = {@ColumnResult(name = "ORDER_COUNT")}
)
public class Member {...}

// 표준 명세 예제 - 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");

// 표준 명세 예제 - 매핑 정보
@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")}
)

• 결과 매핑 어노테이션
  
  
  • @SqlResultSetMapping
    
    
    • name : 결과 매핑 이름
    • entities : @EntityResult를 사용해서 엔티티를 결과로 매핑
    • columns : @ColumnResult를 사용해서 컬럼을 결과로 매핑
  • @EntityResult
    
    
    • entityClass : 결과로 사용할 엔티티 클래스를 지정
    • fileds : @FieldResult를 사용해서 결과 컬럼을 필드와 매핑
    • discriminatorColumn : 엔티티의 인스턴스 타입을 구분하는 필드로 상속에서 사용
  • @FieldResult
    
    
    • name : 결과를 받을 필드명
    • column : 결과 컬럼명
  • @ColumnResult
    
    
    • name : 결과 컬럼명

Named 네이티브 SQL

• JPQL처럼 네이티브 SQL도 Named 네이티브 SQL을 사용해서 정적 SQL을 작성할 수 있음

// 엔티티 조회
@Entity
@NamedNativeQuery( // Named 네이티브 SQL 등록
    name = "Member.memberSQL",
    query = "SELECT ID, AGE, NAME, TEAM_ID " +
            "FROM MEMBER WHERE AGE > ?",
    resultClass = Member.class
)
public class Member {...}

// Named 네이티브 SQL 사용하는 코드
// createNamedQuery메소드를 사용하므로 TypedQuery를 사용할 수 있음
TypedQuery<Member> nativeQuery = 
    em.createNamedQuery("Member.memberSQL", Member.class)
        .setParameter(1, 20);

// Named 네이티브 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 " +
            "FROM MEMBER M " +
            "LEFT JOIN " +
            "    (SELECT IM.ID, COUNT(*) AS ORDER_COUNT " +
            "     FROM ORDERS O, MEMBER ID " +
            "     WHERE O.MEMBER_ID = IM.ID) I " +
            "ON M.ID = I.ID";
    // 조회 결과를 매핑할 대상까지 지정
    resultSetMapping = "memberWithOrderCount"
)
public class Member {...}

// Named 네이티브 쿼리를 사용하는 코드
List<Object[]> resultList = 
    em.createNamedQuery("Member.memberWithOrderCount")
        .getResultList();

• @NamedNativeQuery
  
  
  • name : 네임드 쿼리 이름 (필수)
  • query : SQL 쿼리 (필수)
  • hints : 벤더 종속적인 힌트, 하이버네이트 같은 JPA 구현체에 제공하는 힌트
  • resultClass : 결과 클래스
  • resultSetMapping : 결과 매핑 사용
• 여러 Named 네이티브 쿼리 선언

@NameNativeQueries({
    @NameNativeQuery(...),
    @NameNativeQuery(...)
})

네이티브 SQL XML에 정의

• Named 네이티브 쿼리를 XML에 정의

// ormMember.xml
// <named-native-query>를 먼저 정의하고 <sql-result-set-mappings>를 정의해야 함
<entity-mappings ...>

    <named-native-query name="Member.memberWithOrderCountXml"
        result-set-mapping="memberWithOrderCountResultMap" >
        <query><CDATE[
            SELECT M.ID, AGE, NAME, TEAM_ID, I.ORDER_COUNT
            FROM MEMBER M
            LEFT JOIN
                (SELECT IM.ID, COUNT(*) AS ORDER_COUNT
                 FROM ODERS O, MEMBER IM
                 WHERE O.MEMBER_ID = IM.ID) I
            ON M.ID = I.ID
        ]></query>
    </named-native-query>
    
    <sql-result-set-mapping name="memberWithOrderCountResultMap">
        <entity-result entity-class="jpabook.domain.Member" />
        <column-result name="ORDER_COUNT" />
    </sql-result-set-mapping>

</entity-mappings>

네이티브 SQL과 JPQL API

• 네이티브 SQL도 JPQL을 사용할 때와 마찬가지로 Query, TypeQuery(Named 네이티브 쿼리의 경우에만)를 반환하므로
  JPQL API를 그대로 사용할 수 있음
  예) 페이징 처리 API를 적용할 수 있음

// 네이티브 SQL과 페이징 처리
String sql = "SELECT ID, AGE, NAME, TEAM_ID FROM MEMBER";
Query nativeQuery = em.createNativeQuery(sql, Member.class)
    .setFirstResult(10)
    .setMaxResults(20);
    
// 실행되는 SQL
// 페이징 정보를 추가한 SQL
SELECT ID, AGE, NAME, TEAM_ID
FROM MEMBER
limit ? offset ? // 페이징 정보 추가

스토어드 프로시저

• 스토어드 프로시저 사용
  
  단순히 입력 값을 두 배로 증가시켜 주는 proc_mutiply라는 스토어드 프로시저가 존재하며
  이 프로시저는 첫 번째 파라미터로 값을 입력받고 두 번째 파라미터로 결과를 반환

// proc_muliply MySQL 프로시저
DELIMITER //

CREATE PROCEDURE proc_multiply (INOUT inParam INT, INOUT outParam INT)
BEGIN
    SET outParam = inParam * 2;
END //

// 순서 기반 파라미터 호출
// JPA로 스토어드 프로시저를 호출
// 스토어드 프로시저를 사용하려면 사용할 스토어드 프로시저 이름을 입력
StoredProcedureQuery spq =
    em.createStoredProcedureQuery("proc_multiply");

// 프로시저에서 사용할 파라티머를 순서, 타입, 파라미터 모드 순으로 정의
/* ParameterMode
   public enum ParameterMode {
       IN, // INPUT 파라미터
       INOUT, // INPUT, OUTPUT 파라미터
       OUT, // OUTPUT 파라미터
       REF_CURSOR // CURSOR 파라미터 */
spq.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN);
spq.registerStoredProcedureParameter(2, Integer.class, ParameterMode.OUT);

spq.setParameter(1, 100);
spq.execute();

Integer out = (Integer) spq.getOutputParameterValue(2);
System.out.println("out = " + out); // 결과는 out = 200

// 파라미터에 순서 대신 이름 사용 가능
StoredProcedureQuery spq =
    em.createStoredProcedureQuery("proc_multiply");
spq.registerStoredProcedureParameter("inParam", Integer.class, ParameterMode.IN);
spq.registerStoredProcedureParameter("outParam", Integer.class, ParameterMode.OUT);

spq.setParameter("inParam", 100);
spq.execute();

Integer out = (Integer) spq.getOutputParameterValue("outParam");
System.out.println("out = " + out); // 결과는 out = 200

• Named 스토어드 프로시저 사용
  
  스토어드 프로시저 쿼리에 이름을 부여해서 사용하는 것

// Named 스토어드 프로시저 어노테이션 정의하기
@NamedStoredProcedureQuery(
    name = "multiply", // 이름 부여
    procedureName = "proc_multiplu", // 실제 호출할 프로시저 이름
    parameters = { 
        // 파라미터 정보 정의
        @StoredProcedureParamger(name = "inParam", mode = ParameterMode.IN, type = Integer.class),
        @StoredProcedureParamger(name = "outParam", mode = ParameterMode.OUT, type = Integer.class)
    }
)
@Entity
public class Member {...}

// Named 스토어드 프로시저 XML에 정의하기
<?xml version="1.0", encoding="UTF-8"?>
<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm" version="2.1">

    <named-stored-procedure-query name="multiply" procedure-name="proc_multiply">
        <paramter name="inParma" mode="IN" class="java.lang.Integer" />
        <paramter name="outParma" mode="OUT" class="java.lang.Integer" />
    </named-stored-procedure-query>
    
</entity-mappings>

// Named 스토어드 프로시저 사용 코드
// Naemd 스토어드 프로시저 이름을 파라미터로 사용해서 사용
StoredProcedureQuery spq = em.createNamedStoredProcedureQuery("multiply");

spq.setParameter("inParam", 100);
spq.execute();

Integer out = (Integer) spq.getOutputParameterValue("outParam");
System.out.println("out = " + out);