resultset

[jarid jacob]

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]

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.

[jarid jacob]

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 rs.next()
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]

Yes, rs.next() 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.

[Stan James]

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]

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.