This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "resultset" Watch "resultset" New topic


jarid jacob

Joined: Jul 08, 2005
Posts: 2
My problem is
I want to query the database which contain around 20000 records.
and get all the records and write it to a flat file.
Since there is a large amount of data i need someway to get
the first 200 records in one resultset.
i think i can do this by setting the fetch size of the result set.
But the next time it should get records from 201 to 400 in the
resultset and like that it should continue for 20000 records.
I would somebody can tell me how to get the resultset to contain
records from 200 to 400 second time,400-600 third time and continue like
that till the end of records.
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Hi, welcome to the ranch!

Have you tried just retrieving all the rows? I wonder if a TYPE_FORWARD_ONLY result set isn't free to discard the rows you have read and buffer up a reasonable number of rows you haven't read yet. See if This Tutorial doesn't talk about it.

A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
jarid jacob

Joined: Jul 08, 2005
Posts: 2
Do you mean to say that i dont have to do anything particular
to the resultset object.
once i finish reading the first 200 records and i do a
it will retrieve the next record without any additional statement.
if the resultset contains all 10000 records will it not affect the
jvm heap size say only 256 MB.
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
Yes, will iterate through the entire ResultSet. Depending on the size of the table it may consume too much memory, but you would have to test that. - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
It would be interesting to confirm that the result set holds all the records at the end. If you fetch with a cursor that does not allow scrolling backwards it seems the rs would be free to discard rows after you've seen them.

If you have JDK 5 look into JConsole. Sun calls it experimental and primitive, but you can watch memory usage over time. Maybe scroll a thousand rows, pause for user input, check the console, and so on to end of data.
[ July 11, 2005: Message edited by: Stan James ]
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
My understanding is that the JDBC driver might base a ResultSet on a database cursor and only fetch a small number of rows. These rows are cached on the client side of the connection and, when exhausted, the next block of rows is retrieved by repositioning the cursor.

So, it may appear as if the ResultSet holds all the data, but this would be unlikely if a very large amount of data is involved.

SCJP 1.4, SCWCD 1.3, SCBCD 1.3
I agree. Here's the link:
subject: resultset