How to manage a large dataset using Spring MySQL and RowCallbackHandler

Question

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 1

Here's a Spring solution based on the answer provided by BalusC.

class StreamingStatementCreator implements PreparedStatementCreator {
    private final String sql;

    public StreamingStatementCreator(String sql) {
        this.sql = sql;
    }

    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        final PreparedStatement statement = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        statement.setFetchSize(Integer.MIN_VALUE);
        return statement;
    }
}

Somewhere in your code:

DataSource dataSource = ...;
RowCallbackHandler rowHandler = ...;
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.query(new StreamingStatementCreator("SELECT * FROM huge_table"), rowHandler);

Answer 2

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.