[Querydsl] 동적 쿼리, 성능 최적화 조회 - Builder 사용 + API 컨트롤러

# 회원 검색 조건 클래스

@Data
public class MemberSearchCondition {
    //회원명, 팀명, 나이(ageGoe, ageLoe)

    private String username;
    private String teamName;
    private Integer ageGoe;
    private Integer ageLoe;
}

 

 

# 동적 쿼리 - Builder 사용

// Builder 사용
// 회원명, 팀명, 나이 (ageGoe, ageLoe)
public List<MemberTeamDto> searchByBuilder(MemberSearchCondition condition){
    BooleanBuilder builder = new BooleanBuilder();

    if(StringUtils.hasText(condition.getUsername())){
        builder.and(member.username.eq(condition.getUsername()));
    }
    if(StringUtils.hasText(condition.getTeamName())){
        builder.and(team.name.eq(condition.getTeamName()));
    }
    if(condition.getAgeGoe() != null){
        builder.and(member.age.goe(condition.getAgeGoe()));
    }
    if(condition.getAgeLoe() != null){
        builder.and(member.age.loe(condition.getAgeLoe()));
    }

    return queryFactory
            .select(new QMemberTeamDto(
                    member.id,
                    member.username,
                    member.age,
                    team.id,
                    team.name))
            .from(member)
            .leftJoin(member.team, team)
            .where(builder)
            .fetch();
}

 

 

# 조회 테스트

@Test
public void searchTest(){
	// em 등록...

    MemberSearchCondition condition = new MemberSearchCondition();
    condition.setAgeGoe(35);
    condition.setAgeLoe(40);
    condition.setTeamName("teamB");

    List<MemberTeamDto> result = memberJpaRepository.searchByBuilder(condition);

    assertThat(result).extracting("username").containsExactly("member4");
}

 

 

 

# Where 절 파라미터 사용

//회원명, 팀명, 나이(ageGoe, ageLoe)
public List<MemberTeamDto> search(MemberSearchCondition condition) {
    return queryFactory
            .select(new QMemberTeamDto(
                    member.id,
                    member.username,
                    member.age,
                    team.id,
                    team.name))
            .from(member)
            .leftJoin(member.team, team)
            .where(usernameEq(condition.getUsername()),
                    teamNameEq(condition.getTeamName()),
                    ageGoe(condition.getAgeGoe()),
                    ageLoe(condition.getAgeLoe()))
            .fetch();
}
private BooleanExpression usernameEq(String username) {
    return StringUtils.hasText(username) ? member.username.eq(username) : null;
}
private BooleanExpression teamNameEq(String teamName) {
    return StringUtils.hasText(teamName) ? team.name.eq(teamName) : null;
}
private BooleanExpression ageGoe(Integer ageGoe) {
    return ageGoe == null ? null : member.age.goe(ageGoe);
}
private BooleanExpression ageLoe(Integer ageLoe) {
    return ageLoe == null ? null : member.age.loe(ageLoe);
}

 

 

• BooleanExpression 메서드를 만들면, 조건 재사용이 가능하다
• 혹은 체인 메서드를 이용하여 다앙향 조건 메서드를 만들어낼 수 있다.

private BooleanExpression ageBetween(int ageLoe, int ageGoe){
    return ageGoe(ageGoe).and(ageLoe(ageLoe));
}

private BooleanExpression isValid(...){
    ...
}

 

 

 

# 조회 API 컨트롤러

@RestController
@RequiredArgsConstructor
public class MemberController {
    private final MemberJpaRepository memberJpaRepository;

    @GetMapping("/v1/members")
    public List<MemberTeamDto> searchMemberV1(MemberSearchCondition condition) {
        return memberJpaRepository.search(condition);
    }
}

 

• Condition 객체를 컨트롤러 파라미터로 받으면, 쿼리로 데이터를 받아올 수 있다.

 

http://localhost:8080/v1/members?teamName=teamB