Bind a specific existing Connection instance?

[nilesh]

Assuming I am using all of Spring framework's convenience classes to do data access using both JDBC and Hibernate, I want to use an existing Connection instance for all data access. I don't want this Connection to close until being told to close either explicitly or by timeout. Any ideas how something like this could be done?

My guess from studying the source code would be to do something like this:

TransactionSynchronizationManager.bindResource(dataSource, connection);

The reason I want to do this is we have an Oracle database that has stored procedures that produce temp tables and we want these temp tables to remain active between requests to reduce the cost of having to reload them for every request. The user interaction will be done through web services. So we're planning on holding the Connection instance in HttpSession for a particular user and reuse the same instance of it for every web service call that the user does, until they timeout or call a logout method. I understand there are a number of potential problems with this design, but we want to compare this with having to reload the temp tables on each request and maybe provide both options.

I also may look at doing something with the DataSource, maybe extend DriverManagerDataSource and have it check a ThreadLocal to see if a Connection instance already exists and return that instead for getConnection().

[Rod Johnson]

I would recommend implementing the Spring SmartDataSource interface, which extends javax.sql.DataSource. This enables it to control whether the Spring JDBC framework closes connections.