[bootBoard] N:1(다대일) 연관관계: 11-5. JPQLQuery로 Page<Object[]> 처리: 검색 조건의 처리

파라미터로 전달되는 type 값은 '제목(t), 내용(c), 작성자(w)'를 하나 혹은 조합으로 만든 'tcw'와 같은 형태이다.

이 조건에 대해 BooleanExpression 처리한다. 이 조건은 이전 예제에서도 다룬 적이 있다.

 

1. BooleanExpression 사용하여 searchPage()에 코드 추가

• 변경한 부분은 파라미터에 따라 검색 조건을 추가할 수 있또록 BooleanBuilder와 BooleanExpression이 추가된 것이다.
• tuple.groupBy()의 경우 메서드의 하단에서 처리한다.

//SearchBoardRepositoryImpl.java

@Log4j2
public class SearchBoardRepositoryImpl extends QuerydslRepositorySupport
        implements SearchBoardRepository {

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

//.......

    @Override
    public Page<Object[]> searchPage(String type, String keyword, Pageable pageable) {
        log.info("searchPage............................");

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

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

        //SELECT b, w, count(r) FROM Board b
        //LEFT JOIN b.writer w LEFT JOIN Reply r ON r.board = b
        JPQLQuery<Tuple> tuple = jpqlQuery.select(board, member, reply.count());

        BooleanBuilder booleanBuilder = new BooleanBuilder();
        BooleanExpression expression = board.bno.gt(0L);

        booleanBuilder.and(expression);

        if(type != null){
            String[] typeArr = type.split("");
            //검색 조건 작성
            BooleanBuilder conditionBuilder = new BooleanBuilder();

            for(String t:typeArr) {
                switch (t){
                    case "t":
                        conditionBuilder.or(board.title.contains(keyword));
                        break;
                    case "w":
                        conditionBuilder.or(member.email.contains(keyword));
                        break;
                    case "c":
                        conditionBuilder.or(board.content.contains(keyword));
                        break;
                }
            }
            booleanBuilder.and(conditionBuilder);
        }

        tuple.where(booleanBuilder);
        tuple.groupBy(board);

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

        log.info(result);

        return null;

    }

//..........

}

 

2. 테스트

-이전 포스팅과 동일하게 BoardRepositoryTests.java 실행

 

콘솔로그

2022-10-11 22:59:06.141  INFO 1380 --- [    Test worker] c.e.b.repository.BoardRepositoryTests    : Started BoardRepositoryTests in 4.307 seconds (JVM running for 5.88)
2022-10-11 22:59:06.284  INFO 1380 --- [    Test worker] c.e.b.r.s.SearchBoardRepositoryImpl      : searchPage............................
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
            ) 
    where
        board0_.bno>? 
        and (
            board0_.title like ? escape '!'
        ) 
    group by
        board0_.bno
2022-10-11 22:59:06.406  INFO 1380 --- [    Test worker] c.e.b.r.s.SearchBoardRepositoryImpl      : [[Board(bno=12, title=Title...10, content=Content...10), Member(email=user10@aaa.com, password=11111, name=USER10), 0], [Board(bno=13, title=Title...11, content=Content...11), Member(email=user11@aaa.com, password=11111, name=USER11), 1], [Board(bno=14, title=Title...12, content=Content...12), Member(email=user12@aaa.com, password=11111, name=USER12), 0], [Board(bno=15, title=Title...13, content=Content...13), Member(email=user13@aaa.com, password=11111, name=USER13), 1], [Board(bno=16, title=Title...14, content=Content...14), Member(email=user14@aaa.com, password=11111, name=USER14), 1], [Board(bno=17, title=Title...15, content=Content...15), Member(email=user15@aaa.com, password=11111, name=USER15), 0], [Board(bno=18, title=Title...16, content=Content...16), Member(email=user16@aaa.com, password=11111, name=USER16), 1], [Board(bno=19, title=Title...17, content=Content...17), Member(email=user17@aaa.com, password=11111, name=USER17), 0], [Board(bno=20, title=Title...18, content=Content...18), Member(email=user18@aaa.com, password=11111, name=USER18), 0], [Board(bno=21, title=Title...19, content=Content...19), Member(email=user19@aaa.com, password=11111, name=USER19), 0], [Board(bno=23, title=Title...21, content=Content...21), Member(email=user21@aaa.com, password=11111, name=USER21), 1], [Board(bno=33, title=Title...31, content=Content...31), Member(email=user31@aaa.com, password=11111, name=USER31), 1], [Board(bno=43, title=Title...41, content=Content...41), Member(email=user41@aaa.com, password=11111, name=USER41), 0], [Board(bno=53, title=Title...51, content=Content...51), Member(email=user51@aaa.com, password=11111, name=USER51), 0], [Board(bno=63, title=Title...61, content=Content...61), Member(email=user61@aaa.com, password=11111, name=USER61), 0], [Board(bno=73, title=Title...71, content=Content...71), Member(email=user71@aaa.com, password=11111, name=USER71), 2], [Board(bno=83, title=Title...81, content=Content...81), Member(email=user81@aaa.com, password=11111, name=USER81), 1], [Board(bno=93, title=Title...91, content=Content...91), Member(email=user91@aaa.com, password=11111, name=USER91), 0], [Board(bno=102, title=Title...100, content=Content...100), Member(email=user100@aaa.com, password=11111, name=USER100), 0]]
2022-10-11 22:59:06.431  INFO 1380 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2022-10-11 22:59:06.434  INFO 1380 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-10-11 22:59:06.439  INFO 1380 --- [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 9s
6 actionable tasks: 4 executed, 2 up-to-date
오후 10:59:06: Task execution finished ':test --tests "com.example.bootboard.repository.BoardRepositoryTests.testSearchPage"'.