[bootBoard] N:1(다대일) 연관관계: 8-1. 목록화면에 필요한 JPQL 생성

https://github.com/hojuncha997/bootBoard

GitHub - hojuncha997/bootBoard: 코드로 배우는 스프링부트 웹 프로젝트 ch.5

 

 

목록화면에 필요한 데이터

이 이미지는 무시해도 됨... 딴 짓하다가 저장하기 귀찮아서 여기다 붙여 넣음.

 

• 게시물(Board): 게시물 번호, 게시물 작성 시간
• 회원(Member): 회원 이름/이메일
• 댓글(Reply): 해당 게시물의 댓글 수

위 3개 엔티티 가운데 가장 많은 데이터를 가져오는 것이 Board이다. 따라서 Board를 중심으로 조인 관계를 형성한다.

 조인 후에는 Board를 기준으로 'GROUP BY' 처리를 해서 하나의 게시물 당 하나의 라인이 될 수 있도록 처리한다.

현재 상황

• Member는 Board 내에 writer라는 필드로 연관관계를 맺고 있다.
• Reply는 연관관계가 없다.

1. BoardRepository.java 에 Query 작성

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

//BoardRepository.java

...

@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); // 목록 화면에 필요한 데이터

 

 

 

2. BoardRepositoryTests.java 테스트

 

아래 코드를 추가해 위에서 작성한 JPQL이 정상적으로 동작 가능한지 확인한다.

 

 

• 1페이지의 데이터를 처리한다고 가정하고 페이지 번호는 0으로 지정, 10개를 조회한다.

//BoardRepositoryTests.java

...

@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));
    });
}

 

결과

 

2022-09-28 23:39:19.480  INFO 11960 --- [    Test worker] c.e.b.repository.BoardRepositoryTests    : Started BoardRepositoryTests in 4.205 seconds (JVM running for 6.028)
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_.reg_date as reg_date3_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_.reg_date as reg_date3_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=11111, name=USER100), 4]
[Board(bno=99, title=Title...99, content=Content...99), Member(email=user99@aaa.com, password=11111, name=USER99), 2]
[Board(bno=98, title=Title...98, content=Content...98), Member(email=user98@aaa.com, password=11111, name=USER98), 1]
[Board(bno=97, title=Title...97, content=Content...97), Member(email=user97@aaa.com, password=11111, name=USER97), 3]
[Board(bno=96, title=Title...96, content=Content...96), Member(email=user96@aaa.com, password=11111, name=USER96), 7]
[Board(bno=95, title=Title...95, content=Content...95), Member(email=user95@aaa.com, password=11111, name=USER95), 4]
[Board(bno=94, title=Title...94, content=Content...94), Member(email=user94@aaa.com, password=11111, name=USER94), 5]
[Board(bno=93, title=Title...93, content=Content...93), Member(email=user93@aaa.com, password=11111, name=USER93), 1]
[Board(bno=92, title=Title...92, content=Content...92), Member(email=user92@aaa.com, password=11111, name=USER92), 2]
[Board(bno=91, title=Title...91, content=Content...91), Member(email=user91@aaa.com, password=11111, name=USER91), 2]
2022-09-28 23:39:19.716  INFO 11960 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2022-09-28 23:39:19.718  INFO 11960 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-09-28 23:39:19.724  INFO 11960 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
BUILD SUCCESSFUL in 10s