EP12. JPQL (SQL식 JPQL변환)

JPQL 프로젝션

 

엔티티 프로젝션

영속성 컨텍스트로 관리돼서 set으로 수정해도 update 쿼리 나간다.

SELECT m FROM Member b

SELECT m.team FROM Member b -> SELECT t FROM Member m JOIN m.team t

m.team은 오른쪽껄로 사용해야한다. 둘다 동일한 쿼리가 나가지만 왼쪽은 JPQL을 봤을때 어떤 SQL이 나갈지 쉽게 유추가 안되기 때문에 쉽게 예측이 가능한 오른쪽꺼 처럼 JOIN 명시해서 사용한다. 

 

임베디드 타입 프로젝션

SELECT m.address FROM Member b

 

스칼라 타입 프로젝션

SELECT m.username, m.age FROM Member b

여러가지를 가져오면 타입을 모르기 때문에 new 명령어로 조회한다.

MemberDTO를 생성하고 new로 생성자로 받는다.

 

코드

MemberDTO class 생성

package jpql;

public class MemberDTO {

    private String username;
    private int age;

    public MemberDTO(String username, int age) {
        this.username = username;
        this.age = age;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

new 명령어 사용

List<MemberDTO> result = em.createQuery("select new jpql.MemberDTO(m.username, m.age) from Member m", MemberDTO.class)
                .getResultList();

MemberDTO memberDTO = result.get(0);

 

나중에 queryDSL을 사용하면 깔끔하게 사용가능하다.

 

 

페이징 API

 

List<Member> result = em.createQuery("select m from Member m order by m.age desc", Member.class)
                .setFirstResult(10)
                .setMaxResults(10)
                .getResultList();

setFirstResult(10) : index 10부터

setMaxResults(10) : 10개를 뽑는다.

 

 

 

 

조인

내부 조인

select m from Member m [inner] join m.team t

 

외부 조인

select m from Member m left [outer] join m.team t

 

세타 조인

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

 

ON 절

1. 조인 대상 필터링

예) 회원과 팀을 조인하면서, 팀 이름이 A인 팀만 조인

select m from Member m left join m.team t on t.name = 'A'

 

2. 연관관계 없는 엔티티 외부 조인

예) 회원의 이름과 팀의 이름이 같은 대상 외부 조인

select m, t from Member m left join Team t on m.username = t.name

 

 

서브쿼리

예) 나이가 평균보다 많은 회원

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] EXIST : 결과가 존재하[지 않으]면  참

ex. 팀 A소속인 회원

select m from Member m

where exists (select t from m.team t where t.name='teamA')

 

ALL : 모두 만족하면 참

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

select o from Order o

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

 

ANY, SOME : 조건을 하나라도 만족하면 참

ex. 어떤 팀이든 팀에 소속된 회원

select m from Member m

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

 

[NOT] IN : 서브쿼리의 결과 중 하나라도 같은 것이 있으면 참

 

 

 

주의점

서브쿼리 가능 여부

	where	having	select	from
JPA	o	o	x	x
Hibernate	o	o	o	x

 

결론 : from 절의 서브쿼리는 조인으로 풀어서 사용한다. (조인도 안되면 네이티브 쿼리를 사용하던지, 애플리케이션에서 조작한다.)

 

 

 

JPQL 타입 표현

문자 : 'HELLO', 'She''s'

* '를 표현하려면 앞에 '를 한번 더 넣어준다.

 

숫자 : 10L(Long), 10D(Double), 10F(Float)

Boolean : TRUE, FALSE

ENUM : jpabook.MemberType.Admin (패키지명 포함)

엔티티 타입 : TYPE(m) = Member (상속 관계에서 사용)

 

 

BETWEEN, LIKE, IS NULL 은 SQL과 똑같이 사용 가능

 

 

조건식 - CASE 식

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

            List<String> result = em.createQuery(query, String.class)
                    .getResultList();

            for (String s : result) {
                System.out.println("s = " + s);
            }

 

COALESCE : 하나씩 조회해서 null이 아니면 반환

ex) 사용자 이름이 없으면 "이름 없는 회원"을 반환

String query = "select coalesce(m.username, '이름 없는 회원') from Member m ";

 

NULLIF : 두 값이 같으면 null 반환, 다르면 첫번째 값 반환

ex) 사용자 이름이 '관리자'면 null을 반환하고 나머지는 본인의 이름을 반환

String query = "select NULLIF(m.username, '관리자') from Member m ";

 

 

JPQL 기본 함수

• CONCAT (문자열 합치기)
• SUBSTRING
• TRIM
• LOWER, UPPER
• LENGTH
• LOCATE (문자열 위치)
• ABS, SQRT, MOD
• SIZE, INDEX(JPA 용도)

 

 

 

사용자 정의 함수

 

새로운 클래스를 생성해서 사용하는 DB방언을 상속받고, 사용자 정의 함수 등록하기

package dialect;

import org.hibernate.dialect.H2Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class MyH2Dialect extends H2Dialect {

    public MyH2Dialect() {
        registerFunction("group_concat", new StandardSQLFunction("group_concat", StandardBasicTypes.STRING));
    }
}

 

사용

String query = "select function('group_concat', m.username) from Member m ";

또는

String query = "select group_concat(m.username) from Member m ";