db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Re: Pagination support in Derby some thing like LIMIT in Mysql and Rownum in Oracle
Date Tue, 28 Aug 2007 17:23:45 GMT
> 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



Mime
View raw message