Spring JdbcTemplate with Autocommit

I recently ran across a situation where I was using Spring's JdbcTemplate and trying to insert a record into a table, then turn around and read from it some data to use for a subsequent insert into another table. Something like this:

1 2 3 4 5 6 7 8 9 10

while (someCondition) { jdbcTemplate.update("insert into table1..."); ... } List rows = jdbcTemplate.queryForList("select col1, col2 from table1 where..."); for (Map row : rows) { jdbcTemplate.update("insert into table2...", new Object[] { row.get("col1"), row.get("col2"), ... }); }

Inexplicably (when I started seeing the problem first), there would be no rows in table2. Digging deeper, I found that was because no rows were being returned by the queryForList call, so the code was not entering the for loop at all.

The reason for this strange behavior appears to be as follows. Since JdbcTemplate is configured with a DataSource, which is in turn configured with a pool of Connections, there is no guarantee that the same Connection object will be returned to the JdbcTemplate in subsequent calls. So the first update and the queryForList call may not work against the same Connection object, so the row that was INSERTed may not be visible to the SELECT call. At least, thats true for Oracle, where the default transaction isolation level is READ COMMITTED. I cannot speak for other databases, because the only other database where I have used Spring so far is with MySQL with MyISAM which does not support transactions.

Since the first update and the second queryForList and update are really two distinct code blocks, the correct approach is to either restrict the JdbcTemplate to use a SingleConnectionDataSource, or to put the two operations in their own TransactionTemplate callbacks. Both approaches would have required me to change some code, however. A codeless option would have been to turn auto commit on for my code, but I was using a pre-built reference to the enterprise datasource, so that was not something I could do either. Finally I hit upon the idea of using AOP to intercept update() calls in JdbcTemplate and commit() them on completion. Obviously, it is not ideal if multiple JDBC calls could be grouped into a single transaction, but the concepts used here could be extended to cover that scenario as well, although we would be intercepting DAO methods instead of JdbcTemplate methods.

First, here is the interceptor. It uses a TransactionTemplate callback to wrap all specified (in our case, update()) methods. The "BEGIN TRAN", "COMMIT TRAN" and "ROLLBACK TRAN" debug calls indicate the transaction boundaries for the update() call.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

public class AutocommitInterceptor implements MethodInterceptor { private static final Logger LOGGER = Logger.getLogger(AutocommitInterceptor.class); private List<String> autoCommitableMethods; private TransactionTemplate transactionTemplate; public AutocommitInterceptor() { super(); } public void setAutoCommitableMethods(List<String> autoCommitableMethods) { this.autoCommitableMethods = autoCommitableMethods; } public void setTransactionTemplate(TransactionTemplate transactionTemplate) { this.transactionTemplate = transactionTemplate; } public Object invoke(final MethodInvocation invocation) throws Throwable { if (isAutoCommitableMethod(invocation.getMethod().getName())) { return transactionTemplate.execute(new TransactionCallback() { public Object doInTransaction(TransactionStatus transactionStatus) { LOGGER.debug("BEGIN TRAN"); try { Object retVal = invocation.proceed(); LOGGER.debug("COMMIT TRAN"); return retVal; } catch (Throwable t) { LOGGER.error("A runtime exception has occured:", t); LOGGER.debug("ROLLBACK TRAN"); throw new RuntimeException(t); } } }); } else { return invocation.proceed(); } } private boolean isAutoCommitableMethod(String methodName) { boolean isAutoCommitable = false; for (String autoCommitableMethod : autoCommitableMethods) { if (autoCommitableMethod.equals(methodName)) { isAutoCommitable = true; break; } } return isAutoCommitable; } }

We configure a bean to be a Proxy for JdbcTemplate. Since we are proxying a class (not an interface) we need to have the CGLIB JAR in our classpath. A reference to this proxy can then be passed into the beans wherever the JdbcTemplate reference was being passed in. Here is the Spring configuration.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

<!-- The original JdbcTemplate definition --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource" /> </bean> <!-- Definition for the autocommit version of JdbcTemplate --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate"> <property name="transactionManager" ref="transactionManager" /> <property name="propagationBehaviorName" value="PROPAGATION_REQUIRED" /> </bean> <bean id="autocommitInterceptor" class="com.mycompany.interceptors.AutocommitInterceptor"> <property name="autoCommitableMethods"> <list> <value>update</value> </list> </property> <property name="transactionTemplate" ref="transactionTemplate" /> </bean> <bean id="autoCommittingJdbcTemplate" class="org.springframework.aop.framework.ProxyFactoryBean"> <property name="target" ref="jdbcTemplate" /> <property name="proxyTargetClass" value="true" /> <property name="interceptorNames"> <list><value>autocommitInterceptor</value></list> </property> </bean>

So there you have it. With just a single additional interceptor class, and a few lines of configuration, all the update() calls from JdbcTemplate will be transactional, thereby fixing the problem I was seeing. Database purists may argue that this approach is too simple minded. I agree that splitting the application into two distinct transaction blocks may be a much better idea in terms of performance, and I may even end up using that approach, but for a lot of cases, the autoCommit behavior that is the JDBC default is quite acceptable.