This forum is now a read-only archive. All commenting, posting, registration services have been turned off. Those needing community support and/or wanting to ask questions should refer to the Tag/Forum map, and to http://spring.io/questions for a curated list of stackoverflow tags that Pivotal engineers, and the community, monitor.

Multiple Statements in single Connection support

Jul 30th, 2005, 02:51 PM

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.

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.

Comment

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.

Comment

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.

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.