[자바 ORM 표준 JPA 프로그래밍] 10.5 네이티브 SQL

Reference. 자바 ORM 표준 JPA 프로그래밍

책 목차 및 이전 글

들어가기 전 JPA 특징, Q&A

1. JPA 소개

    1.1 SQL을 직접 다룰 때 발생하는 문제점

    1.2 패러다임의 불일치

    1.3 JPA란 무엇인가?

2. JPA 시작

3. 영속성 관리

    3.1 엔티티 매니저 팩토리와 엔티티 매니저

    3.2 영속성 콘텍스트란?

    3.3 엔티티의 생명주기

    3.4 영속성 콘텍스트의 특징

    3.5 플러시

    3.6 준영속

4. 엔티티 매핑

    4.1 - 4.3 @Entity, @Table, 다양한 매핑

    4.4 - 4.5 데이터베이스 스키마 자동 생성, DDL 생성 기능

    4.6 기본 키 매핑

    4.7 필드와 칼럼 매핑: 레퍼런스

5. 연관관계 매핑 기초

    5.1 단방향 연관관계

    5.2 단방향 연관관계

    5.3 양방향 연관관계

6. 다양한 연관관계 매핑

    6.1 다대일

    6.2 일대다

    6.3 일대일

    6.4 다대다

7. 고급매핑

    7.1 상속 관계 매핑

    7.2 @MappedSuperclass

    7.3 복합 키와 식별 관계 매핑

    7.4 조인 테이블

    7.5 엔티티 하나에 여러 테이블 매핑

8. 프록시와 연관관계 관리

    8.1~2 프록시, 즉시 로딩과 지연 로딩

    8.3 지연 로딩 활용

    8.4~5 영속성 전이, 고아 객체

9. 값 타입

    9.1~2 기본값 타입, 임베디드 타입

    9.3~5 값 타입과 불변 객체, 값 비교, 값 타입 컬렉션

10. 객체지향 쿼리 언어

    10.1 객체체지향 쿼리 소개

    10.2 JPQL

    10.3 Criteria

    10.4 QueryDSL

10.5 네이티브 SQL

• JPQL은 표준 SQL이 지원하는 대부분의 문법과 SQL 함수를 지원 하지만, 특정 데이터베이스에 종속적인 기능을 지원하지 못하는 기능이 존재
  • 특정 데이터베이스만 지원하는 함수, 문법, SQL 쿼리 힌트
  • 인라인 뷰(From 절에서 사용하는 서브쿼리), UNION, INTERSECT
  • 스토어드 프로시저

특정 데이터베이스에 종속적인 기능을 지원하는 방법

• 특정 데이터베이스만 사용하는 함수
  • JPQL에서 네이티브 SQL 함수를 호출 가능
  • 하이버네이트는 데이터베이스 방언에 종속적인 함수들을 정의 또한 직접 호출할 함수 정의 가능

• 특정 데이터베이스만 지원하는 SQL 쿼리 힌트
  • 하이버네이트를 포함한 몇몇 JPA 구현체들이 지원

• 인라인 뷰(From 절에서 사용하는 서브쿼리), UNION, INTERSECT
  • 하이버네이트는 지원하지 않지만 일부 JPA 구현체들이 지원

• 스토어 프로시저
  • JPQL에서 스토어드 프로시저를 호출 가능

• 특정 데이터베이스만 지원하는 문법
  • 오라클의 CONNECT BY처럼 너무 종속된 SQL문법은 지원하지 않으므로 네이티브 SQL 사용

JPA가 지원하는 네이티브 SQL과 JDBC API의 차이

• 네이티브 SQL을 사용하면 엔티티를 조회 가능하고 영속성 컨텍스트 기능 사용 가능

• JDBC API는 단순히 데이터만을 조회

10.5.1 네이티브 SQL 사용

엔티티 조회

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

• JPQL와 거의 비슷하지만 실제 DB 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 = nativeQuery.getResultList();

• 가장 중요한 점은 네이티브 SQL로 SQL만 직접 사용할 뿐 나머지는 JPQL과 동일 조회한 엔티티도 영속성 컨텍스트에서 관리

• JPA는 위치 기반 파라미터만 지원하지만 하이버네이트는 이름 기반 파라미터 사용 가능

값 조회

• 단순히 값만 조회하는 방법 (엔티티로 조회 X)

  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 = " + row[0]);
  		System.out.println("age = " + row[1]);
  		System.out.println("name = " + row[2]);
  		System.out.println("team_id = " + row[3]);
  }

결과 매핑 사용

• 엔티티와 스칼라 값을 함께 조회하는 것처럼 매핑이 복잡해지면 @SqlREsultSetMapping을 정의해서 결과 매핑으로 사용

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

  결과 매핑을 정의

  • @FieldREsult를 사용해서 컬럼명과 필드명을 직접 매핑이 가능
    • @FieldResult를 한 번이라도 사용하면 전체 필드 매핑이 필요
    • 두 엔티티를 조회하는데 컬럼명이 중복될 때 @FieldREsult를 사용해서 별칭으로 매핑

  @Entity
  //1. Entity에 바로 매핑
  @SqlResultSetMapping(name = "memberWithOrderCount",
  		entities = {@EntityResult(entityClass = Member.class)},
  		columns = {@ColumnResult(name = "ORDER_COUNT")}
  )
  
  //2. Entity에 매핑할 컬럼 지정
  @SqlResultSetMapping(name = "memberWithOrderCount",
  		entities = {@EntityResult(entityClass = Member.class, fields = {
  				@FieldResult (name="id", column="order_id"),
  				@FieldResult (name="quantity", column="order_quantity"),
  				...
  		})},
  		columns = {@ColumnResult(name = "ORDER_COUNT")}
  )
  public class Member {...}

결과 매핑 어노테이션

• 결과 매핑에 사용하는 어노테이션들

  @SqlResultSetMapping 속성

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

  @EntityResult 속성

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

  @FieldResult 속성

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

  @ColumnResult 속성

  속성	기능
  name	결과 컬럼명

10.5.2 Named 네이티브 SQL

• Named 네이티브 SQL을 사용해서 정적 SQL 작성이 가능

  resultClass 사용

  //Entity Named 네이트브 SQL 설정
  @Entity
  @NamedNativeQuery(
  		name = "Member.memberSQL",
  		query = "SELECT ID, AGE, NAME, TEAM_ID " +
  					"FROM MEMBER WHERE AGE > ?",
  		resultClass = Member.class
  )
  public class Member {...}
  
  //실행
  TypeQuery<Member> nativeQuery = 
  		em.createNamedQuery("Member.memberSQL", Member.class)
  				.setParameter(1, 20);

  resultSetMapping 사용

  //Entity 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 IM " +
  					"     WHERE O.MEMBER_ID = IM.ID) I " +
  					"ON M.ID = I.ID",   
  		resultSetMapping = "memberWithOrderCount"
  )
  public class Member {...}
  
  //실행
  List<Object[]> resultList = em.createNamedQuery("Member.memberWithOrderCount")
                  .getResultList();

  @NamedNativeQuery

  @NamedNativeQuery 속성

  속성	기능
  name	네임드 쿼리 이름(필수)
  query	SQL 쿼리(필수)
  hints	벤더 종속적인 힌트
  resultClass	결과 클래스
  resultSetMapping	결과 매핑 사용

  • hints 속성은 SQL 힌트가 아니라 JPA 구현체에 제공하는 힌트
  • 여러 Named 네이티브 쿼리를 서언하는 방법

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

  10.5.3 네이티브 SQL XML에 정의

  • 네이티브 SQL은 보통 JPQL로 작성하기 어려운 복잡한 쿼리이므로 라인수가 많음 따라서 어노테이션보다 XML을 사용하는 것이 편리, 자바는 멀티 라인 문자열을 지원하지 않으므로 불편함 유발, 반면 XML은 SQL을 바로 붙여서 사용이 가능

    ormMember.xml

    <entity-mappings ..>
    		<name-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>
    			</name-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>

10.5.4 네이티브 SQL 정리

• 네이티브 SQL도 JPQL을 사용할때와 같이 Query, TypeQuery를 반환
  • JPQL과 같이 네이티브 SQL을 사용해도 페이징 처리 API 적용이 가능

    String sql = "SELECT ID, AGE, NAME, TEAM_ID FROM MEMBER";
    Query nativeQuery = em.createNativeQuery(sql, Member.class)
    		.setFirstResult(10)
    		.setMaxResults(20)

• 네이티브 SQL은 관리가 쉽지 않고 자주 사용하면 종속적인 쿼리가 증가하여 이식성이 저하

• 사용 우선순위 권장 : 표준 JPQL > 하이버에니트 JPA 구현체 > 네이티브 SQL

• 네이티브 SQL로 부족함을 느끼면 Mybatis나 스프링 프레임워크가 제공하는 SQL 매퍼를 고려

10.5.5 스토어드 프로시져(JPA 2.1)

• JPA 2.1부터 스토어드 프로시저를 지원

스토어드 프로시저 사용

• 프로시저를 사용하려면 em.createStoredProcedureQuery 메소드에 프로시저 이름 입력

• registerStoredProcedureParameter 메소드를 사용해서 사용할 파라미터를 순서, 타입, 파라미터 모드 순으로 정의
  • 파라미터 모드는 ParamterMode에 정의

proc_multiply MySQL 프로시저

DELIMITER //

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

javax.persistence.ParamterMode

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

순서 기반 파라미터 호출

StoredProcedureQueryt spq = 
		em.createStoredProcedureQuery("proc_multiply");
spq.registerStoredProcedureParameter(1, Integer.class, ParamterMode.IN);
spq.registerStoredProcedureParameter(2, Integer.class, ParameterMode.OUT);

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

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

파라미터에 이름 사용

StoredProcedureQueryt spq = 
		em.createStoredProcedureQuery("proc_multiply");

spq.registerStoredProcedureParameter("inParam", Integer.class, ParamterMode.IN);
spq.registerStoredProcedureParameter("outParam", Integer.class, ParameterMode.OUT);

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

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

Named 스토어드 프로시저 사용

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

  @NamedStoredProcedureQuery(
  		name = "multiply",
  		procedureName = "proc_multiply",
  		paramters = {
  				@StoredProcedureParamter(name = "inParam", mode = 
  						ParamterMode.IN, type = Integer.class),
  				@StoredProcedureParamter(name = "outParam", mode = 
  						ParamterMode.OUT, type = Integer.class)
  		}
  )
  @Entity
  public class Member {...}

  • name 속성으로 이름을 부여
  • procedureName 속성엔 실제 호출할 프로시저 이름 매핑
  • @StoredProcedureParamter를 사용해 파라미터 정보 정의
  • 둘 이상 정의하려면 @NamedStoredProcedureQueries를 사용

• Named 스토어드 프로시저 XML에 정의

  <?xml version="1.0" encoding="UTF-8"?>
  <entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
                   version="2.2">
  
      <named-stored-procedure-query name="multiplyXml"
                                    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>

Named 스토어드 프로시저 사용

StoredProcedureQuery spq =
//                em.createStoredProcedureQuery("multiplyXml");
                em.createStoredProcedureQuery("multiply");
spq.setParameter("inParam", 100);
spq.execute();

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