Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-2416

setFirstResult/setMaxResults (Paging) with Oracle

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Not A Problem
    • Affects Version/s: 2.1.1
    • Fix Version/s: None
    • Component/s: query
    • Labels:
      None

      Description

      We are using OpenJPA with Oracle 11g Database. For Paging we use setFirstResult and setMaxResults to Page through the result set.

      Lets take an example: PageSize 25.

      first Statement: firstResult: 0; maxResults: 26 (1 more to check if it is truncated)
      SQL generated:
      SELECT * FROM ([my statement]) WHERE ROWNUM <= 26

      second Statement: firstResult: 25; maxResults: 26
      SQL generated:
      SELECT * FROM (SELECT r.*, ROWNUM RNUM FROM ([my statement]) r WHERE ROWNUM <= 50) WHERE RNUM > 25

      The way limiting the resultset for paging is sometimes not correct. The last result of the statement should be equal to the first result of the new page - but it isn't always. If the sorting is done on a non-unique column in the database, there are different sortings in the resultset of these two statements. I don't know why it is done this way, I would prefer the following statement (this works for all values of firstResult and maxResults with non-unique sort column):

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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              andreas.mader Andreas Mader
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: