JPQL도 SQL과 같이 서브 쿼리 지원.
대신 WHERE, HAVING 절에만 사용 O, SELECT, FROM 절에서는 사용 X
+) 하이버네이트의 HQL은 SELECT 절 O, 일부 JPA 구현체 FROM 절 O
// 나이가 평균보다 많은 회원
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 Oder o where m = o.member) > 0
// 컬렉션 값 연관 필드의 size 기능 사용 (위와 같은 결과, 실행되는 SQL도 같음)
select m from Member m
where m.orders.size > 0
서브 쿼리 함수
- [NOT] EXISTS (subquery)
- {ALL | ANY | SOME} (subquery)
- [NOT] IN (subquery)
EXISTS
// 팀A 소속인 회원
select m from Member m where EXISTS (select t from m.team t where t.name = '팀A')
{ALL | ANY | 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
// 20세 이상을 보유한 팀
select t from Team t where t IN (select t2 from Team t2 JOIN t2.members m2 where m2.age >= 20)
- 타입 표현
: JPQL에서 사용하는 타입, 대소문자 구분 X
종류 | 설명 | 예제 |
---|---|---|
문자 | 작은 따옴표 사이에 표현 (작은 따옴표 표현하고 싶으면 작은 따옴표 연속 두개('') 사용) | '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 |
엔티티 타입 | 엔티티의 타입 표현 (주로 상속 관련해 사용) | TYPE(m) = Member |
- 연산자 우선 순위
- 경로 탐색 연산(.)
- 수학 연산 : +, -(단항 연산자), *, /, +, -
- 비교 연산 : =, >, >=, <, <=, <>(다름), [NOT] BETWEEN, [NOT] LIKE, [NOT] IN, IS [NOT] NULL, IS [NOT] EMPTY, [NOT] MEMBER [OF], [NOT] EXISTS
- 논리 연산 : NOT, AND, OR
Between 식
// 나이가 10~20인 회원
select m from Member m where m.age BETWEEN 10 AND 20
IN 식
// 이름이 회원1이나 회원2인 회원
select m from Member m where m.username IN ('회원1', '회원2')
Like 식
// 중간에 원이라는 단어가 들어가는 회원 (좋은 회원, 회원, 원)
select m from Member m where m.username LIKE '%원%'
// 처음에 회원이라는 단어가 들어가는 회원 (회원1, 회원ABC)
select m from Member m where m.username LIKE '회원%'
// 마지막에 회원이라는 단어가 들어가는 회원 (좋은 회원, A회원)
select m from Member m where m.username LIKE '%회원'
// (회원A, 회원1)
select m from Member m where m.username LIKE '회원_'
// (회원3)
select m from Member m where m.username LIKE '__3'
// (회원%)
select m from Member m where m.username LIKE '회원\%' ESCAPE '\'
NULL 비교식
where m.username IS NULL
where null = null // 거짓
where 1 = 1 //참
컬렉션 식
: 컬렉션에만 사용하는 특별한 기능 (컬렉션은 컬렉션 식 이외에 다른 식 사용 X)
빈 컬렉션 비교 식
// 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)
// * 오류 *
select m from Member m where m.orders IS NULL
컬렉션 멤버 식
select t from Team t where :memberParam MEMBER OF t.members
스칼라 식
: 스칼라(숫자, 문자, 날짜, case, 엔티티 타입(엔티티의 타입 정보) 같은 가장 기본 타입) 타입에 사용하는 식
수학 식
문자 함수
함수 | 설명 | 예제 |
---|---|---|
CONCAT(문자1, 문자2, ...) | 문자를 합함 | CONCAT('A', 'B') = 'AB' |
SUBSTRING(문자, 위치, [길이]) | 위치부터 시작해 길이만큼 문자를 구함 (길이 값 없으면 나머지 전체 길이 뜻함) | SUBSTRING('ABCDEF', 2, 3) = BCD |
TRIM([ [LEADING | TRAILING | BOTH][트림 문자] FROM ] 문자) | LEADING(왼쪽), TRAILING(오른쪽), BOTH(양쪽) 다 트림 문자 제거 (기본값 BOTH, 트림 문자 기본값 공백(SPACE)) | TRIM(' ABC ') = 'ABC' |
LOWER(문자) | 소문자로 | LOWER('ABC') = 'abc' |
UPPER(문자) | 대문자로 | UPPER('abc') = 'ABC' |
LOCATE(찾을 문자, 원본 문자, [검색시작위치]) | 검색위치부터 문자 검색 (1부터 시작, 못 찾으면 0 반환) | LOCATE('DE', 'ABCDEFG') = 4 |
+) HQL 은 CONCAT 대신 || 사용 O
수학 함수
함수 | 설명 | 예제 |
---|---|---|
ABS(수학식) | 절대값 | ABS(-10) = 10 |
SQRT(수학식) | 제곱근 | SQRT(4) = 2.0 |
MOD(수학식, 나눌 수) | 나머지 | MOD(4, 3) = 1 |
SIZE(문자) | 컬렉션 크기 | SIZE(t.members) |
INDEX(문자) | LIST 타입 컬렉션의 위치값 (컬렉션이 @OrderColumn을 사용하는 LIST 타입일 때만 사용 O) | t.members m where INDEX(m) > 3 |
날짜 함수
: 데이터베이스의 현재 시간 조회
select CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP from Team t
// 결과 : 2013-08-19, 23:38:17, 2013-08-19 23:38: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
- 데이터 베이스들은 각자의 방식으로 더 많은 날짜 함수 지원
- 각각의 날짜 함수는 하이버네이트가 제공하는 데이터베이스 방언에 등록 됨
ex) 오라클 방언 -> to_date, to_char 함수 사용 가능 (다른 DB 사용하면 동작 X)
CASE
{WHEN <조건식> THEN <스칼라식>}+
ELSE <스칼라식>
END
// 사용 예
select
CASE WHEN m.age <= 10 THEN '학생요금'
WHEN m.age >= 60 THEN '경로요금'
ELSE '일반요금'
END
from Member m
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
// m.username이 null이면 다음 스칼라식인 '이름 없는 회원' 반환
select COALESCE(m.username, '이름 없는 회원') from Member m
// 사용자 이름이 '관리자'면 null 반환, 나머지는 본인 이름 반환
select NULLIF(m.username, '관리자') from Member m
출처 : 자바 ORM 표준 JPA 프로그래밍 책