Originally posted by Karthik Prabu:
I assume this could be because of not closing the Statement objects and its not being GC'ed.
Possibly not. If your driver conforms to the
JDBC specs, then when you close the connection, the Statement and the ResultSet should be closed for you by the driver. (It never hurts to explicitly close them though).
More likely you're running out of memory while working on the ResultSet, although it depends somewhat on what database/driver you are using. That might be an easy fix or a hard fix, depending on whether you need the entire ResultSet in memory at once or not and how you process it. Some drivers (such as Postgres) will by default bring the entire ResultSet into memory when the statement is executed or when the first run is fetched. Other drivers don't work that way be default, and will only retrieve a fixed number of rows at a time into memory, buffering the retrieval. However, you can suggest to the driver that the default buffering be overriden, by using Statement.setFetchSize(). However, as the JavaDocs note, this is a hint to the driver, and the JDBC spec does not require the driver to honor the hint. Most drivers do honor the hint when they can, but there are some other options that can make honoring the hint impossible for some databases.
Of course, the other aspect of making you code work for large numbers of rows is making sure what ever happens in:
doesn't have to all be in memory at once...