JDBC PreparedStatement pooling for Scala

Question

I am using a JDBC driver for Microsoft SQL Server 2012 (sqljdbc_4.0) and although the Connection instances are thread-safe, the Statement instances do not appear to be (particularly PreparedStatement).

I am using Scala .par.foreach method calls on some collections that then execute PreparedStatement instances. I am currently creating new PreparedStatement instances inside the closure of the .foreach to process each item, since they are not thread-safe.

Each of the PreparedStatement queries is the same, with different values substituted for the question-mark parameters (?). I am wondering if there is any way to create a pool of PreparedStatement instances equal in size to the number of threads used by the parallel collection, and then just reuse a free one for each iteration of the .foreach closure, returning them to the pool after reuse.

Answer 1

Although the Connection is thread safe the idiomatic way to do this would be to use a pool of connections and have each thread use a separate connection.

This will cause less contention between threads over the connection and the underlying transport. It will also better allow the database on the other end to use different worker threads for each connection as well. You gain throughput at cost of client side memory, with the caveat that you can still experience contention at the database level over the actual data if you're doing updates at the same time.

You could try BoneCP, c3p0 or DBCP for pooling, all three are widely used. They allow you to configure statement pooling as well to save on the cost of re-preparing them.