Announcement Announcement Module
No announcement yet.
Multiple Statements in single Connection support Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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?


  • #2
    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.


    • #3
      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.



      • #4
        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.

        SingleConnectionDataSource ds = new SingleConnectionDataSource(connection, false);
        JdbcTemplate jt = new JdbcTemplate(ds);
        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.