[JPA(Jakarta Persistence API)] JPQL 문법(SELECT, PAGING, JOIN, 서브쿼리, 조건식, 함수)

JPQL, Java Persistence Query Language

JPQL은 객체지향 쿼리 언어이기 때문에,

테이블을 대상으로 쿼리하는 것이 아니라 엔티티 객체를 대상으로 쿼리합니다.

JPQL은 SQL을 추상화해서 DB에 독립적입니다.

 

select m from Member as m where m.age > 30

엔티티와 속성은 대소문자를 구분합니다(Member, age).

JPQL 키워드는 대소문자를 구분하지 않습니다(SELECT, FROM, where).

테이블 이름이 아니라 엔티티 이름을 사용해야합니다.

별칭(as)을 사용해야 합니다(m).

(SQL과 동일하게 as는 생략할 수 있습니다)

TypeQuery, Query

TypeQuery는 반환타입이 명확할 때 사용합니다.

TypeQuery<Member> query = em.createQuery("SELECT m FROM Member m", Member.class);

Query는 반환타입이 명확하지 않을 때 사용합니다.

Query query = em.createQuery("SELECT m.name, m.age FROM Member m");

결과 조회 API

query.getResultList()을 사용했을 때,

결과가 하나 이상이면 리스트를 반환하고 결과가 없으면 빈 리스트를 반환합니다.

query.getSingleResult()는 결과가 정확 하나, 단일 객체를 반환할 때 사용합니다.

(결과가 없으면 NoResultException을, 둘 이상이면 NonUniqueResultException 예외를 발생시킵니다)

파라미터 바인딩 - 이름 기준, 위치 기준

//이름 기준
SELECT m FROM Member m where m.name=:name
query.setParameter("name", nameParam);

//위치 기준
SELECT m FROM Member m where m.name=?1
query.setParameter(1,nameParam);

이름을 기준으로 하는 것이 더 유연합니다.

프로젝션 (SELECT)

프로젝션은 SELECT 절에 조회할 대상을 지정하는 것을 말합니다.

프로젝션 대상은,

엔티티, 임베디드 타입, 스칼라 타입(숫자, 문자 등 기본 데이터 타입)이 될 수 있습니다.

// 엔티티 프로젝션
SELECT m FROM Member m
SELECT m.team FROM Member m // 조인문을 직접 작성하는 것이 최적화하기 편합니다.
SELECT t FROM Member m JOIN m.team t // 조인문을 적용한 예시입니다.

// 임베디드 타입 프로젝션
// 임베디드 타입만으로 조회가 불가능합니다.
SELECT m.address FROM Member m // 소유한 엔티티를 통해 프로젝션합니다.

// 스칼라 타입 프로젝션
SELECT distinct m.name, m.age FROM Member m // 원하는 값만 프로젝션 합니다.

DISTINCT로 중복을 제거할 수 있습니다.

 

타입이 다른 필드 여러 개를 프로젝션으로 조회할 때,

Query 타입, Object[] 타입, new 명령어로 조회할 수 있습니다.

new 명령어를 사용하여 값을 DTO로 받아올 수 있습니다.

SELECT new com.ride.UserDTO(m.name, m.age) FROM Member m

public class UserDTO {
    public UserDTO(String name, int age) { // 생성자를 통해 호출됩니다.
        this.name = name;
        this.age = age;
    }
    private String name;
    private int age;
}

이 때, 패키지 명을 포함한 전체 클래스 명을 입력해야 합니다.

그리고 반환 값과 DTO의 순서, 타입을 일치시켜야 합니다.

페이징

JPA는 페이징을 API로 추상화합니다.

setFirtResult(int startPosition) : 조회 시작 위치(0부터 시작)

setMaxResults(int maxResult) : 조회할 데이터 수

String jpql = "SELECT m FROM Member m ORDER BY m.name desc";
List<Member> resultList = em.createQuery(jpql, Member.class)
    .setFirstResult(0) //offset
    .setMaxResults(10) //limit
    .getResultList();

아래는 페이징에 대한  SQL문입니다.

//오라클
SELECT * FROM (
  SELECT a.*, ROWNUM rnum FROM (
    SELECT * FROM 테이블명 ORDER BY 컬럼명
  ) a WHERE ROWNUM <= (시작번호 + 가져올행수)
) WHERE rnum >= 시작번호;

//오라클12c 이상 버전
SELECT * FROM 테이블명
ORDER BY 컬럼명
OFFSET 시작번호 ROWS
FETCH NEXT 가져올행수 ROWS ONLY;

//MySQL
SELECT * FROM 테이블명
ORDER BY 컬럼명
LIMIT 가져올행수 OFFSET 시작번호;

//MySQL
SELECT * FROM 테이블명
ORDER BY 컬럼명
LIMIT 시작번호, 가져올행수;

//MS SQL Server
SELECT * FROM 테이블명
ORDER BY 컬럼명
OFFSET 시작번호 ROWS
FETCH NEXT 가져올행수 ROWS ONLY;

 

조인

SQL조인과 JPQL 조인은 동일하지만 엔티티 위주로 동작합니다.

// 내부 조인
SELECT m FROM Member m [INNER] JOIN m.team t

// 외부 조인
SELECT m FROM Member m LEFT [OUTER] JOIN m.team t

// 세타 조인
// 연관관계가 없는 것을 아래와 같은 문법을 사용하여 비교할 수도 있습니다.
select count(m) from Member m, Team t where m.name=t.name

JPQL에서 ON절을 활용하여 조인대상을 필터링하거나,

회원과 팀을 조인하면서, 팀 이름이 A인 팀만 조인
// 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'

연관관계가 없는 엔티티 외부 조인을 할 수 있습니다.

(하이버네이트 5.1미만에선 내부 조인만 지원했습니다.)

회원의 이름과 팀의 이름이 같은 대상 외부 조인
// JPQL
SELECT m, t FROM Member m LEFT JOIN Team t ON m.name=t.name

//SQL
SELECT m.*, t.*
FROM Member m
LEFT JOIN Team t
ON m.name=t.name

서브쿼리

JPQL의 서브쿼리는 SQL에서의 서브쿼리와 같은 개념입니다.

(메인 쿼리와 서브쿼리의 관계가 없도록 아래처럼 (m, m2) 작성해야 성능이 잘 나옵니다)

// 나이가 평균보다 많은 회원
select m from Member m
where m.age > (select ave(m2.age) from Member m2)

// 한 건이라도 주문한 고객
select m from Member m
where (select count(o) from Order o where m = o.member) > 0

서브쿼리에서 지원하는 함수가 있습니다.

// 서브쿼리 결과가 존재하면 참
[NOT] EXISTS (subquery)
    {ALL | ANY | SOME} (subquery)
    ALL // 모두 만족하면 참
    ANY, SOME // 조건을 하나라도 만족하면 참
    
// 서브쿼리 결과 중 하나라도 같은 것이 있으면 참
[NOT] IN (subquery)

아래는 그 예제입니다.

// 팀 A 소속인 회원
select m from Member m
where exists (select t from m.team t where t.name = 'A')

// 전체 상품 각각의 재고보다 주문량이 많은 주문들
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)

JPA 서브쿼리에는 한계가 있습니다.

표준스펙에선 WHERE, HAVING 절에서만 사용할 수 있으며,

하이버네이트에서는 SELECT 절에서도 사용이 가능합니다.

FROM 절의 서브 쿼리는 JPQL에서 불가능하여 조인으로 풀어서 사용합니다.

(Native 쿼리를 사용하거나 쿼리 두 번으로 나누는 방법도 가능합니다)

JPQL 타입 표현식과 기타식

JPQL의 타입 표현은 문자, 숫자, Boolean, ENUM, 엔티티 타입이 있으며,

파라미터 바인딩하여 사용합니다.

// 문자 - 따옴표를 표현하려면 아래와 같이 따옴표를 하나 더 작성합니다
'HELLO', 'She''s'
HELLO She's

//숫자
10L //Long
10D //Double
10F //Float

//Boolean
TRUE, FALSE

//ENUM - 패키지명을 포함해야 합니다
com.ride.RoleType.Admin

//엔티티 타입 - 상속관계에서 사용합니다
TYPE(i) = Book
...
select i
from Item i
where type(i) = Book

기타 식은 SQL과 문법이 같습니다.

EXISTS, IN
AND, OR, NOT
=, >, >=, <, <=, <>
BETWEEN, LIKE, IS NULL

조건식 (CASE 등)

조건식은 Java의 Switch case문과 유사합니다.

 

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

// 단순 CASE 식
select
    case t.name
        when 'A' then '110%'
        when 'B' then '120%'
        else '105%'
    end
from Team t

// COALESCE - 하나씩 조회해서 null이 아니면 반환
// 사용자 이름이 없으면 unkown을 반환
select coalesce(m.name, 'unkown') from Member m

// NULLIF - 두 값이 같으면 null 반환, 다르면 첫번째 값 반환
// 사용자 이름이 admin이면 null 반환, 나머지는 본인의 이름을 반환
select NULLIF(m.name, 'admin') from Member m

JPQL 기본 함수

JPQL 기본 함수는 DB에 종속되지 않기 때문에, 필요한 상황에서 언제든 사용할 수 있습니다.

CONCAT // 문자 더하기
select 'a' || 'b' from Member m
select concat('a', 'b') from Member m

SUBSTRING // 문자 슬라이스
select substring(m.name, 2, 3) from Member m

TRIM // 공백 제거 lTrim, rTrim
LOWER, UPPER // 소문자, 대문자로 변경
LENGTH // 길이
LOCATE // 위치 찾기 - Integer 반환
select locate('de', 'abcdefg') from Member m //4

ABS, SQRT, MOD // Math
SIZE // 컬렉션 크기
select size(t.members) from Team t

INDEX // JPA 의 @OrderColumn에 사용

사용자 정의 함수

하이버네이트에 dialect를 추가하고 함수를 등록합니다.

DB에 종속적이지만, 기본 함수 외의 기능을 편의에 따라 사용할 수 있게 합니다.

public class CustomMySQLDialect extends MySQLDialect {
    public CustomMySQLDialect() {
        registerFunction("group_concat", new StandardSQLFunction("group_concat", StandardBasicTypes.String));
    }
}

select function('group_concat', i.name) from Item i
// 하이버네이트 구현체를 사용하면 아래와 같이 사용할 수도 있습니다.
select group_concat(i.name) from Item i