EclipseLink/Examples/JPA/Pagination

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.

FirstResult/MaxRows

The JPA Query object contains support for configuring the first and max rows to retrieve when executing a query. When using this method it is important to use an ORDER BY, as multiple querys will be used for each page, and you want to ensure you get back the results in the same order.

By default EclipseLink translated these values into the parameters provided to the JDBC statement. This then leaves it up the JDBC driver to handle the range of rows requested. In many JDBC implementations this provides little performance value as the database does not limit the rows returned but they are instead filtered on the JDBC client. Most drivers support setting the max results, but the first result involves paging through the results on the JDBC client.

Database Specific SQL

EclipseLink's target database platform enables the generation of native SQL that can more optimally retrieve a set of rows.

As of the EclipseLink 2.0 release the following database platforms have pagination support in EclipseLink:

• Oracle
• MySQL
• PostgreSQL
• DB2
• H2
• HSQL
• Firebird

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]

Other Databases

The SQL syntax for pagination is non standard, and only some databases support a custom syntax. If you require this support on a database platform that EclipseLink does not support it on please post a message on the forum so that the work can be properly prioritized based on demand.

Chunking using Ids with IN

This involves performing a query on the database for all of the Id values that match the criteria and then using these values to retrieve specific sets. This only works for singleton Ids not composite Ids. For composite Ids it is still possible through using dynamic SQL, or native SQL on some database, but much more complex.

Query query = em.createQuery("SELECT e.id FROM Employee e ORDER BY e.lastName ASC, e.firstName ASC");
List<Integer> empIds = query.getResultList();
 
// As of EclipseLink 1.2, IN can be used with a parameter List, in previous releases a native EclipseLink Expression query is required.
Query query = em.createQuery("SELECT e FROM Employee e WHERE e.id IN :empIds");
query.setParameter("empIds", empIds.subList(5, 10));
List<Employee> emps = query.getResultList();

The SQL generated for this query looks like:

SELECT t0.EMP_ID FROM EMPLOYEE t0, SALARY t1 WHERE (t1.EMP_ID = t0.EMP_ID) ORDER BY t0.L_NAME ASC, t0.F_NAME ASC
 
 
SELECT t0.EMP_ID, t1.EMP_ID, t0.F_NAME, t0.L_NAME, t0.START_TIME, t0.END_TIME, t0.GENDER, t1.SALARY, t0.VERSION, 
       t0.START_DATE, t0.END_DATE, t0.MANAGER_ID, t0.ADDR_ID FROM EMPLOYEE t0, SALARY t1 
    WHERE ((t0.EMP_ID IN (?, ?, ?, ?, ?)) AND (t1.EMP_ID = t0.EMP_ID))
 
    bind => [8, 1, 12, 3, 10]

Using a ScrollableCursor

EclipseLink supports returning a ScrollableCursor object from a Query. A ScrollableCursor can be enabled using query hints. This returns a database cursor on the Query's result set, and allows the client to scroll through the results page by page. The drawback for this technique is that the ScrollableCursor represents a live cursor and connection with the database, so will normally not live across web page requests, but useful for scrolling for a server process or batch job.

Query query = em.createQuery("SELECT e FROM Employee e ORDER BY e.lastName ASC, e.firstName ASC");
query.setHint("eclipselink.cursor.scrollable", true);
ScrollableCursor scrollableCursor = (ScrollableCursor)query.getSingleResult();
List<Employee> emps = scrollableCursor.next(10);

Paging List

Using a simple utility class the paging of query results (using first/max results) can be encapsulated and hidden from the application developer. An example of this is avilable in bug 370875.

Usage Example:

TypedQuery<Contact> query = em.createQuery("SELECT c FROM Contact c WHERE c.name = :NAME ORDER BY c.id", Contact.class);
query.setParameter("NAME", "Liam");
 
List<Contact> results = new PagingList<Contact>(query, 20);