28. 동적 쿼리와 성능 최적화 조회, Builder

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 query.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();
}

BooleanBuilder를 이용해 동적쿼리 생성.

 

public final class BooleanBuilder implements Predicate, Cloneable  {

저 BooleanBuilder도

Predicate를 상속 받았음

 

여튼 빌더는 조건들을 boolean? 그러니까 null인지를 따져서 and나 or 등등으로 묶어주는 쿼리를 만들어 주는 거임.

들어온 condition, 조건에 따라서 조건이 있으면 and 하면서 Builder에 넣어줌.

 

그래서 그걸 where안에 넣어놓으면, builder의 predicate를 뽑아오는 모양. 그래서 where에 표현식이 넣어짐.

 

게다가 이거, Dto임.

@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;
    }
}

이렇게 만들고, 생성자에

@QueryProjection

저거 한 다음 compileQuerydsl 해서 빌드 하면 저거에 대한 Q파일이 만들어짐.

@Generated("com.querydsl.codegen.DefaultProjectionSerializer")
public class QMemberTeamDto extends ConstructorExpression<MemberTeamDto> {

    private static final long serialVersionUID = 1379582437L;

    public QMemberTeamDto(com.querydsl.core.types.Expression<Long> memberId, com.querydsl.core.types.Expression<String> username, com.querydsl.core.types.Expression<Integer> age, com.querydsl.core.types.Expression<Long> teamId, com.querydsl.core.types.Expression<String> teamName) {
        super(MemberTeamDto.class, new Class<?>[]{long.class, String.class, int.class, long.class, String.class}, memberId, username, age, teamId, teamName);
    }

}

엔티티에 대한 정보를 가지고 있는 Q파일과는 좀 다르게 저거는 생성자 표현식임.

 

return query.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();

이렇게, as해서 Dto의 필드명과 맞춤. 저 필드

물론 사실 근데 이거는 생성자라 타입, 순서가 중요한거지, 꼭 저렇게 as 해서 맞춰줄 필요는 없음.

 

근데 이거 단점이, Dto 클래스 내에 @QueryProjection해서 QueryDSL기술을 붙이는 거 자체가 QueryDSL에 의존하는 클래스를 만든다는 거임. 그게 좀 단점이므로, 싫으면 

https://qwefdg3.tistory.com/934

Projections. 해서 원하는 방식으로 넣으면 됨.

 

 

 

 

@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(20);
    condition.setAgeLoe(40);
    condition.setTeamName("teamB");

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

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

 

테스트. 

member1 ~ 4를 넣고 찾아오는 실험.

 

컨디션에 ageGoe와 ageLoe를 넣음. 즉 20 ~ 40에 팀이름이 teamB인 member들만 가져옴.

 

/* 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.team_id=m1_0.team_id 
    where
        t1_0.name=? 
        and m1_0.age>=? 
        and m1_0.age<=?

 

쿼리도 생각했던 대로 나갔고, 테스트도 문제없이 통과.

 

MemberSearchCondition condition = new MemberSearchCondition();
condition.setTeamName("teamB");

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

name만 넣었을 때

/* select
    member1.id,
    member1.username,
    member1.age,
    team.id,
    team.name 
from
    Member member1   
left join
    member1.team as team 
where
    team.name = ?1 */ 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.team_id=m1_0.team_id 
    where
        t1_0.name=?

 

 

MemberSearchCondition condition = new MemberSearchCondition();

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

아무것도 넣지 않았을 때

 

/* select
    member1.id,
    member1.username,
    member1.age,
    team.id,
    team.name 
from
    Member member1   
left join
    member1.team as team */ 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.team_id=m1_0.team_id

 

 

모두 의도했던 대로 쿼리가 나감.

 

 

근데 조회해 올 때 조심해야 하는게, 꼭 limit를 쓰셈.