openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andreas Mader (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (OPENJPA-2416) setFirstResult/setMaxResults (Paging) with Oracle
Date Wed, 31 Jul 2013 06:53:50 GMT

    [ https://issues.apache.org/jira/browse/OPENJPA-2416?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13724937#comment-13724937
] 

Andreas Mader commented on OPENJPA-2416:
----------------------------------------

Wouldn't it be better, if you get correct data all the time (with and without unique ordering)?
My solution provides this...

We use Paging and Sorting in JSF-Datatables (Primefaces) - if the user takes a column which
is not unique as orderby-column, he gets a problem, he sees some data twice and some data
never. The only solution would be to add a unique column to every request, also if there is
no ordering applied by the user!
                
> setFirstResult/setMaxResults (Paging) with Oracle
> -------------------------------------------------
>
>                 Key: OPENJPA-2416
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2416
>             Project: OpenJPA
>          Issue Type: Improvement
>          Components: query
>    Affects Versions: 2.1.1
>            Reporter: Andreas Mader
>
> 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

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message