oracle Spring JDBC 支持和大型数据集

Spring JDBC support and large dataset

提问by yawn

When using one of the various JDBC template methods I am confused on how to iterate/scroll over large result sets (which won't fit into memory). Even without a direct exposure of an Iterable interface I would at least expect instances of RowCallbackHandler to get called whilethe query is executing not after it's finished (or the heap overfloats).

当使用各种 JDBC 模板方法之一时，我对如何迭代/滚动大型结果集（不适合内存）感到困惑。即使没有直接暴露 Iterable 接口，我至少希望 RowCallbackHandler 的实例在查询执行时被调用，而不是在查询完成后（或堆溢出）。

I did have a look a at this(which changed nothing for me despite being similar in spirit to this poston stack overflow) and at thispost in the spring forums. The latter seems to suggest that the callback handler should indeed get called while the cursor is fetching data. My tests however show no such behaviour.

我确实看过这个（尽管与关于堆栈溢出的这篇文章在精神上相似，但它对我没有任何改变）和春季论坛的这篇文章。后者似乎表明在游标获取数据时确实应该调用回调处理程序。然而，我的测试显示没有这种行为。

The database is an Oracle10g. I am using the 11.1.0.7.0-Production driver and Spring 2.5.6.SEC01. Any ideas anyone how to iterate over result sets, preferably while keeping the mapping logic of RowMapper etc.?

数据库是Oracle10g。我正在使用 11.1.0.7.0-Production 驱动程序和 Spring 2.5.6.SEC01。关于如何迭代结果集的任何想法，最好同时保持 RowMapper 等的映射逻辑？

回答by skaffman

The Oracle JDBC driver has proper support for the setFetchSize()method on java.sql.Statement, which allows you to control how many rows the driver will fetch in one go.

Oracle JDBC 驱动程序setFetchSize()对 on 方法有适当的支持java.sql.Statement，它允许您控制驱动程序一次将获取多少行。

However, RowMapperas used by Spring works by reading each row into memory, getting the RowMapperto translate it into an object, and storing each row's object in one big list. If your result set is huge, then this list will get big, regardless of how JDBC fetches the row data.

但是，RowMapper正如 Spring 所使用的那样，它通过将每一行读入内存，RowMapper将其转换为对象，并将每一行的对象存储在一个大列表中来工作。如果您的结果集很大，那么无论 JDBC 如何获取行数据，该列表都会变大。

If you need to handle large result sets, then RowMapper isn't scaleable. You might consider using RowCallbackHandlerinstead, along with the corresponding methods on JdbcTemplate. RowCallbackHandlerdoesn't dictate how the results are stored, leaving it up to you to store them.

如果您需要处理大型结果集，则 RowMapper 不可扩展。您可以考虑使用RowCallbackHandler，以及 JdbcTemplate 上的相应方法。RowCallbackHandler不规定结果的存储方式，由您来存储它们。

回答by alexkasko

You may use springjdbc-iterablelibrary:

您可以使用springjdbc-iterable库：

CloseableIterator<MyObj> iter = jt.queryForIter("select ...", params, mapper);

Iterator will be auto-closed on exhaustion or may be closed manually. It will work only within transaction bounds.

迭代器将在耗尽时自动关闭或手动关闭。它只会在事务范围内工作。

Disclaimer: I wrote this library

免责声明：我写了这个库

回答by oxbow_lakes

It's a property of the driver/connection whether to stream data back to you or whether to send it back in one chunk. For example, in SQL Server, you use the SelectMethodproperty on the connection URL:

它是驱动程序/连接的一个属性，是将数据流回给您还是以一个块的形式将其发送回。例如，在 SQL Server 中，您使用SelectMethod连接 URL 上的属性：

jdbc:microsoft:sqlserver://gsasql03:1433;DatabaseName=my_db;SelectMethod=direct

jdbc:microsoft:sqlserver://gsasql03:1433;DatabaseName=my_db;SelectMethod=direct

The value of directmeans that the results should come in one go. The other choice is cursor, which allows you to specify that you want the connection to stream results back to you. I'm not sure what the analog for an Oracledata source is, I'm afraid

的值direct意味着结果应该是一次性的。另一个选项是cursor，它允许您指定希望连接将结果流回给您。我不确定Oracle数据源的模拟是什么，恐怕

the RowCallbackHandlercertainly works for me.

在RowCallbackHandler肯定对我的作品。

回答by Rob Breidecker

1. Create a custom stored procedure that extends StoredProcedure
2. Create a RowCallBackHandlerthat can handle each row, one at a time.
3. Declare your parameters. If you have a Result Set, declare that one first. Use the SqlReturnResultSetclass and create it using your RowCallBackHandler
4. Declare any other parameters
5. Compile
6. I did steps 2 through 5 in the constructor of my customer stored procedure
7. Create a Map containing your input parameters
8. Execute your stored procedures with the input parameters

1. 创建一个扩展的自定义存储过程 StoredProcedure
2. 创建一个RowCallBackHandler可以处理每一行的，一次一个。
3. 声明你的参数。如果您有结果集，请先声明该结果集。使用SqlReturnResultSet该类并使用您的RowCallBackHandler
4. 声明任何其他参数
5. 编译
6. 我在客户存储过程的构造函数中执行了第 2 步到第 5 步
7. 创建包含输入参数的 Map
8. 使用输入参数执行存储过程

I would provide code, but the following article contains all of this information.

我会提供代码，但以下文章包含所有这些信息。

Calling Stored Procedures with Spring JDBC Templates

使用 Spring JDBC 模板调用存储过程

回答by jli_123

here's a good library for pulling java sql resultsets all into memory.

这是一个很好的库，用于将 java sql 结果集全部拉入内存。

http://casperdatasets.googlecode.com

http://casperdatasets.googlecode.com

you can scroll / iterate through the dataset, you can issue queries against it, and build indexes for optimization. it also implements the java.sql.resultset interface so you can continue to operate on results from this dataset with minimals chnages to your jdbc code.

您可以滚动/迭代数据集，可以针对它发出查询，并构建索引以进行优化。它还实现了 java.sql.resultset 接口，因此您可以继续对该数据集的结果进行操作，而对 jdbc 代码进行最少的更改。