File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Writing Resultset to File - best way? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Writing Resultset to File - best way?" Watch "Writing Resultset to File - best way?" New topic

Writing Resultset to File - best way?

Toby Davis
Ranch Hand

Joined: Apr 09, 2002
Posts: 65
Need a high-level opinion.
In writing a resultset to file (through JDBC connection), is it more efficient to write straight to file while looping through the resultset? Or better to put the resultset into a util collection (such as an ArrayList) and then close the JDBC connection before writing the collection to file?
Pros and cons, anyone?

SCJP2 (1.4)
Sally ZX

Joined: Nov 12, 2002
Posts: 4
I am not an expert, but this is what I learnt in the past 2 weeks, which I think might help you a little bit.
When you get the resultSet from a database, there is a default FetchSize. Everytime you do, it will first check whether there are records already fetched. If not, it will connect to the database again and fetch some records(FetchSize amount) and then let you go through them until there is no more records left. So it will be a big problem if your resultset is huge.
My suggestion is use CachedRowSet instead. It will save the entire ResultSet in the cache, and then the database connection closed. Then you can use and other methods to retrive it and do whatever to it, just like what you would do with the ResultSet. This one should be very fast. However, there is also problem with this one. If your ResultSet is really huge, you may get OutOfMemory Error. If that is the case, you may want to look at the JDBCRowSet or WebRowSet. You can check all three of them out at the tutorials. Here is the link:
Again, these are what I learnt in past 2 weeks. I am trying it, and ran into some problems. I posted my program and some running results too. The topic is "Please help me with CachedRowSet preformance!" If you interested, you can check it out. Hope someone will help me with it, and maybe it will solve your problem at the mean time.
I agree. Here's the link:
subject: Writing Resultset to File - best way?
jQuery in Action, 3rd edition