Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Examples/JPA/Pagination"
< EclipseLink | Examples | JPA
(New page: <div style="border:1px solid #999999;background-color:#ffffff;align:center"> <table border="0" cellpadding="5" align="center"><tr><td width="20">image:Catnicon.gif</td><td>This page is...) |
|||
| Line 18: | Line 18: | ||
=== Oracle ROWNUM === | === Oracle ROWNUM === | ||
| + | |||
| + | |||
When the target platform is Oracle then ROWNUM is used in the generated SQL in addition to a hint to retrieve a page of results. | When the target platform is Oracle then ROWNUM is used in the generated SQL in addition to a hint to retrieve a page of results. | ||
| Line 32: | Line 34: | ||
<pre> | <pre> | ||
| − | SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM (SELECT t0.EMP_ID AS EMP_ID1, t1.EMP_ID AS EMP_ID2, t0.F_NAME AS F_NAME3, t0.L_NAME AS L_NAME4, t0.START_TIME AS START_TIME5, t0.END_TIME AS END_TIME6, t0.GENDER AS GENDER7, t1.SALARY AS SALARY8, t0.VERSION AS VERSION9, t0.START_DATE AS START_DATE10, t0.END_DATE AS END_DATE11, t0.MANAGER_ID AS MANAGER_ID12, t0.ADDR_ID AS ADDR_ID13 FROM EMPLOYEE t0, SALARY t1 WHERE (t1.EMP_ID = t0.EMP_ID) ORDER BY t0.L_NAME ASC, t0.F_NAME ASC) a WHERE ROWNUM <= ?) WHERE rnum > ? | + | SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM ( |
| + | SELECT t0.EMP_ID AS EMP_ID1, t1.EMP_ID AS EMP_ID2, t0.F_NAME AS F_NAME3, t0.L_NAME AS L_NAME4, t0.START_TIME AS START_TIME5, | ||
| + | t0.END_TIME AS END_TIME6, t0.GENDER AS GENDER7, t1.SALARY AS SALARY8, t0.VERSION AS VERSION9, t0.START_DATE AS START_DATE10, | ||
| + | t0.END_DATE AS END_DATE11, t0.MANAGER_ID AS MANAGER_ID12, t0.ADDR_ID AS ADDR_ID13 | ||
| + | FROM EMPLOYEE t0, SALARY t1 | ||
| + | WHERE (t1.EMP_ID = t0.EMP_ID) ORDER BY t0.L_NAME ASC, t0.F_NAME ASC) a WHERE ROWNUM <= ?) WHERE rnum > ? | ||
| + | |||
bind => [10, 5] | bind => [10, 5] | ||
</pre> | </pre> | ||
== Chunking using PKs with IN == | == Chunking using PKs with IN == | ||
Revision as of 22:38, 28 March 2008
 | This page is under construction. |
Contents
How to use EclipseLink Pagination
There are a variety of solution options for paging results in an EclipseLink JPA application. This how-to highlights various approaches with code samples of how they can be used.
Database Cursors
In a stateful environment a cursor can be used to pull in only those entities/data requested.
FirstResult/MaxRows
Database Specific SQL
EclipseLink's target database platform enables the generation of native SQL that can more optimally retrieve a set of rows.
Oracle ROWNUM
When the target platform is Oracle then ROWNUM is used in the generated SQL in addition to a hint to retrieve a page of results.
Query query = em
.createQuery("SELECT e FROM Employee e ORDER BY e.lastName ASC, e.firstName ASC");
query.setFirstResult(5);
query.setMaxResults(5);
List<Employee> emps = query.getResultList();
Generates the SQL like:
SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM ( SELECT t0.EMP_ID AS EMP_ID1, t1.EMP_ID AS EMP_ID2, t0.F_NAME AS F_NAME3, t0.L_NAME AS L_NAME4, t0.START_TIME AS START_TIME5, t0.END_TIME AS END_TIME6, t0.GENDER AS GENDER7, t1.SALARY AS SALARY8, t0.VERSION AS VERSION9, t0.START_DATE AS START_DATE10, t0.END_DATE AS END_DATE11, t0.MANAGER_ID AS MANAGER_ID12, t0.ADDR_ID AS ADDR_ID13 FROM EMPLOYEE t0, SALARY t1 WHERE (t1.EMP_ID = t0.EMP_ID) ORDER BY t0.L_NAME ASC, t0.F_NAME ASC) a WHERE ROWNUM <= ?) WHERE rnum > ? bind => [10, 5]