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

조인

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();