<div id="readability-page-1" class="page"><p><cite>seungdols</cite>
                <span> 2024. 1. 10. 00:07</span>
              </p><div>
                                <h3>JPQL Type Expression</h3>
<ul>
<li>문자<ul>
<li>'Hello'</li>
</ul>
</li>
<li>숫자<ul>
<li>10L(Long)</li>
<li>10D(Double)</li>
<li>10F(Float)</li>
</ul>
</li>
<li>Boolean</li>
<li>ENUM<ul>
<li><strong>package path 포함</strong></li>
</ul>
</li>
<li>Entity<ul>
<li>TYPE(m) = 상속관계</li>
<li><code>select i from Item i where type(i) = Book</code></li>
</ul>
</li>
</ul>
<h3>조건식 - CASE 식</h3>
<ul>
<li><p>기본 CASE 식</p>
<pre><code>select
  case when m.age &lt;= 10 then '학생요금'
       when m.age &gt;= 60 then '경로요금'
       else '일반요금'
   end
from Member m     </code></pre></li>
<li><p>단순 CASE 식</p>
<pre><code>select 
  case t.name
      when '팀A' then '인센티브 110%'
      when '팀B' then '인센티브 120%'
      else '인센티브 105%'
  end
from Team t</code></pre></li>
<li><p>COALESCE: 하나씩 조회해서 null이 아니면 반환 </p>
<pre><code>select coalesce(m.username, '이름 없는 회원') from Member m</code></pre></li>
<li><p>NULLIF: 두  값이 같으면 null 반환, 다르면 첫번째 값 반환 </p>
<pre><code>select NULLIF(m.username, '관리자') from Member m</code></pre></li>
</ul>
<h3>JPQL 기본 함수</h3>
<ul>
<li>concat</li>
<li>substring</li>
<li>trim</li>
<li>lower, upper</li>
<li>length</li>
<li>locate</li>
<li>abs, sort, mod</li>
<li>size, index (JPA 용도)</li>
</ul>
<h5>사용자 정의 함수 호출</h5>
<ul>
<li>하이버네이트는 사용전 방언에 추가해야 한다. <ul>
<li><code>select function('group_concat', i.name) from Item i</code></li>
</ul>
</li>
</ul>
<h2>경로 표현식</h2>
<p><code>.</code>을 찍어 객체 그래프를 탐색하는 것 </p>
<p>select m.username -&gt; 상태 필드<br>    from Member m<br>    join m.team t -&gt; 단일 값 연관 필드<br>    join m.orders o -&gt; 컬렉션 값 연관 필드<br>where t.name = '팀A'</p>
<p>상태 필드 </p>
<ul>
<li>단순히 값을 저장하기 위한 필드<br>연관 필드 </li>
<li>연관 관계를 위한 필드 <ul>
<li>단일 값 연관 필드 <ul>
<li>@ManyToOne, @OneToOne, 대상이 entity</li>
</ul>
</li>
<li>컬렉션 값 연관 필드 <ul>
<li>@OneToMany, @ManyToMany, 대상이 컬렉션 </li>
</ul>
</li>
</ul>
</li>
</ul>
<h3>특징</h3>
<ul>
<li>상태 필드: 경로 탐색의 끝, 탐색 X</li>
<li>단일 값 연관 경로: <strong>묵시적 내부 조인(inner join) 발생</strong>, 탐색 O<ul>
<li>JPQL: select o.member from Ordero</li>
<li>SQL: select m.* from Orders o inner join Member m on o.member_id = m.id</li>
</ul>
</li>
<li>컬렉션 값 연관 경로: <strong>묵시적 내부 조인 발생</strong>, 탐색 X<ul>
<li>FROM 절에서 명시적 조인을 통해 별칭을 얻으면 별칭을 통해 탐색 가능</li>
<li><code>select m.name From Team t join t.members m</code></li>
</ul>
</li>
<li>묵시적 내부 조인이 발생하지 않도록 코드를 작성해야 함.</li>
</ul>
<p>명시적 조인 </p>
<ul>
<li><code>select m from Member m joint m.team t</code><br>묵시적 조인 </li>
<li><code>select m.team from Member m</code></li>
</ul>
<h3>예시</h3>
<ul>
<li>select o.member.team from Order o -&gt; OK</li>
<li>select t.members from Team -&gt; OK</li>
<li>select t.members.username from Team t -&gt; NO (collection type이기 때문에 탐색 불가)</li>
<li>select m.username from Team t join t.members m -&gt; OK</li>
</ul>
<h2>Fetch Join</h2>
<ul>
<li>SQL 조인 종류 X </li>
<li>JPQL에서 성능 최적화를 위해 제공하는 기능 </li>
<li>연관 된 엔티티나 컬렉션을 SQL 한 번에 함께 조회 하는 기능 </li>
<li>회원을 조회하면서 연관된 팀도 함께 조회 <ul>
<li>JPQL: select m from Member m join fetch m.team</li>
<li>SQL: <code>select M.*, T.* from member M inner join team T on M.team_id = T.id</code></li>
</ul>
</li>
</ul>
<pre><code>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&lt;Member&gt; resultList = em.createQuery(query, Member.class)  
        .getResultList();  

// 회원1, 팀A(SQL)  
// 회원2, 팀A(1차 캐시)  
// 회원3, 팀B(SQL) --&gt; 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</code></pre><p>fetch join 적용 </p>
<pre><code>String query = "select m from Member m join fetch m.team";  
List&lt;Member&gt; 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</code></pre><h3>collection fetch join</h3>
<ul>
<li>1:N 관계, 컬렉션 fetch join 의 경우, 데이터가 뻥튀기 됨.</li>
<li>JPQL: select t from Team t join fetch t.members where t.name = '팀A'</li>
<li>SQL: <code>select T.*, M.* from team T inner join member M on T.id =M.team_id where T.name=팀A</code></li>
</ul>
<pre><code>String query = "select t from Team t join fetch t.members";  
List&lt;Team&gt; 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</code></pre>
<pre><code>String query = "select t from Team t join fetch t.members where t.name = 'TeamA'";  
List&lt;Team&gt; 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("-&gt; username: " + member.getUsername() + ", member=" + member);  
    }  
}

team.name = TeamA, team=com.study.jpa.jpql.Team@2f860823
-&gt; username: 회원1, member=com.study.jpa.jpql.Member@52b06bef
-&gt; username: 회원2, member=com.study.jpa.jpql.Member@7af3874e
team.name = TeamA, team=com.study.jpa.jpql.Team@2f860823
-&gt; username: 회원1, member=com.study.jpa.jpql.Member@52b06bef
-&gt; username: 회원2, member=com.study.jpa.jpql.Member@7af3874e</code></pre>
<h3>fetch join 과 DISTINCT</h3>
<ul>
<li>SQL의 distinct는 중복된 결과를 제거 </li>
<li>JPQL의 distinct 2가지 기능 제공 <ul>
<li>SQL에 distinct 추가 <ul>
<li>데이터가 다르므로 SQL 결과에서는 중복 제거 실패 </li>
</ul>
</li>
<li>어플리케이션에서 엔티티 중복제거 </li>
</ul>
</li>
</ul>
<pre><code>
String query = "select distinct t from Team t join fetch t.members";  
List&lt;Team&gt; 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("-&gt; 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
-&gt; username: 회원1, member=com.study.jpa.jpql.Member@e76b097
-&gt; username: 회원2, member=com.study.jpa.jpql.Member@49f40c00
team.name = TeamB, team=com.study.jpa.jpql.Team@643d2dae
-&gt; username: 회원3, member=com.study.jpa.jpql.Member@52d6d273</code></pre>
<h3>fetch join과 일반 join의 차이</h3>
<ul>
<li>일반 조인의 경우 연관된 엔티티를 함께 조회하지 않음</li>
<li>JPQL은 결과를 반환할 때 연관관계 고려 X</li>
<li>단지 select 절에 지정한 엔티티만 조회.<ul>
<li>아래 쿼리에서는 Team만 조회하고, members에 대해서 조회하지 않음.</li>
</ul>
</li>
<li>fetch join을 사용할때만, 연관된 엔티티도 함께 조회 (즉시 로딩)</li>
<li><strong>fetch join은 객체 그래프를 SQL 한번에 조회 하는 개념</strong></li>
</ul>
<pre><code>String query = "select distinct t from Team t join t.members";  
List&lt;Team&gt; 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("-&gt; 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=?
-&gt; username: 회원1, member=com.study.jpa.jpql.Member@204abeff
-&gt; 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=?
-&gt; username: 회원3, member=com.study.jpa.jpql.Member@58fa5769</code></pre>
<h2>fetch join의 특징과 한계</h2>
<ul>
<li>fetch join 대상에는 별칭을 줄 수 없다. <ul>
<li>하이버네이트는 가능하나, 가급적 사용 안하는 게 좋음.</li>
</ul>
</li>
<li>둘 이상의 컬렉션은 fetch join 불가능 </li>
<li>엔티티에 직접 적용하는 글로벌 로딩 전략보다 우선함.</li>
<li>실무에서 글로벌 로딩 전략은 모두 지연 로딩 </li>
<li>최적화가 필요한 곳은 fetch join 적용</li>
<li>모든 것을 fetch join으로 해결 할 수는 없음.</li>
<li>fetch join은 객체 그래프를 유지할 때 사용하면 효과적.</li>
<li>여러 테이블을 조인해서 엔티티가 가진 모양이 아닌 전혀 다른 결과를 내야하면, fetch join 보다는 일반 조인을 사용하고 필요한 데이터들만 조회해서 dto로 반환 하는 것이 효과적</li>
<li>컬렉션을 fetch join 하면 paging API를 사용할 수 없다.<ul>
<li>일대일, 다대일 같은 단일 값 연관 필드들은 fetch join 해도 페이징 가능</li>
<li>하이버네이트는 경고 로그를 남기고 메모리에서 페이징 (매우 위험)</li>
</ul>
</li>
</ul>
<pre><code>
String query = "select t from Team t join fetch t.members";  
List&lt;Team&gt; resultList = em.createQuery(query, Team.class)  
                          .setFirstResult(0)  
                          .setMaxResults(1)  
                          .getResultList();
WARN: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!</code></pre>
<pre><code>String query = "select m from Member m join fetch m.team t";</code></pre>
<p>방향을 다대일로 쿼리하여 데이터를 뽑을 수 있음.</p>
<p>@BatchSize를 이용하는 방법 </p>
<pre><code>@OneToMany(mappedBy = "team")  
@BatchSize(size = 100)  
private List&lt;Member&gt; members = new ArrayList&lt;&gt;();</code></pre>
<pre><code>String query = "select t from Team t";  
List&lt;Team&gt; 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("-&gt; 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 (
            ?, ?
        )</code></pre>
<h2>다형성 쿼리</h2>
<ul>
<li>조회 대상을 특정 자식으로 한정 <ul>
<li>JPQL: select i from item i where type(i) IN (Book, Movie)</li>
<li>SQL: <code>select i from i where i.DTYPE in ('B', 'M')</code></li>
</ul>
</li>
<li>TREAT (JPA 2.1)<ul>
<li>자바의 타입 캐스팅과 유사 </li>
<li>상속 구조에서 부모 타입을 특정 자식 타입으로 다룰때 사용 </li>
<li>FROM, WHERE, SELECT(hibernate 지원) 사용 <ul>
<li>JPQL: <code>select i from Item i where treat(i as Book).auther='kim'</code></li>
<li>SQL: <code>select i.* from Item i where i.DTYPE='B' and i.auther = 'kim'</code></li>
</ul>
</li>
</ul>
</li>
</ul>
<h2>엔티티 직접 사용</h2>
<h3>기본 키 값</h3>
<ul>
<li>JPQL에서 엔티티를 직접 사용하면 SQL에서 해당 엔티티의 기본 키 값을 사용<ul>
<li>JPQL<ul>
<li><code>select count(m.id) from Member m</code> // 엔티티의 아이디를 사용 </li>
<li><code>select count(m) from Member m</code> // 엔티티를 직접 사용 </li>
</ul>
</li>
<li>SQL (두 JPQL이 같은 sql로 치환)<ul>
<li><code>select count(m.id) as cnt from Member m</code></li>
</ul>
</li>
</ul>
</li>
</ul>
<pre><code>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());  </code></pre>
<h3>외래키 값</h3>
<pre><code>String query = "select m from Member m where m.team = :team";  
List&lt;Member&gt; result = em.createQuery(query, Member.class)  
                            .setParameter("team", teamA)  
                            .getSingleResult();  </code></pre>
<p>SQL</p>
<ul>
<li><code>select m.* from Member m where m.team_id=?</code></li>
</ul>
<h2>Named 쿼리</h2>
<pre><code>@Entity 
@NamedQuery(
    name = "Member.findByUsername",
    query = "select m from Member m where m.username = :username "
)


List&lt;Member&gt; resultList = em.createNamedQuery("Member.findByUsername", Member.class)
.setParameter("username", "회원1")
.getResultList();</code></pre>
<ul>
<li>미리 정의해서 이름을 부여해두고 사용하는 JPQL</li>
<li>정적 쿼리 </li>
<li>Annotation, XML에 정의 가능 <ul>
<li>XML이 항상 우선 순위가 높음.</li>
<li>어플리케이션 운영 환경에 따라 다른 XML을 배포할 수 있음.</li>
</ul>
</li>
<li>Application 로딩 시점에 초기화 후 재사용 </li>
<li><strong>Application 로딩 시점에 쿼리를 검증</strong> </li>
</ul>
<h2>벌크 연산</h2>
<ul>
<li>재고가 10개 미만인 모든 상품의 가격을 10% 상승하려면? </li>
<li>JPA 변경 감지 기능으로 실행하려면 너무 많은 SQL이 실행 됨.<ul>
<li>재고가 10개 미만인 상품 리스트 조회 </li>
<li>상품 엔티티의 가격을 10% 증가 </li>
<li>트랜잭션 커밋 시점에 변경 감지 동작 </li>
</ul>
</li>
<li>변경 된 데이터가 100건이라면 100번의 UPDATE SQL가 실행</li>
</ul>
<h3>예시</h3>
<ul>
<li>쿼리 한 번으로 여러 테이블 Row 변경 (엔티티)</li>
<li>executeUpdate()의 결과는 영향 받는 엔티티 수 반환 </li>
<li>UPDATE, DELETE 지원 </li>
<li>INSERT(insert into...select, 하이버네이트 지원)</li>
</ul>
<pre><code>String query = "update Product p" +
                "set p.price = p.price * 1.1 "+
                "where p.stockAmount &lt; :stockAmount";
    int resultCount = em.createQuery(query)
                        .setParameter("stockAmount", 10)
                        .executeUpdate();</code></pre>
<h3>주의할 점</h3>
<ul>
<li>벌크 연산은 영속성 컨텍스트를 무시하고, 데이터베이스에 직접 쿼리 <ul>
<li>해결 방안<ul>
<li>벌크 연산을 먼저 실행 하는 방법</li>
<li>벌크 연산 후 영속성 컨텍스트 초기화 하는 방법</li>
</ul>
</li>
</ul>
</li>
</ul>

            
            
                </div></div>

Seungdols Company

seungdols

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 = 60 then '경로요금'..

JPQL

댓글