[Spring] 게시물 검색/조회 리펙토링 하기 - Querydsl, MySQL Full Text Search

현재 아래와 같이 작성되어있는 코드를 QueryDSL을 이용해서 분기처리를 최소화하고, MySQL Full Text Search를 적용해보려고 한다.

@Transactional
public PageBoardResponseDto getBoardList(FilterEnum filter, String keyword, Pageable pageable, String email, SubEnum sub) {
    Page<Board> boardPage;
    if(sub.equals(SubEnum.title)){
        if(Objects.equals(filter, FilterEnum.challenge)) {
            log.info("title, challenge search");
            boardPage = boardRepository.findByTitleContainingAndCategory(keyword, Category.CHALLENGE, pageable);
        } else if(Objects.equals(filter, FilterEnum.daily)) {
            log.info("title, daily search");
            boardPage = boardRepository.findByTitleContainingAndCategory(keyword, Category.DAILY, pageable);
        } else if(Objects.equals(filter, FilterEnum.my)){
            log.info("title, my search");
            User user = getUser(email);
            boardPage = boardRepository.findByTitleContainingAndUser(keyword, user, pageable);
        }else{
            log.info("title search");
            boardPage = boardRepository.findByTitleContaining(keyword, pageable);
        }
    }else if(sub.equals(SubEnum.content)){
        if(Objects.equals(filter, FilterEnum.challenge)) {
            log.info("content, challenge search");
            boardPage = boardRepository.findByContentContainingAndCategory(keyword, Category.CHALLENGE, pageable);
        } else if(Objects.equals(filter, FilterEnum.daily)) {
            log.info("content, daily search");
            boardPage = boardRepository.findByContentContainingAndCategory(keyword, Category.DAILY, pageable);
        } else if(Objects.equals(filter, FilterEnum.my)){
            log.info("content, my search");
            User user = getUser(email);
            boardPage = boardRepository.findByContentContainingAndUser(keyword, user, pageable);
        }else{
            log.info("content search");
            boardPage = boardRepository.findByContentContaining(keyword, pageable);
        }
    }else{
        if(Objects.equals(filter, FilterEnum.challenge)) {
            log.info("challenge search");
            boardPage = boardRepository.findAllByCategory(Category.CHALLENGE, pageable);
        } else if(Objects.equals(filter, FilterEnum.daily)) {
            log.info("daily search");
            boardPage = boardRepository.findAllByCategory(Category.DAILY, pageable);
        } else if(Objects.equals(filter, FilterEnum.my)){
            log.info("my search");
            User user = getUser(email);
            boardPage = boardRepository.findByUser(user, pageable);
        }else{
            log.info("search");
            boardPage = boardRepository.findAll(pageable);
        }
    }


    return new PageBoardResponseDto(
            BoardResponseDto.getDtoList(boardPage.getContent()),
            boardPage
    );
}

1. 첫번째 해줘야할 일은 쿼리 DSL을 사용하려면, build.gradle 셋팅을 해주어야 한다.

buildscript {
    ext {
        queryDslVersion = "5.0.0"
    }
}

plugins {
	...
    //querydsl 추가
    id "com.ewerk.gradle.plugins.querydsl" version "1.0.10"
	...
}

...

dependencies {
    ...
    
    //querydsl 추가
    implementation "com.querydsl:querydsl-jpa:${queryDslVersion}"
    implementation "com.querydsl:querydsl-apt:${queryDslVersion}"
    
	...

}

...

def querydslDir = "$buildDir/generated/querydsl"

querydsl {
    jpa = true
    querydslSourcesDir = querydslDir
}
sourceSets {
    main.java.srcDir querydslDir
}
compileQuerydsl{
    options.annotationProcessorPath = configurations.querydsl
}
configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
    querydsl.extendsFrom compileClasspath
}

fetchResults() - deprecated

queryDsl의 fetchResult의 경우 count를 하기위해선 count용 쿼리를 만들어서 실행해야 하는데, 카운트를 하려는 select 쿼리를 기반으로 count 쿼리를 만들어 실행한다.

groupby having 절을 사용하는 등의 복잡한 쿼리 문에서 예외가 발생한다고 한다.

더불어 대부분의 dialect에서는 count쿼리가 유효하지만 JPQL에서는 아니다. 더 찾아보니 모든 dialect에서 지원하는 것도 아니라고 한다.

그렇기 때문에 카운트하려면 그냥 fetch() 를 쓰고 따로 자바쪽에서 count를 세거나 카운트 쿼리를 따로 작성하면 된다. fetchCount() 도 마찬가지로 fetch().size() 를 이용해서 풀어 나가야 한다.

 

List<BoardResponseDtoV2> content = queryFactory.
        select(Projections.fields(BoardResponseDtoV2.class,
                board.id.as("boardId"),
                board.content.as("boardContent"),
                board.imageUrl,
                board.title,
                board.category,
                board.user.email.as("authorEmail"),
                board.user.username.as("authorNick"),
                board.user.profileImageUrl.as("authorProfileImageUrl"),
                board.chatRoomId,
                board.createdDate.as("boardCreatedDate"),
                Projections.constructor(BoardTodoResponseDto.class, board.boardTodo).as("todo")
                ))
        .from(board)
        .leftJoin(board.boardTodo, boardTodo)
        .leftJoin(board.user, user)
        .where(filterEq(searchCondition.getFilter(), searchCondition.getUser()))
        .offset(pageable.getOffset())
        .limit(pageable.getPageSize())
        .fetch();

[해당 오류 발생]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. as col_10_0_, boardtodo6_.id as id1_1_, boardtodo6_.created_date as created_2_' at line 1

select
        board0_.id as col_0_0_,
        board0_.content as col_1_0_,
        board0_.image_url as col_2_0_,
        board0_.title as col_3_0_,
        board0_.category as col_4_0_,
        user2_.email as col_5_0_,
        user2_.username as col_6_0_,
        user2_.profile_image_url as col_7_0_,
        board0_.chat_room_id as col_8_0_,
        board0_.created_date as col_9_0_,
        . as col_10_0_,
        boardtodo6_.id as id1_1_,
        boardtodo6_.created_date as created_2_1_,
        boardtodo6_.modified_date as modified3_1_,
        boardtodo6_.board_id as board_id7_1_,
        boardtodo6_.category as category4_1_,
        boardtodo6_.content as content5_1_,
        boardtodo6_.todo_date as todo_dat6_1_ 
    from
        board board0_ 
    left outer join
        board_todo boardtodo1_ 
            on board0_.id=boardtodo1_.board_id 
    left outer join
        user user2_ 
            on board0_.user_user_seq=user2_.user_seq 
    inner join
        board_todo boardtodo6_ 
            on board0_.id=boardtodo6_.board_id limit ?

board_todo 테이블이 2번 조인을 하고 있다 왜일까...?

[원인]

• List로 조회되는 boardTodo를 Aggregation 하지 못해서 . 라는 불문명한 컬럼 사용
• board.boardTodo와 .leftJoin(board.boardTodo, board)이 각각 적용되어 boardTodo에 대한 Inner Join과 Outer Join이 모두 발생했습니다.

https://jojoldu.tistory.com/342

Querydsl 에서 OneToMany 관계에서 Left Outer Join 이 필요할 경우

[해결]

List<Board> content = queryFactory.
        selectFrom(board)
        .where(filterEq(searchCondition.getFilter(), searchCondition.getUser()))
        .offset(pageable.getOffset())
        .limit(pageable.getPageSize())
        .fetch();

단일 엔티티만 조회해서 boardTodo를 Lazy Loading으로 in 절로 가져오는 방식으로 해결

[오류 발생]

No enum constant com.example.backend.board.domain.Category.daily

 

[원인]

 

FilterEnum 과 Category 가 대소문자의 차이가 있어서 마지막 else 부분에서 같은 enum을 찾을 수 없었다.

private BooleanExpression filterEq(FilterEnum filterEnum, User user) {
    if (filterEnum == null){
        return null;
    }else if(filterEnum.my.equals(filterEnum)){
        return board.user.eq(user);
    }else{
        return board.category.eq(Category.valueOf(filterEnum.name()));
    }
}

 

[해결]
그래서 아래 처럼 바꿔 주었다.

private BooleanExpression filterEq(FilterEnum filterEnum, User user) {
    if (filterEnum == null){
        return null;
    }else if(filterEnum.my.equals(filterEnum)){
        return board.user.eq(user);
    }else{
        return board.category.eq(Category.valueOf(filterEnum.name().toUpperCase()));
    }
}

 

[ MYSQL 8 - FULL TEXT SEARCH SETTING ]

1. TITLE, CONTENT -> TEXT Type 으로 변경

2. 아래 쿼리로 FULLTEXT 인덱스 생성

ALTER TABLE board ADD FULLTEXT INDEX idx_board_content_title(title, content) WITH PARSER NGRAM;

3. SELECT * FROM board WHERE MATCH(title, content) AGAINST("'미슐' " in boolean mode);

4. 1 글자 검색 까지 하려면, RDS 파라미터 그룹에서 해당 값 변경해주기! , 그리고 데이터베이스 재부팅!

5.  IDX DROP 한다음 다시 생성해야함

ALTER TABLE board drop INDEX idx_board_content_title;

 

6. 제목으로 검색과 내용으로 검색이 따로 구현되어야 하기때문에 

ALTER TABLE board ADD FULLTEXT INDEX idx_board_title(title) WITH PARSER NGRAM;
ALTER TABLE board ADD FULLTEXT INDEX idx_board_content(content) WITH PARSER NGRAM;

위 인덱스들도 추가!

7. match 함수를 사용하기 위해 프로젝트 설정

spring.jpa.properties.hibernate.dialect=com.example.backend.config.MySQL8DialectCustom

public class MySQL8DialectCustom extends MySQL8Dialect {

    public MySQL8DialectCustom(){
        super();

        registerFunction(
                "match",
                new SQLFunctionTemplate(StandardBasicTypes.DOUBLE, "match(?1) against (?2 in boolean mode)")
        );
    }

}

8. 테스트 코드 작성!

@Test
    public void matchTest(){
        NumberTemplate booleanTemplate = Expressions.numberTemplate(Double.class,
                "function('match',{0},{1})", board.title, "+" + "나다" + "*");

        List<Board> content = queryFactory.
                select(board)
                .from(board)
                .where(booleanTemplate.gt(0))
                .fetch();

//        for (Board board1 : content) {
//            System.out.println("boardTitle = " + board1.getTitle());
//            System.out.println("boardContent = " + board1.getContent());
//        }

    }

9. 쿼리 확인!

select
        board0_.id as id1_0_,
        board0_.created_date as created_2_0_,
        board0_.modified_date as modified3_0_,
        board0_.category as category4_0_,
        board0_.chat_room_id as chat_roo5_0_,
        board0_.content as content6_0_,
        board0_.image_url as image_ur7_0_,
        board0_.participating_count as particip8_0_,
        board0_.title as title9_0_,
        board0_.user_user_seq as user_us10_0_ 
    from
        board board0_ 
    where
        match(board0_.title) against (? in boolean mode)>?

10. 적용

private BooleanExpression keywordAndSubject(String keyword, SubEnum subEnum){
    if (subEnum == null){
        return null;
    }else if (isEmpty(keyword)){
        return null;
    }else if(subEnum.title.equals(subEnum)){
        NumberTemplate booleanTemplate = Expressions.numberTemplate(Double.class,
                "function('match',{0},{1})", board.title, "+" + keyword + "*");
        return booleanTemplate.gt(0);
    }else if(subEnum.content.equals(subEnum)){
        NumberTemplate booleanTemplate = Expressions.numberTemplate(Double.class,
                "function('match',{0},{1})", board.content, "+" + keyword + "*");
        return booleanTemplate.gt(0);
    }else{
        NumberTemplate booleanTemplate = Expressions.numberTemplate(Double.class,
                "function('matchs',{0},{1},{2})", board.title, board.content, "+" + keyword + "*");
        return booleanTemplate.gt(0);
    }
}

List<Board> content = queryFactory.
        selectFrom(board)
        .where(
                filterEq(searchCondition.getFilter(), searchCondition.getUser()),
                keywordAndSubject(searchCondition.getKeyword(), searchCondition.getSub())
        )
        .offset(pageable.getOffset())
        .limit(pageable.getPageSize())
        .fetch();