[JAVA/SpringBoot] Query Dsl 적용해보기

jordy·2023년 2월 18일

JPA Querydsl SpringData SrpingBoot

spring-jpa

목록 보기

1/1

게시판 검색 기능을 리팩토링 해보자

QueryDsl 의 존재는 알고 있었지만 최근 관심이 생겨 한번 기존의 코드를 리팩토링을 해보려 한다

QueryDsl

우선 QueryDsl 세팅부터 난이도가 높은걸로 알려져 있지만 세팅부분은 생략한다
( 사실 같이 작업하는 다른 개발자분이 세팅해주셨다 )

Before (JPQL)

기존의 소스는 SpringBoot 를 처음 공부했을 당시 작성한 토이프로젝트의 검색 로직으로 JpaRepository 인터페이스 메소드만을 이용한다.

검색 조건

BoardType : Required
Title : Optional
Content : Optional
MemNo : Optional

CommonBoardRepository.java

public interface CommonBoardRepository extends JpaRepository<CommonBoard, Long> {

    Page<CommonBoard> findAllByBoardType(BoardType boardType, Pageable pageable);

    Page<CommonBoard> findAllByBoardTypeAndTitleContainsOrContentContainsOrMemNo(BoardType boardType, String title, String content, long memNo, Pageable pageable);

    Page<CommonBoard> findAllByBoardTypeAndTitleContaining(BoardType boardType, String keyword, Pageable pageable);

    Page<CommonBoard> findAllByBoardTypeAndUsMember_MemNo(BoardType boardType, @Param("memNo")long memNo, Pageable pageable);

    Page<CommonBoard> findAllByBoardTypeAndContentContaining(BoardType boardType, String keyword, Pageable pageable);

}

CommonBoardServiceImpl.java

...

   @Override
    public Page<CommonBoardResponseDto> searchBoardList(BoardType boardType, SearchType searchType, String keyword, Pageable pageable) {
        log.trace("searchBoardList() :: boardType = {}, searchType = {}, keyword = {}, pageNumber = {}, pageSize = {}", boardType, searchType,
            keyword, pageable.getPageNumber(),
            pageable.getPageSize());
        switch (searchType) {
            case ALL:
                return this.commonBoardRepository
                    .findAllByBoardTypeAndTitleContainsOrContentContainsOrMemNo(boardType, keyword, keyword,
                        loginService.getMemNoByNickname(keyword), pageable)
                    .map(commonBoardMapper::toResponseDto);
            case TITLE:
                return this.commonBoardRepository
                    .findAllByBoardTypeAndTitleContaining(boardType, keyword, pageable)
                    .map(commonBoardMapper::toResponseDto);
            case NICKNAME:
                return
                    this.commonBoardRepository
                        .findAllByBoardTypeAndUsMember_MemNo(boardType, loginService.getMemNoByNickname(keyword), pageable)
                        .map(commonBoardMapper::toResponseDto);
            case CONTENT:
                return this.commonBoardRepository
                    .findAllByBoardTypeAndContentContaining(boardType, keyword, pageable)
                    .map(commonBoardMapper::toResponseDto);
            default:
                throw new InvalidValueException("Invalid SearchType, Input SearchType : {}" + searchType);
        }
    }
    
...

Query

02:59 INFO  p6spy - 23.02.19 02:59:35 | OperationTime : 4ms|
HeFormatSql(P6Spy sql,Hibernate format):
    select
        commonboar0_.id as id1_3_,
        commonboar0_.modi_date as modi_dat2_3_,
        commonboar0_.reg_date as reg_date3_3_,
        commonboar0_.board_type as board_ty4_3_,
        commonboar0_.content as content5_3_,
        commonboar0_.hits as hits6_3_,
        commonboar0_.mem_no as mem_no7_3_,
        commonboar0_.title as title8_3_,
        commonboar0_.image_id_list as image_id9_3_,
        (SELECT
            count(1) 
        FROM
            dev.reply r 
        WHERE
            r.common_board_id = commonboar0_.id) as formula0_ 
    from
        dev.common_board commonboar0_ 
    where
        commonboar0_.board_type='CB' 
    order by
        commonboar0_.id desc limit 25
02:59 INFO  p6spy - 23.02.19 02:59:35 | OperationTime : 3ms|
HeFormatSql(P6Spy sql,Hibernate format):
    select
    
    ...
    # 이후 17개 정도의 n+1 문제를 포함한 많은 쿼리들...

정적 쿼리의 한계를 갖고 각 조건에 따른 메소드가 분리되어 있다
N+1 이슈로 많은 쿼리가 날라가고있다

After (QueryDsl)

QueryDsl 만 추가하려다 보니 일이 커졌다..

FetchJoin 으로 N+1 해소
@QueryProjection 추가하여 DTO 형태 리턴
주인 변경을 통한 FetchType Lazy 적용
중복 JPAQuery 소스 공통화

QueryDsl 적용 후

CommonBoardCustomRepositoryImpl

@RequiredArgsConstructor
public class CommonBoardCustomRepositoryImpl implements CommonBoardCustomRepository {

    private final JPAQueryFactory query;

    // todo. member 까지 fetch join 할 수 없을까..?
    @Override
    public Page<CommonBoardResponseDto> findAllByBoardType(BoardType boardType, Pageable pageable) {

        List<CommonBoardResponseDto> fetch = this.commonBoardResponseDtoJPAQuery()
            .where(commonBoard.boardType.eq(boardType))
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .fetch();

        JPAQuery<CommonBoard> countQuery = query
            .selectFrom(commonBoard)
            .where(commonBoard.boardType.eq(boardType));

        return PageableExecutionUtils.getPage(fetch, pageable, countQuery::fetchCount);
    }

    @Override
    public Page<CommonBoardResponseDto> findCommonBoardWithSearchCondition(BoardType boardType, SearchType searchType, String keyword,
        Pageable pageable) {

        List<CommonBoardResponseDto> fetch = this.commonBoardResponseDtoJPAQuery()
//            .on(commonBoard.boardType.eq(boardType))
            .where(commonBoard.boardType.eq(boardType).and(searchCondition(searchType, keyword)))
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .fetch();

        JPAQuery<CommonBoard> countQuery = query
            .selectFrom(commonBoard)
            .where(commonBoard.boardType.eq(boardType).and(searchCondition(searchType, keyword)));

        return PageableExecutionUtils.getPage(fetch, pageable, countQuery::fetchCount);
    }

    private JPAQuery<CommonBoardResponseDto> commonBoardResponseDtoJPAQuery() {
        return query
            .select(
                new QCommonBoardResponseDto(
                    commonBoard.id,
                    commonBoard.boardType,
                    commonBoard.title,
                    commonBoard.usMember.nickname,
                    commonBoard.hits,
                    commonBoard.memNo,
//                    commonBoard.replyList.size(),
                    query.select(commonBoard.replyList.size())
                        .from(reply)
                        .where(commonBoard.id.eq(reply.commonBoard.id)),
                    commonBoard.usMember.profileImageUrl,
                    commonBoard.regDate,
                    commonBoard.modiDate
                ))
            .from(commonBoard)
            // select 문에 replyList.size 로 인한 count query 가 필요하게 되어 groupBy 추가
            .groupBy(
                commonBoard.id,
                commonBoard.boardType,
                commonBoard.title,
                commonBoard.usMember.nickname,
                commonBoard.hits,
                commonBoard.memNo,
                commonBoard.usMember.profileImageUrl,
                commonBoard.regDate,
                commonBoard.modiDate
            );
    }

    protected BooleanBuilder searchCondition(SearchType searchType, String keyword) {
        switch (searchType) {
            case ALL:
                return containTitle(keyword).or(containContent(keyword)).or(equalNickName(keyword));
            case TITLE:
                return containTitle(keyword);
            case CONTENT:
                return containContent(keyword);
            case NICKNAME:
                return equalNickName(keyword);
            default:
                throw new InvalidValueException("Invalid SearchType, Input SearchType : {}" + searchType);

        }
    }

    BooleanBuilder equalNickName(String content) {
        return nullSafeBuilder(() -> commonBoard.usMember.nickname.eq(content));
    }

    BooleanBuilder containTitle(String content) {
        return nullSafeBuilder(() -> commonBoard.title.contains(content));
    }

    BooleanBuilder containContent(String content) {
        return nullSafeBuilder(() -> commonBoard.content.contains(content));
    }

    BooleanBuilder nullSafeBuilder(Supplier<BooleanExpression> f) {
        try {
            return new BooleanBuilder(f.get());
        } catch (Exception e) {
            return new BooleanBuilder();
        }
    }

}

Query

줄어든 쿼리

03:55 INFO  p6spy - 23.02.19 03:55:57 | OperationTime : 11ms|
HeFormatSql(P6Spy sql,Hibernate format):
    select
        commonboar0_.id as col_0_0_,
        commonboar0_.board_type as col_1_0_,
        commonboar0_.title as col_2_0_,
        usmember1_.nickname as col_3_0_,
        commonboar0_.hits as col_4_0_,
        commonboar0_.mem_no as col_5_0_,
        (select
            count(replylist3_.common_board_id) 
        from
            dev.reply reply2_ cross 
        join
            dev.reply replylist3_ 
        where
            commonboar0_.id=replylist3_.common_board_id 
            and commonboar0_.id=reply2_.common_board_id) as col_6_0_,
        usmember1_.profile_image_url as col_7_0_,
        commonboar0_.reg_date as col_8_0_,
        commonboar0_.modi_date as col_9_0_ 
    from
        dev.common_board commonboar0_ cross 
    join
        dev.us_member usmember1_ 
    where
        commonboar0_.mem_no=usmember1_.mem_no 
        and commonboar0_.board_type='FB' 
    group by
        commonboar0_.id ,
        commonboar0_.board_type ,
        commonboar0_.title ,
        usmember1_.nickname ,
        commonboar0_.hits ,
        commonboar0_.mem_no ,
        usmember1_.profile_image_url ,
        commonboar0_.reg_date ,
        commonboar0_.modi_date limit 25
03:55 INFO  p6spy - 23.02.19 03:55:57 | OperationTime : 6ms|
HeFormatSql(P6Spy sql,Hibernate format):
    select
        count(commonboar0_.id) as col_0_0_ 
    from
        dev.common_board commonboar0_ 
    where
        commonboar0_.board_type='FB'