스프링 데이터 JPA 페이징과 정렬

아래 인터페이스 2개로 공통화를 시켰는데 페이징을 공통화 시켰다고 할 수 있다.(Sort와 Pageable 두개로)

또 아래와 같이 특별하게 반환 타입을 지정해줘서 표준화를 해줄수가 있다.

Page =>(totalCount와 contents 둘다 같이 가져와주고) page index는 0부터

Slice => (contents만 가져와줘서 +1 해준다.)

 

스프링 데이터 JPA 작성을 위해서 아래와 같이 MemberRepositoryTest를 작성을 해주고   

@Test
    public void paging() {
        memberRepository.save(new Member("member1", 10));
        memberRepository.save(new Member("member2", 10));
        memberRepository.save(new Member("member3", 10));
        memberRepository.save(new Member("member4", 10));
        memberRepository.save(new Member("member5", 10));

        int age = 10;
        PageRequest pageRequest = PageRequest.of(0, 3, Sort.by(Sort.Direction.DESC, "username"));

        // when
        Page<Member> page = memberRepository.findByAge(age, pageRequest);
//         totalCount는 필요없다 => 반환타입이 page인걸 인식하고 totalCount까지 같이 날린다.
//        long totalCount = memberRepository.totalCount(age);

        // then
        List<Member> content = page.getContent();
        long totalElements = page.getTotalElements();

        for (Member member : content) {
            System.out.println("member = " + member);
        }

        System.out.println("totalElements = " + totalElements);
    }

아래 부분 작성중에 pageRequest 부분에서 에러가 났다. 

        // when
        Page<Member> page = memberRepository.findByAge(age, pageRequest);
//         totalCount는 필요없다 => 반환타입이 page인걸 인식하고 totalCount까지 같이 날린다.
//        long totalCount = memberRepository.totalCount(age);

아래 문구로 해결

아래와 같이 run 실행결과를 확인할 수 있다.(limit 3 / age 10)

Desc "username"으로 설정을 해서 5 4 3으로 해서 나오고 totalElements도 잘 나온다.

    select
        member0_.member_id as member_i1_0_,
        member0_.age as age2_0_,
        member0_.tema_id as tema_id4_0_,
        member0_.username as username3_0_ 
    from
        member member0_ 
    where
        member0_.age=? 
    order by
        member0_.username desc limit ? offset ?
2022-04-17 19:14:19.053  INFO 37823 --- [           main] p6spy                                    : #1650190459053 | took 0ms | statement | connection 3| url jdbc:h2:tcp://localhost/~/datajpa
select member0_.member_id as member_i1_0_, member0_.age as age2_0_, member0_.tema_id as tema_id4_0_, member0_.username as username3_0_ from member member0_ where member0_.age=? order by member0_.username desc limit ? offset ?
select member0_.member_id as member_i1_0_, member0_.age as age2_0_, member0_.tema_id as tema_id4_0_, member0_.username as username3_0_ from member member0_ where member0_.age=10 order by member0_.username desc limit 3 offset 1;

member = Member(id=5, username=member5, age=10)
member = Member(id=4, username=member4, age=10)
member = Member(id=3, username=member3, age=10)
totalElements = 5

 

이제 아래와 같이 page에 관한 코드를 작성해서 돌리면 결과가 아래와 같다.

@Test
    public void paging() {
        memberRepository.save(new Member("member1", 10));
        memberRepository.save(new Member("member2", 10));
        memberRepository.save(new Member("member3", 10));
        memberRepository.save(new Member("member4", 10));
        memberRepository.save(new Member("member5", 10));

        int age = 10;
        PageRequest pageRequest = PageRequest.of(0, 3, Sort.by(Sort.Direction.DESC, "username"));

        // when
        // 반환타입에 따라서 totalcount를 날릴지 안날릴지 결정을 한다.
        Page<Member> page = memberRepository.findByAge(age, pageRequest);
//         totalCount는 필요없다 => 반환타입이 page인걸 인식하고 totalCount까지 같이 날린다.
//        long totalCount = memberRepository.totalCount(age);

        // then
        List<Member> content = page.getContent();
        long totalElements = page.getTotalElements();


		// 사이즈는 3개
        assertThat(content.size()).isEqualTo(3);
        // totalelement는 5개
        assertThat(page.getTotalElements()).isEqualTo(5);
        // 페이지 번호는
        assertThat(page.getNumber()).isEqualTo(0);
        // 진행 한 후에 총 페이지 개수 
        assertThat(page.getTotalPages()).isEqualTo(2);
        // 첫번째 페이지인지
        assertThat(page.isFirst()).isTrue();
        // 다음 페이지가 있냐
        assertThat(page.hasNext()).isTrue();
    }

select count(member0_.member_id) as col_0_0_ from member member0_ where member0_.age=?
select count(member0_.member_id) as col_0_0_ from member member0_ where member0_.age=10;

 

 

이제 Slice를 써보자 Slice는 limit에 +1해서 4개를 요청한다.

MemberRepository

List<Member> findListByUsername(String username); // 컬렉션
Member findMemberByUsername(String username); // 단건
Optional<Member> findOptionalByUsername(String username); // 단건 Optional

Slice<Member> findByAge(int age, Pageable pageable);

 

 

MemberRepositoryTest 해서 실행하면

@Test
    public void paging() {
        memberRepository.save(new Member("member1", 10));
        memberRepository.save(new Member("member2", 10));
        memberRepository.save(new Member("member3", 10));
        memberRepository.save(new Member("member4", 10));
        memberRepository.save(new Member("member5", 10));

        int age = 10;
        PageRequest pageRequest = PageRequest.of(0, 3, Sort.by(Sort.Direction.DESC, "username"));

        // when
        Slice<Member> page = memberRepository.findByAge(age, pageRequest);
//         totalCount는 필요없다 => 반환타입이 page인걸 인식하고 totalCount까지 같이 날린다.
//        long totalCount = memberRepository.totalCount(age);

        // then
        List<Member> content = page.getContent();


        assertThat(content.size()).isEqualTo(3);
//        assertThat(page.getTotalElements()).isEqualTo(5);
        assertThat(page.getNumber()).isEqualTo(0);
//        assertThat(page.getTotalPages()).isEqualTo(2);
        assertThat(page.isFirst()).isTrue();
        assertThat(page.hasNext()).isTrue();
    }

 

count query(total count)가 없고. p6spy 덕택에 limit 값은 알 수가 있다.

select
        member0_.member_id as member_i1_0_,
        member0_.age as age2_0_,
        member0_.tema_id as tema_id4_0_,
        member0_.username as username3_0_ 
    from
        member member0_ 
    where
        member0_.age=? 
    order by
        member0_.username desc limit ?
2022-04-17 19:39:16.694  INFO 38217 --- [           main] p6spy                                    : #1650191956694 | took 0ms | statement | connection 3| url jdbc:h2:tcp://localhost/~/datajpa
select member0_.member_id as member_i1_0_, member0_.age as age2_0_, member0_.tema_id as tema_id4_0_, member0_.username as username3_0_ from member member0_ where member0_.age=? order by member0_.username desc limit ?
// p6spy가 limit 4라고 명시해준다.
select member0_.member_id as member_i1_0_, member0_.age as age2_0_, member0_.tema_id as tema_id4_0_, member0_.username as username3_0_ from member member0_ where member0_.age=10 order by member0_.username desc limit 4;

 

 

그냥 아래와 같이 List 라고 명시해서 쿼리만 확인할 수가 있다.

 

MemberRepository

List<Member> findByAge(int age, Pageable pageable);

 

MemberRepositoryTest

List<Member> page = memberRepository.findByAge(age, pageRequest);

 

limit 3이라고 확인을 할 수 있다.

select
        member0_.member_id as member_i1_0_,
        member0_.age as age2_0_,
        member0_.tema_id as tema_id4_0_,
        member0_.username as username3_0_ 
    from
        member member0_ 
    where
        member0_.age=? 
    order by
        member0_.username desc limit ?

 

select member0_.member_id as member_i1_0_, member0_.age as age2_0_, member0_.tema_id as tema_id4_0_, member0_.username as username3_0_ from member member0_ where member0_.age=10 order by member0_.username desc limit 3;

 

 

그리고 아래와 같이 count query를 분리할 수 가 있다.

@Query(value = "select m from Member m left join m.team t",
            countQuery = "select count(m) from Member m")
    Page<Member> findByAge(int age, Pageable pageable);

select
        count(member0_.member_id) as col_0_0_ 
    from
        member member0_
2022-04-17 19:54:49.326  INFO 38412 --- [           main] p6spy                                    : #1650192889326 | took 3ms | statement | connection 3| url jdbc:h2:tcp://localhost/~/datajpa

 

 

그리고 MemberRepositoryTest에서 아래 구문에서 Member Entity를 무조건 외부에 노출시키지 말고 DTO(Data Transfer Object)로 변환해서 내보내야 한다.

 

MemberRepositoryTest에서

MemberDto로 변환해서 외부로 반환을 해도 된다.

int age = 10;
PageRequest pageRequest = PageRequest.of(0, 3, Sort.by(Sort.Direction.DESC, "username"));

// when
Page<Member> page = memberRepository.findByAge(age, pageRequest);

Page<MemberDto> toMap = page.map(m -> new MemberDto(m.getId(), m.getUsername(), null));

// then
List<Member> content = page.getContent();

 

 

 

 

 

 

 

 

 

<출처 김영한: 실전! 스프링 데이터 JPA >

https://www.inflearn.com/course/%EC%8A%A4%ED%94%84%EB%A7%81-%EB%8D%B0%EC%9D%B4%ED%84%B0-JPA-%EC%8B%A4%EC%A0%84/dashboard

실전! 스프링 데이터 JPA - 인프런 | 강의