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

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

 

동적쿼리 Builder 사용 준비

 

MemberTeamDto - 조회 최적화용 DTO 추가

package study.querydsl.dto;

import com.querydsl.core.annotations.QueryProjection;
import lombok.Data;

@Data
public class MemberTeamDto {

    private Long memberId;
    private String username;
    private int age;
    private Long teamId;
    private String teamName;

    @QueryProjection
    public MemberTeamDto(Long memberId, String username, int age, Long teamId, String teamName) {
        this.memberId = memberId;
        this.username = username;
        this.age = age;
        this.teamId = teamId;
        this.teamName = teamName;
    }
}

▶ @QeuryProjection

• QMemberTeamDto를 생성하기 위해 ./gradlew compileQuerydsl을 한번 실행해 주어야 Q DTO class가 생성된다.
• 해당 애노테이션을 사용하게 되면, DTO가 Querydsl을 의존하게 된다. 따라서 이러한 의존이 싫다면, 해당 에노테이션을 제거하고 다른 것을 사용하자
  • Projection.bean()
  • Projection.fields()
  • Projection.constructor()

2023.01.13 - [Java] - [JPA] 프로젝션과 결과 반환 - @QueryProjection

 

회원 검색 조건

package study.querydsl.dto;

import lombok.Data;

@Data
public class MemberSearchCondition {
    //회원명, 팀명, 나이(ageGoe, ageLoe)
    private String username;
    private String teamName;
    private Integer ageGoe;
    private Integer ageLoe;
}

• Integer을 사용한 이유는 값이 null 일수도 있기 때문이다.
• 이름이 너무 길면 MemberCond 등을오 줄여 사용해도 된다.

 

동적쿼리 Builder 사용

Builder을 사용한 예제

@Repository
@RequiredArgsConstructor
public class MemberJpaRepository {

    private final EntityManager em;
    private final JPAQueryFactory queryFactory;

    public List<MemberTeamDto> searchByBuilder(MemberSearchCondition condition) {

        BooleanBuilder builder = new BooleanBuilder();

        //null or "" //ctrl + shift + enter
        if (hasText(condition.getUsername())) {
            builder.and(member.username.eq(condition.getUsername()));
        }
        if (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.as("memberId"),
                        member.username,
                        member.age,
                        team.id.as("teamId"),
                        team.name.as("teamName")
                ))
                .from(member)
                .leftJoin(member.team, team)
                .where(builder)
                .fetch();
    }

}

• member.id.as("memberId") 등으로 적었는데, QMemberTeamDto는 생성자를 사용하기 때문에 굳이 필드 이름을 맞추지 않아도 된다.
• 따라서 member.id만 적어도 상관 없다.

 

조회 예제 테스트

@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.searchByBuilder(condition);

        assertThat(result).extracting("username").containsExactly("member4");
    }
}
----------------------------------------------------------------------------------------
2023-01-16T16:33:09.920+09:00 DEBUG 17984 --- [           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<=?