[JPA] Chapter 10. 객체지향 쿼리 언어 3 - 네이티브 SQL

joyful·2021년 9월 4일

JPA

목록 보기

16/18

들어가기 앞서

이 글은 김영한 님의 저서 「자바 ORM 표준 JPA 프로그래밍」을 학습한 내용을 정리한 글입니다. 모든 출처는 해당 저서에 있습니다.

10.4 네이티브 SQL

JPQL은 특정 데이터베이스에 종속적인 기능 지원 x
- 특정 데이터베이스에서만 지원하는 함수, 문법, SQL 쿼리 힌트
- 인라인 뷰, UNION, INTERSECT
  
  💡 인라인 뷰
  
  From 절에서 사용하는 서브쿼리
- 스토어드 프로시져
JPA는 특정 데이터베이스에 종속적인 기능을 지원하는 방법들을 제공하며, 특히 JPA 표준보다 JPA 구현체에서 다양한 방법을 지원
- 특정 데이터베이스에서만 사용하는 함수
  - JPQL에서 네이티브 SQL 함수 호출 가능(JPA 2.1)
  - 하이버네이트
    - 데이터베이스 방언에 각 데이터베이스에 종속적인 함수들 정의
    - 직접 호출할 함수 정의 가능
- 특정 데이터베이스만 지원하는 SQL 쿼리 힌트
  - 몇몇 JPA 구현체들에서 지원 ex)하이버네이트
- 인라인 뷰, UNION, INTERSECT
  - 하이버네이트를 제외한 일부 JPA 구현체들에서 지원
- 스토어 프로시저
  - JPQL에서 스토어드 프로시저 호출 가능(JPA 2.1)
- 특정 데이터베이스만 지원하는 문법
  - 특정 데이터베이스에 너무 종속적인 SQL 문법은 지원 x ex)오라클의 CONNECT BY
  - 네이티브 SQL 사용 필요

✅ 네이티브 SQL

JPQL을 사용할 수 없을 때 JPA가 SQL을 직접 사용할 수 있도록 제공하는 기능

네이티브 SQL vs JPQL

네이티브 SQL JPQL
개발자가 SQL 직접 정의 JPA가 SQL 생성
수동 모드 자동 모드
네이티브 SQL vs JDBC API 직접 사용

네이티브 SQL JDBC API
엔티티 조회 가능 데이터의 나열 조회
JPA가 지원하는 영속성 컨텍스트 기능
그대로 사용 가능

네이티브 SQL	JPQL
개발자가 SQL 직접 정의	JPA가 SQL 생성
수동 모드	자동 모드

네이티브 SQL	JDBC API
엔티티 조회 가능	데이터의 나열 조회
JPA가 지원하는 영속성 컨텍스트 기능 그대로 사용 가능

10.4.1 네이티브 SQL 사용

📝 네이티브 쿼리 API

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

//결과 타입 정의 불가능 할 때(값 조회)
public Query createNativeQuery(String sqlString);

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

✅ 엔티티 조회

문법 : em.createNativeQuery(SQL, 결과 클래스)
- 파라미터
  - 첫 번째 : 네이티브 SQL
  - 두 번째 : 조회할 엔티티 클래스의 타입
실제 데이터베이스 SQL 사용
위치기반 파라미터만 지원

💻 예제

//SQL 정의
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 = netiveQuery.getResultList();

네이티브 SQL로 SQL을 직접 사용하는 부분 외에는 JPQL 사용과 동일
조회한 엔티티는 영속성 컨텍스트에서 관리

📖 참고

JPA는 공식적으로 네이티브 SQL에서 위치 기반 파라미터만 지원

하이버네이트는 네이티브 SQL에 이름 기반 사용 가능

📖 참고

em.createNativeQuery() 호출 시 타입 정보 지정에도 불구하고 TypeQuery가 아닌 Query 리턴
→ JPA1.0에서 API 규약이 정의되었기 때문

✅ 값 조회

💻 예제

//SQL 정의
String sql = "SELECT ID, AGE, NAME, TEAM_ID FROM MEMBER WHERE AGE > ?";

Query nativeQuery = em.createNativeQuery(sql)
                      .setParameter(1, 10);
                      
List<Object[]> resultList = netiveQuery.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]);
}

스칼라 값들 조회 → 영속성 컨텍스트에서 관리 x
JDBC로 데이터를 조회한 것과 비슷

✅ 결과 매핑 사용

@SqlResultMapping : 네이티브 SQL 쿼리 결과의 매핑 지정

@Target(value=TYPE)
@Retention(value=RUNTIME)
public @interface SqlResultSetMapping {...}

💻 결과 매핑 사용 - 회원 엔티티와 회원이 주문한 상품 수 조회

//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 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 = " + member);
    System.out.println("orderCount = " + orderCount);
}

💻 결과 매핑 정의

@Entity
@SqlResultSetMapping(  //회원 엔티티와 ORDER_COUNT 컬럼 매핑
    name = "memberWithOrderCount",
    entities = {@EntityResult(entityClass = Member.class)},
    columns = {@ColumnResult(name = "ORDER_COUNT")}
)
public class Member {...}

매핑
- ID, AGE, NAME, TEAM_ID → Member 엔티티
- ORDER_COUNT → 값
entities, columns → 여러 엔티티와 여러 컬럼 매핑 가능

💻 표준 명세 예제 - 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_quentity > 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")}
)

@FieldResult
- 컬럼명과 필드명 직접 매핑
- 엔티티의 필드에 정의한 @Column보다 우선순위
- 한 번이라도 사용 시 전체 필드를 @FieldResult로 매핑해야 함
- 두 엔티티 조회 시 컬럼명 중복될 때 사용
  → 별칭을 적절히 사용하고 @FieldResult로 매핑

✅ 결과 매핑 어노테이션

📊 `@SqlResultSetMapping` 속성

속성	기능
name	결과 매핑 이름
entites	`@EntityResult`를 사용하여 엔티티를 결과로 매핑
columns	`@ColumnResult`를 사용하여 컬럼을 결과로 매핑

📊 `@EntityResult` 속성

속성	기능
entityClass	결과로 사용할 엔티티 클래스 지정
fields	`@FieldResult`를 사용하여 결과 컬럼을 필드와 매핑
discriminatorColumn	엔티티의 인스턴스 타입을 구분하는 필드(상속에서 사용)

📊 `@FieldResult` 속성

속성	기능
name	결과를 받을 필드명
column	결과 컬럼명

📊 `@ColumnResult` 속성

속성	기능
name	결과 컬럼명

10.4.2 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 사용

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

JPQL Named 쿼리와 같은 createNamedQuery 메소드 사용
→ TypeQuery 사용 가능

💻 결과 매핑 사용

@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 IM " +
            "ON M.ID = I.ID",
    resultSetMapping = "memberWithOrderCount"  //조회 결과 매핑 대상 지정
)
public class Member {...}

💻 Named 네이티브 쿼리 사용

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

✅ `@NamedNativeQuery`

📊 `@NamedNativeQuery` 속성

속성	기능
name	네임드 쿼리 이름(필수)
query	SQL 쿼리(필수)
hints	◾ 벤더 종속적인 힌트 ◾ JPA 구현체에 제공하는 힌트
resultClass	결과 클래스
resultSetMapping	결과 매핑 사용

💻 Named 네이티브 쿼리 다중 선언

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

10.4.3 네이티브 SQL XML에 정의

💻 ormMember.xml

<entity-mappings ...>

    <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) 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-mapping>

반드시 <named-native-query>를 먼저 정의한 후 <sql-result-set-mapping>를 정의해야 함

💻 사용 코드

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

📖 참고

네이티브 SQL을 사용하는 경우

JPQL로 작성하기 어려운 복잡한 SQL 쿼리 작성 시

SQL을 최적화하여 데이터베이스 성능을 향상시킬 때

위와 같은 케이스에 해당하는 경우, 쿼리들이 대체로 복잡하고 라인수가 많음
→ 어노테이션보다는 XML 사용 권장

자바 : 멀티 라인 문자열 지원 x
→ 라인 변경할 때마다 문자열을 더해야 함

XML : SQL 개발 도구에서 완성한 SQL을 바로 붙여넣을 수 있음

10.4.4 네이티브 SQL 정리

Query, TypeQuery(Named 네이티브 쿼리의 경우에만) 반환
→ JPQL API 그대로 사용 가능
JPQL이 자동 생성하는 SQL을 수동으로 직접 정의
→ JPA 제공 기능 그대로 사용 가능
단점
- 관리가 어려움
- 자주 사용시 특정 데이터베이스에 종속적인 쿼리 증가 → 이식성 감소
최후의 방법으로 네이티브 SQL 사용하는 것 권장

될 수 있으면 표준 JPQL을 사용하고, 기능이 부족하면 차선책으로 JPA 구현체가 제공하는 기능 사용
단독 사용시 부족할 경우 SQL매퍼와 JPA 함께 사용 고려

10.4.5 스토어드 프로시저(JPA 2.1)

✅ 스토어드 프로시저 사용

💻 proc-multiply MySQL 프로시저

DELIMITER //

/*proc_multiply : 입력 값 두 배 증가*/
//파라미터 : 첫 번째 - 입력 값, 두 번째 - 결과 반환
CREATE PROCEDURE proc-multiply (INOUT inParam INT, INOUT outParam INT)
BEGIN
    SET outParam = inParam * 2;
END //

💻 순서 기반 파라미터 호출

StoredProcedureQuery spq =
    em.createStoredProcedureQuery("proc_multiply");  //proc_multiply → 사용할 프로시저 이름
//프로시저에서 사용할 파라미터를 순서, 타입, 파라미터 모드 순으로 정의
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);  //결과 = 200

💻 ParameterMode

public enum ParameterMode {
    IN,  //INPUT 파라미터
    INOUT,  //INPUT, OUTPUT 파라미터
    OUT,  //OUTPUT 파라미터
    REF_CURSOR  //CURSOR 파라미터
}

💻 파라미터에 이름 사용

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("outPram");
System.out.println("out = " + out);  //결과 = 200

✅ Named 스토어드 프로시저 사용

💡 Named 스토어드 프로시저

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

💻 어노테이션에 정의

//@NamedStoredProcedureQueries  //둘 이상 정의 시
@NamedStoredProcedureQuery(  //정의
    name = "multiply",  //이름 부여
    procedureName = "proc_multiply",  //실제 호출할 프로시저 이름
    parameters = {
        @StoredProcedureParameter(  //파라미터 정보 정의
            name = "inParam",
            mode = ParameterMode.IN,
            type = Integer.class
        ),
        @StoredProcedureParameter(
            name = "outParam",
            mode = ParameterMode.OUT,
            type = Integer.class
        )
    }
)
@Entity
public class Member {...}

💻 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>
        <parameter name="inParam" mode="IN" class="java.lang.Integer" />
        <parameter name="outParam" mode="OUT" class="java.lang.Integer" />
    </named-stored-procedure-query>
    
</entity-mappings>

💻 사용 코드

StoredProcedureQuery spq = 
    em.createNamedStoredProcedureQuery("multiply");
    
spq.setParameter("inParam", 100);
spq.execute();

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

em.createNamedStoredProcedureQuery() 메소드에 등록한 Named 스토어드 프로시저 이름을 파라미터로 사용하여 조회 가능

📚 참고

Annotation Type SqlResultSetMapping

joyful

기쁘게 코딩하고 싶은 백엔드 개발자

이전 포스트

[JPA] Chapter 10. 객체지향 쿼리 언어 2 - QueryDSL

다음 포스트

[JPA] Chapter 10. 객체지향 쿼리 언어 3 - 네이티브 SQL

JPA

들어가기 앞서

10.4 네이티브 SQL

💡 인라인 뷰

✅ 네이티브 SQL

10.4.1 네이티브 SQL 사용

📝 네이티브 쿼리 API

✅ 엔티티 조회

💻 예제

📖 참고

📖 참고

✅ 값 조회

💻 예제

✅ 결과 매핑 사용

💻 결과 매핑 사용 - 회원 엔티티와 회원이 주문한 상품 수 조회

💻 결과 매핑 정의

💻 표준 명세 예제 - SQL

💻 표준 명세 예제 - 매핑 정보

✅ 결과 매핑 어노테이션

📊 @SqlResultSetMapping 속성

📊 @EntityResult 속성

📊 @FieldResult 속성

📊 @ColumnResult 속성

10.4.2 Named 네이티브 SQL

💻 엔티티 조회

💻 Named 네이티브 SQL 사용

💻 결과 매핑 사용

💻 Named 네이티브 쿼리 사용

✅ @NamedNativeQuery

📊 @NamedNativeQuery 속성

💻 Named 네이티브 쿼리 다중 선언

10.4.3 네이티브 SQL XML에 정의

💻 ormMember.xml

💻 사용 코드

📖 참고

10.4.4 네이티브 SQL 정리

10.4.5 스토어드 프로시저(JPA 2.1)

✅ 스토어드 프로시저 사용

💻 proc-multiply MySQL 프로시저

💻 순서 기반 파라미터 호출

💻 ParameterMode

💻 파라미터에 이름 사용

✅ Named 스토어드 프로시저 사용

💡 Named 스토어드 프로시저

💻 어노테이션에 정의

💻 XML에 정의

💻 사용 코드

[JPA] Chapter 10. 객체지향 쿼리 언어 2 - QueryDSL

[JPA] Chapter 10. 객체지향 쿼리 언어 4 - 객체지향 쿼리 심화

0개의 댓글

📊 `@SqlResultSetMapping` 속성

📊 `@EntityResult` 속성

📊 `@FieldResult` 속성

📊 `@ColumnResult` 속성

✅ `@NamedNativeQuery`

📊 `@NamedNativeQuery` 속성