在 Spring 中并发访问 JDBC 结果集
我正在 Spring JDBC DAO 中处理大量数据。 DAO 直接返回一个迭代器，该迭代器使用 take() 对有界 BlockingQueue 进行操作，而检索操作发生在单独的线程中（使用 ExecutorService ）。
在该线程中，我看到以下行为：检索工作正常，但对 ResultSet 的某些调用导致调用挂起。这些调用是
- isClosed() 和
- isLast()，
但不是
- isAfterLast() 或
- isBeforeFirst() 或
- >isFirst()
显然，我需要知道最后一个元素是什么（以便将一个特殊元素插入到阻塞队列中，该元素在迭代器 hasNext() 方法中产生 false）。我可以通过在将对象放入 BlockingQueue 之前找出 ResultSet 中的行数来解决这个问题，但这感觉有点笨拙。是否有线程安全的方式来处理结果集？
切换到多线程数据源（我测试了 C3PO ComboPooledDataSource）似乎没有帮助。
注意：这个问题是我首先（错误地）在此处发现的
I am processing a large amount of data in a Spring JDBC DAO. The DAO directly returns an Iterator over the objects which operates on a bounded BlockingQueue using take() while the retrieval operation is happening in a separate thread (using an ExecutorService).
Inside this thread I see the following behaviour: the retrieval works but certain calls to the ResultSet are causing the call to hang. These calls are
- isClosed() and
- isLast()
but not
- isAfterLast() or
- isBeforeFirst() or
- isFirst()
Obviously I need to know what the last element is (in order to insert a special element into the blocking queue that yields false in the iterators hasNext() method). I could work around it by finding out the number of rows in the ResultSet before putting objects into the BlockingQueue but this feels a bit clumsy. Is there a thread-safe way to work with ResultSets?
Switching to a multi-threaded datasource (I tested C3POs ComboPooledDataSource) does not seem to help.
Note: this issue was first (incorrectly) identified by me here
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我不认为 java.sql.ResultSet 是线程安全的，尽管不可否认，javadoc 中实际上并没有提到这一点。如果从不同线程调用 ResultSet 上的方法导致这些方法调用挂起，我一点也不会感到惊讶。
作为替代方案，我建议让您的检索线程作为
ResultSet的唯一用户，拉出行，然后将数据本身转储到您的BlockingQueue上。然后检测结果集的结尾并将 EOF 标记放入队列就变得很简单了。JDBC 中迭代非常大的结果集的首选机制是使用 java.sql.Statement 的 fetchSize 属性，尽管这高度依赖于数据库和 JDBC司机。我知道 Oracle 驱动程序遵循此设置，但不确定其他设置。如果驱动程序决定在提供第一行之前需要将整个结果集提取到内存中，那么无论您做什么，在提取下一行时都将无法处理第一行。
I don't think that
java.sql.ResultSetis thread-safe, although admittedly this is not actually mentioned in the javadoc. I wouldn't be at all surprised if calling methods on aResultSetfrom different threads causes those method calls to hang.As an alternative, I suggest having your retrieval thread as the only user of the
ResultSet, pulling the rows off and then dumping the data itself on to yourBlockingQueue. It then becomes trivial to detect the end of the result set and put your EOF marker on the queue.The generally preferred mechanism in JDBC for iterating over very large result sets is to use the
fetchSizeproperty ofjava.sql.Statement, although this is highly dependent on the database and JDBC driver. I know that the Oracle driver honours this setting, but not sure about others. If the driver decides that it needs to fetch the whole result set into memory before giving you the first row, then no matter what you do you won't be able to process the first rows while fetching the next ones.正确的解决方案是设置适当的 ResultSet 类型。默认“TYPE_FORWARD_ONLY”为isLast() 不支持。 ResultSet 的类型可以通过使用 PreparedStatementCreator 而不是 SQL 字符串来设置，例如 query() 调用 JdbcTemplate< /em>.此类实例是通过PreparedStatementCreatorFactory 获取的。在这样的工厂中，可以设置 ResultSet 的类型（例如“TYPE_SCROLL_INSENSITIVE”）。
The correct solution is to set an appropriate ResultSet type. The default "TYPE_FORWARD_ONLY" is not supported by isLast(). The type of ResultSet can be set by using a PreparedStatementCreator instead of an SQL string for e.g. query() calls to a JdbcTemplate. Such instances are acquired through a PreparedStatementCreatorFactory. On such a factory the type of the ResultSet (e.g. "TYPE_SCROLL_INSENSITIVE") can be set.