Sunday, December 17, 2006

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.

12 comments (moderated to prevent spam):

Lukáš Vlček said...

Thanks for this post. It served me good while Spring's API site was down and I didn't want to search directly in the source code.

Sujit Pal said...

Thanks Lukas, although since the time I implemented this and wrote the blog, I'm thinking that while this implementation would work for this particular use case, using the standard pattern for using database transactions in Spring (ie wrapping it in a TransactionProxyFactoryBean) would have been a better idea.

Srinivas said...

I could not get it working as the update call on the jdbctemplate is not entering the invoke() method of the interceptor.

Could you let me know what could be the problem with it ?

Sujit Pal said...

Hi Srinivas, it works for me, so not sure whats going wrong at your end. I would look at the configuration to make sure.

Anonymous said...

Sujit, how is the rollback being handled by the interceptor, in case of an exception. Also, you need to specify the isolation level, dont you?

Rajesh

Sujit Pal said...

Hi Rajesh, to answer your first question, rollback is happening in the catch (Throwable t) block in the doInTransaction() method. For the second question, I think if you don't set the isolation level, it defaults to the default isolation level for the database server.

Bakann DY said...

You can write "getJdbcTemplate().getDataSource().getConnection().commit();" after the update() and it works fine!

Sujit Pal said...

Hi Eurrsk, not sure if I tried your option, it was a quite a long time ago, but I think your approach would work and also be much simpler :-). Thanks for the tip.

Unknown said...

when i tried to do what Eurrsk is suggesting it gave me error that can not commit when autocommit is true ..so i explicitly set it to false..but the error was same ..so ithink that getConnection method doesn't return same connection always..
Also sujit dont we have to expliciltly say transaction.commit() and transaction.setRollbackonly() once invoke method returns..please pinch me if i am being an idiot..

Sujit Pal said...

Sailesh, this was a very long time ago, and turns out that Spring already provides wrappers for this sort of stuff, so currently I use the (more of the textbook) approach described here.

Anonymous said...

hi.. i am using spring2.5.
And is still not working as the update call on the jdbctemplate does not call the invoke().
is it version related issue or something else?

Thanks and Regards
Shubhashish

Sujit Pal said...

Hi Subhashish, the approach described on this blog should work, its basically creating a proxy with an interceptor, which I don't think has changed between Spring 2.0 (which I think I was using when writing this stuff). Check your configuration. However, you may want to use a more standard approach described here.