Querydsl - 동적 쿼리와 성능 최적화 조회 Builder 사용

동적 쿼리와 성능 최적화 조회 - Builder 사용

MemberTeamDto - 조회 최적화용 DTO 추가

@Data
public class MemberTeamDto {
    private Long memberId;
    private String usename;
    private int age;
    private Long teamId;
    private String teamName;

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

위 코드를 작성하고 Gradle -> other -> compileQuerydsl을 해주자.

참고로 @QueryProjection을 사용하면 해당 DTO가 Querydsl을 의존하게 된다.
이런 의존이 싫으면, 해당 에노테이션을 제거하고, Projection.bean(), fields(), constructor()을 사용하면 된다.

회원 검색 조건

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

동적쿼리 - Builder 사용

public List<MemberTeamDto> searchByBuilder(MemberSearchCondition condition) {

    BooleanBuilder builder = new BooleanBuilder();
    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,
                    member.username,
                    member.age,
                    team.id,
                    team.name
            ))
            .from(member)
            .leftJoin(member.team, team)
            .where(builder)
            .fetch();
}

테스트

@Test
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 = memberJapRepository.searchByBuilder(condition);
    assertThat(result).extracting("username").containsExactly("member4");
}

select
        member1.id,
        member1.username,
        member1.age,
        team.id,
        team.name
from
    Member member1
left join
    member1.team as team
where
    team.name = ?1
    and member1.age >= ?2
    and member1.age <= ?3


select
    member0_.member_id as col_0_0_,
    member0_.username as col_1_0_,
    member0_.age as col_2_0_,
    team1_.team_id as col_3_0_,
    team1_.name as col_4_0_
from
    member member0_
left outer join
    team team1_
        on member0_.team_id=team1_.team_id
where
    team1_.name=?
    and member0_.age>=?
    and member0_.age<=?

참고

검색 조건을 아예 설정하지 않았을 때는 모든 데이터를 다 끌고 오기 때문에 데이터가 많이 쌓였을 경우 쿼리 하나에 수 많은 데이터를 다 가져오게 된다.
운영 초기 데이터가 없을 경우에는 문제가 되지 않지만, 운영하면서 데이터가 많이 쌓였을 때 문제가 된다.
때문에 동적 쿼리를 할 때는 기본 조건이 있는게 좋고, 하다못해 limit() 이라도 설정하는 것이 좋다.
또 페이징 쿼리를 같이 사용하면 더 좋다.

인프런 김영한 지식공유자님 강의 : 실전! Querydsl