[bootBoard] N:1(다대일) 연관관계: 11-3. Tuple객체, groupBy()를 사용한 집합 함수 처리

1. groupBy()를 사용한 집합 함수 처리

//searchBoardRepositoryImpl.java

@Log4j2
public class SearchBoardRepositoryImpl extends QuerydslRepositorySupport
        implements SearchBoardRepository {

    //생성자
    public SearchBoardRepositoryImpl(){
        super(Board.class); //여기서 super는 querydslRepositorySupport이다.
    }

    @Override
    public Board search1() {
        log.info("search1............");
        //JPQL 사용
        //이제 로그가 찍힐 뿐만 아니라 아래의 JPQL도 실행된다.

        QBoard board = QBoard.board;
        QReply reply = QReply.reply;
        QMember member = QMember.member;

        JPQLQuery<Board> jpqlQuery = from(board);
        jpqlQuery.leftJoin(member).on(board.writer.eq(member));
        jpqlQuery.leftJoin(reply).on(reply.board.eq(board));

        //집합함수 처리 : groupBy()
        jpqlQuery.select(board, member.email, reply.count())
                .groupBy(board);

        log.info("------------------------------");
        log.info(jpqlQuery);
        log.info("------------------------------");

        List<Board> result = jpqlQuery.fetch();
        return null;
    }
}

변경된 부분은 Member에 대한 leftjoin()과 select() 뒤의 groupBy()를 적용한 부분이다.

select() 내에서도 여러 객체(board, member.email, reply.count())를 가져오는 형태로 변경되었다.

 

• 이렇게 정해진 엔티티 객체 단위가 아니라 각각의 데이터를 추출하는 경우에는 Tuple이라는 객체를 이용한다.

 

위의 코드를 아래처럼 수정하여 Tuple을 사용해 보자.

 

 

2. 튜플을 사용하여 각각 여러 데이터를 추출하는 방식 사용: SearchBoardRepositoryImpl 코드 수정

 

select()의 결과를  JPQLQuery<Tuple>을 이용해서 처리하도록 변경

result 변수의 타입도 List<Tuple> 타입으로 변경됨.

//SearchBoardRepositoryImpl.java

@Log4j2
public class SearchBoardRepositoryImpl extends QuerydslRepositorySupport
        implements SearchBoardRepository {

    //생성자
    public SearchBoardRepositoryImpl(){
        super(Board.class); //여기서 super는 querydslRepositorySupport이다.
    }

    @Override
    public Board search1() {
        log.info("search1............");
        //JPQL 사용
        //이제 로그가 찍힐 뿐만 아니라 아래의 JPQL도 실행된다.

        QBoard board = QBoard.board;
        QReply reply = QReply.reply;
        QMember member = QMember.member;

        JPQLQuery<Board> jpqlQuery = from(board);
        jpqlQuery.leftJoin(member).on(board.writer.eq(member));
        jpqlQuery.leftJoin(reply).on(reply.board.eq(board));

        //집합함수 처리 : groupBy()
        
        //튜플 사용!!!!!
        JPQLQuery<Tuple> tuple = jpqlQuery.select(board, member.email, reply.count());
        tuple.groupBy(board);

//        jpqlQuery.select(board, member.email, reply.count())
//                .groupBy(board);

        log.info("------------------------------");
        log.info(tuple); //jpqlQuery -> tuple
        log.info("------------------------------");

        List<Tuple> result = tuple.fetch();
//        List<Board> result = jpqlQuery.fetch();

        log.info(result);

        return null;
    }

}

 

3. 테스트 결과

 

콘솔 로그

2022-10-11 08:55:06.431  INFO 17124 --- [    Test worker] c.e.b.r.s.SearchBoardRepositoryImpl      : search1............
2022-10-11 08:55:06.475  INFO 17124 --- [    Test worker] c.e.b.r.s.SearchBoardRepositoryImpl      : ------------------------------
2022-10-11 08:55:06.478  INFO 17124 --- [    Test worker] c.e.b.r.s.SearchBoardRepositoryImpl      : select board, member1.email, count(reply)
from Board board
  left join Member member1 with board.writer = member1
  left join Reply reply with reply.board = board
group by board
2022-10-11 08:55:06.478  INFO 17124 --- [    Test worker] c.e.b.r.s.SearchBoardRepositoryImpl      : ------------------------------
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_,
        board0_.moddate as moddate2_0_,
        board0_.reg_date as reg_date3_0_,
        board0_.content as content4_0_,
        board0_.title as title5_0_,
        board0_.writer_email as writer_e6_0_ 
    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
2022-10-11 08:55:06.571  INFO 17124 --- [    Test worker] c.e.b.r.s.SearchBoardRepositoryImpl      : [[Board(bno=3, title=제목 변경합니다!!!!!!!!!!!, content=내용 변경합니다!!!!!!!!!!!), user1@aaa.com, 1], [Board(bno=4, title=Title...2, content=Content...2), user2@aaa.com, 0], [Board(bno=5, title=Title...3, content=Content...3), user3@aaa.com, 1], [Board(bno=6, title=Title...4, content=Content...4), user4@aaa.com, 0], [Board(bno=7, title=Title...5, content=Content...5), user5@aaa.com, 0], [Board(bno=8, title=Title...6, content=Content...6), user6@aaa.com, 0], [Board(bno=9, title=Title...7, content=Content...7), user7@aaa.com, 0], [Board(bno=10, title=Title...8, content=Content...8), user8@aaa.com, 0], [Board(bno=11, title=Title...9, content=Content...9), user9@aaa.com, 0], [Board(bno=12, title=Title...10, content=Content...10), user10@aaa.com, 0], [Board(bno=13, title=Title...11, content=Content...11), user11@aaa.com, 1], [Board(bno=14, title=Title...12, content=Content...12), user12@aaa.com, 0], [Board(bno=15, title=Title...13, content=Content...13), user13@aaa.com, 1], [Board(bno=16, title=Title...14, content=Content...14), user14@aaa.com, 1], [Board(bno=17, title=Title...15, content=Content...15), user15@aaa.com, 0], [Board(bno=18, title=Title...16, content=Content...16), user16@aaa.com, 1], [Board(bno=19, title=Title...17, content=Content...17), user17@aaa.com, 0], [Board(bno=20, title=Title...18, content=Content...18), user18@aaa.com, 0], [Board(bno=21, title=Title...19, content=Content...19), user19@aaa.com, 0], [Board(bno=22, title=Title...20, content=Content...20), user20@aaa.com, 0], [Board(bno=23, title=Title...21, content=Content...21), user21@aaa.com, 1], [Board(bno=24, title=Title...22, content=Content...22), user22@aaa.com, 0], [Board(bno=25, title=Title...23, content=Content...23), user23@aaa.com, 0], [Board(bno=26, title=Title...24, content=Content...24), user24@aaa.com, 1], [Board(bno=27, title=Title...25, content=Content...25), user25@aaa.com, 1], [Board(bno=28, title=Title...26, content=Content...26), user26@aaa.com, 0], [Board(bno=29, title=Title...27, content=Content...27), user27@aaa.com, 0], [Board(bno=30, title=Title...28, content=Content...28), user28@aaa.com, 0], [Board(bno=31, title=Title...29, content=Content...29), user29@aaa.com, 1], [Board(bno=32, title=Title...30, content=Content...30), user30@aaa.com, 2], [Board(bno=33, title=Title...31, content=Content...31), user31@aaa.com, 1], [Board(bno=34, title=Title...32, content=Content...32), user32@aaa.com, 1], [Board(bno=35, title=Title...33, content=Content...33), user33@aaa.com, 1], [Board(bno=36, title=Title...34, content=Content...34), user34@aaa.com, 2], [Board(bno=37, title=Title...35, content=Content...35), user35@aaa.com, 0], [Board(bno=38, title=Title...36, content=Content...36), user36@aaa.com, 0], [Board(bno=39, title=Title...37, content=Content...37), user37@aaa.com, 0], [Board(bno=40, title=Title...38, content=Content...38), user38@aaa.com, 1], [Board(bno=41, title=Title...39, content=Content...39), user39@aaa.com, 0], [Board(bno=42, title=Title...40, content=Content...40), user40@aaa.com, 0], [Board(bno=43, title=Title...41, content=Content...41), user41@aaa.com, 0], [Board(bno=44, title=Title...42, content=Content...42), user42@aaa.com, 1], [Board(bno=45, title=Title...43, content=Content...43), user43@aaa.com, 0], [Board(bno=46, title=Title...44, content=Content...44), user44@aaa.com, 0], [Board(bno=47, title=Title...45, content=Content...45), user45@aaa.com, 0], [Board(bno=48, title=Title...46, content=Content...46), user46@aaa.com, 0], [Board(bno=49, title=Title...47, content=Content...47), user47@aaa.com, 0], [Board(bno=50, title=Title...48, content=Content...48), user48@aaa.com, 1], [Board(bno=51, title=Title...49, content=Content...49), user49@aaa.com, 0], [Board(bno=52, title=Title...50, content=Content...50), user50@aaa.com, 2], [Board(bno=53, title=Title...51, content=Content...51), user51@aaa.com, 0], [Board(bno=54, title=Title...52, content=Content...52), user52@aaa.com, 0], [Board(bno=55, title=Title...53, content=Content...53), user53@aaa.com, 0], [Board(bno=56, title=Title...54, content=Content...54), user54@aaa.com, 0], [Board(bno=57, title=Title...55, content=Content...55), user55@aaa.com, 1], [Board(bno=58, title=Title...56, content=Content...56), user56@aaa.com, 0], [Board(bno=59, title=Title...57, content=Content...57), user57@aaa.com, 1], [Board(bno=60, title=Title...58, content=Content...58), user58@aaa.com, 0], [Board(bno=61, title=Title...59, content=Content...59), user59@aaa.com, 1], [Board(bno=62, title=Title...60, content=Content...60), user60@aaa.com, 0], [Board(bno=63, title=Title...61, content=Content...61), user61@aaa.com, 0], [Board(bno=64, title=Title...62, content=Content...62), user62@aaa.com, 1], [Board(bno=65, title=Title...63, content=Content...63), user63@aaa.com, 0], [Board(bno=66, title=Title...64, content=Content...64), user64@aaa.com, 0], [Board(bno=67, title=Title...65, content=Content...65), user65@aaa.com, 0], [Board(bno=68, title=Title...66, content=Content...66), user66@aaa.com, 0], [Board(bno=69, title=Title...67, content=Content...67), user67@aaa.com, 0], [Board(bno=70, title=Title...68, content=Content...68), user68@aaa.com, 0], [Board(bno=71, title=Title...69, content=Content...69), user69@aaa.com, 0], [Board(bno=73, title=Title...71, content=Content...71), user71@aaa.com, 2], [Board(bno=74, title=Title...72, content=Content...72), user72@aaa.com, 0], [Board(bno=75, title=Title...73, content=Content...73), user73@aaa.com, 1], [Board(bno=76, title=Title...74, content=Content...74), user74@aaa.com, 0], [Board(bno=77, title=Title...75, content=Content...75), user75@aaa.com, 0], [Board(bno=78, title=Title...76, content=Content...76), user76@aaa.com, 0], [Board(bno=79, title=Title...77, content=Content...77), user77@aaa.com, 1], [Board(bno=80, title=Title...78, content=Content...78), user78@aaa.com, 1], [Board(bno=81, title=Title...79, content=Content...79), user79@aaa.com, 0], [Board(bno=82, title=Title...80, content=Content...80), user80@aaa.com, 0], [Board(bno=83, title=Title...81, content=Content...81), user81@aaa.com, 1], [Board(bno=84, title=Title...82, content=Content...82), user82@aaa.com, 0], [Board(bno=85, title=Title...83, content=Content...83), user83@aaa.com, 0], [Board(bno=86, title=Title...84, content=Content...84), user84@aaa.com, 0], [Board(bno=87, title=Title...85, content=Content...85), user85@aaa.com, 2], [Board(bno=88, title=Title...86, content=Content...86), user86@aaa.com, 0], [Board(bno=89, title=Title...87, content=Content...87), user87@aaa.com, 1], [Board(bno=90, title=Title...88, content=Content...88), user88@aaa.com, 1], [Board(bno=91, title=Title...89, content=Content...89), user89@aaa.com, 0], [Board(bno=92, title=Title...90, content=Content...90), user90@aaa.com, 0], [Board(bno=93, title=Title...91, content=Content...91), user91@aaa.com, 0], [Board(bno=94, title=Title...92, content=Content...92), user92@aaa.com, 0], [Board(bno=95, title=Title...93, content=Content...93), user93@aaa.com, 0], [Board(bno=96, title=Title...94, content=Content...94), user94@aaa.com, 0], [Board(bno=97, title=Title...95, content=Content...95), user95@aaa.com, 0], [Board(bno=98, title=Title...96, content=Content...96), user96@aaa.com, 1], [Board(bno=99, title=Title...97, content=Content...97), user97@aaa.com, 1], [Board(bno=100, title=Title...98, content=Content...98), user98@aaa.com, 1], [Board(bno=101, title=Title...99, content=Content...99), user99@aaa.com, 0], [Board(bno=102, title=Title...100, content=Content...100), user100@aaa.com, 0], [Board(bno=103, title=Test., content=Test ...), user55@aaa.com, 0]]
2022-10-11 08:55:06.602  INFO 17124 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2022-10-11 08:55:06.605  INFO 17124 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-10-11 08:55:06.613  INFO 17124 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Deprecated Gradle features were used in this build, making it incompatible with Gradle 8.0.

You can use '--warning-mode all' to show the individual deprecation warnings and determine if they come from your own scripts or plugins.

See https://docs.gradle.org/7.5/userguide/command_line_interface.html#sec:command_line_warnings
BUILD SUCCESSFUL in 8s

 

 

//아래가 tuple fetch결과
2022-10-11 08:55:06.571  INFO 17124 --- [    Test worker] c.e.b.r.s.SearchBoardRepositoryImpl      : [[Board(bno=3, title=제목 변경합니다!!!!!!!!!!!, content=내용 변경합니다!!!!!!!!!!!), user1@aaa.com, 1], [Board(bno=4, title=Title...2, content=Content...2), user2@aaa.com, 0], [Board(bno=5, title=Title...3, content=Content...3), user3@aaa.com, 1], [Board(bno=6, title=Title...4, content=Content...4), user4@aaa.com, 0], [Board(bno=7, title=Title...5, content=Content...5), user5@aaa.com, 0], [Board(bno=8, title=Title...6, content=Content...6), user6@aaa.com, 0], [Board(bno=9, title=Title...7, content=Content...7), user7@aaa.com, 0], [Board(bno=10, title=Title...8, content=Content...8), user8@aaa.com, 0], [Board(bno=11, title=Title...9, content=Content...9), user9@aaa.com, 0], [Board(bno=12, title=Title...10, content=Content...10), user10@aaa.com, 0], [Board(bno=13, title=Title...11, content=Content...11), user11@aaa.com, 1], [Board(bno=14, title=Title...12, content=Content...12), user12@aaa.com, 0], [Board(bno=15, title=Title...13, content=Content...13), user13@aaa.com, 1], [Board(bno=16, title=Title...14, content=Content...14), user14@aaa.com, 1], [Board(bno=17, title=Title...15, content=Content...15), user15@aaa.com, 0], [Board(bno=18, title=Title...16, content=Content...16), user16@aaa.com, 1], [Board(bno=19, title=Title...17, content=Content...17), user17@aaa.com, 0], [Board(bno=20, title=Title...18, content=Content...18), user18@aaa.com, 0], [Board(bno=21, title=Title...19, content=Content...19), user19@aaa.com, 0], [Board(bno=22, title=Title...20, content=Content...20), user20@aaa.com, 0], [Board(bno=23, title=Title...21, content=Content...21), user21@aaa.com, 1], [Board(bno=24, title=Title...22, content=Content...22), user22@aaa.com, 0], [Board(bno=25, title=Title...23, content=Content...23), user23@aaa.com, 0], [Board(bno=26, title=Title...24, content=Content...24), user24@aaa.com, 1], [Board(bno=27, title=Title...25, content=Content...25), user25@aaa.com, 1], [Board(bno=28, title=Title...26, content=Content...26), user26@aaa.com, 0], [Board(bno=29, title=Title...27, content=Content...27), user27@aaa.com, 0], [Board(bno=30, title=Title...28, content=Content...28), user28@aaa.com, 0], [Board(bno=31, title=Title...29, content=Content...29), user29@aaa.com, 1], [Board(bno=32, title=Title...30, content=Content...30), user30@aaa.com, 2], [Board(bno=33, title=Title...31, content=Content...31), user31@aaa.com, 1], [Board(bno=34, title=Title...32, content=Content...32), user32@aaa.com, 1], [Board(bno=35, title=Title...33, content=Content...33), user33@aaa.com, 1], [Board(bno=36, title=Title...34, content=Content...34), user34@aaa.com, 2], [Board(bno=37, title=Title...35, content=Content...35), user35@aaa.com, 0], [Board(bno=38, title=Title...36, content=Content...36), user36@aaa.com, 0], [Board(bno=39, title=Title...37, content=Content...37), user37@aaa.com, 0], [Board(bno=40, title=Title...38, content=Content...38), user38@aaa.com, 1], [Board(bno=41, title=Title...39, content=Content...39), user39@aaa.com, 0], [Board(bno=42, title=Title...40, content=Content...40), user40@aaa.com, 0], [Board(bno=43, title=Title...41, content=Content...41), user41@aaa.com, 0], [Board(bno=44, title=Title...42, content=Content...42), user42@aaa.com, 1], [Board(bno=45, title=Title...43, content=Content...43), user43@aaa.com, 0], [Board(bno=46, title=Title...44, content=Content...44), user44@aaa.com, 0], [Board(bno=47, title=Title...45, content=Content...45), user45@aaa.com, 0], [Board(bno=48, title=Title...46, content=Content...46), user46@aaa.com, 0], [Board(bno=49, title=Title...47, content=Content...47), user47@aaa.com, 0], [Board(bno=50, title=Title...48, content=Content...48), user48@aaa.com, 1], [Board(bno=51, title=Title...49, content=Content...49), user49@aaa.com, 0], [Board(bno=52, title=Title...50, content=Content...50), user50@aaa.com, 2], [Board(bno=53, title=Title...51, content=Content...51), user51@aaa.com, 0], [Board(bno=54, title=Title...52, content=Content...52), user52@aaa.com, 0], [Board(bno=55, title=Title...53, content=Content...53), user53@aaa.com, 0], [Board(bno=56, title=Title...54, content=Content...54), user54@aaa.com, 0], [Board(bno=57, title=Title...55, content=Content...55), user55@aaa.com, 1], [Board(bno=58, title=Title...56, content=Content...56), user56@aaa.com, 0], [Board(bno=59, title=Title...57, content=Content...57), user57@aaa.com, 1], [Board(bno=60, title=Title...58, content=Content...58), user58@aaa.com, 0], [Board(bno=61, title=Title...59, content=Content...59), user59@aaa.com, 1], [Board(bno=62, title=Title...60, content=Content...60), user60@aaa.com, 0], [Board(bno=63, title=Title...61, content=Content...61), user61@aaa.com, 0], [Board(bno=64, title=Title...62, content=Content...62), user62@aaa.com, 1], [Board(bno=65, title=Title...63, content=Content...63), user63@aaa.com, 0], [Board(bno=66, title=Title...64, content=Content...64), user64@aaa.com, 0], [Board(bno=67, title=Title...65, content=Content...65), user65@aaa.com, 0], [Board(bno=68, title=Title...66, content=Content...66), user66@aaa.com, 0], [Board(bno=69, title=Title...67, content=Content...67), user67@aaa.com, 0], [Board(bno=70, title=Title...68, content=Content...68), user68@aaa.com, 0], [Board(bno=71, title=Title...69, content=Content...69), user69@aaa.com, 0], [Board(bno=73, title=Title...71, content=Content...71), user71@aaa.com, 2], [Board(bno=74, title=Title...72, content=Content...72), user72@aaa.com, 0], [Board(bno=75, title=Title...73, content=Content...73), user73@aaa.com, 1], [Board(bno=76, title=Title...74, content=Content...74), user74@aaa.com, 0], [Board(bno=77, title=Title...75, content=Content...75), user75@aaa.com, 0], [Board(bno=78, title=Title...76, content=Content...76), user76@aaa.com, 0], [Board(bno=79, title=Title...77, content=Content...77), user77@aaa.com, 1], [Board(bno=80, title=Title...78, content=Content...78), user78@aaa.com, 1], [Board(bno=81, title=Title...79, content=Content...79), user79@aaa.com, 0], [Board(bno=82, title=Title...80, content=Content...80), user80@aaa.com, 0], [Board(bno=83, title=Title...81, content=Content...81), user81@aaa.com, 1], [Board(bno=84, title=Title...82, content=Content...82), user82@aaa.com, 0], [Board(bno=85, title=Title...83, content=Content...83), user83@aaa.com, 0], [Board(bno=86, title=Title...84, content=Content...84), user84@aaa.com, 0], [Board(bno=87, title=Title...85, content=Content...85), user85@aaa.com, 2], [Board(bno=88, title=Title...86, content=Content...86), user86@aaa.com, 0], [Board(bno=89, title=Title...87, content=Content...87), user87@aaa.com, 1], [Board(bno=90, title=Title...88, content=Content...88), user88@aaa.com, 1], [Board(bno=91, title=Title...89, content=Content...89), user89@aaa.com, 0], [Board(bno=92, title=Title...90, content=Content...90), user90@aaa.com, 0], [Board(bno=93, title=Title...91, content=Content...91), user91@aaa.com, 0], [Board(bno=94, title=Title...92, content=Content...92), user92@aaa.com, 0], [Board(bno=95, title=Title...93, content=Content...93), user93@aaa.com, 0], [Board(bno=96, title=Title...94, content=Content...94), user94@aaa.com, 0], [Board(bno=97, title=Title...95, content=Content...95), user95@aaa.com, 0], [Board(bno=98, title=Title...96, content=Content...96), user96@aaa.com, 1], [Board(bno=99, title=Title...97, content=Content...97), user97@aaa.com, 1], [Board(bno=100, title=Title...98, content=Content...98), user98@aaa.com, 1], [Board(bno=101, title=Title...99, content=Content...99), user99@aaa.com, 0], [Board(bno=102, title=Title...100, content=Content...100), user100@aaa.com, 0], [Board(bno=103, title=Test., content=Test ...), user55@aaa.com, 0]]