순수 JPA 페이징과 정렬

• 검색 조건: 나이가 10살
• 정렬 조건은 이름으로 내림차순
• 페이징 조건: 첫 번째 페이지, 페이지당 보여줄 데이터는 3건으로 나타낼 수 있다.

 

MemberJPARepository에서 아래와 같은 코드를 짜보면

public List<Member> findByPage(int age, int offset, int limit) {
        em.createQuery("select m from Member m where m.age = :age order by m.username desc")
                .setParameter("age", age)
                .setFirstResult(offset)
                .setMaxResults(limit)
                .getResultList();
    }

JPQL만 짜놓으면 현재 db에 맞는 SQL 쿼리를 날려서 db에서 페이징을 해온다.

그리고 total count도 가지고 오자.

public long totalCount(int age) {
        return em.createQuery("select count(m) from Member m where m.age = :age", Long.class)
                .setParameter("age", age)
                .getSingleResult();
}

 

 

MemberJpaRepositoryTest에서 코드를 작성하고 돌려보면

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

        int age = 10;
        int offset = 0;
        int limit = 3;

        // when
        List<Member> members = memberJpaRepository.findByPage(age, offset, limit);
        // total count도 가지고 온다.
        long totalCount = memberJpaRepository.totalCount(age);

        // 페이지 계산 종식 적용...
        // totalPage = totalCount / size...
        // 마지막 페이지 ...
        // 최초 페이지 ..

        // then
        assertThat(members.size()).isEqualTo(3);
        assertThat(totalCount).isEqualTo(5);
    }

run을 돌리면 데이터를 가져오는 결과를 볼 수 있다.

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;

 

offset = 1 limit = 3을 하고 돌리게 되면 offset,  limit의 값이 각각 할당되어 run 결과를 볼 수가 있다.

그리고 JPA는 db가 바뀌어도 상관이 없다. 현재 db에 맞는 쿼리가 나가기 때문이다.

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;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<출처 김영한: 실전! 스프링 데이터 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 - 인프런 | 강의