Geoff hendrey wrote:
Get the result set. Use a loop to increment integer n by PAGE_SIZE, and inside the loop use ResultSet.absolute(n) combined with stmt.setFetchSize(1) to retrieve a "marker" row that signifies the begining of each "page" of the result set. I use the primary keys of these "markers" as page boundaries so that my web application can provide links to a set of pages evenly distributes throughout the result set.
I use something similar, except instead of traversing the entire result set and storing keys for each 'page' I retain the keys of the first and last rows in the current 'page'. For subsequent fetches I use '> lastKey ... order by ... asc' to scroll forwards and '< firstKey ... order by ... desc' to scroll backwards.
I too would be interested to know how that approach compares to the new offset/fetch clauses.
-- Alan Burlison --
