📚 공부한 책 : 코드로배우는 스프링 부트 웹프로젝트
❤️ github 주소 : https://github.com/qkralswl689/LearnFromCode/tree/main/board2022

1.left(outer) join

SpringBoot 2 버전 이후 포함되는 JPA 버전은 Entity 클래스 내에 전혀 연관관계가 없더라고 조인을 이용할 수 있다
-> 조인을 할 때 INNER JOIN 혹은 JOIN 과 같이 일반적인 조인을 이용할 수 있고, LEFT OUTER JOIN 혹은 LEFT JOIN을 이용할 수 있다

2.엔티티 클래스 내부에 연관관계가 있는 경우 JPQL 처리

Board 엔티티 클래스 내부에는 Member 엔티티 클래스를 변수로 선언하고 연관관계를 맺고 있다
-> Board의 writer 변수를 이용해 조인 처리한다

2-1.Repository interface

getBoardWithWriter()는 Board를 사용하고 있지만 Member를 같이 조회해야 하는 상황이다
-> Board 클래스에는 Memeber와의 연관관계를 맺고 있으므로 b.writer와 같은 형태로 사용한다

• 내부에 있는 엔티티를 이용할 때는 LEFT JOIN 뒤에 ON을 이용하는 부분이 없다

import com.example.board2022.entity.Board;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface BoardRepository extends JpaRepository<Board,Long> {

    // 한개의 로우(object) 내에 Object[]로 나옴
    // Board를 사용하고 있지만 Member를 같이 조회해야 하는 상황에서 사용
    @Query("select b , w from Board b left join b.writer w where b.bno =:bno")
    Object getBoardWithWriter(@Param("bno") Long bno);
}

2-2.getBoardWithWriter() 테스트

조인처리 되어 한 번에 board 터에블과 member 테이블을 이용하는 것을 확인할 수 있다

import com.example.board2022.entity.Board;
import com.example.board2022.entity.Member;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
public class BoardRepositoryTests {

    @Autowired
    BoardRepository boardRepository;

    @Test
    public void testReadWithWriter(){
        Object result = boardRepository.getBoardWithWriter(100L);

        Object[] arr = (Object[]) result;

        System.out.println("---------------");
        System.out.println(Arrays.toString(arr));
    }

}

• 실행된 쿼리

Hibernate: 
    select
        board0_.bno as bno1_0_0_,
        member1_.email as email1_1_1_,
        board0_.moddate as moddate2_0_0_,
        board0_.regdate as regdate3_0_0_,
        board0_.content as content4_0_0_,
        board0_.title as title5_0_0_,
        board0_.writer_email as writer_e6_0_0_,
        member1_.moddate as moddate2_1_1_,
        member1_.regdate as regdate3_1_1_,
        member1_.name as name4_1_1_,
        member1_.password as password5_1_1_ 
    from
        board board0_ 
    left outer join
        member member1_ 
            on board0_.writer_email=member1_.email 
    where
        board0_.bno=?
---------------
[Board(bno=100, title=Title...100, content=Content...100), Member(email=user100@aaa.com, password=1111, name=USER100)]

3.엔티티 클래스 내부에 연관관계가 없는 경우 JPQL 처리

연관관계가 없는 엔티티 조인처리에는 직접 조인에 필요한 조건은 on 을 작성해서 처리해야 한다

3-1.Repository interface

LEFT JOIN Reply r 과 r.board 사이에 ON 이 사용되면서 조인조건을 직접 지정하는 부분이 추가되었다

import com.example.board2022.entity.Board;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface BoardRepository extends JpaRepository<Board,Long> {

    // 연관관계가 없는 엔티티 조인 처리 (ON 사용)
    @Query("SELECT b, r FROM Board b LEFT JOIN Reply r ON r.board = b WHERE b.bno =:bno")
    List<Object[]> getBoardWithReply(@Param("bno") Long bno);

}

3-2.getBoardWithReply() 테스트

import com.example.board2022.entity.Board;
import com.example.board2022.entity.Member;
import com.example.board2022.entity.Reply;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Sort;

import java.util.Arrays;
import java.util.List;

@SpringBootTest
public class BoardRepositoryTests {

    @Autowired
    BoardRepository boardRepository;

    @Test
    public void testGetBoardWithReply(){

        List<Object[]> result = boardRepository.getBoardWithReply(100L);

        for(Object[] arr : result){
            System.out.println(Arrays.toString(arr));
        }
    }
}

• 실행 쿼리

Hibernate: 
    select
        board0_.bno as bno1_0_0_,
        reply1_.rno as rno1_2_1_,
        board0_.moddate as moddate2_0_0_,
        board0_.regdate as regdate3_0_0_,
        board0_.content as content4_0_0_,
        board0_.title as title5_0_0_,
        board0_.writer_email as writer_e6_0_0_,
        reply1_.moddate as moddate2_2_1_,
        reply1_.regdate as regdate3_2_1_,
        reply1_.board_bno as board_bn6_2_1_,
        reply1_.replyer as replyer4_2_1_,
        reply1_.text as text5_2_1_ 
    from
        board board0_ 
    left outer join
        reply reply1_ 
            on (
                reply1_.board_bno=board0_.bno
            ) 
    where
        board0_.bno=?
[Board(bno=100, title=Title...100, content=Content...100), Reply(rno=129, text=Reply....129, replyer=guest)]
[Board(bno=100, title=Title...100, content=Content...100), Reply(rno=201, text=Reply....201, replyer=guest)]
[Board(bno=100, title=Title...100, content=Content...100), Reply(rno=210, text=Reply....210, replyer=guest)]
[Board(bno=100, title=Title...100, content=Content...100), Reply(rno=275, text=Reply....275, replyer=guest)]

4.목록 화면에 필요한 JPQL 만들기

목록 화면에 필요한 데이터

• board : 게시물번호,제목,작성시간
• member : 이름,이메일
• Reply : 해당게시물의 댓글 수
  => 세 개의 엔티티 중 가장 많은 데이터를 가져오는 쪽 : board -> board를 중심으로 조인관계 작성
  : Member 는Board 내에 writer 라는 필드로 연관관계를 맺고,Reply 는 연관관계가 없다
  ==> 조인 후 Board를 기준으로 GROUP BY 처리를 해 하나의 게시물 당 하나의 라인이 될 수 있도록 처리해야 한다

4-1.Repository interface 작성

Pageable을 파라미터로 전달받고 Page<Object[]> 리턴 타입의 getBoardWithReplyCount()를 작성한다

import com.example.board2022.entity.Board;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface BoardRepository extends JpaRepository<Board,Long> {

    // 한개의 로우(object) 내에 Object[]로 나옴
    // Board를 사용하고 있지만 Member를 같이 조회해야 하는 상황에서 사용
    @Query("select b , w from Board b left join b.writer w where b.bno =:bno")
    Object getBoardWithWriter(@Param("bno") Long bno);

    // 연관관계가 없는 엔티티 조인 처리 (ON 사용)
    @Query("SELECT b, r FROM Board b LEFT JOIN Reply r ON r.board = b WHERE b.bno =:bno")
    List<Object[]> getBoardWithReply(@Param("bno") Long bno);

    @Query(value = "SELECT b, w, count(r) " +
            " FROM Board b " +
            " LEFT JOIN b.writer w " +
            " LEFT JOIN Reply r ON r.board = b " +
            " GROUP BY b",
            countQuery = "SELECT count(b) FROM Board b")
    Page<Object[]> getBoardWithReplyCount(Pageable pageable); // 목록 화면에 필요한 데이터

}

4-2.testWithReplyCount 테스트

import com.example.board2022.entity.Board;
import com.example.board2022.entity.Member;
import com.example.board2022.entity.Reply;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

import javax.swing.text.html.Option;
import javax.transaction.Transactional;
import java.util.Arrays;
import java.util.List;
import java.util.Optional;
import java.util.stream.IntStream;

@SpringBootTest
public class BoardRepositoryTests {

    @Autowired
    BoardRepository boardRepository;

    @Test
    public void testWithReplyCount(){

        Pageable pageable = PageRequest.of(0,10, Sort.by("bno").descending());

        Page<Object[]> result = boardRepository.getBoardWithReplyCount(pageable);

        result.get().forEach(row -> {
            Object[] arr = (Object[]) row;

            System.out.println(Arrays.toString(arr));
        });
    }
}

• 실행 쿼리

Hibernate: 
    select
        board0_.bno as col_0_0_,
        member1_.email as col_1_0_,
        count(reply2_.rno) as col_2_0_,
        board0_.bno as bno1_0_0_,
        member1_.email as email1_1_1_,
        board0_.moddate as moddate2_0_0_,
        board0_.regdate as regdate3_0_0_,
        board0_.content as content4_0_0_,
        board0_.title as title5_0_0_,
        board0_.writer_email as writer_e6_0_0_,
        member1_.moddate as moddate2_1_1_,
        member1_.regdate as regdate3_1_1_,
        member1_.name as name4_1_1_,
        member1_.password as password5_1_1_ 
    from
        board board0_ 
    left outer join
        member member1_ 
            on board0_.writer_email=member1_.email 
    left outer join
        reply reply2_ 
            on (
                reply2_.board_bno=board0_.bno
            ) 
    group by
        board0_.bno 
    order by
        board0_.bno desc limit ?
Hibernate: 
    select
        count(board0_.bno) as col_0_0_ 
    from
        board board0_
[Board(bno=100, title=Title...100, content=Content...100), Member(email=user100@aaa.com, password=1111, name=USER100), 4]
[Board(bno=99, title=Title...99, content=Content...99), Member(email=user99@aaa.com, password=1111, name=USER99), 3]
[Board(bno=98, title=Title...98, content=Content...98), Member(email=user98@aaa.com, password=1111, name=USER98), 3]
[Board(bno=97, title=Title...97, content=Content...97), Member(email=user97@aaa.com, password=1111, name=USER97), 5]
[Board(bno=96, title=Title...96, content=Content...96), Member(email=user96@aaa.com, password=1111, name=USER96), 2]
[Board(bno=95, title=Title...95, content=Content...95), Member(email=user95@aaa.com, password=1111, name=USER95), 2]
[Board(bno=94, title=Title...94, content=Content...94), Member(email=user94@aaa.com, password=1111, name=USER94), 4]
[Board(bno=93, title=Title...93, content=Content...93), Member(email=user93@aaa.com, password=1111, name=USER93), 3]
[Board(bno=92, title=Title...92, content=Content...92), Member(email=user92@aaa.com, password=1111, name=USER92), 3]
[Board(bno=91, title=Title...91, content=Content...91), Member(email=user91@aaa.com, password=1111, name=USER91), 2]

5.조회화면에서 필요한 JPQL 구성하기

Board 와 Member를 주로 이용하고 해당 게시물이 몇 개의 댓글이 있는지 알려주는 지 작성한다

5-1.Repository interface 작성

import com.example.board2022.entity.Board;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface BoardRepository extends JpaRepository<Board,Long> {

	// ... 	생략
    @Query("SELECT b, w, count(r) " +
            " FROM Board b LEFT JOIN b.writer w " +
            " LEFT OUTER JOIN Reply r ON r.board = b" +
            " WHERE b.bno = :bno")
    Object getBoardByBno(@Param("bno") Long bno);
}

5-2.getBoardByBno() 테스트

import com.example.board2022.entity.Board;
import com.example.board2022.entity.Member;
import com.example.board2022.entity.Reply;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Arrays;
import java.util.Optional;

@SpringBootTest
public class BoardRepositoryTests {

    @Autowired
    BoardRepository boardRepository;

    @Test
    public void testRead3(){
        Object result = boardRepository.getBoardByBno(100L);

        Object[] arr = (Object[]) result;

        System.out.println(Arrays.toString(arr));
    }
}

-실행 쿼리

Hibernate: 
    select
        board0_.bno as col_0_0_,
        member1_.email as col_1_0_,
        count(reply2_.rno) as col_2_0_,
        board0_.bno as bno1_0_0_,
        member1_.email as email1_1_1_,
        board0_.moddate as moddate2_0_0_,
        board0_.regdate as regdate3_0_0_,
        board0_.content as content4_0_0_,
        board0_.title as title5_0_0_,
        board0_.writer_email as writer_e6_0_0_,
        member1_.moddate as moddate2_1_1_,
        member1_.regdate as regdate3_1_1_,
        member1_.name as name4_1_1_,
        member1_.password as password5_1_1_ 
    from
        board board0_ 
    left outer join
        member member1_ 
            on board0_.writer_email=member1_.email 
    left outer join
        reply reply2_ 
            on (
                reply2_.board_bno=board0_.bno
            ) 
    where
        board0_.bno=?
[Board(bno=100, title=Title...100, content=Content...100), Member(email=user100@aaa.com, password=1111, name=USER100), 4]