JPA - 객체지향 쿼리 언어 - 네이티브 SQL

- 참조: 자바 ORM 표준 JPA 프로그래밍

- fieldResult 명세: https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/FieldResult.html

 

---

- 네이티브 SQL

여태까지 많은 JPQL 의 기능들을 살펴보았다. 대부분의 표준 SQL 의 문법과 함수들을 지원하지만 특정 상황에서는 어쩔 수 없이 DB 제품군에 따른 특화된 기능을 사용해야할 때가 있다. (ex - 특정 함수, 문법, SQL 힌트, 인라인 뷰, 스토어드 프로시져...)

 

JPA 는 이런 상황에 대비해 SQL 을 직접 사용할 수 있는 기능을 제공하는데 이를 네이티브 SQL 이라고 한다. 네이티브 SQL 을 사용할거면 JPA 를 굳이 사용하지 않아도 될텐데 왜 JPA 의 네이티브 SQL을 사용할까? JDBC 와의 달리 네이티브 SQL 을 사용해도 엔티티 조회 및 JPA 의 영속성 컨텍스트 기능을 그대로 사용할 수 있기 때문이다.

---

- 엔티티 조회

Criteria 나 JPQL을 사용할 때에는 엔티티 매니저에서 createQuery 메소들 사용했지만 네이티브 SQL 을 사용할 때에는 아래에 해당하는 메소드를 사용한다.

 

public Query createNativeQuery(String sqlString);

public Query createNativeQuery(String sqlString, Class resultClass);

public Query createNativeQuery(String sqlString, String resultSetMapping);

 

createQuery 사용시 엔티티의 클래스형을 지정하여 조회했었던것 처럼 네이티브 SQL 에서 엔티티를 조회하는법부터 알아보자.

 

String queryMembers = "SELECT MEMBER_ID, NAME, AGE, CITY, STREET, ZIPCODE, INSERT_DATETIME, UPDATE_DATETIME "
		+ "FROM MEMBER "
		+ "WHERE AGE > ?";

List<Member> members = em.createNativeQuery(queryMembers, Member.class)
	.setParameter(1, 19)
	.getResultList();

members.forEach(System.out::println);

 

createNativeQuery 도 JPQL 을 사용할 때와 같이 Query 와 결과형 클래스를 넘겨주면 된다. 다만 JPQL 에서는 m 으로 간단하게 엔티티를 표현할 수 있지만 네이티브 SQL 은 Member 엔티티의 속성명들을 전부 열거해주어야 Member 형 엔티티 맵핑이 된다.

 

Hibernate: 
    /* dynamic native SQL query */ SELECT
        MEMBER_ID,
        NAME,
        AGE,
        CITY,
        STREET,
        ZIPCODE,
        INSERT_DATETIME,
        UPDATE_DATETIME 
    FROM
        MEMBER 
    WHERE
        AGE > ?
Member [id=2, age=21, name=Name#2]
Member [id=3, age=31, name=Name#3]
Member [id=4, age=41, name=Name#4]

 

생성된 SQL 을 보면 dynamic native SQL Query 라고 표시되고 SQL 만 출력된것을 볼 수 있다. 결과도 19 세를 초과하는 엔티티들만 잘 산출되었다.

 

이번에는 Entity 가 아니라 단순한 값으로 조회하는법을 알아본다. 이것 역시 JPQL 방식과 같이 Object[] 형을 받아서 변환해줘야 한다.

 

String queryMembers = "SELECT MEMBER_ID, NAME, AGE "
		+ "FROM MEMBER "
		+ "WHERE AGE > ?";

List<Object[]> members = em.createNativeQuery(queryMembers)
	.setParameter(1, 19)
	.getResultList();

members.forEach(member -> {
	String memberInfo = "";
	memberInfo += "ID.: " + member[0] + ", ";
	memberInfo += "Name: " + member[1] + ", ";
	memberInfo += "Age: " + member[2];
	
	System.out.println(memberInfo);
});

 

createNativeQuery 를 보면 2번째 인자 없이 SQL 만 넘겨서 질의하였다. Object 배열의 List 형에서 순서대로 Member 정보 값을 꺼내와서 출력해야 한다.

 

만약 엔티티와 값들을 같이 조회해서 맵핑을 해야 한다면 @SqlResultSetMapping 을 정의해서 결과 맵핑을 사용해야 한다.

 

String queryMembers = "SELECT M.MEMBER_ID, M.NAME, M.AGE, M.INSERT_DATETIME, M.UPDATE_DATETIME, M.CITY, M.STREET, M.ZIPCODE, "
		+ "(SELECT COUNT(O.ORDER_ID)"
		+ " FROM ORDERS O "
		+ " WHERE O.MEMBER_ID = M.MEMBER_ID) ORDER_COUNT "
		+ "FROM MEMBER M "
		+ "WHERE M.AGE > ?";

List<Object[]> memberWithOrderCounts = 
	em.createNativeQuery(queryMembers, "memberWithOrderCount")
	.setParameter(1, 19)
	.getResultList();

memberWithOrderCounts.forEach(memberWithOrderCount -> {
	Member member = (Member) memberWithOrderCount[0];
	BigInteger orderCount = (BigInteger) memberWithOrderCount[1];
	
	System.out.print(member);
	System.out.println("Order count: " + orderCount);
});

 

위의 Query 는 Member 엔티티와 해당 Member 가 주문간 주문 건수를 구하는 Query 이다. createNativeQuery 의 2 번째 인자로 전달된 memberWithOrderCount 는 ResultMapping 에 사용된 이름이다.

 

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

 

@SqlResultSetMapping 에서 name 속성에는 ResultMapping 에 사용할 이름을 지정한다. entities 와 columns 에는 각각 엔티티 클래스와 스칼라 값 컬럼을 맵핑한다.

 

만약 여러 테이블을 Join 해서 컬럼 중복이 발생하는 상황이라면 alias 를 다르게 준 후 @EntityResult 내의 fields 속성으로@FieldResult 를 사용해서 맵핑해야 한다. @FieldResult 에 대한 예제는 표준 명세 https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/FieldResult.html 를 참조하라.

---

- Named 네이티브 SQL

JPQL 처럼 네이티브 SQL 도 Named 네이티브 SQL 을 사용해서 정적 SQL 작성이 가능하다. 우선 Named 네이티브 SQL 부터 등록해주어야 한다.

 

@NamedNativeQuery(name = "Member.memberGtAge",
		query = "SELECT M.MEMBER_ID, M.NAME, M.AGE, M.INSERT_DATETIME, M.UPDATE_DATETIME, M.CITY, M.STREET, M.ZIPCODE "
				+ "FROM MEMBER M "
				+ "WHERE M.AGE > ?",
		resultClass = Member.class)

 

속성명을 보면 유추할 수 있듯이 query 명과 SQL 그리고 SQL 에서 Member 엔티티를 조회하므로 resultClass 에는 Member 클래스를 맵핑해준다.

 

List<Member> members = em.createNamedQuery("Member.memberGtAge", Member.class)
	.setParameter(1, 19)
	.getResultList();

members.forEach(member -> {
	System.out.println("Name: " + member.getName() +
			", Age: " + member.getAge());
});

 

사용법은 JPQL 과 같아서 특별한점은 없다. 똑같이 createNamedQuery 메소드를 이용하면 된다. 만약 결과가 하나의 엔티티로 맵핑되지 않고 복합적(엔티티 + 스칼라 값)이라면 resultSetMapping 속성명에 이용하여 @SqlResultSetMapping 이름을 맵핑해줄 수도 있다.

---

- 네이티브 SQL XML 정의

@NamedNativeQuery 를 이용해서 네이티브 SQL 을 정의할 수도 있지만 어노테이션을 사용하여 네이티브 SQL 을 사용할 확률은 적다고 보면 된다.

 

실제 업무에서는 쿼리가 훨씬 복잡할 확률이 높다. 또한 XML 파일로 별도로 빼는게 관리하기도 수월할것이기 때문에 네이티브 SQL을 XML 로 정의하는법이 유용할것이다.

 

<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm" version="2.1">

	<named-native-query name="Member.findByAge"
		result-set-mapping="member">
		<query><![CDATA[
		SELECT 
			M.MEMBER_ID
			,M.NAME
			,M.AGE
			,M.INSERT_DATETIME
			,M.UPDATE_DATETIME
			,M.CITY
			,M.STREET
			,M.ZIPCODE
		FROM MEMBER M 
		WHERE M.AGE > ?
		]]></query>
	</named-native-query>
	
	<sql-result-set-mapping name="member">
		<entity-result entity-class="com.example.demo.member.Member"/>
	</sql-result-set-mapping>

 

JPQL의 named-query 를 사용할 때와 비슷하다. name 에는 이름을 정해주고 맵핑할 Result Set 은 result-set-mapping 에 해당 이름을 지정해준다. @SqlResultSetMapping 어노테이션에 해당하는 Result Set 정의는 sql-result-set-mapping 태그를 이용하여 지정해준다. 엔티티가 아니라 컬럼을 추가적으로 지정해주어야 한다면 entity-result 밑에 column-result 를 지정해주면 된다.

 

책에서는 named-native-query 다음으로 sql-result-set-mapping 순서대로 정의해야 한다고 하는데 실제로 순서를 바꿔서 실행해봐도 에러는 나지 않는다.