ThreadLocal + java.sql.Connection + servlet filter = 2009?

Question

I am writing some servlets with plain old mostly-JDBC patterns. I realized that I have several objects that would like to share a single transaction, and I'd like to enforce that one HTTP transaction = one database transaction.

I think I can do this via passing a Connection around in a ThreadLocal variable, and then having a servlet filter handling the creation/commit/rollback of said Connection.

Is there an existing framework that does this that I'm not privy to, or is this a reasonable late-00's way to do things?

Answer 1

It is generally better to pass object with "Parameterisation from Above", the sleazing through with ThreadLocal. In the case of ServletFilter, an attribute of the ServletRequest would be an obvious place. The interface to non-servlet dependent code can extract the Connection to meaningful context.

Answer 2

Having a filter manage the transaction is a good approach to rolling your own transaction management.

The JEE specification provides for transaction management, and alternative frameworks like Spring provide similar support (though that's not an endorsement; Spring doesn't necessarily do this well).

However, use of a ThreadLocal can create problems. For example, there are no guarantees that a single thread is used throughout a request, anything can access the Connection through the global variable, and testing can become more difficult if you are depending on some global state to be set up. I'd consider using a dependency injection container to explicitly pass a Connection to objects that need one.

Answer 3

Spring transaction management does exactly what you describe, it might be a little over whelming at first glance but all you will be needing (for the simplest case) is:

org.springframework.jdbc.datasource.DataSourceTransactionManager org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy org.springframework.transaction.support.TransactionTemplate

Wire up your existing DataSource and wrap it in the TransctionAwareDataSourceProxy then create a DataSourceTransactionManager with the wrapped data source, keep these in your ServletContext. Then for each transaction create a TransactionTemplate passing in the transaction manager and call the execute(TransactionCallback) method to run your code. eg:

new TransactionTemplate(transactionManager).execute(new TransactionCallback(){
    public void doInTransaction(TransactionStatus ts){
        // run your code here...use the dataSource to get a connection and run stuff
        Connection c = dataSourceProxy.getConnection();
        // to rollback ... throw a RuntimeException out of this method or call 
        st.setRollbackOnly();
    }
});

The connection will be bound to a thread local so as long as you always get the connection form the same datasource i.e. the wrapped one, you'll get the same connection in the same transaction.

Note this is the simplest possible spring transaction setup ... not nessarly the best or recommended one, for that have a look at the spring reference doc's or read spring in action.

... so I guess as a direct answer, yes it is a reasonable thing to be doing, it's what the spring framework has been doing for a long time.

Answer 4

Most appServer todays support JTA (Java Transaction Api): A transaction that spans over multiple open/close jdbc connections. It does the "threadLocal" stuff for you and it's J2EE compliant. You use it like this in your filter:

    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
 throws IOException, ServletException {
 UserTransaction transaction = null;
 try {
  transaction = (UserTransaction)new InitialContext().lookup("java:comp/UserTransaction");
  transaction.begin();
  chain.doFilter(request, response);
  transaction.commit();
 } catch (final Exception errorInServlet) {
  try {
   transaction.rollback();
  } catch (final Exception rollbackFailed) {
   log("No ! Transaction failed !",rollbackFailed);
  }
  throw new ServletException(errorInServlet);
 }
}

On the app-server, declare a Datasource with a jndi name, and use it in your code to retrieve a connection (do NOT make cx.commit(), cx.rollback() or cx.setAutocommit() stuff, it will interfere with JTA). You can open and close your connection several times in the same HTTP transaction, JTA will take care of it:

public void doingDatabaseStuff() throws Exception {
 DataSource datasource = (DataSource)new InitialContext().lookup("/path/to/datasource");
 Connection connection = datasource.getConnection();
 try {
  // doing stuff
 } finally {
  connection.close();
 }
}

Answer 5

If you cannot rely on a "real" app server and you want to avoid the not-so-lightweightness of Spring, using a filter to provide a connection, keep it on the thread and close it at the end of the request is indeed a practical and reasonable solution.

You would need some (essentially static) accessor class that allows to get() a connection and a setRollbackOnly().

Upon end of the request, from the filter's perspective, make sure to catch exceptions (upon which you should log and set to rollback only) and commit/rollback, close the transaction accordingly.

In most applications and web containers (and JTA usually makes similar assumptions) a request will be processed by exactly one thread and associating the one database connection with the thread for re-use between layers during the request is just the right thing to do.