Jump to: navigation, search

Difference between revisions of "EclipseLink/Examples/JPA/Pagination"

(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 21:38, 28 March 2008

Catnicon.gifThis page is under construction.

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]

Chunking using PKs with IN