jeonyoungho Oct 28, 2021 2021-10-28T00:00:00+09:00
Oct 29, 2021 2021-10-29T11:08:47+09:00 2 min
본 포스팅은 인프러의 JPA 기본편을 수강하고 정리하는 내용입니다.
페이징 API
Oracle DB나 MSSQL같은 경우엔 페이징이 거지 같다. 쿼리를 막 rownum쓰고 그거를 order by까지 적용하려면 3depth로 거지같아진다.. 이러한 문제들을 깔끔하게 해결해준다.
- JPA는 페이징을 다음 두 API로 추상화
setFirstResult(int startPosition) : 조회 시작 위치(0부터 시작)setMaxResults(int maxResult) : 조회할 데이터 수
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
| //페이징 쿼리
for (int i=0; i< 100; i++) {
Member member = new Member();
member.setUsername("member" + i);
member.setAge(i);
em.persist(member);
}
em.flush();
em.clear();
List<Member> result = em.createQuery("select m from Member m order by m.age desc", Member.class)
.setFirstResult(1)
.setMaxResults(10)
.getResultList();
System.out.println("result.size = " + result.size());
for (Member member1 : result) {
System.out.println("member1 = " + member1);
}
SQL:
Hibernate:
/* 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 ? offset ?
|
만약 방언을 Oracle로 바꾼다면? Oracle에 맞는 rownum 쿼리로 바껴나간다. 엔티티 매핑 정보와 dialect(방언) 정보를 합쳐서 쿼리가 나간다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| Hibernate:
/* select
m
from
Member m
order by
m.age desc */ select
*
from
( select
row_.*,
rownum rownum_
from
( 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 ) row_
where
rownum <= ?
)
where
rownum_ > ?
|
데이터베이스별 페이징 쿼리 비교
1
2
3
4
5
6
7
8
9
| 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 ?, ?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| 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_ > ?
|
