Querydsl - Spring Data JPA에서 제공하는 페이징 활용

 

 

Spring Data JPA에서 제공하는 페이징 활용

• QueryDSl에서 페이징 사용
• Count 쿼리 최적화
• Controller 개발

 

 

QueryDSL에서 페이징 사용

 

1. 커스텀 인터페이스에 메서드 추가

public interface MemberRepositoryCustom {
    List<MemberTeamDto> search(MemberSearchCondition condition);
    Page<MemberTeamDto> searchPage(MemberSearchCondition condition, Pageable pageable);	// (1)
}

• (1) : Pageable 페이징에 필요한 정보 전달(offset, limit)

반응형

 

2. 커스텀 인터페이스 구현체에서 메서드 구현

@Override
public Page<MemberTeamDto> searchPage(MemberSearchCondition condition, Pageable pageable) {
    List<MemberTeamDto> content = queryFactory	// (1)
            .select(new QMemberTeamDto(
                    member.id,
                    member.username,
                    member.age,
                    team.id,
                    team.name))
            .from(member)
            .leftJoin(member.team, team)
            .where(
                    usernameEq(condition.getUsername()),
                    teamNameEq(condition.getTeamName()),
                    ageGoe(condition.getAgeGoe()),
                    ageLoe(condition.getAgeLoe())
            )
            .offset(pageable.getOffset())   // (2) 페이지 번호
            .limit(pageable.getPageSize())  // (3) 페이지 사이즈
            .fetch();

    Long count = queryFactory		// (4) 
            .select(member.count())
            .from(member)
//                .leftJoin(member.team, team)		(5) 검색조건 최적화
            .where(
                    usernameEq(condition.getUsername()),
                    teamNameEq(condition.getTeamName()),
                    ageGoe(condition.getAgeGoe()),
                    ageLoe(condition.getAgeLoe())
            )
            .fetchOne();

    return new PageImpl<>(content, pageable, count);	// (6) PageImpl 반환
}

• (1) : 페이징을 사용한 데이터 조회
• (2) : 페이지 offset(0부터 시작)
• (3) : 페이지 limit(페이지 사이즈)
• (4) : count 조회
• (5) : 검색조건 최적화(성능 최적화), count에 필요없는 leftJoin 제거 후 count
• (6) : 페이징과 관련된 정보 반환

반응형

3. 코드 최적화

@Override
public Page<MemberTeamDto> searchPageComplex(MemberSearchCondition condition, Pageable pageable) {
    List<MemberTeamDto> content = getMemberTeamDtos(condition, pageable);

    Long count = getCount(condition);

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

private Long getCount(MemberSearchCondition condition) {
    Long count = queryFactory
            .select(member.count())
            .from(member)
//                .leftJoin(member.team, team)
            .where(
                    usernameEq(condition.getUsername()),
                    teamNameEq(condition.getTeamName()),
                    ageGoe(condition.getAgeGoe()),
                    ageLoe(condition.getAgeLoe())
            )
            .fetchOne();
    return count;
}

private List<MemberTeamDto> getMemberTeamDtos(MemberSearchCondition condition, Pageable pageable) {
    List<MemberTeamDto> content = queryFactory
            .select(new QMemberTeamDto(
                    member.id,
                    member.username,
                    member.age,
                    team.id,
                    team.name))
            .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();
    return content;
}

 

 참고로 fetchResult를 사용하면 페이징 처리한 데이터와 count를 같이 조회할 수 있다. 하지만 fetchResult를 사용하면 count 쿼리 부분에서 문제가 된다. count에 상관없는 테이블을 조회하는 등의 문제로 성능 이슈가 발생한다. 따라서 현재 Querydsl에서는 fetchResult를 사용하지 않도록 deprecate 된 상태이므로 위에서 작성한 예시 코드처럼 페이징으로 데이터 조회하는 쿼리와 count를 구하는 쿼리를 따로 작성하는 것을 권장한다.

 

반응형

4. 테스트 코드

@Test
@DisplayName("Page 테스트")
public void searchWithPage2() {
    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);		// (1)


    Page<MemberTeamDto> results = memberRepository.searchPageComplex(condition, pageRequest);	// (2)

    assertThat(results.getSize()).isEqualTo(3);
    assertThat(results.getContent()).extracting("username").containsExactly("member1", "member2", "member3");

}

• (1) : 페이징에 사용할 page, size 데이터를 갖는 PageRequest 생성
• (2) : 페이징 결과 반환

---

 

Count 쿼리 최적화

 

• PageableExecutionUtils를 사용하면 Count 쿼리 최적화할 수 있다.
• 내부적으로 Count 쿼리가 필요없으면 조회해오지 않는다
• 조건 : 페이지 시작이면서 컨텐츠 사이즈가 페이지 사이즈보다 작을 때
          마지막 페이지 일 때(offset + 컨텐츠 사이즈를 더해서 전체 사이즈를 구함)

 

@Override
public Page<MemberTeamDto> searchPageComplex(MemberSearchCondition condition, Pageable pageable) {
    List<MemberTeamDto> content = getMemberTeamDtos(condition, pageable);

    JPAQuery<Long> countQuery = getCount(condition);

    return PageableExecutionUtils.getPage(content, pageable, () -> countQuery.fetchOne()); // (1)
//        return new PageImpl<>(content, pageable, count);
}

private JPAQuery<Long> getCount(MemberSearchCondition condition) {
    JPAQuery<Long> countQuery = queryFactory
            .select(member.count())
            .from(member)
//                .leftJoin(member.team, team)
            .where(
                    usernameEq(condition.getUsername()),
                    teamNameEq(condition.getTeamName()),
                    ageGoe(condition.getAgeGoe()),
                    ageLoe(condition.getAgeLoe())
            );
    return countQuery;	// (2)
}

• (1) : PageableExecutionUtils가 내부적으로 count 쿼리가 필요없으면 조회해오지 않는다.
• (2) : JPQL 쿼리 리턴

---

반응형

Controller 개발

 

1. 페이징처리를 위한 컨트롤러 구현

@GetMapping("/v2/members")
public Page<MemberTeamDto> searchMemberV2(MemberSearchCondition condition, Pageable pageable) {
    return memberRepository.searchPageComplex(condition, pageable); // 바인딩 된다 page, size, sort
}

 

2. Postman 요청

 

 참고로 파라미터 없이 요청하면 기본 설정으로 Pageable에 page와 size가 바인딩된다.(page=0, size=20)

 

http://localhost:8080/v2/members

    /* select
        member1.id,
        member1.username,
        member1.age,
        team.id,
        team.name 
    from
        Member member1   
    left join
        member1.team as team */ 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 fetch first ? rows only

...

    /* select
        count(member1) 
    from
        Member member1 */ select
            count(member0_.member_id) as col_0_0_ 
        from
            member member0_

 

http://localhost:8080/v2/members?page=1&size=5

    /* select
        member1.id,
        member1.username,
        member1.age,
        team.id,
        team.name 
    from
        Member member1   
    left join
        member1.team as team */ 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 offset ? rows fetch next ? rows only
    
...
    
    
    /* select
        count(member1) 
    from
        Member member1 */ select
            count(member0_.member_id) as col_0_0_ 
        from
            member member0_

 

 

http://localhost:8080/v2/members?page=0&size=200

    /* select
        member1.id,
        member1.username,
        member1.age,
        team.id,
        team.name 
    from
        Member member1   
    left join
        member1.team as team */ 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 fetch first ? rows only

• count 쿼리로 조회해오지 않는다.