It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes JDBC ResultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "JDBC ResultSet" Watch "JDBC ResultSet" New topic

JDBC ResultSet

JeanLouis Marechaux
Ranch Hand

Joined: Nov 12, 2001
Posts: 906
I've got a question for JDBC specialists.
When you perform an executeQuery on a Statement like this :
Results rs = s.executeQuery("select ... where ...");
Is the ResultSet populated with ALL the rows from the query in one shot, or is it populated "on demand" when you perform a;
My understanding is that only JDBC 2.0 Scrollable Resultset offers the "on demand" feature, while with "classical" ResultSet, the whole rs is populated in one shot.
Is that correct ?
Any document on that subject is welcome.

Just to expplain my need, I want to set up a search feature in a 3-tier environment and to minimize :
- the number of access to the DB
- the amount of rows retrieve from the db when the client does not explicitally need to display them.
[ January 22, 2003: Message edited by: Bill Bailey ]

/ JeanLouis<br /><i>"software development has been, is, and will remain fundamentally hard" (Grady Booch)</i><br /> <br />Take a look at <a href="" target="_blank" rel="nofollow">Agile OpenUP</a> in the Eclipse community
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

'classic' ResultSets bring into memory the amount of rows specified by the fetch size of the Statement. For example if fetch size = 10 and you have 100 results, then a classic resultset will fetch into memory the first 10, then when you to the 11th row, it fetches the next 10 rows into memory, replacing the last 10. So at one time, you will have a maximum of 10 records held in memory.
Things are different for scrollable resultsets, depending on the vendor implementation. Generally, they work the same way as classic, except when it needs the the 11th row, it will keep the first 10 in memory. So the resultset grows in memory as you read more rows.
If you are looking for resultset paging techniques, have a look at this article which provides different solutions to this problem.
JeanLouis Marechaux
Ranch Hand

Joined: Nov 12, 2001
Posts: 906
Thanks Jamie it does help.
With the "fetch size" stuff, does it mean there is a DB access each time the fetch limit is reached.
So with your example, if the fetch size is 10, I'll have 10 db accesses to fetch all the rs, while there is only 1 db access with a fetch size = 100 ??
I've already read Claudio's article on TSS, but something is puzzling me.
Isn't it dangerous to use a scrollable result set for Large resultsets ?
I mean, if the client browses thru the whole RS , it is kept in memory.
So in a 3-tier application, can my app server be confronted by out of memory problems due to the scrollable resultset mechanism ?
I agree. Here's the link:
subject: JDBC ResultSet
It's not a secret anymore!