Pagination support in Derby some thing like LIMIT in Mysql and Rownum in Oracle

[ramakishore <ki...@ttsiglobal.com>]

We need to implement the pagination solution using the Derby DB.
For example if a make a query select * from table a.  I want to retrieve
records from 1 to 100, 101 to 200, 201 to 300 etc.. using some thing like
LIMIT with offset etc..
I am looking for any derby database features to support this featue.
-- 
View this message in context: http://www.nabble.com/Pagination-support-in-Derby-some-thing-like-LIMIT-in-Mysql-and-Rownum-in-Oracle-tf4342993.html#a12372108
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Pagination support in Derby some thing like LIMIT in Mysql and Rownum in Oracle

[Bryan Pendleton <bp...@amberpoint.com>]

> We need to implement the pagination solution using the Derby DB.
> For example if a make a query select * from table a.  I want to retrieve
> records from 1 to 100, 101 to 200, 201 to 300 etc.. using some thing like
> LIMIT with offset etc..
> I am looking for any derby database features to support this featue.

Thanks for your interest in Derby! In the long term, DERBY-581 has
been logged to request a standards-compliant solution to this
problem: https://issues.apache.org/jira/browse/DERBY-581

If this interests you, you could vote for that issue, or, even better,
contribute to the implementation of the feature(s).

In the short term, you will need to do at least some of the work
yourself, in your application. For example, you could change your
application to create a "paging table" with two columns:
  - a "row number" integer column
  - a "row id" column which is a foreign key to the actual table
    that you are querying.

Then, when you run a query, take the results and populate the
paging table, then make your paging queries against the join of
the paging table and the primary table. When you are done with the
query, discard the rows from the paging table. It may help to
encapsulate this code by writing it as procedures and calling those
procedures from your application.

There are other solutions involving self joins with views, etc.

Hopefully others on this list will chime in with solutions that
have worked for them.

thanks,

bryan