rmarimon rmarimon - 4 months ago 15
SQL Question

How to manage a large dataset using Spring MySQL and RowCallbackHandler

I'm trying to go over each row of a table in MySQL using Spring and a JdbcTemplate. If I'm not mistaken this should be as simple as:

JdbcTemplate template = new JdbcTemplate(datasource);
template.setFetchSize(1);
// template.setFetchSize(Integer.MIN_VALUE) does not work either
template.query("SELECT * FROM cdr", new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
System.out.println(rs.getString("src"));
}
});


I get an OutOfMemoryError because it is trying to read the whole thing. Any ideas?

Answer

The Statement#setFetchSize() javadoc already states:

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database

The driver is actually free to apply or ignore the hint. Some drivers ignore it, some drivers applies it directly, some drivers needs more parameters. The MySQL JDBC driver falls in the last category. If you check the MySQL JDBC driver documentation, you'll see the following information (scroll about 2/3 down until header ResultSet):

To enable this functionality, you need to create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

Please read the entire section of the document, it describes the caveats of this approach as well.

To get it to work in Spring, you'll however need to extend/override the JdbcTemplate with a custom implementation. As I don't do Spring I can't go in detail about this, but now you at least know where to look.

Good luck.

Comments