Announcement Announcement Module
Collapse
No announcement yet.
Multiple Statements in single Connection support Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • 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?

    Michael

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

    Comment


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

      Michael

      Comment


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

        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

        Comment

        Working...
        X