객체지향 쿼리 언어(4) 네이티브 SQL

5. 네이티브 SQL

JPQL은 표준 SQL이 지원한ㄴ 대부분의 문법과 SQL 함수들을 지원하지만 특정 DB에 종속적인 기능은 지원하지 않는다. 예를들어

• 특정 DB만 지원하는 함수, 문법, SQL 쿼리 힌트
• 인라인 뷰(From 절에서 사용하는 서브쿼리), UNION, INTERSECT
• 스토어드 프로시저

특정 DB에 종속적인 기능을 지원하는 방법은 다음과 같다.

• 특정 DB만 사용하는 함수
  - JPQL에서 네이티브 SQL 함수 호출 가능
  - 하이버네이트는 DB 방언에 각 DB에 종속적인 함수를 정의, 호출할 함수를 정의할 수 있다.
• 특정 DB만 지원하는 SQL 쿼리 힌트
  - 하이버네이트를 포함한 몇몇 JPA 구현체들이 지원한다.
• 인라인뷰, UNION, INTERSECT
  - 하이버네이트는 지원하지 않지만 일부 JPA 구현체들이 지원한다.
• 스토어 프로시저
  - JPQL에서 스토어드 프로시저를 호출할 수 있다.
• 특정 DB에서만 지원하는 문법
  - 너무 종속적인 SQL(오라클 CONNECT BY) 문법은 지원X, 이때는 네이티브 SQL을 사용해야 한다.

다양한 이유로 JPQL을 사용할 수 없을 때 JPA는 SQL을 직접 사용할 수 있는 기능을 제공, 이것을 네이티브 SQL이라 한다.

 

네이티브 SQL을 사용하면 엔티티를 조회할 수 있고 JPA가 지원하는 영속성 컨텍스트의 기능을 그대로 사용할 수 있다.

 

네이티브 SQL 사용

네이티브 쿼리 API 는 3가지가 있다.

우선 조회부터 해보자.

 

- 엔티티 조회

네이티브 SQL은 밑과 같이 em.createNativeQuery(SQL, 결과 클래스) 를 사용한다. 첫 번째 파라미터는 네이티브 SQL을 입력하고 두 번째 파라미터는 조회할 엔티티 클래스의 타입을 입력한다.  실제 DB SQL을 사용한다는 것과 위치기반 파라미터만 지원하는 차이가 있다.

엔티티 조회 코드

가장 중요한 점은 네이티브 SQL로 SQL만 직접 사용할 뿐 나머지는 JPQL을 사용할 때와 같다. 조회한 엔티티도 영속성 컨텍스트에서 관리된다.

 

- 값 조회

값 조회

단순히 값으로 조회했다. 이렇게 여러 값으로 조회하려면 em.createNativeQuery(SQL)의 두 번째 파라미터를 사용하지 않으면 된다. 마치 JDBC로 데이터를 조회한 것과 비슷하다. (결과를 영속성 컨텍스트가 관리 X)

 

- 결과 매핑 사용

지금까지 특정 엔티티 조회, 스칼라 값들을 나열해서 조회하는 단순한 조회 방법을 설명했다. 엔티티와 스칼라 값을 함께 조회하는 것처럼 매핑이 복잡해지면 @SqlResultSetMapping을 정의해서 결과 매핑을 사용해야 한다.

결과 매핑 사용

회원 엔티티와 회원이 주만한 상품 수를 조회했다.

em.createNatve(sql, "memberWithOrderCount") 의 두 번째 파라미터 결과 매핑 정보의 이름이 사용되었다.

 

결과 매핑을 정의

회원 엔티티와 ORDER_COUNT를 매핑했다. 위에서 사용한 쿼리 결과에서 ID, AGE, NAME, TEAM_ID는 Member 엔티티와 매핑하고 ORDER_COUNT는 단순히 값으로 매핑한다. 그리고 entities, columns라는 이름에서 알 수 있듯이 여러 엔티티와 여러 컬럼을 매핑할 수 있다.

 

표준 명세 예제 - SQL

표준 명세 예제 - 매핑 정보

위를 잘 보면 @FieldResult를 사용하여 컬럼명과 필드명을 직접 매핑한다. 이 설정은 필드에 정의한 @Column보다 앞선다. 조금 불편한 것은 @FieldResult를 한 번이라도 사용하면 전체 필드를 @FieldResult로 매핑해야 한다.

다음 처럼 두 엔티티를 조회하는데 컬럼명이 중복될 때도 @FieldResult를 사용해야 한다.

둘 다 ID 라는 필드를 갖고 있어서 컬럼명이 충돌한다 따라서 다음과 같이 사용하고 @FieldResult로 매핑하자.

 

- 결과 매핑 어노테이션

• @SqlResultSetMapping 속성
• @EntityResult 속성
• @FieldResult 속성
• @ColumnResult 속성

@SqlResultSetMapping 속성

@EntityResult 속성

@FieldResult 속성

@ColumnResult 속성

 

Named 네이티브 SQL

엔티티 조회

@NamedNativeQuery로 Named 네이티브 SQL을 등록했다. 다음으로 사용하는 예제를 보자.

JPQL Named 쿼리와 같은 createNamedQuery 메서드를 사용한다. 따라서 TypeQuery를 사용할 수 있다.

결과 매핑 사용

Named 네이티브 쿼리에서 resultSetMapping = "memberWithOrderCount"로 조회 결과를 매핑할 대상까지 지정했다. 다음으로 사용하는 코드이다.

 

- @NamedNativeQuery

@NamedNativeQuery 속성

 

여기서 hints 는 SQL 힌트가 아닌 하이버네이트 JPA 구현체에 제공하는 힌트다. 여러 네이티브 쿼리를 선언하려면 다음처럼 사용

 

네이티브 SQL XML에 정의

ormMember.xml

 

XML 어노테이션 둘 다 사용하는 코드는 다음과 같다.

 

네이티브 SQL 정리

네이티브 SQL도 JPQL을 사용할 때와 마찬가지로 Query, TypeQuery를 반환한다. 따라서 JPQL API를 그대로 사용할 수있다. 페이징 처리도 할 수 있다.

네이티브 SQL과 페이징 처리

실행 SQL

 

순서는 표준 JPQL을 사용하고 기능이 부족하면 차선책으로 하이버네이트 같은 JPA 구현체가 제공되는 기능을 사용, 그래도 부족함을 느끼면 네이티브 SQL 그 이후 MyBatis나 스프링 프레임워크가 제공하는 JdbcTemplate 같은 SQL 매퍼와 JPA를 함께 사용하는 것도 고려할만하다.

 

스토어드 프로시저

- 스토어드 프로시저 사용

단순히 값을 두 배로 증가시켜 주는 proc_multiply 스토어드 프로시저가 있다.

proc_multiply MySQL 프로시저

순서 기반 파라미터 호출

스토어드 프로시저를 사용하려면 em.createStoredProcedureQuery() 메서드에 사용할 스토어드 프로시저 이름을 입력, 그리고 registerStoredProcedureParameter() 메서드를 사용하여 프로시저에서 사용할 파라미터를 순서, 타입, 파라미터 모드 순으로 정의하면 된다.

ParameterMode

파라미터 순서 대신 이름을 사용

파라미터에 이름 사용

 

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

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

Named 스토어드 프로시저 어노테이션에 정의하기

@NamedStoredProcedureQuery로 정의하고 name 속성으로 이름 부여.

procedureName 속성에 실제 호출할 프로시저 이름 작성하고 @StoredProcedureParameter를 사용하여 파라미터 정보를 정의.

참고로 둘 이상을 정의하려면 @NamedStoredProcedureQueries를 사용하면 된다.

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

Named 스토어드 프로시저 사용

위와 같이 사용 하면 Named 스토어드 프로시저는 em.createNamedStoredProcedureQuery() 메서드에 등록한 Named 스토어드 프로시저 이름을 파라미터로 사용해서 찾아올 수 있다.

 

6. 객체지향 쿼리 심화

벌크 연산

엔티티 수정시 영속성 컨텍스트의 변경 감지 기능이나 병합을 사용, 삭제하려면 EntityManager.remove() 메서드를 사용한다. 하지만 너무 오래걸리므로 이럴때 여러 건을 한 번에 수정, 삭제하는 벌크 연산을 사용하면 된다.

ex) 재고가 10개 미만인 모든 상품의 가격을 10% 상승시켜보자.

UPDATE 벌크 연산

벌크 연산은 executeUpdate() 메서드를 사용한다. 이 메서드는 벌크 연산으로 영향을 받은 엔티티 건수를 반환

DELETE 벌크 연산

 

- 벌크 연산의 주의점

영속성 컨텍스트를 무시하고 DB에 직접 쿼리한다는 점에 주의하자. 다음은 벌크 연산시 문제가 발생한 예제이다. DB에는 가격이 1000원인 상품A가 있다.

벌크 연산 시 주의점 예제

1. 가격이 1000원인 상품 A를 조회햇다. 조회된 상품A는 영속성 컨텍스트에서 관리된다.
2. 벌크 연산으로 모든 가격을 10% 상승시켰다. 따라서 상품A의 가격은 1100원이 되어야 한다.
3. 벌크 연산을 수행한 후 상품A의 가격을 출력하면 기대했던 1100원이 아닌 1000원이 출력된다.

벌크 연산 직전

벌크 연산 수행 후

- 이러한 문제를 해결하는 방법

em.refresh() 사용 : DB에서 상품 A를 다시 조회하면 된다.

벌크 연산 먼저 실행 : 가장 실용적인 해결책은 벌크 연산을 가장 먼저 실행하는 것이다.

벌크 연산 수행 후 영속성 컨텍스트 초기화 : 영속성 컨텍스트를 초기화하면 이후 엔티티를 조회할 때 벌크 연산이 적용된 DB에서 엔티티를 조회한다.

 

영속성 컨텍스트와 JPQL

- 쿼리 후 영속 상태인 것과 아닌 것

JPQL 조회 대상은 엔티티, 임베디드 타입, 값 타입 같이 다양하다. JPQL로 엔티티를 조회하면 영속성 컨텍스트에서 관리되지만 엔티티가 아니면 영속성 컨텍스트에서 관리되지 않는다.

결론은 조회한 엔티티만 영속성 컨텍스트가 관리한다.

 

- JPQL로 조회한 엔티티와 영속성 컨텍스트

영속성 컨텍스트에 회원1이 이미 있는데 다시 조회

JPQL로 회원1을 다시 조회

JPQL로 DB에 조회한 엔티티가 영속성 컨텍스트에 이미 있으면  JPQL로 DB에서 조회한 결과를 버리고 대신에 영속성 컨텍스트에 있던 엔티티를 반환한다.

엔티티 쿼리 1, 엔티티 쿼리와 결과

엔티티 쿼리 2, 결과 비교와 리턴

1. JPQL을 사용해서 조회를 요청한다.
2. JPQL은 SQL로 변환되어 DB를 조회한다.
3. 조회한 결과와 영속성 컨텍스트를 비교한다.
4. 식별자 값을 기준으로 member1은 이미 영속성 컨텍스트에 있으므로 버리고 기존에 있던 member1이 반환 대상이 된다.
5. 식별자 값을 기준으로 member2는 없으므로 컨텍스트에 추가된다.
6. 쿼리 결과인 memeber1, 2 를 반환한다. 여기서 member1은 쿼리 결과가 아닌 영속성 컨텍스트에 있던 엔티티다.

JPQL로 조회한 엔티티는 영속 상태다.

영속성 컨텍스트에 이미 존재하는 엔티티가 있으면 기존 엔티티를 반환한다.

 

그렇다면 기존 엔티티를 새로 검색한 엔티티로 대체하면 어떤 문제가 발생하나?

1. 새로운 엔티티를 영속성 컨텍스트에 하나 더 추가한다.
2. 기존 엔티티를 새로 검색한 엔티티로 대체한다.
3. 기존 엔티티는 그대로 두고 새로 검색한 엔티티를 버린다.

1번은 기본 키 값을 가진 엔티티라 등록할 수 없고, 2번은 언뜻 합리적인 것 같지만, 영속성 컨텍스트에 수정 중인 데이터가 사라질 수 있으므로 위험하다. 그래서 3번으로 동작한다.

영속성 컨텍스트는 영속 상태인 엔티티의 동일성을 보장한다.

 

- find() vs JPQL

em.find()는 영속성 컨텍스트에서 찾고 없으면 DB에서 찾는다. 그래서 해당 엔티티가 영속성 컨텍스트에 있으면 메모리에서 찾으므로 성능상 이점이 있다. (1차 캐시)

성능상 이점

JPQL 동작 과정

 

JPQL은 항상 DB에 SQL을 실행해서 결과를 조회한다.

em.find() 메서드는 영속성 컨텍스트에서 엔티티를 먼저 찾고 없으면 DB를 조회하지만 JPQL을 사용하면 DB를 먼저 조회한다.

쿼리 결과가 영속성 컨텍스트에 존재

 

- JPQL 특징

• JPQL은 항상 DB를 조회한다.
• JPQL로 조회한 엔티티는 영속 상태다.
• 영속성 컨텍스트에 이미 존재하는 엔티티가 있으면 기존 엔티티를 반환한다.

 

JPQL과 플러시 모드

플러시는 영속성 컨텍스트의 변경 내역을 DB에 동기화하는 것이다. JPA는 플러시가 일어날 때 영속성 컨텍스트에 등록, 수정, 삭제한 엔티티를 찾아서 INSERT, UPDATE, DELETE SQL을 만들어 DB에 반영한다. 플러시를 호출하려면 em.flush() 메서드를 직접 사용해도 되지만 보통 플러시 모드에 따라 커밋하기 직전이나 쿼리 실행 직전에 자도으로 플러시가 호출된다.

 

- 쿼리와 플러시 모드

영속성 컨텍스트가 아직 플러시되지 않은 상황

쿼리와 플러시 모드 예제

product.setPrice(2000)을 호출하면 영속성 컨텍스트의 상품 가격이 1000원에서 2000원으로 변하지만 DB는 1000원인 상태로 남아있다. 다음으로 JPQL을 호출해서 가격이 2000원인 상품을 조회했는데 이때 플러시 모드를 따로 설정하지 않으면 AUTO이므로 쿼리 실행 직전에 영속성 컨텍스트가 플러시 된다. 따라서 방금 2000원으로 수정한 상품을 조회할 수 있다.

만약 플러시 모드를 COMMIT으로 설정하면 조회할 수없다. 이때는 직접 em.flush()를 호출하거나 다음 코드처럼 Query객체에 밑과 같이 플러시 모드를 설정해 주면 된다.

플러시 모드 설정

commit 모드로 하면  플러시를 자동으로 호출하지 않는다. 그럼 왜 COMMIT 모드를 사용하는 걸까?

 

- 플러시 모드와 최적화

em.setFlushMode(FlushModeType.COMMIT) 이런 상황은 잘못하면 데이터 무결성에 심각한 피해를 줄 수 있따. 그럼에도 다음과 같이 플러시가 너무 자주 일어나는 상황에 이 모드를 사용하면 쿼리시 발생하는 플러시 횟수를 줄여서 성능을 최적화할 수 있다.

• FlushModeType.AUTO : 쿼리와 커밋할 때 총 4번 플러시한다.
• FlushModeType.COMMIT : 커밋 시에만 1번 플러시한다.