Any way to force JdbcTemplate to use a specific Connection?

[kdenehy]

I started to convert some jdbc code to use JdbcTemplate, but ran into a problem. Each call to jdbcTemplate.update was getting a new Connection from the DataSource. This was a problem because the first call was used to write to an Oracle temporary table (data is only visible to the session/connection that creates it) so subsequent jdbcTemplate operations would see that temp table as being empty. I need some simple way of ensuring that each jdbcTemplate call uses the same Connection object.

I'm currently using DriverManagerDataSource to provide Connections, since this is a standalone single-threaded app that performs batch updates.

Is there a simple way to handle this?

Thanks.

[Dan Washusen]

You could take advantage of the DataSourceTransactionManager class. It binds a connection to the current thread so that the same connection can be used for each transaction...

[trisberg]

You could use the SingleConnectionDataSource - it will hold the connection open until you call the destroy() method.

[kdenehy]

Excellent - thanks for the 2 great options.

[wpoitras]

Is there a way to do it similar to the transaction case, but without having to tie all the database calls into a transaction? Basically a non-transactional transaction manager?

In talking with some middleware folks, they are concerned if you do several JdbcTemplate operations in a single business method, putting them all into a transaction might be too expensive. But not tying them all to a single connection could be problematic as each one could potentially have its own connection

[wpoitras]

Perhaps AOP, ThreadLocal and a DelegatingDataSource could offer a solution:

public SameConnectionDataSource extends DelegatingDataSource
{
     private static ThreadLocal sameConnection = new ThreadLocal();
     public static Object getSameConnection() { sameConnection.get(); }
     public static void setSameConnection(Object c) { sameConnection.set(c); }


     public Connection getConnection() {
       Object con = sameConnection.get();
       if (con == null) {
         con = getTargetDataSource().getConnection();
         sameConnection.set(con);
       }
       return con;
     }

     public Connection getConnection(String user, String password) {
       Object con = sameConnection.get();
       if (con == null) {
         con = getTargetDataSource().getConnection(user, password);
         sameConnection.set(con);
       }
       return con;
     }

}

public SameConnectionInterceptor implements MethodInterceptor {
    public Object invoke(MethodInvocation invocation) throws Throwable {
        // Save old connection in case its invoked in a nested manner.
        Object oldConnection =      SameConnectionDataSource.getSameConnection();
        Object rval = invocation.proceed();
        SameConnectionDataSource.setSameConnection(oldConnection);
        return rval;
    }
}

Does this look like it would work?

[trisberg]

It looks to me as if you are trying to work around using a transaction based on the assumption that it would be more expensive. Have you run any benchmarks to verify this?

[wpoitras]

No I haven't. One of our architects I was talking to about Spring had brought it up, and felt that adding a transaction where its not needed solely for the purpose of tying one connection to several stored procedure calls seemed unnecessary or even undesirable, as did having each database call aquire its own connection (as would happen with multiple calls to JdbcTemplate with no transaction). Especially since straight JDBC has no trouble doing this.

When trying to sell new technology I try not and add more contraints to people because a toolkit requires it. It makes the case much harder to make. And localized tests are not always effective in convincing anyone because system performance issues tend to manifest during performance tests at the end of the development cycle. Particularly for our system, which is a high performance messaging system.

When moving from one technology to another, or adding on new technology, people like to start where they were before. Instead of forcing a benchmark upfront I'd rather give them what they wanted and then later convincing them to do it in a more correct manner with fewer hacks. So I wanted to have the answer to this question up front and get feedback. In the event the benchmark showed that transactions and Spring was significantly slower than straight JDBC, it would make Spring look bad in general.

The wonderful thing about this particular issue is that I can code the application using transactions or my hack and to change between them is a matter of configuration.

[trisberg]

Seems like there is a concern with aquiring a connection multiple times. This is usually never a real problem since most of time you are using a connection pool anyway. By releasing the connection and reaquiring another one from the pool later, you are actually using fewer resources since you don't hog the connection for the entire time.

On the other hand, if you have multiple database operations that rely on each other or on state held for the connection, then I would suggest wrapping the entire set of operations in a transaction. That way you don't have additional coding work to make sure you don't leave anything behind should one of your operations fail.

It sounds like this is not an option for you right now, so some solution that keeps the same connection around for all the operations would be best. If you manage the Connection yourself, you will have to actually close the connection as well since the framework code will not know when this should be done. I personally think that SingleConnectionDataSource is the simplest solution for this. I'm sure you can use DelegatingDataSource and a ThreadLocal as well. One way to verify that you get the same connection is to just log con.toString() and then compare the output.

[wpoitras]

You are 100% correct. The root problem was the multiple connections. The reason given was a theoretical situation where multiple calls depended on using the same database session. And the person I was talking to assumed a transaction would hurt performance.

I actually don't disagree with you. But I'm the technology salesman in my group. And I strongly believe Spring would bring lots of benefit to our development. And anything I can do to show that Spring can do anything straight JDBC can do (and be WAY cleaner) the easier it is to sell. So I was just making sure I could code the equivalent of using one Connection across multiple database calls without a transaction. Because you can do it in JDBC. Which is a completely different issue than whether you should.