Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
321 views
in Technique[技术] by (71.8m points)

java - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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);

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...