Cursor handling in servlets

[Gaurav Chikara]

I have obtained the output of a query thru the code
while(rs.next())
{
//retrieving result here
}
but i want to show only set of records on my page and next set
of records on click of next hyperlink but resultset interface do not provide dynamic cursor handling in java
can any one suggest how to go for itGaurav

[Thomas Paul]

You have to rerun the request and skip the records you have already displayed.
while (rs.next()) {
if (counter++ >= startingRowNumber)
processRecord(rs);
}

[William Brogden]

How about attaching rs to a session and keeping it around that way? Of course that could chew up memory.

[Gaurav Chikara]

Thanks Thomas
but my doubt is that each time i run this servlet
counter will be initialised to its default value
and we cannot skip the records we have already displayed
and thus we cannot get a value in starting RowNumber

Originally posted by Thomas Paul:
You have to rerun the request and skip the records you have already displayed.
while (rs.next()) {
if (counter++ >= startingRowNumber)
processRecord(rs);
}

[Frank Carver]

William Brogden wrote: How about attaching rs to a session and keeping it around that way? Of course that could chew up memory.
While this might work on your system, it is very dangerous and not portable acrosss servlet containers. A basic ResultSet is not Serializable, and the Servlet API specifies that only serializable objects may be placed in a session.
Many servers don't actually check that an object placed in a session is serializable, but if you try and do this on one which does it might throw an exception or just not store your data.
It's also risky because it may be a long time before the next request comes in (say I view the first page of the list, then go to lunch or go home, then come back and try to access the next page). You are at the mercy of any timeouts in the driver, the connection or the database - and timeouts are very hard to test.
There are a few possible solutions:
1. Read the whole list of results into a data structure (say a Vector of application-specific objects) which you store in the session, and render the pages from this data structure. This has the advantage of speed - the database is only contacted once, but may take up a lot of memory, especially if there are a lot of concurrent sessions, each with a copy of a large data set.
2. Keep a single local data structure in the servlet, shared between all sessions, and update it from time to time from the database. This is also quick, and scales well to lots of sessions, but can only be done if all sessions are requesting broadly similar queries.
3. Store a "how far have we got" value in the session, and don't bother showing results up to that point in the response. (This is Thomas Paul's suggestion). This is flexible and robust, but can be slower than the solutions above if the database access takes a significant amount of time. It will also get slower as you page through the results.
4. Store some sort of incrementing value associated with each row in the database, and modify the query on each request to only select rows where this value is greater than the last shown value. This scales better than the previous option, but can only be done if (a) you have control over the data at that level, and (b) you retrieve/show the results ordered by the incrementing value.
5. Use one of the available "smart" ResultSets. There are several classes available which attempt to solve this problem by cacheing results or using non-standard database extensions to allow access back to an old result set. If one of these suits you, it may be the easiest solution.
There are probably other options, too.