Spring JDBC support and large dataset
The Oracle JDBC driver has proper support for the setFetchSize()
method on java.sql.Statement
, which allows you to control how many rows the driver will fetch in one go.
However, RowMapper
as used by Spring works by reading each row into memory, getting the RowMapper
to translate it into an object, and storing each row's object in one big list. If your result set is huge, then this list will get big, regardless of how JDBC fetches the row data.
If you need to handle large result sets, then RowMapper isn't scaleable. You might consider using RowCallbackHandler
instead, along with the corresponding methods on JdbcTemplate. RowCallbackHandler
doesn't dictate how the results are stored, leaving it up to you to store them.
You may use springjdbc-iterable library:
CloseableIterator<MyObj> iter = jt.queryForIter("select ...", params, mapper);
Iterator will be auto-closed on exhaustion or may be closed manually. It will work only within transaction bounds.
Disclaimer: I wrote this library
It's a property of the driver/connection whether to stream data back to you or whether to send it back in one chunk. For example, in SQL Server, you use the SelectMethod
property on the connection URL:
jdbc:microsoft:sqlserver://gsasql03:1433;DatabaseName=my_db;SelectMethod=direct
The value of direct
means that the results should come in one go. The other choice is cursor
, which allows you to specify that you want the connection to stream results back to you. I'm not sure what the analog for an Oracle data source is, I'm afraid
the RowCallbackHandler
certainly works for me.