ProgrammingQueryDSL

QueryDSL - 동적 쿼리와 성능 최적화 조회

목차

동적 쿼리와 성능 최적화 조회 - 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.getUsername()));
    }

    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();
}
@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");
}
/* 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<=?

동적 쿼리와 성능 최적화 조회 - where 절 파라미터 사용

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

    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();
}
@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");
    }
/* 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<=?
Share