[ACCEPTED]-Spring JDBC support and large dataset-jdbc
The Oracle JDBC driver has proper support 14 for the setFetchSize() method on java.sql.Statement, which allows you to 13 control how many rows the driver will fetch 12 in one go.
However, RowMapper as used by Spring works 11 by reading each row into memory, getting 10 the RowMapper to translate it into an object, and 9 storing each row's object in one big list. If 8 your result set is huge, then this list 7 will get big, regardless of how JDBC fetches 6 the row data.
If you need to handle large 5 result sets, then RowMapper isn't scaleable. You 4 might consider using RowCallbackHandler instead, along with 3 the corresponding methods on JdbcTemplate. RowCallbackHandler doesn't 2 dictate how the results are stored, leaving 1 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 3 on exhaustion or may be closed manually. It 2 will work only within transaction bounds.
Disclaimer: I 1 wrote this library
It's a property of the driver/connection 10 whether to stream data back to you or whether 9 to send it back in one chunk. For example, in 8 SQL Server, you use the SelectMethod property on the 7 connection URL:
jdbc:microsoft:sqlserver://gsasql03:1433;DatabaseName=my_db;SelectMethod=direct
The value of direct means that 6 the results should come in one go. The other 5 choice is cursor, which allows you to specify 4 that you want the connection to stream results 3 back to you. I'm not sure what the analog 2 for an Oracle data source is, I'm afraid
the RowCallbackHandler certainly 1 works for me.
- Create a custom stored procedure that extends
StoredProcedure - Create a
RowCallBackHandlerthat can handle each row, one at a time. - Declare your parameters. If you have a Result Set, declare that one first. Use the
SqlReturnResultSetclass and create it using yourRowCallBackHandler - Declare any other parameters
- Compile
- I did steps 2 through 5 in the constructor of my customer stored procedure
- Create a Map containing your input parameters
- Execute your stored procedures with the input parameters
I would provide code, but the following 1 article contains all of this information.
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.