DEV ℧ Developer Diary

[JPA] JPQL - 페이징

13 Aug 2022

해당 포스트는 인프런 김영한님의 자바 ORM 표준 JPA 프로그래밍 - 기본편 을 듣고 정리한 글입니다.

JPQL - 페이징

페이징 API

  • JPA는 페이징을 다음 두 API로 추상화
  • setFirstResult(int startPosition) : 조회 시작 위치(0부터 시작)
  • setMaxResults(int maxResult) : 조회할 데이터 수

setFirstResult와 setMaxResults를 이용해서 간단하게 페이징처리를 해 조회를 해보도록 하자.

/* 페이징 확인을 위해 Member 데이터를 100개 집어넣음 */
for (int i = 0; i < 100; i++) {
    Member member = new Member();
    member.setUsername("member" + i);
    member.setAge(i+5);
    entityManager.persist(member);
}

entityManager.flush();
entityManager.clear();

List<Member> memberList = entityManager.createQuery("SELECT m FROM Member m ORDER BY m.age DESC", Member.class)
                .setFirstResult(0) /* 조회 시작 위치 */
                .setMaxResults(10) /* 조회할 데이터 수 */
                .getResultList();

System.out.println("memberList.size : " + memberList.size());
for (Member findMember : memberList) {
    System.out.println("findMember.username : " + findMember);
}

limit가 호출되며 제한된 수의 목록을 가져오는걸 확인 할 수 있다.

/* SELECT
        m
    FROM
        Member m
    ORDER BY
        m.age DESC */ select
            member0_.id as id1_0_,
            member0_.age as age2_0_,
            member0_.TEAM_ID as team_id4_0_,
            member0_.username as username3_0_
        from
            Member member0_
        order by
            member0_.age DESC limit ?

memberList.size : 10
findMember.username : Member{id=100, username='member99', age=104}
findMember.username : Member{id=99, username='member98', age=103}
findMember.username : Member{id=98, username='member97', age=102}
findMember.username : Member{id=97, username='member96', age=101}
findMember.username : Member{id=96, username='member95', age=100}
findMember.username : Member{id=95, username='member94', age=99}
findMember.username : Member{id=94, username='member93', age=98}
findMember.username : Member{id=93, username='member92', age=97}
findMember.username : Member{id=92, username='member91', age=96}
findMember.username : Member{id=91, username='member90', age=95}

limit의 경우 해당 SQL의 방언이 H2의 형식이기 때문에 limit을 이용해서 페이징을 처리한다. 각 SQL 종류마다 페이징 쿼리를 간단하게 알아보자.

페이징 API - SQL별 방언

MySQL

SELECT
     M.ID AS ID,
     M.AGE AS AGE,
     M.TEAM_ID AS TEAM_ID,
     M.NAME AS NAME
FROM
    MEMBER M
ORDER BY
    M.NAME DESC LIMIT ?, ?

Oracle

SELECT * FROM
    ( SELECT ROW_.*, ROWNUM ROWNUM_
        FROM
            ( SELECT
                     M.ID AS ID,
                     M.AGE AS AGE,
                     M.TEAM_ID AS TEAM_ID,
                     M.NAME AS NAME
                FROM MEMBER M
            ORDER BY M.NAME
            ) ROW_
        WHERE ROWNUM <= ?
    )
WHERE ROWNUM_ > ?

Related posts