Multiple Statements in single Connection support

I have started looking through Spring about a month ago. The JDBC support is excellent, the IoC container amazingly useful ...

I have one question that strikes me when I started usin the JDBC packages. I have DAO objects that respond to a single method call by running multiple queries and maybe writing some updates based on the combination of the query results and the parameters passed. All this should run in a single Connection for efficiency (closing a Connection sends the PoolableConnection back to the pool to be obtained for the next query/update). Yet I would have to write a custom ConnectionCallback for this. I want to use all the nice interfaces like ResultSetExtractor and its implementations.

Wouldn't it be a good idea to write some more methods on the JdbcTemplate for this. Something like
public Object subQuery(Connection conn, String sql, ResultSetExtractor);

Not hard to do and it would make it a bit more efficient for this sort of query.

Is there a reason that I'm missing for why this is not there?

Michael

The operations will run in the same connection, assuming you're executing them in a single transaction. Spring's transaction management will bind the transaction to the current thread. This would happen also in a JTA environment in an app server.

Thanks for the quick reply Rod.

My issue is that I use SAS and this is mainly for a small number of users (no need for scalability there) Decision Support read-only operations. As a result, SAS doesn't do transactions at all. Ever. The only time I think they do this is when their server reads from a database such as Oracle. Hence no re-use of Connections. Yet opening and closing a Connection object is expensive because it wraps a CORBA object that is mapped back to the server.

Michael

Our recommended strategy is indeed to execute within a transaction. Even a PROPAGATION_SUPPORTS "transaction" is sufficient, as it demarcates a scope that Spring will synchronize a Connection for.

So you could simply proxy your service with a TransactionProxyFactoryBean and specify PROPAGATION_SUPPORTS for all methods. This would give you one shared JDBC Connection for that scope, automatically detected and used by JdbcTemplate, while still not executing a database transaction. PROPAGATION_REQUIRED would additionally execute a database transaction.

Alternatively, you could create a custom JdbcTemplate instance for your operation, passing in a SingleConnectionDataSource into JdbcTemplate. SingleConnectionDataSource will always expose the same given Connection, so all operations performed on that JdbcTemplate will effectively operate on the same Connection.

Code:

---

SingleConnectionDataSource ds = new SingleConnectionDataSource(connection, false);
JdbcTemplate jt = new JdbcTemplate(ds);
jt...

---

Note that you need to create a JdbcTemplate instance per operation here, rather than use one single shared JdbcTemplate across multiple threads. The overhead of that is negligible, though, as JdbcTemplate is cheap to instantiate.

Juergen