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
