[Querydsl] 동적 쿼리와 성능 최적화 조회 - where 조건 절 파라미터

해당 글은 김영한 님의 querydsl을 수강하며 정리하려고 적는 포스팅입니다.

 

해당 부분은 앞서 포스팅한 DTO, Builder를 사용한 조회 포스팅에서 추가로 수정한 것이다.

먼저 아래 게시글을 참고하면 될 것 같다.

[Querydsl] 동적 쿼리 성능 최적화 조회 - DTO, Builder 사용

 

Where 절에 파라미터를 사용한 예제

public List<MemberTeamDto> search(MemberSearchCondition condition) {
    return queryFactory
            .select(new QMemberTeamDto(
                    member.id.as("memberId"),
                    member.username,
                    member.age,
                    team.id.as("teamId"),
                    team.name.as("teamName")
            ))
            .from(member)
            .leftJoin(member.team, team)
            .where(
                    usernameEq(condition.getUsername()),
                    teamNameEq(condition.getTeamName()),
                    ageGoe(condition.getAgeGoe()),
                    ageLoe(condition.getAgeLoe()))
            .fetch();
}

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

private BooleanExpression usernameEq(String username) {
    return hasText(username) ? member.username.eq(username) : null;
}

private BooleanExpression teamNameEq(String teamName) {
    return hasText(teamName) ? team.name.eq(teamName) : null;
}

private BooleanExpression ageGoe(Integer ageGoe) {
    return ageGoe != null ? member.age.goe(ageGoe) : null;
}

private BooleanExpression ageLoe(Integer ageLoe) {
    return ageLoe != null ? member.age.loe(ageLoe) : null;
}

 

Where 절에 파라미터 방식을 사용하면 조건 재사용 가능

//재사용성
public List<Member> searchMember(MemberSearchCondition condition) {
    return queryFactory
            .selectFrom(member)
            .leftJoin(member.team, team)
            .where(
                    usernameEq(condition.getUsername()),
                    teamNameEq(condition.getTeamName()),
                    ageGoe(condition.getAgeGoe()),
                    ageLoe(condition.getAgeLoe()))
            .fetch();
}

• Member 조회로 변경을 하더라도, Eq로 만들어둔 함수를 사용하여 where절에 파라미터로 재사용 가능하다.

 

Where 절에 파라미터 방식 테스트

@SpringBootTest
@Transactional
class MemberJpaRepositoryTest {

    @PersistenceContext
    EntityManager em;

    @Autowired
    MemberJpaRepository memberJpaRepository;

    @Test
    public void searchTest() {
        Team teamA = new Team("teamA");
        Team teamB = new Team("teamB");
        em.persist(teamA);
        em.persist(teamB);

        Member member1 = new Member("member1", 10, teamA);
        Member member2 = new Member("member2", 20, teamA);
        Member member3 = new Member("member3", 30, teamB);
        Member member4 = new Member("member4", 40, teamB);
        em.persist(member1);
        em.persist(member2);
        em.persist(member3);
        em.persist(member4);

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

        //List<MemberTeamDto> result = memberJpaRepository.searchMember(condition);
        List<MemberTeamDto> result = memberJpaRepository.search(condition);

        assertThat(result).extracting("username").containsExactly("member4");
    }
}
---------------------------------------------------------------------------------
2023-01-17T15:35:53.232+09:00 DEBUG 13296 --- [           main] org.hibernate.SQL                        : 
/* select
    member1.id as memberId,
    member1.username,
    member1.age,
    team.id as teamId,
    team.name as teamName 
from
    Member member1   
left join
    member1.team as team 
where
    team.name = ?1 
    and member1.age >= ?2 
    and member1.age <= ?3 */ select
        m1_0.member_id,
        m1_0.username,
        m1_0.age,
        m1_0.team_id,
        t1_0.name 
    from
        member m1_0 
    left join
        team t1_0 
            on t1_0.id=m1_0.team_id 
    where
        t1_0.name=? 
        and m1_0.age>=? 
        and m1_0.age<=?