Java 有没有办法在 JdbcTemplate 上智能设置获取大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18383448/
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
Is there a way to intelligently set fetch size on JdbcTemplate?
提问by stilltrackin
I have query that return reports by a date range. If the date range is large, 50k rows may be returned. If the date range is very small, 10 records could be returned. I've found that setting the fetch size to 1000, when 50k rows are returned greatly speeds up the execution time. But setting it to 1000 when 10 rows are returned slows it down and uses up excessive memory. This is just one example, I have many queries that return few or many rows based on various conditions (type of job running, etc).
我有按日期范围返回报告的查询。如果日期范围很大，可能会返回 50k 行。如果日期范围很小，则可以返回 10 条记录。我发现将 fetch size 设置为 1000，当返回 50k 行时会大大加快执行时间。但是当返回 10 行时将其设置为 1000 会减慢速度并使用过多的内存。这只是一个示例，我有许多查询根据各种条件（正在运行的作业类型等）返回很少或很多行。
Ideally, it would be nice if this could be auto-set after the query is executed (but before the rows are returned).
理想情况下，如果可以在执行查询之后（但在返回行之前）自动设置，那就太好了。
Is there a better way to do this?
有一个更好的方法吗？
I'm using org.springframework.jdbc.core.support.JdbcDaoSupport.SimpleJdbcDaoSupport getJdbcTemplate().setFetchSize(1000);
我正在使用 org.springframework.jdbc.core.support.JdbcDaoSupport.SimpleJdbcDaoSupport getJdbcTemplate().setFetchSize(1000);
回答by Vincent Malgrat
This is the default fetch size. There is no best value obviously since as you've described it's a trade-off between memory used and database round-trip (a default value of 100 seems to work well enough for me).
这是默认的提取大小。显然没有最佳值，因为正如您所描述的，这是使用的内存和数据库往返之间的权衡（默认值 100 对我来说似乎足够好）。
Anyway, you can set the fetch size individually for each result set with ResultSet.setFetchSize().
无论如何，您可以为每个结果集单独设置提取大小ResultSet.setFetchSize()。
回答by alfasin
First, the docs says that SimpleJdbcDaoSupport is deprecatedsince Spring 3.1 - better use either JdbcDaoSupportor NamedParameterJdbcDaoSupport
首先，文档说 SimpleJdbcDaoSupport自 Spring 3.1 以来已被弃用- 最好使用JdbcDaoSupport或NamedParameterJdbcDaoSupport
Second, in org.springframework.jdbc.core.support Class JdbcDaoSupport there's a method: applyStatementSettings(Statement stmt)which you can use in order to applying statement settings such as fetch size, max rows, and query timeout.
其次，在 org.springframework.jdbc.core.support 类 JdbcDaoSupport 中有一个方法：applyStatementSettings(Statement stmt)，您可以使用它来应用语句设置，例如获取大小、最大行数和查询超时。
Another option is to LIMIT the number or rows returned in the result set in the query (in MySQL use: LIMIT, for PL\SQL use " and rownum < 50" - for example)
另一种选择是限制查询结果集中返回的数量或行数（在 MySQL 中使用：LIMIT，对于 PL\SQL 使用“和 rownum < 50” - 例如）
回答by stilltrackin
I found a solution. I found that fetch size can be modified at any point (even while retrieving rows). My first implementation defaulted the fetch size to 1. When I saw a second row, I changed it to 10. When I saw an 11th row, I changed it to 100, etc. I ran several performance tests using local and remote databases and landed on the following. (Spring JDBC uses row mappers. This is how I implemented org.springframework.jdbc.RowMapper).
我找到了解决方案。我发现可以随时修改提取大小（即使在检索行时）。我的第一个实现默认fetch size为1，看到第二行就改成10，看到第11行就改成100，等等。我用本地和远程数据库跑了几次性能测试，都登陆了在以下。（Spring JDBC 使用行映射器。这就是我实现 org.springframework.jdbc.RowMapper 的方式）。
public T mapRow(ResultSet rs, int index) {
T dto = null;
if (index == 0) {
setFetchSize(rs, 50);
} else if (index == 50) {
setFetchSize(rs, 500);
} else if (index == 1000) {
setFetchSize(rs, 1000);
}
try {
dto = mapRowToDto(rs, index);
} catch (SQLException e) {
throw new RuntimeException(e.getMessage(), e);
}
return dto;
}
