QueryDSL을 이용하여 동적 쿼리 구현하기

1. 의존성 추가

implementation 'com.querydsl:querydsl-jpa:5.0.0:jakarta'
annotationProcessor "com.querydsl:querydsl-apt:${dependencyManagement.importedProperties['querydsl.version']}:jakarta"
annotationProcessor "jakarta.annotation:jakarta.annotation-api"
annotationProcessor "jakarta.persistence:jakarta.persistence-api"

의존성이 제대로 추가되었다면 Q 클래스가 생성됨

 

2. QuerydslConfig 파일 생성

@Configuration
public class QuerydslConfig {

    @PersistenceContext
    private EntityManager entityManager;

    @Bean
    public JPAQueryFactory jpaQueryFactory() {
        return new JPAQueryFactory(entityManager);
    }
}

 

3. Repository 생성

1.  TodoRepository에서 TodoRepositoryQuery를 상속하기

public interface TodoRepository extends JpaRepository<Todo, Long>, TodoRepositoryQuery {
}

 

2. TodoRepositoryQuery 인터페이스 생성

public interface TodoRepositoryQuery {

    Optional<Todo> findByIdWithUser(@Param("todoId") Long todoId);

    Optional<Todo> findByTitle(String title);

    Page<Todo> findBySearchKeyword(Pageable pageable, String title, LocalDate createdStartAt, LocalDate createdEndAt, String managerNickName);

}

 

3. TodoRepositoryQueryImpl 생성

@RequiredArgsConstructor
public class TodoRepositoryQueryImpl implements TodoRepositoryQuery {}

 

4. QueryDSL 기본 사용법

var query = jpaQueryFactory.select(threadEmotion.thread)
        .from(threadEmotion)
        .join(threadEmotion.thread)
        .where(userEq(user))
        .orderBy(threadEmotion.createdAt.desc())
        .offset(pageable.getOffset())
        .limit(pageable.getPageSize());

 

5. 동적쿼리 처리하기

1. BooleanBuilder을 이용하여 검색 조건의 데이터 유무에 따라서 동적 쿼리 수행

• 메서드를 각각 확인하면서 쿼리 조건을 확인해야하기 때문에 코드를 읽기가 어려움
• 유지보수성이 좋지 않음

private List<Member> searchMember(String nameCond, Integer ageCond) {
    BooleanBuilder builder = new BooleanBuilder();
    
    if(nameCond != null) {
        builder.and(member.name.eq(nameCond));
    }
    if(ageCond != null) {
        builder.and(member.age.eq(ageCond));
    
    return queryFactory
            .selectFrom(member)
            .where(builder)
            .fetch();
}

 

2. BooleanExpression을 이용하여 검색 조건의 데이터 유무에 따라서 동적 쿼리 수행

• where()에 null이 들어오면 where()절을 사용하지 않음
• where()에 , 을 and 조건으로 사용

    @Override
    public Page<Todo> findBySearchKeyword(Pageable pageable, String title, LocalDate createdStartAt, LocalDate createdEndAt, String managerNickName) {
        List<Todo> results = jpaQueryFactory.select(todo)
                .from(todo)
                .join(todo.managers)
                .fetchJoin()
                .where(titleEq(title)
                        ,managerNickNameEq(managerNickName)
                        ,createdStartAtEq(createdStartAt)
                        ,createdEndAtEq(createdEndAt))
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .orderBy(todo.createdAt.desc())
                .fetch();

        JPAQuery<Long> countQuery = jpaQueryFactory.select(todo.count())
                .from(todo)
                .where(titleEq(title),managerNickNameEq(managerNickName),createdStartAtEq(createdStartAt),createdEndAtEq(createdEndAt));

        return PageableExecutionUtils.getPage(results, pageable, countQuery::fetchOne);
    }


    private BooleanExpression titleEq(String title) {
        if (title == null) {
            return null;
        }
        return todo.title.contains(title);
    }

    private BooleanExpression managerNickNameEq(String managerNickName) {
        if (managerNickName == null) {
            return null;
        }
        return todo.managers.any().user.nickName.like("%" + managerNickName +"%");
    }

    private BooleanExpression createdStartAtEq(LocalDate createdStartAt) {
        if (createdStartAt == null) {
            return null;
        }

        LocalDateTime startAt = createdStartAt.atStartOfDay();


        return todo.createdAt.goe( startAt ); // createdAt >= startAt
    }

    private BooleanExpression createdEndAtEq(LocalDate createdEndAt) {
        if (createdEndAt == null) {
            return null;
        }

        LocalDateTime endAt = createdEndAt.atTime(LocalTime.MAX);

        return todo.createdAt.loe(endAt); // createdAt <= endAt
    }

 

Service

public Page<TodoSearchResponse> getSearchTodos(int page, int size, String title, LocalDate createdStartAt, LocalDate createdEndAt, String managerNickName) {
        Pageable pageable = PageRequest.of(page - 1, size);
        Page<Todo> todos = todoRepository.findBySearchKeyword(pageable, title, createdStartAt, createdEndAt, managerNickName);
        Page<TodoSearchResponse> todoDtos = todos.map(TodoSearchResponse::from);
        return todoDtos;
    }

 

Controller

@GetMapping("/todos/search")
    public ResponseEntity<Page<TodoSearchResponse>> getSearchTodos(
            @RequestParam(defaultValue = "1") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(required = false) String title,
            @RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate start,
            @RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end,
            @RequestParam(required = false) String managerNickName
    ) {
        return ResponseEntity.ok(todoService.getSearchTodos(page, size, title, start, end, managerNickName));
    }