실전 QueryDsl 3 (중급문법 , 순수 JPA)

 

 

DTO 조회

    @Test
    public void findDtoByJPQL() throws Exception
    {
        List<MemberDto> resultList = em.createQuery(
                "select new study.querydsl.dto.MemberDto(m.username, m.age) from Member m", MemberDto.class)
                .getResultList();
    }
    @Test
    public void findDtoSetter() throws Exception
    {
        List<MemberDto> result = queryFactory
                .select(Projections.bean(MemberDto.class, member.username, member.age))
                .from(member)
                .fetch();
        for (MemberDto memberDto : result)
        {
            System.out.println("memberDto = " + memberDto);
        }
    }

bean 방식말고 Field, Constructor 방법도 있다. 

    @Test
    public void findUserDto() throws Exception
    {
        QMember memberSub = new QMember("memberSub");
        List<MemberDto> result = queryFactory
                .select(Projections.fields(MemberDto.class,
                        member.username.as("name"),
                        ExpressionUtils.as(JPAExpressions
                                .select(memberSub.age.max())
                                .from(memberSub), "age")
                ))
                .from(member)
                .fetch();
    }

alias 로 넣어야할 이름이 다를떄는 직접 정해주면 된다. 

다음에는 sub 쿼리로 모든 row의 age를 max로 채워넣고 싶을때의 쿼리이다.

ExpressionUtils.as(query, alias) 를 지정해줄 수 있다.

 

@QueryProjection

@Data
@NoArgsConstructor(access = AccessLevel.PUBLIC)
public class MemberDto
{
    private String username;
    private int age;

    @QueryProjection
    public MemberDto(String username, int age)
    {
        this.username = username;
        this.age = age;
    }
}

annotation을 적은후에 querydslcompile을 해주어야한다. 이러면 QMemberDto 로 생성해준다.

    @Test
    public void findDtoByQueryProjection() throws Exception
    {
        List<MemberDto> result = queryFactory
                .select(new QMemberDto(member.username, member.age))
                .from(member)
    }

constructor 와 다르게 field 를 (member.id) 를 추가하면 컴파일 오류로 알려준다.

@QueryProjection 을 써줌으로 querydsl 이 의존성을 가지게 된다. 

 

동적쿼리 - BooleanBuilder

    @Test
    public void dynamicQuery_BooleanBuilder() throws Exception
    {
        String usernameParam = "member1";
        Integer ageParam = 10;

        List<Member> result = searchMember1(usernameParam,ageParam);
        assertThat(result.size()).isEqualTo(1);
    }

    private List<Member> searchMember1(String usernameCond, Integer ageCond)
    {
        BooleanBuilder builder = new BooleanBuilder();
        if(usernameCond != null)
        {
            builder.and(member.username.eq(usernameCond));
        }
        if(ageCond != null)
        {
            builder.and(member.age.eq(ageCond));
        }

        return queryFactory
                .selectFrom(member)
                .where(builder)
                .fetch();
    }

    /* select
        member1 
    from
        Member member1 
    where
        member1.username = ?1 
        and member1.age = ?2 */ select
            member0_.member_id as member_i1_1_,
            member0_.age as age2_1_,
            member0_.team_id as team_id4_1_,
            member0_.username as username3_1_ 
        from
            member member0_ 
        where
            member0_.username=? 
            and member0_.age=?

동적쿼리를 통해 주어진 인자가 null인 경우 where 문에서 빼줄수 있다. 

 

동적쿼리 - Where 다중 파라미터 사용

    @Test
    public void dynamicQuery_WhereParam() throws Exception
    {
//        String usernameParam = "member1";
        String usernameParam = null;
        Integer ageParam = 10;

        List<Member> result = searchMember2(usernameParam,ageParam);
        assertThat(result.size()).isEqualTo(1);
    }
    private List<Member> searchMember2(String usernameCond, Integer ageCond)
    {
        return queryFactory
                .selectFrom(member)
//                .where(usernameEq(usernameCond), ageEq(ageCond))
                .where(allEq(usernameCond,ageCond))
                .fetch();
    }
    private BooleanBuilder usernameEq(String usernameCond)
    {
        return nullSafeBuilder(() ->member.username.eq(usernameCond));
    }
    private BooleanBuilder ageEq(Integer ageCond)
    {
        return nullSafeBuilder(() ->member.age.eq(ageCond));
    }
    private BooleanBuilder allEq(String usernameCond, Integer ageCond)
    {
        return usernameEq(usernameCond).and(ageEq(ageCond));
    }
    public static BooleanBuilder nullSafeBuilder(Supplier<BooleanExpression> f)
    {
        try
        {
            return new BooleanBuilder(f.get());
        }
        catch (IllegalArgumentException e)
        {
            return new BooleanBuilder();
        }
    }

동적쿼리를 함수로 분리하여서 좀더 가독성을 높여서 볼 수 있다.

이렇게 해야 null 이 들어왔을떄 Exception을 터트리지않고 사용가능하다.

강사님 where 다중 파라미터를 이용한 동적 쿼리 사용에 대한 질문입니다. - 인프런 | 질문 & 답변 (inflearn.com)

강사님 where 다중 파라미터를 이용한 동적 쿼리 사용에 대한 질문입니다. - 인프런 | 질문 & 답변

 

BulkUpdate

	    @Test
    public void bulkUpdate() throws Exception
    {
        queryFactory
                .update(member)
                .set(member.username,"비회원")
                .where(member.age.lt(28))
                .execute();

        List<Member> result = queryFactory
                .selectFrom(member)
                .fetch();
        //영속성 컨텍스트는 그대로 임으로 변경안된것들이 나온다. em.flush() , em.clear()를 해줘야 같아짐
        
    }

    @Test
    public void bulkAdd() throws Exception
    {
        long count = queryFactory
                .update(member)
                .set(member.age, member.age.add(1))
                .execute();
    }
    @Test
    public void bulkDelete() throws Exception
    {
        queryFactory
                .delete(member)
                .where(member.age.gt(18))
                .execute();
    }

add 와 delete

 

Builder 를 통한 Search Query

@Data
public class MemberSearchCondition
{
    private String userName;
    private String teamName;
    private Integer ageGoe;
    private Integer ageLoe;
}

    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 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() throws Exception
    {
        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");
    }

 

조합을 통한 Search Query

    public List<MemberTeamDto> search(MemberSearchCondition condition)
    {
        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(
                        usernameEq(condition.getUserName()),
                        teamNameEq(condition.getTeamName()),
                        ageGoe(condition.getAgeGoe()),
                        ageLoe(condition.getAgeLoe())
                )
                .fetch();
    }


    private BooleanBuilder usernameEq(String username)
    {
//        return StringUtils.hasText(username) ? null : member.username.eq(username);
        return nullSafeBuilder(() -> member.username.eq(username));
    }

    private BooleanBuilder teamNameEq(String teamName)
    {
//        return StringUtils.hasText(teamName) ? null : team.name.eq(teamName);
        return nullSafeBuilder(() -> team.name.eq(teamName));
    }

    private BooleanBuilder ageGoe(Integer ageGoe)
    {
//        return ageGoe == null ? null : member.age.goe(ageGoe);
        return nullSafeBuilder(() -> member.age.goe(ageGoe));
    }

    private BooleanBuilder ageLoe(Integer ageLoe)
    {
//        return ageLoe == null ? null : member.age.loe(ageLoe);
        return nullSafeBuilder(() -> member.age.loe(ageLoe));
    }

    public static BooleanBuilder nullSafeBuilder(Supplier<BooleanExpression> f)
    {
        try
        {
            return new BooleanBuilder(f.get());
        } catch (Exception e)
        {
            return new BooleanBuilder();
        }
    }