Scrollable result sets and physical reads...

[SAFROLE YUTANI]

If I create a scrollable result set like this....
Statement st = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = statement.executeQuery("Select * from products");
rs.setFectchSize( 50);
Lets say that there are 10,000 rows that match the query criteria, I only want to fetch, say, 50 rows at a time. Clearly, I don't want the first fetch to get all the 10,000 rows because I am storing the result set in the session.

[Jeanne Boyarsky]

Safrole,
Storing a result set in the session is dangerous because the resultset is tied to your statement, which is tied to your connection. The connection could close at any time. You would be better off storing a copy of the data you need.

[David O'Meara]

Search this forum for 'ResultSet paging', there have been a few discussions on ways to do this efficiently. The short answer is that there are multiple ways to do it, and no single solution matches all requirements.
There was one really good one where someone brought all the discussions together. I'll have a look for it.
Dave

[David O'Meara]

Found it, the thread I liked is here
Dave

[SAFROLE YUTANI]

Ok, thanks for the advice!