자주 사용하는 JPQL 문법 정리

서브 쿼리 함수

JPQL도 SQL처럼 서브 쿼리를 지원한다.

여기서는 몇 가지 제약이 있는데, 서브쿼리를 WHERE, HAVING 절에만 사용할 수 있고 SELECT, FROM 절에서는 사용할 수 없다.
하이버네이트의 HQL은 SELECT 절의 서브 쿼리도 허용한다. 하지만 아직까지 FROM 절의 서브 쿼리는 지원하지 않는다. 일부 JPA 구현체는 FROM 절의 서브 쿼리도 지원한다.

// 평균 나이보다 나이가 많은 회원 조회
"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"

 

[NOT] EXISTS

서브쿼리에 결과가 존재하면 참이다. 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 : 둘은 같은 의미다. 조건을 하나라도 만족하면 참이다.

// 상품 재고보다 주문량이 많은 주문 조회
"SELECT o FROM Order o WHERE o.orderAmount > ALL (SELECT p.stockAmount FROM Product p)"

 

[NOT] IN

서브쿼리의 결과 중 하나라도 같은 것이 있으면 참이다.

참고로 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	패키지명을 포함한 전체 이름을 사용해야 한다.	project.package.MemberType
엔티티 타입	엔티티의 타입을 표현한다. 주로 상속과 관련해서 사용한다.	TYPE(m) = Member

 

연산자 우선 순위

1. 경로 탐색 연산(.)
2. 수학 연산(+, -, *, /)
3. 비교 연산(=, >, >=, <, <=, <>)
4. 논리 연산(NOT, AND, OR)

 

[NOT] BETWEEN A AND B

A ~ B 사이의 값이면 참

// 나이가 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 '%김%'"

// 처음에 김이라는 단어가 들어간 회원 조회
"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 '_박%'";

// 특수문자가 들어갈땐 ESCAPE를 사용해야 문자열로 인식한다.
// 처음에 김\이라는 단어가 들어간 회원 조회
"SELECT m FROM Member m WHERE m.username LIKE '김\%' ESCAPE '\'";

 

NULL 비교식

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

// 이름이 NULL이 아닌 회원
"SELECT m FROM Member m WHERE m.username IS NULL

---

컬렉션 식

컬렉션 식은 컬렉션에만 사용하는 특별한 기능이다. 컬렉션 식 이 외에 다른 식은 사용할 수 없다.

IS [NOT] EMPTY

컬렉션에 값이 비었으면 참

// 한번 이상 주문한 회원 조회
"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
)"

 

{엔티티 or 값} [NOT] MEMBER [OF] {컬렉션 값 연관 경로}

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

"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 : 왼쪽만 TAILING : 오른쪾만 BOTH : 양쪽 다 트림 문자를 제거한다. 기본값은 BOTH, 트림 문자의 기본값은 공백(SPACE)다.	TRIM(' ABC ') = 'ABC'
LOWER(문자)	소문자로 변경	LOWER('ABC') = 'abc'
UPPER(문자)	대문자로 변경	UPPER('abc') = 'ABC'
LENGTH(문자)	문자 길이	LENGTH('ABC') = 3
LOCATE(찾을 문자, 원본 문자, [검색시작위치])	검색위치부터 문자를 검색한다. 1부터 시작, 못 찾으면 0 반환	LOCATE('DE', 'ABCDEFG') = 4

HQL은 CONCAT 대신에 || 도 사용할 수 있다.

 

날짜 함수

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

• CURRENT_DATE : 현재 날짜
• CURRENT_TIME : 현재 시간
• CURRENT_TIMESTAMP : 현재 날짜 시간

// 종료된 이벤트 조회 
"SELECT e FROM Event e WHERE e.endDate < CURRENT_DATE"

하이버네이트는 날짜 타입에서 년(YEAR), 월(MONTH), 일(DAY), 시간(HOUR), 분(MINUTE), 초(SECOND) 값을 구하는 기능을 지원한다.

 

데이터베이스들은 각자의 방식으로 더 많은 날짜 함수를 지원한다. 그리고 각각의 날짜 함수는 하이버네이트가 제공하는 데이터베이스 방언에 등록되어 있다. 예를 들어 오라클 방언을 사용하면 to_date, to_char 함수를 사용할 수 있다.