Study/spring

자바 ORM 표준 JPA 프로그래밍(13) - 조인, 서브 쿼리, 조건식, 컬렉션 식, 스칼라 식, CASE 식

유경호 2021. 1. 7. 21:43
반응형

조인

JPQL이 지원하는 조인은 SQL 조인과 기능은 같고 문법만 약간 다르다.

 

내부 조인(INNER JOIN)

SELECT m FROM Member m [INNER] JOIN m.team t

  • INNER JOIN을 사용한다. INNER는 생략 가능
  • 생성된 내부 조인은 아래와 같다.
SELECT
    M.ID AS ID,
    M.AGE AS AGE,
    M.TEAM_ID AS TEAM_ID,
    M.NAME AS NAME
FROM
    MEMBER M INNER JOIN TEAM T ON M.TEAM_ID=T.ID
  • JPQL 조인의 가장 큰 특징은 연관 필드를 사용한다는 점
  • JPQL 조인을 SQL 조인처럼 사용하면 문법 오류가 발생한다.
    • FROM Member m JOIN Team t →잘못된 JPQL 조인, 오류!

외부 조인(OUTER JOIN)

SELECT m FROM Member m LEFT [OUTER] JOIN m.team t

  • 기능상 SQL의 외부 조인과 같다.
  • LEFT OUTER JOIN을 사용한다. OUTER는 생략 가능

세타 조인

select count(m) from Member m, Team t where m.username = t.name

  • WHERE 절을 이용하여 세타 조인을 할 수 있다.
  • 세타 조인은 내부 조인만 지원한다.
  • 세타 조인을 사용하면 위 예제와 같이 전혀 관계없는 엔티티도 조인할 수 있다.
    • 전혀 관계없는 Meber.username과 Team.name을 조인한다

ON 절

  • 조인 대상 필터링할 수 있다. → 회원과 팀을 조인하면서, 팀 이름이 A인 팀만 조인
  • 연관관계가 없는 조인을 외부 조인할 수 있음(JPA 2.1 이상 지원)
  • 내부 조인의 ON 절은 WHERE 절을 사용할 때와 같으므로 보통 ON 절은 외부 조인에만 사용함
// JPQL
select m,t from Member m left join m.team t on t.name = 'A'

// SQL
SELECT m.*, t.* FROM Member m LEFT JOIN Team t ON m.TEAM_ID=t.id and t.name='A'

서브 쿼리

JPQL도 SQL 처럼 서브 쿼리를 지원한다. 몇 가지 제약이 있는데, 서브 쿼리를 WHERE, HAVING 절에서만 사용할 수 있고 SELECT, FROM 절에서는 사용할 수 없다.

  • 하이버네이트 구현체는 SELECT 절에서도 지원

  • FROM 절의 서브 쿼리는 현재 JQPL에서 불가능

    → JOIN으로 풀어서 사용할 수 밖에 없다.

  • 자바 ORM 표준 JPA 프로그래밍 김영한저 기준

서브 쿼리 사용 예

나이가 평균보다 많은 회원

select m from Member m where m.age > (select avg(m2.age) from Member m2)

한 건이라도 주문한 고객

select m from Member m where (select count(o) from Order o where m = o.member) > 0

→ 참고로 이 쿼리는 다음처럼 컬렉션 값 연관 필드의 size 기능을 사용해도 같은 결과를 얻을 수 있다.

  • slelct m from Member m where m.orders.size > 0

서브 쿼리 지원 함수

EXISTS

  • 문법: [NOT] EXISTS (subquery)
  • 설명: 서브쿼리에 결과가 존재하면 참, NOT은 반대

예시:

  • 팀A 소속인 회원

    select m from Member m where exists (select t from m.team t where t.name = ‘팀A')

{ALL | ANY | SOME}

  • 문법: [ALL | ANY | SOME] (subquery)
  • 설명: 비교 연산자와 같이 사용한다. { = | > | >= | < | <= | <>}
    • ALL: 조건을 모두 만족하면 참
    • ANY or SOME: 둘은 같은 의미로 조건을 하나라도 만족한다면 참

예시:

  • 전체 상품 각각의 재고보다 주문량이 많은 주문들

    select o from Order o where o.orderAmount > ALL (select p.stockAmount from Product p)

  • 어떤 팀이든 팀에 소속된 회원

    select m from Member m where m.team = ANY (select t from Team t)

IN

  • 문법: [NOT] IN (subquery)
  • 설명: 서브쿼리의 결과 중 하나라도 같은 것이 있으면 참, IN은 서브쿼리가 아닌 곳에서도 사용할 수 있다.

예시:

  • 20세 이상을 보유한 팀

    select t from Team t where t IN (select t2 from Team t2 join t2.members m2 where m2.age >= 20)

조건식

타입 표현

종류 설명 예제
문자 작은 따옴표 사이에 표현
작은 따옴표를 표현하고 싶으면 작은 따옴표 연속 두 개('') 사용		 'HELLO'
'She''s'
숫자 L(Long 타입 지정)
D(Double 타입 지정) F(Float 타입 지정)		 10L 10D 10F
날짜 DATE {d 'yyyy-mm-dd'}
TIME {t 'hh-mm-ss'}
DATETIME {ts 'yyyy-mm-dd hh:mm:ss.f'}		 {d '2012-03-24'}
{t '10-11-11'}
{ts '2012-03-24 10-11-11.123'}
m.createDate = {d '2012-03-24'}
Boolean TRUE, FALSE  
Enum 패키지명을 포함한 전체 이름을 사용해야 한다. jpabook.MemberType.Admin
엔티티 타입 엔티티 타입을 표현한다. 주로 상속과 관련해서 사용한다. (DTYPE 사용 시) Type(m) = Member

 

연산자 우선 순위

연산자 우선 순위는 아래와 같다.

  1. 경로 탐색 연산(.)
  2. 수학 연산: +, - (단항 연산자), *, /, +, -
  3. 비교 연산: =, >, >=, <, <=, <>(다름), [NOT] BETWWEN, [NOT] LIKE, [NOT] IN, IS [NOT] NULL, IS [NOT] EMPTY, [NOT] MEMBER [OF], [NOT] EXISTS
  4. 논리 연산: NOT, AND, OR

논리 연산과 비교식

  • 논리 연산

    • AND: 둘 다 만족하면 참
    • OR: 둘 중 하남나 만족해도 참
    • NOT: 조건식의 결과 반대

  • 비교식

    = | > | >= | < | <= | <>

Between, IN, Like, NULL 비교

  • Between

    문법: X [NOT] BETWEEN A AND B

    설명: X는 A ~ B 사이의 값이면 참 (A, B 값 포함)

    예: 나이가 10~20인 회원을 찾아라

    select m from Member m where m.age between 10 and 20

  • IN 식

    문법: X [NOT] IN (예제)

    설명: X와 같은 값이 예제에 하나라도 있으면 참. IN 식의 예제에는 서브 쿼리를 사용할 수 있다.

    예: 이름이 회원1이나 회원2인 회원을 찾아라

    select m from Member m where m.username in ('회원1','회원2')

  • Like 식

    문법: 문자표현식 [NOT] LIKE 패턴값 [ESCAPE 이스케이프문자]

    설명: 문자표현식과 패턴값을 비교한다.

    • %(퍼센트): 아무 값들이 입력되어도 된다(값이 없어도 됨).

    • _(언더라인): 한 글자는 아무 값이 입력되어도 되자만 값이 있어야 한다.

      예: select m from Member m where m.username like '%원%'

      select m from Member m where m.username like '회원%'

      select m from Member m where m.username like '%회원'

      select m from Member m where m.username like '회원_'

      select m from Member m where m.username like '__3'

      select m from Member m where m.username like '회원\%' ESCAPE '\'

  • NULL 비교식

    문법: { 단일값 경로 | 입력 파라미터 } IS [NOT] NULL

    설명: NULL 인지 비교한다. NULL은 =으로 비교하면 안 되고 꼭 IS NULL을 사용해야 한다.

    예: where m.username is null

    where null = null // 거짓

    where 1=1 // 참

컬렉션 식

  • 컬렉션 식은 컬렉션에만 사용하는 특별한 기능이다.

  • 컬렉션은 컬렉션 식 외에 다른 식은 사용할 수 없다.

 

  • 빈 컬렉션 비교 식

    문법: { 컬렉션 값 연관 경로 } IS [NOT] EMPTY

    설명: 컬렉션에 값이 비었으면 참

    빈 컬렉션을 비교하는 예제:

      // JPQL: 주문이 하나라도 있는 회원 조회
  select m from Member m where m.orders is not empty
  //실행된 SQL
  select m.* from Member m
  where
      exists (
          select o.id
          from Orders o
          where m.id=o.member_id
      )

    주의점: 컬렉션은 컬렉션 식만 사용할 수 있다는 점에 주의할 것. 다음과 같이 is null처럼 컬렉션 식이 아닌 것은 사용할 수 없다

    select m from Member m where m.orders is null(오류!!)

  • 컬렉션의 멤버 식

    문법: { 엔티티나 값 } [NOT] MEMBER [OF] { 컬렉션 값 연관 경로 }

    설명: 엔티니나 값이 컬렉션에 포함되어 있으면 참

    예: select t from Team t where :memberParam member of t.members

스칼라 식

스칼라는 숫자, 문자, 날짜, case, 엔티티 타입(엔티티의 타입 정보) 같은 가장 기본적인 타입들을 의미한다.

  • 수학 식

    • +, -: 단항 연산자
    • *, /, +, -: 사칙 연산

  • 문자 함수

    • CONCAT(문자1, 문자2, ...): 문자를 합한다
    • SUBSTRING(문자, 위치, [길이]): 위치부터 시작해 길이만큼 문자를 구한다. 길이 값이 없으면 나머지 전체 길이를 반환한다.
    • TRIM([[LEADING | TRAILING | BOTH] [트림 문자] FROM] 문자):
      • LEADING: 왼쪽만
      • TRAILING: 오른쪽만
      • BOTH: 양쪽 다 트림 문자를 제거한다.
      • 기본 값은 BOTH, 트림 문자의 기본값은 공백(SPACE)다.
      • 예: TRIM(' ABC ') = 'ABC'
    • LOWER(문자): 소문자로 변경
    • UPPER(문자): 대문자로 변경
    • LENGTH(문자): 문자 길이 반환
    • LOCATE(찾을 문자, 원본 문자, [검색시작위치]): 검색위치부터 문자를 검색한다. 1부터 시작, 못 찾으면 0 반환

  • 수학함수

    • ABS(수학식): 절대값을 구한다.

      예: ABS(-10) = 10

    • SQRT(수학식): 제곱근을 구한다.

      예: SQRT(4) = 2.0

    • MOD(수학식, 나눌 수): 나머지를 구한다.

      예: MOD(4,3) = 1

    • SIZE(컬렉션 값 연관 경로식): 컬렉션의 크기를 구한다.

      예: SIZE(t.members)

    • INDEX(별칭): LIST 타입 컬렉션의 위치값을 구함, 단 컬렉션이 @OrderColumn을 사용하는 LIST 타입일 때만 사용할 수 있다.

      예: t.members m where INDEX(m) > 3

  • 날짜함수

    날짜함수는 데이터베이스의 현재 시간을 조회한다.

    • CURRENT_DATE: 현재 날짜

    • CURRENT_TIME: 현재 시간

    • CURRENT_TIMESTAMP: 현재 날짜 시간

      예: select CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP from Team t

      → 2021-01-07, 20:17:17, 2021-01-07 20:17:17.736

      종료 이벤트 조회

      select e from Event e where e.endDate < CURRENT_DATE

    • 하이버네이트는 날짜 타입에서 년, 월, 일, 시간, 분, 초 값을 구하는 기능을 지원한다.

      → YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

      예: select year(CURRENT_TIMESTAMP), month(CURRENT_TIMESTAMP), day(CURRENT_TIMESTAMP) from Member

CASE 식

특정 조건에 따라 분기할 때 CASE 식을 사용한다.

  • 기본 CASE

    문법:

      CASE
      {WHEN <조건식> THEN <스칼라식>}+
      ELSE <스칼라식>
  END

    예:

      select
      case when m.age <= 10 then '학생요금'
               when m.age >= 60 then '경로요금'
               else '일반요금'
      end
  from Member m

  • 심플 CASE

    조건식을 사용할 수 없으나 문법이 단순함. 자바의 switch case 문과 유사함.

    문법:

      CASE <조건대상>
      {WHEN <스칼라식1> THEN <스칼라식2>}+
      ELSE <스칼라식>
  END

    예:

      select
      case t.name
               when '팀A' then '인센티브110%'
               when '팀B' then '인센티브120%'
               else '인센티브105%'
      end
  from Team t

  • COALESCE

    문법: COALESCE(<스칼라식> {,<스칼라식>}+, <스칼라식>)

    설명: 스칼라식을 차례대로 조회해서 null이 아니면 마지막 <스칼라식>을 반환한다.

    예: m.username이 null이면 '이름 없는 회원을 반환하라'

    select coalesce(m.username, '이름 없는 회원') from Member m

COALESCE 추가 내용:

'자바 ORM 표준 JPA 프로그래밍' 책의 내용만으로는 잘 와닿지 않아서 Hibernate의 User Docs를 참고하여 예제를 추가함.

Simple case expression example

List<String> nickNames = entityManager.createQuery(
    "select " +
    "    case p.nickName " +
    "    when 'NA' " +
    "    then '<no nick name>' " +
    "    else p.nickName " +
    "    end " +
    "from Person p", String.class )
.getResultList();

// same as above
List<String> nickNames = entityManager.createQuery(
    "select coalesce(p.nickName, '<no nick name>') " +
    "from Person p", String.class )
.getResultList();

Searched case expression example

List<String> nickNames = entityManager.createQuery(
    "select " +
    "    case " +
    "    when p.nickName is null " +
    "    then " +
    "        case " +
    "        when p.name is null " +
    "        then '<no nick name>' " +
    "        else p.name " +
    "        end" +
    "    else p.nickName " +
    "    end " +
    "from Person p", String.class )
.getResultList();

// coalesce can handle this more succinctly
List<String> nickNames = entityManager.createQuery(
    "select coalesce( p.nickName, p.name, '<no nick name>' ) " +
    "from Person p", String.class )
.getResultList();

위 두 예제와 같이 COALESCE는 기본 CASE, 심플 CASE 문을 이용해 특정 값이 null인 경우 원하는 값을 반환하고자할 때 단순하게 쓸 수 있도록 해주는 문법이다.

 

  • NULLIF

    문법: NULLIF(<스칼라식>, <스칼라식>)

    설명: 두 값이 같으면 null을 반환하고 다르면 첫 번째 값을 반환한다. 집합 함수는 null을 포함하지 않으므로 보통 집합 함수와 함께 사용한다.

    예: 사용자 이름이 '관리자'면 null을 반환하고 나머지느 본인의 이름을 반환하라.

    select NULLIF(m.username, '관리자') from Member m

    기본 CASE 문법과 NULLIF 비교

      List<String> nickNames = entityManager.createQuery(
      "select nullif( p.nickName, p.name ) " +
      "from Person p", String.class )
  .getResultList();

  // equivalent CASE expression
  List<String> nickNames = entityManager.createQuery(
      "select " +
      "    case" +
      "    when p.nickName = p.name" +
      "    then null" +
      "    else p.nickName" +
      "    end " +
      "from Person p", String.class )
  .getResultList();

 

반응형