java 如何使用 Spring MySQL 和 RowCallbackHandler 管理大型数据集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2095490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to manage a large dataset using Spring MySQL and RowCallbackHandler
提问by Ricardo Marimon
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:
我正在尝试使用 Spring 和 JdbcTemplate 遍历 MySQL 中表的每一行。如果我没记错的话，这应该很简单：
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?
我收到 OutOfMemoryError 因为它试图读取整个内容。有任何想法吗？
回答by Edward Dale
Here's a Spring solution based on the answerprovided by BalusC.
这是基于BalusC 提供的答案的 Spring 解决方案。
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);
回答by BalusC
The Statement#setFetchSize()javadocalready states:
在Statement#setFetchSize()javadoc中已经指出：
Gives the JDBC driver a hintas to the number of rows that should be fetched from the database
为 JDBC 驱动程序提供有关应从数据库中提取的行数的提示
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):
驱动程序实际上可以自由应用或忽略提示。有些驱动程序忽略它，有些驱动程序直接应用它，有些驱动程序需要更多参数。MySQL JDBC 驱动程序属于最后一类。如果您查看MySQL JDBC 驱动程序文档，您将看到以下信息（向下滚动约 2/3 直到标题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);
要启用此功能，您需要按以下方式创建 Statement 实例：
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE);
Please read the entiresection 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.
但是，要使其在 Spring 中工作，您需要使用自定义实现扩展/覆盖 JdbcTemplate。因为我不做 Spring，所以我不能详细介绍这个，但现在你至少知道去哪里找。
Good luck.
祝你好运。
回答by Mihir Mathuria
If you suspect you are getting the OutOfMemory error due to the entire table read, why dont you try splitting your query. Use filters, LIMIT clause etc.
如果您怀疑由于读取整个表而出现 OutOfMemory 错误，为什么不尝试拆分查询。使用过滤器、LIMIT 子句等。
