Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Examples/JPA/Pagination"
(→FirstResult/MaxRows) |
|||
Line 21: | Line 21: | ||
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. | ||
− | < | + | <source lang="java"> |
− | + | 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(); | |
− | + | </source> | |
− | </ | + | |
Generates the SQL like: | Generates the SQL like: | ||
− | < | + | <source lang="sql"> |
SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM ( | 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, | 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, | ||
Line 40: | Line 39: | ||
bind => [10, 5] | bind => [10, 5] | ||
− | </ | + | </source> |
==== Other Databases ==== | ==== Other Databases ==== | ||
Line 50: | Line 49: | ||
This involve performing a query on the database for all of the primary key values that match the criteria and then using these values to retieve specific sets. | This involve performing a query on the database for all of the primary key values that match the criteria and then using these values to retieve specific sets. | ||
− | < | + | <source lang="java"> |
− | + | Query query = em.createQuery("SELECT e.id FROM Employee e ORDER BY e.lastName ASC, e.firstName ASC"); | |
− | + | List<Integer> empIds = query.getResultList(); | |
− | + | ||
− | + | ReadAllQuery raq = new ReadAllQuery(Employee.class); | |
− | + | ExpressionBuilder eb = raq.getExpressionBuilder(); | |
− | + | raq.setSelectionCriteria(eb.get("id").in(empIds.subList(5, 10))); | |
− | + | List<Employee> emps = JpaHelper.createQuery(raq, em).getResultList(); | |
− | </ | + | </source> |
The SQL generated for this query looks like: | The SQL generated for this query looks like: | ||
− | < | + | <source lang="sql"> |
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 FROM EMPLOYEE t0, SALARY t1 WHERE (t1.EMP_ID = t0.EMP_ID) ORDER BY t0.L_NAME ASC, t0.F_NAME ASC | ||
Line 72: | Line 70: | ||
bind => [8, 1, 12, 3, 10] | bind => [8, 1, 12, 3, 10] | ||
− | </ | + | </source> |
Revision as of 15:37, 23 June 2008
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.
Contents
FirstResult/MaxRows
The JPA Query object contains support for configuring the first and max rows to retrieve when executing a query. By default EclipseLink translated thes 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.
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]
Other Databases
At present there is not yet custom SQL support for other database vendors but this needs to be addressed and some bugs have been filed. If you require this support please post a message on the newsgroup so that the work can be properly prioritized based on demand.
Chunking using PKs with IN
This involve performing a query on the database for all of the primary key values that match the criteria and then using these values to retieve specific sets.
Query query = em.createQuery("SELECT e.id FROM Employee e ORDER BY e.lastName ASC, e.firstName ASC"); List<Integer> empIds = query.getResultList(); ReadAllQuery raq = new ReadAllQuery(Employee.class); ExpressionBuilder eb = raq.getExpressionBuilder(); raq.setSelectionCriteria(eb.get("id").in(empIds.subList(5, 10))); List<Employee> emps = JpaHelper.createQuery(raq, em).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]