Querydsl 페이징 연동

1. SpringData Paging 활용

---

• 스프링 데이터의 Page, Pageable을 활용해보자.
  
  • 전체 카운트를 한번에 조회하는 단순한 방법
  • 데이터 내용과 전체 카운트를 별도로 조회하는 방법

 

 

 

2. 인터페이스에 두 가지 기능 추가

---

public interface MemberRepositoryCustom {

    List<MemberTeamDto> search(MemberSearchCondition condition);
   
    Page<MemberTeamDto> searchPageSimple(MemberSearchCondition condition, Pageable pageable);
    Page<MemberTeamDto> searchPageComplex(MemberSearchCondition condition, Pageable pageable);
    
}

 

 

3. 전체 카운트를 한번에 조회하는 단순한 방법

---

• searchPageSimple(), fetchResults() 사용
• fetchResults()를 사용하면 content + paging 정보 같이 가져옴
• offset : pageable.getOffset()
• limit : pageable.getPageSize()
• PageImpl : Page의 구현체

	public PageImpl(List<T> content, Pageable pageable, long total) {

		super(content, pageable);

		this.total = pageable.toOptional().filter(it -> !content.isEmpty())//
				.filter(it -> it.getOffset() + it.getPageSize() > total)//
				.map(it -> it.getOffset() + content.size())//
				.orElse(total);
	}

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

• Test
  • PageRequest로 요청 정보 넘김. : Pageable의 구현체

    @Test
    public void searchSimple(){
        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();
        PageRequest pageRequest = PageRequest.of(0, 3);

        Page<MemberTeamDto> result = memberRepository.searchPageSimple(condition, pageRequest);
        assertThat(result.getSize()).isEqualTo(3);
        assertThat(result.getContent()).extracting("username").containsExactly("member1", "member2", "member3");

• Querydsl이 제공하는 fetchResults() 를 사용하면 
• 내용과 전체 카운트를 한번에 조회할 수 있다.(실제쿼리는 2번 호출 : 조회 쿼리, count쿼리)
• fetchResult() 는 카운트 쿼리 실행시 필요없는 order by 는 제거한다.

 

 

4. 데이터 내용과 전체 카운트를 별도로 조회하는 방법

---

• 카운트 쿼리 최적화.
• searchPageComplex()
• Contetn가져오는 쿼리와 Count쿼리를 분리
• 상황에 따라 count쿼리에 join이 필요 없다든지, simple한 경우가 있을 때 분리해서 사용하면 이점이 있음.

    @Override
    public Page<MemberTeamDto> searchPageComplex(MemberSearchCondition condition, Pageable pageable) {

       //content를 바로 가져오고.
        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();

        //count쿼리는 직접 날림.
        long total = queryFactory
                .select(member)
                .from(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);
    }

 

 

 

5. GitHub : 210904 Querydsl Paging

---

GitHub - bsh6463/Querydsl