Querydsl - BooleanBuilder를 사용한 동적 쿼리와 성능 최적화

 

 

BooleanBuilder를 사용한 동적 쿼리와 성능 최적화

 

 Member와 Team을 동적 쿼리를 이용하여 해당 조건에 맞는 데이터를 가져와 보자.

 

 

 

 

1. 성능 최적화를 위해 DB에서 필요한 데이터만 가져올 수 있는 DTO를 생성한다.

@Data
public class MemberTeamDto {

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

    public MemberTeamDto() {
    }

    @QueryProjection	// (1)
    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;
    }
}

• (1) : Q-Type을 만들기위한 어노테이션

---

 

2. 동적 쿼리에 사용할 검색 조건 클래스 생성

@Data
public class MemberSearchCondition {

    // 회원명, 팀명, 나이(ageGoe, ageLoe)

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

---

 

3. Repository에 Builder를 이용한 동적 쿼리 메서드 구현

public List<MemberTeamDto> searchByBuilder(MemberSearchCondition condition) {

    BooleanBuilder builder = new BooleanBuilder();
    if (StringUtils.hasText(condition.getUsername())) {	// (1)
        builder.and(member.username.eq(condition.getUsername()));
    }
    if (StringUtils.hasText(condition.getTeamName())) {	// (2)
        builder.and(team.name.eq(condition.getTeamName()));
    }
    if (condition.getAgeGoe() != null) {	// (3)
        builder.and(member.age.goe(condition.getAgeGoe()));
    }
    if (condition.getAgeLoe() != null) {	// (4)
        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)		//(5)
            .fetch();
}

• (1) : StringUtils.hasText를 이용하여 null과 빈문자("") 체크 (null or "" 이면 false)
• (2) : StringUtils.hasText를 이용하여 null과 빈문자("") 체크 (null or "" 이면 false)
• (3) : ageGoe이 null인지 체크
• (4) : ageLoe이 null 인지 체크
• (1), (2), (3), (4) : true 일 경우 builder에 and조건 추가
• (5) : where문에 builder 적용

---

 

4. 조회 테스트

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

    assertThat(results).extracting("username").containsExactly("member4");

}

 

Querydsl 쿼리와 JPQL 쿼리

    /* 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
            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<=?

 

 위 쿼리를 보면 MemberSearchCondition의 username값이 null이므로 검색조건에 제외된 것을 볼 수 있다. 이렇듯 null

값이면 검색조건에 제외, null이 아닌 데이터 값이 있을 경우 검색 조건에 추가되는 동적 쿼리를 사용할 수 있다.

 

 

 만약 MemberSearchCondition의 모든 필드가 null일 경우 해당 Querydsl의 쿼리와 JPQL 쿼리는 다음과 같다.

    /* 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 */ 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

 

 위 쿼리를 보면 알수있듯이 MemberSearchCondition의 모든 필드가 null이므로 검색 조건이 모두 제외된 것을 볼 수 있다.