Transactions with JdbcTemplate

1 year ago by in Articles, Spring, SQL Tagged: , , ,

Spring provides a simple API to work with SQL called JdbcTemplate (JavaDoc). This is a very popular class as it simplifies the code when dealing with databases. The JdbcTemplate does not provide transaction support out-of-the-box and in this article we will see how to make use of transactions.

This article assumes that the reader is familiar with SQL and knows how to use JdbcTemplate. All code listed below is available at: https://java-creed-examples.googlecode.com/svn/spring/TransactionalJdbcTemplate. Most of the examples will not contain the whole code and may omit fragments which are not relevant to the example being discussed. The readers can download or view all code from the above link.

Scenario

For simplicity, let say we have two tables and we would like to insert some data into these two tables. If the second insert fails we want to roll back the first insert, and leave the tables as these were before. Thus we either insert into either both tables, or nothing.

Solution

This can be achieved using transactions. Spring provides transactional support and described in the following steps.

  1. First we need to create the transaction manager. We can have more than one transaction manager and these can be connected to different data sources. Here we only have one transaction manager which is called: “tjtJTransactionManager” as shown in the following code fragment.
    <bean id="tjtJTransactionManager"
      class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
      scope="singleton">
      <property name="dataSource" ref="tjtDataSource" />
    </bean>
    

    Note that by default, Spring expects the transaction manager to be called: “transactionManager“. Thus if you choose a different name, as we did here, you need to refer to it by its name, otherwise it will fail.

  2. Enable the annotation configuration to simplify the use of transactions as shown next
    <tx:annotation-driven transaction-manager="tjtJTransactionManager" />
    

    Here we are setting the default transaction manager to be used with the annotations. As you can see in the above example, the transaction manager name is the same as the one defined in the previous step: “tjtJTransactionManager“. When using the annotations, we do not have to specify the transaction manager as it is configured over here. With that said, should you have more than one transaction manager, you have to define which transaction manager to use at the annotation level.

  3. With the transaction manager configured we can use it within our code as shown in the following example.
    package com.javacreed.examples.db.tjt;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Lazy;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Component;
    import org.springframework.transaction.annotation.Transactional;
    
    @Lazy
    @Component
    public class TjtDaoImpl2 implements TjtDao {
    
      @Autowired
      private JdbcTemplate jdbcTemplate;
    
      private void insert(final String tableName, final String value) {
        jdbcTemplate.update("INSERT INTO " + tableName + " VALUES (?)", value);
      }
    
      @Override
      @Transactional("tjtJTransactionManager")
      public void save(final String value) {
        insert("T1", value);
        insert("T2", value);
      }
    }
    

    Here we have a simple Data Access Object (DAO) which interacts with the database and provides one method which can be used to save an entry into both tables. The save() method includes the @Transactional("tjtJTransactionManager") annotation. This means than this method needs to be executed within a transaction. Note that the transaction manager name is also provided as the annotation value. This was not required as we defined the default transaction manager in the previous step. But should you have more than one transaction manager, you can select which one to use by specifying its name as shown above.

In order for you to be able to use transactions, you must use a database that supports transactions. The code shown above was tested with an H2 database (website) instance and works well. Please note that the above code will fails with databases, such as the MySQL MyISAM Storage Engine (website), that do not support transactions.

Conclusion

Using transactions is quite easy and straight forward with Spring. You can convert non-transactional DAO classes, such as the one shown above, into a transactional one by simply following the three simple steps illustrated above. You do not have to change the business logic. If you do not like to use annotations, Spring allows you to define which methods are to be treated as transactional methods by providing the correct XML configuration.

Albert Attard

Albert Attard is a Java passionate and technical lead at a research group. You can find him on . Over the past years Albert worked on various Java projects including traditional server/client applications, modular applications, large data handling applications and concurrent data manipulation applications to name a few. He has a BSc degree from the University of London (Homepage) and an MSc Information Security with the same university. His MSc thesis (Book) received the 2012 SearchSecurity.co.UK award (Website).

4 Responses to “Transactions with JdbcTemplate”


jesus
August 27, 2013 Reply

Esta transacion marca error @Transactional(“tjtJTransactionManager”)

Q version de spring usas ?

Albert Attard Albert Attard
August 27, 2013 Reply

Translated to English using Google Translate

This brand transaction mistake @ Transactional(“tjtJTransactionManager”)

Q version of spring you use?

This project uses version: 3.2.2.RELEASE of Spring, as you can see in the POM file.

Abhi
January 10, 2014 Reply

What if I have multiple datasources? Should I need to define Transaction manager for each datasources?

Albert Attard Albert Attard
January 10, 2014 Reply

It depends what your needs are. If you want to treat these two independent, then you can simply clone the configuration and have two transaction managers. Otherwise, if you want these to be managed by one transaction manager, then you need to use a JtaTransactionManager (Java Doc). The JtaTransactionManager allows you to manage transactions in a distributed fashion. I don’t have a handy example that I can share with you, but will write on in the near future.

Leave a Comment



− 1 = seven