Search code examples
paginationopenjpaoracle-database

OpenJPA Paging (setMaxResults/setFirstResult)

I got the same problem as described in this post.

There's a solution, which adds the primary key to the sort clause, which I think is ok, but correct pagination should be done by OpenJPA itself I think. We use OpenJPA 2.1.1 and Oracle 11g.

My solution would be (change in OpenJPA):

Change the SQL Statement-Generation. Actually it's the following, which leads to the described problems.

SELECT * FROM (SELECT r.*, ROWNUM RNUM FROM ([my statement]) r WHERE ROWNUM <= 50) WHERE RNUM > 25

I would prefer:

SELECT outer.* FROM ( 
  SELECT ROWNUM rn, inner.* FROM 
    ([my statement]) 
  inner) 
outer WHERE outer.rn > 25 AND outer.rn <= 50

Why the first one is used? Better performance? Otherwise the second one would be the right one for paging with OpenJPA.

Is there any other possibility in OpenJPA to do Paging the right way?

Solution

  • If you use paging, you should order by a unique sort column! I add the primary key as sorting criterion now and it works perfect!