Would anyone happen to have a recommendation on how to perform paging ? In particular, say you read pages out of a PRODUCT table, with 100 rows per page, and you need page 9.
Now, if it's Oracle, the following should work:
However, when using DB2 (or SQL Server), it's my understanding you can only do FETCH FIRST (or SELECT TOP), which means you need to get the first 1000 rows, and then deduce the fist 900. In that case, what's the best way to go about it without hindering performance ? I've considered the following 2 methods, but any other recommendations would be welcome.
Method 1 - use more complex SQL:
Method 2: perhaps it would be more efficient to fetch only the first 1000, and then programmatically extract the last 100:
However, is there a CachedRowSet implementation that allows to copy selected rows into it ? I've tried Sun's new CachedRowSetImpl, but got the impression you can only add data to it provided you add it simultaniously to the database. which is of course not required here.
Any solutions, or other approaches, would be very very welcome. However, i'd rather avoid stored procedures unless there's absolutely no other choice.