JPQL

프로그래밍/JPA

JPQL

seungdols 2024. 1. 10. 00:07

JPQL Type Expression

문자
- 'Hello'
숫자
- 10L(Long)
- 10D(Double)
- 10F(Float)
Boolean
ENUM
- package path 포함
Entity
- TYPE(m) = 상속관계
- select i from Item i where type(i) = Book

조건식 - 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이 아니면 반환

select coalesce(m.username, '이름 없는 회원') from Member m

NULLIF: 두 값이 같으면 null 반환, 다르면 첫번째 값 반환
```
select NULLIF(m.username, '관리자') from Member m
```

JPQL 기본 함수

concat
substring
trim
lower, upper
length
locate
abs, sort, mod
size, index (JPA 용도)

사용자 정의 함수 호출

하이버네이트는 사용전 방언에 추가해야 한다.
- select function('group_concat', i.name) from Item i

경로 표현식

.을 찍어 객체 그래프를 탐색하는 것

select m.username -> 상태 필드
from Member m
join m.team t -> 단일 값 연관 필드
join m.orders o -> 컬렉션 값 연관 필드
where t.name = '팀A'

상태 필드

단순히 값을 저장하기 위한 필드
연관 필드
연관 관계를 위한 필드
- 단일 값 연관 필드
  - @ManyToOne, @OneToOne, 대상이 entity
- 컬렉션 값 연관 필드
  - @OneToMany, @ManyToMany, 대상이 컬렉션

특징

상태 필드: 경로 탐색의 끝, 탐색 X
단일 값 연관 경로: 묵시적 내부 조인(inner join) 발생, 탐색 O
- JPQL: select o.member from Ordero
- SQL: select m.* from Orders o inner join Member m on o.member_id = m.id
컬렉션 값 연관 경로: 묵시적 내부 조인 발생, 탐색 X
- FROM 절에서 명시적 조인을 통해 별칭을 얻으면 별칭을 통해 탐색 가능
- select m.name From Team t join t.members m
묵시적 내부 조인이 발생하지 않도록 코드를 작성해야 함.

명시적 조인

select m from Member m joint m.team t
묵시적 조인
select m.team from Member m

예시

select o.member.team from Order o -> OK
select t.members from Team -> OK
select t.members.username from Team t -> NO (collection type이기 때문에 탐색 불가)
select m.username from Team t join t.members m -> OK

Fetch Join

SQL 조인 종류 X
JPQL에서 성능 최적화를 위해 제공하는 기능
연관 된 엔티티나 컬렉션을 SQL 한 번에 함께 조회 하는 기능
회원을 조회하면서 연관된 팀도 함께 조회
- JPQL: select m from Member m join fetch m.team
- SQL: select M.*, T.* from member M inner join team T on M.team_id = T.id

Team teamA = new Team();  
teamA.setName("TeamA");  

Team teamB = new Team();  
teamB.setName("TeamB");  

em.persist(teamA);  
em.persist(teamB);  

Member member1 = new Member();  
member1.setUsername("회원1");  
member1.setAge(10);  
member1.setTeam(teamA);  

Member member2 = new Member();  
member2.setUsername("회원2");  
member2.setAge(10);  
member2.setTeam(teamA);  
Member member3 = new Member();  
member3.setUsername("회원3");  
member3.setAge(10);  
member3.setTeam(teamB);  

em.persist(member1);  
em.persist(member2);  
em.persist(member3);  

em.flush();  
em.clear();  

String query = "select m from Member m";  
List<Member> resultList = em.createQuery(query, Member.class)  
        .getResultList();  

// 회원1, 팀A(SQL)  
// 회원2, 팀A(1차 캐시)  
// 회원3, 팀B(SQL) --> N + 1  
for (Member member : resultList) {  
    System.out.println("member = " + member.getUsername() + ", " + member.getTeam().getName());  
}

/* select
        m 
    from
        Member m */ select
            member0_.id as id1_0_,
            member0_.age as age2_0_,
            member0_.TEAM_ID as team_id4_0_,
            member0_.username as username3_0_ 
        from
            Member member0_
Hibernate: 
    select
        team0_.id as id1_1_0_,
        team0_.name as name2_1_0_ 
    from
        Team team0_ 
    where
        team0_.id=?
member = 회원1, TeamA
member = 회원2, TeamA
Hibernate: 
    select
        team0_.id as id1_1_0_,
        team0_.name as name2_1_0_ 
    from
        Team team0_ 
    where
        team0_.id=?
member = 회원3, TeamB

fetch join 적용

String query = "select m from Member m join fetch m.team";  
List<Member> resultList = em.createQuery(query, Member.class)  
        .getResultList();  

for (Member member : resultList) {  
    System.out.println("member = " + member.getUsername() + ", " + member.getTeam().getName());  
}

    /* select
        m 
    from
        Member m 
    join
        fetch m.team */ select
            member0_.id as id1_0_0_,
            team1_.id as id1_1_1_,
            member0_.age as age2_0_0_,
            member0_.TEAM_ID as team_id4_0_0_,
            member0_.username as username3_0_0_,
            team1_.name as name2_1_1_ 
        from
            Member member0_ 
        inner join
            Team team1_ 
                on member0_.TEAM_ID=team1_.id
member = 회원1, TeamA
member = 회원2, TeamA
member = 회원3, TeamB

collection fetch join

1:N 관계, 컬렉션 fetch join 의 경우, 데이터가 뻥튀기 됨.
JPQL: select t from Team t join fetch t.members where t.name = '팀A'
SQL: select T.*, M.* from team T inner join member M on T.id =M.team_id where T.name=팀A

String query = "select t from Team t join fetch t.members";  
List<Team> resultList = em.createQuery(query, Team.class)  
        .getResultList();  

for (Team team : resultList) {  
    System.out.println("team = " + team.getName() + ", " + team.getMembers().size());  
}

    /* select
        t 
    from
        Team t 
    join
        fetch t.members */ select
            team0_.id as id1_1_0_,
            members1_.id as id1_0_1_,
            team0_.name as name2_1_0_,
            members1_.age as age2_0_1_,
            members1_.TEAM_ID as team_id4_0_1_,
            members1_.username as username3_0_1_,
            members1_.TEAM_ID as team_id4_0_0__,
            members1_.id as id1_0_0__ 
        from
            Team team0_ 
        inner join
            Member members1_ 
                on team0_.id=members1_.TEAM_ID
team = TeamA, 2
team = TeamA, 2
team = TeamB, 1

String query = "select t from Team t join fetch t.members where t.name = 'TeamA'";  
List<Team> resultList = em.createQuery(query, Team.class)  
        .getResultList();  

for (Team team : resultList) {  
    System.out.println("team.name = " + team.getName() + ", team=" + team);  
    for (Member member : team.getMembers()) {  
        System.out.println("-> username: " + member.getUsername() + ", member=" + member);  
    }  
}

team.name = TeamA, team=com.study.jpa.jpql.Team@2f860823
-> username: 회원1, member=com.study.jpa.jpql.Member@52b06bef
-> username: 회원2, member=com.study.jpa.jpql.Member@7af3874e
team.name = TeamA, team=com.study.jpa.jpql.Team@2f860823
-> username: 회원1, member=com.study.jpa.jpql.Member@52b06bef
-> username: 회원2, member=com.study.jpa.jpql.Member@7af3874e

fetch join 과 DISTINCT

SQL의 distinct는 중복된 결과를 제거
JPQL의 distinct 2가지 기능 제공
- SQL에 distinct 추가
  - 데이터가 다르므로 SQL 결과에서는 중복 제거 실패
- 어플리케이션에서 엔티티 중복제거


String query = "select distinct t from Team t join fetch t.members";  
List<Team> resultList = em.createQuery(query, Team.class)  
        .getResultList();  

for (Team team : resultList) {  
    System.out.println("team.name = " + team.getName() + ", team=" + team);  
    for (Member member : team.getMembers()) {  
        System.out.println("-> username: " + member.getUsername() + ", member=" + member);  
    }  
}
    /* select
        distinct t 
    from
        Team t 
    join
        fetch t.members */ select
            distinct team0_.id as id1_1_0_,
            members1_.id as id1_0_1_,
            team0_.name as name2_1_0_,
            members1_.age as age2_0_1_,
            members1_.TEAM_ID as team_id4_0_1_,
            members1_.username as username3_0_1_,
            members1_.TEAM_ID as team_id4_0_0__,
            members1_.id as id1_0_0__ 
        from
            Team team0_ 
        inner join
            Member members1_ 
                on team0_.id=members1_.TEAM_ID
team.name = TeamA, team=com.study.jpa.jpql.Team@32fa809f
-> username: 회원1, member=com.study.jpa.jpql.Member@e76b097
-> username: 회원2, member=com.study.jpa.jpql.Member@49f40c00
team.name = TeamB, team=com.study.jpa.jpql.Team@643d2dae
-> username: 회원3, member=com.study.jpa.jpql.Member@52d6d273

fetch join과 일반 join의 차이

일반 조인의 경우 연관된 엔티티를 함께 조회하지 않음
JPQL은 결과를 반환할 때 연관관계 고려 X
단지 select 절에 지정한 엔티티만 조회.
- 아래 쿼리에서는 Team만 조회하고, members에 대해서 조회하지 않음.
fetch join을 사용할때만, 연관된 엔티티도 함께 조회 (즉시 로딩)
fetch join은 객체 그래프를 SQL 한번에 조회 하는 개념

String query = "select distinct t from Team t join t.members";  
List<Team> resultList = em.createQuery(query, Team.class)  
        .getResultList();  

for (Team team : resultList) {  
    System.out.println("team.name = " + team.getName() + ", team=" + team);  
    for (Member member : team.getMembers()) {  
        System.out.println("-> username: " + member.getUsername() + ", member=" + member);  
    }  
}

   /* select
        distinct t 
    from
        Team t 
    join
        t.members */ select
            distinct team0_.id as id1_1_,
            team0_.name as name2_1_ 
        from
            Team team0_ 
        inner join
            Member members1_ 
                on team0_.id=members1_.TEAM_ID
team.name = TeamA, team=com.study.jpa.jpql.Team@4ba02375
Hibernate: 
    select
        members0_.TEAM_ID as team_id4_0_0_,
        members0_.id as id1_0_0_,
        members0_.id as id1_0_1_,
        members0_.age as age2_0_1_,
        members0_.TEAM_ID as team_id4_0_1_,
        members0_.username as username3_0_1_ 
    from
        Member members0_ 
    where
        members0_.TEAM_ID=?
-> username: 회원1, member=com.study.jpa.jpql.Member@204abeff
-> username: 회원2, member=com.study.jpa.jpql.Member@3456558
team.name = TeamB, team=com.study.jpa.jpql.Team@788ba63e
Hibernate: 
    select
        members0_.TEAM_ID as team_id4_0_0_,
        members0_.id as id1_0_0_,
        members0_.id as id1_0_1_,
        members0_.age as age2_0_1_,
        members0_.TEAM_ID as team_id4_0_1_,
        members0_.username as username3_0_1_ 
    from
        Member members0_ 
    where
        members0_.TEAM_ID=?
-> username: 회원3, member=com.study.jpa.jpql.Member@58fa5769

fetch join의 특징과 한계

fetch join 대상에는 별칭을 줄 수 없다.
- 하이버네이트는 가능하나, 가급적 사용 안하는 게 좋음.
둘 이상의 컬렉션은 fetch join 불가능
엔티티에 직접 적용하는 글로벌 로딩 전략보다 우선함.
실무에서 글로벌 로딩 전략은 모두 지연 로딩
최적화가 필요한 곳은 fetch join 적용
모든 것을 fetch join으로 해결 할 수는 없음.
fetch join은 객체 그래프를 유지할 때 사용하면 효과적.
여러 테이블을 조인해서 엔티티가 가진 모양이 아닌 전혀 다른 결과를 내야하면, fetch join 보다는 일반 조인을 사용하고 필요한 데이터들만 조회해서 dto로 반환 하는 것이 효과적
컬렉션을 fetch join 하면 paging API를 사용할 수 없다.
- 일대일, 다대일 같은 단일 값 연관 필드들은 fetch join 해도 페이징 가능
- 하이버네이트는 경고 로그를 남기고 메모리에서 페이징 (매우 위험)


String query = "select t from Team t join fetch t.members";  
List<Team> resultList = em.createQuery(query, Team.class)  
                          .setFirstResult(0)  
                          .setMaxResults(1)  
                          .getResultList();
WARN: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

String query = "select m from Member m join fetch m.team t";

방향을 다대일로 쿼리하여 데이터를 뽑을 수 있음.

@BatchSize를 이용하는 방법

@OneToMany(mappedBy = "team")  
@BatchSize(size = 100)  
private List<Member> members = new ArrayList<>();

String query = "select t from Team t";  
List<Team> resultList = em.createQuery(query, Team.class)  
                          .setFirstResult(0)  
                          .setMaxResults(2)  
                          .getResultList();  

for (Team team : resultList) {  
    System.out.println("team.name = " + team.getName() + ", team=" + team);  
    for (Member member : team.getMembers()) {  
        System.out.println("-> username: " + member.getUsername() + ", member=" + member);  
    }  
}

    /* select
        t 
    from
        Team t */ select
            team0_.id as id1_1_,
            team0_.name as name2_1_ 
        from
            Team team0_ limit ?
team.name = TeamA, team=com.study.jpa.jpql.Team@194d329e
Hibernate: 
    /* load one-to-many com.study.jpa.jpql.Team.members */ select
        members0_.TEAM_ID as team_id4_0_1_,
        members0_.id as id1_0_1_,
        members0_.id as id1_0_0_,
        members0_.age as age2_0_0_,
        members0_.TEAM_ID as team_id4_0_0_,
        members0_.username as username3_0_0_ 
    from
        Member members0_ 
    where
        members0_.TEAM_ID in (
            ?, ?
        )

다형성 쿼리

조회 대상을 특정 자식으로 한정
- JPQL: select i from item i where type(i) IN (Book, Movie)
- SQL: select i from i where i.DTYPE in ('B', 'M')
TREAT (JPA 2.1)
- 자바의 타입 캐스팅과 유사
- 상속 구조에서 부모 타입을 특정 자식 타입으로 다룰때 사용
- FROM, WHERE, SELECT(hibernate 지원) 사용
  - JPQL: select i from Item i where treat(i as Book).auther='kim'
  - SQL: select i.* from Item i where i.DTYPE='B' and i.auther = 'kim'

엔티티 직접 사용

기본 키 값

JPQL에서 엔티티를 직접 사용하면 SQL에서 해당 엔티티의 기본 키 값을 사용
- JPQL
  - select count(m.id) from Member m // 엔티티의 아이디를 사용
  - select count(m) from Member m // 엔티티를 직접 사용
- SQL (두 JPQL이 같은 sql로 치환)
  - select count(m.id) as cnt from Member m

String query = "select m from Member m where m = :member";  
Member findMember = em.createQuery(query, Member.class)  
                            .setParameter("member", member1)  
                            .getSingleResult();  

System.out.println("findMember: " + findMember.getUsername());

외래키 값

String query = "select m from Member m where m.team = :team";  
List<Member> result = em.createQuery(query, Member.class)  
                            .setParameter("team", teamA)  
                            .getSingleResult();

SQL

select m.* from Member m where m.team_id=?

Named 쿼리

@Entity 
@NamedQuery(
    name = "Member.findByUsername",
    query = "select m from Member m where m.username = :username "
)


List<Member> resultList = em.createNamedQuery("Member.findByUsername", Member.class)
.setParameter("username", "회원1")
.getResultList();

미리 정의해서 이름을 부여해두고 사용하는 JPQL
정적 쿼리
Annotation, XML에 정의 가능
- XML이 항상 우선 순위가 높음.
- 어플리케이션 운영 환경에 따라 다른 XML을 배포할 수 있음.
Application 로딩 시점에 초기화 후 재사용
Application 로딩 시점에 쿼리를 검증

벌크 연산

재고가 10개 미만인 모든 상품의 가격을 10% 상승하려면?
JPA 변경 감지 기능으로 실행하려면 너무 많은 SQL이 실행 됨.
- 재고가 10개 미만인 상품 리스트 조회
- 상품 엔티티의 가격을 10% 증가
- 트랜잭션 커밋 시점에 변경 감지 동작
변경 된 데이터가 100건이라면 100번의 UPDATE SQL가 실행

예시

쿼리 한 번으로 여러 테이블 Row 변경 (엔티티)
executeUpdate()의 결과는 영향 받는 엔티티 수 반환
UPDATE, DELETE 지원
INSERT(insert into...select, 하이버네이트 지원)

String query = "update Product p" +
                "set p.price = p.price * 1.1 "+
                "where p.stockAmount < :stockAmount";
    int resultCount = em.createQuery(query)
                        .setParameter("stockAmount", 10)
                        .executeUpdate();

주의할 점

벌크 연산은 영속성 컨텍스트를 무시하고, 데이터베이스에 직접 쿼리
- 해결 방안
  - 벌크 연산을 먼저 실행 하는 방법
  - 벌크 연산 후 영속성 컨텍스트 초기화 하는 방법

저작자표시 비영리 변경금지