실전! Querydsl 4 (Spring Data JPA + Querydsl)

 

public interface MemberRepositoryCustom
{
    List<MemberTeamDto> search(MemberSearchCondition condition);
}

public class MemberRepositoryImpl implements MemberRepositoryCustom
{
    private final JPAQueryFactory queryFactory;

    public MemberRepositoryImpl(EntityManager em)
    {
        this.queryFactory = new JPAQueryFactory(em);
    }

    @Override
    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();
        }
    }
}

기존의 JPA에서 작성했던 동적쿼리는 똑같다. 가장 맘에드는 스타일로 가져왔다. 주의할것은 SpringDataJPA로 하여금 인식하게 할려면 이름이 MembeRepository + Impl 이어야 한다는것이다. 

public interface MemberRepository extends JpaRepository<Member,Long> , MemberRepositoryCustom
{
    //select m from Member m where m.username = ?
    List<Member> findByUsername(String username);
}

이렇게 상속을 추가해주면 이제 MemberRepositoryCustom 을 구현한 MemberRepositoryImpl 에서의 search 를 쓸수 있다.

    @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 = memberRepository.search(condition);

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

너무 어떤 특정화면에 특화된 쿼리이면 아예 따로 Repository를 만들어줘서 사용하는게 유지보수가 용이하다. 굳이 막 Custom에 억압되서 다 때려박는것도 좋은 설계가 아니다.

 

스프링 데이터 페이징 활용

    @Override
    public Page<MemberTeamDto> searchPageSimple(MemberSearchCondition condition, Pageable pageable)
    {
        QueryResults<MemberTeamDto> results = 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())
                )
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetchResults();
        List<MemberTeamDto> content = results.getResults();
        long total = results.getTotal();
        return new PageImpl<>(content,pageable,total);
    }

쿼리 중간에 orderby가 들어간다면 count를 가져올때는 적용이 안된체로 쿼리가 나간다.

    @Override
    public Page<MemberTeamDto> searchPageComplex(MemberSearchCondition condition, Pageable pageable)
    {
        List<MemberTeamDto> content = 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())
                )
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetch();

        long total = queryFactory
                .selectFrom(member)
                .leftJoin(member.team, team)
                .where(
                        usernameEq(condition.getUserName()),
                        teamNameEq(condition.getTeamName()),
                        ageGoe(condition.getAgeGoe()),
                        ageLoe(condition.getAgeLoe())
                )
                .fetchCount();

        return new PageImpl<>(content,pageable,total);
    }

카운트 쿼리만 다르게 날려줘서 최적화할 수 있다

 

Spring Data JPA 에서는 카운트쿼리를 생략할수 있는 경우가 있는데 

1. 페이지시작이면서 컨텐츠 사이즈가 페이즈 사이즈보다 작을 때
2. 마지막 페이지이면서 offset + 컨텐츠 사이즈를 통해 total 을 구한다.

    @Override
    public Page<MemberTeamDto> searchPageComplex(MemberSearchCondition condition, Pageable pageable)
    {
        List<MemberTeamDto> content = 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())
                )
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetch();

        JPAQuery<Member> countQuery = queryFactory
                .selectFrom(member)
                .leftJoin(member.team, team)
                .where(
                        usernameEq(condition.getUserName()),
                        teamNameEq(condition.getTeamName()),
                        ageGoe(condition.getAgeGoe()),
                        ageLoe(condition.getAgeLoe())
                );
        return PageableExecutionUtils.getPage(content,pageable,() -> countQuery.fetchCount());
//        return new PageImpl<>(content,pageable,total);
    }

PageableExecutionUtils.getPage를 이용하면 마지막에 람다를 전달함으로써 함수호출을 지연하여 위에 언급한 2가지 조건이 만족하면 count쿼리를 호출하지않고 간단 계산으로 total count를 PageImpl에 담아서 넣어준다.