Accessing the JDBC ResultSet concurrently in Spring

Question

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

Answer 1

I don't think that java.sql.ResultSet is thread-safe, although admittedly this is not actually mentioned in the javadoc. I wouldn't be at all surprised if calling methods on a ResultSet from 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 your BlockingQueue. 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 fetchSize property of java.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.

Answer 2

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.