Saturday, March 24, 2007

Spring, JdbcTemplate and Transactions

Yet another item in my dirty-secrets category is the fact that so far, until about a week ago, I had never used database transactions with Spring and JDBC, although I have been using Spring for more than 3 years now. Before Spring, I did a lot of classic JDBC coding with the try..catch..finally pattern. My first application using Spring was to wrap another third party application which updated its MySQL databases using JDBC as well. In a few instances we would add hooks to insert/update additional data using JDBC. Subsequently I discovered JdbcTemplate, but I was now working on applications where the main focus was transaction speeds and it was acceptable to lose a few records here and there, so we used MySQL with the MyISAM storage engine (which does not support transactions) was chosen. To the readers who are cringing in horror, I am happy to report that we only had about half an hour of downtime in about 3 years of operation because of data corruption, and that half hour included figuring out why our servers were hung and switching to the replication server.

At my current job, we are using Spring with Oracle. So far I did not have to use transactions in my Spring JDBC code. There is a lot of code written in classic JDBC (using transactions) in the application, and most of the work I had done so far was reading that data, which did not need transactions. However, sometime early last week, I had to write the Data Access Object for a bean which consisted of one parent table and three child tables with foreign key dependencies on the parent. The save() and delete() methods of this DAO needed to be transactional since all the JDBC operations needed to succeed or fail as a unit.

I have used transactions with Spring and Hibernate in a small project (also on MySQL, but with the InnoDb storage engine) before. However, since Spring-Hibernate usage is so widely documented on the web, the effort did not involve much understanding. Spring-JDBC usage gets significantly less coverage, perhaps because it is not as popular. I began by applying the prescriptive examples I found on the web, but no matter what I tried, Oracle would complain about missing foreign keys and throw an exception. It ultimately turned out to be an application bug, but the problem led me to read up on transactions, and look at Spring code and find out how the TransactionTemplate is implemented, etc, so happily, I now understand a little more about Spring and J2EE transactions than I did before.

Setting up the Transaction Manager

No matter what approach (Declarative or Programmatic) you take with making your Spring-JDBC code transactional, you will need a Transaction Manager. Spring provides a DataSourceTransactionManager which you can use if your application speaks to a single database, otherwise you may need to choose the appropriate JTA Transaction Manager implementation. Regardless, switching out the Transaction Manager just involves changing the bean definition below. Here is the definition:

1
2
3
4
5
6
7
8
9
  <!-- you should already have one of these -->
  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    ...
  </bean>

  <!-- this should be added for transaction support -->
  <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
     <property name="dataSource" ref="dataSource"/>
  </bean>

The Programmatic Approach

My initial implementation was using the programmatic transaction approach using a TransactionTemplate to wrap the code that needs to be in a transaction. To do this, my DAO bean declared a dependency on the TransactionManager bean defined above.

1
2
3
4
  <bean id="myDao" class="com.mycompany.daos.MyDao">
    <property name="dataSource" ref="dataSource"/>
    <property name="transactionManager" ref="transactionManager"/>
  </bean>

Our DAO code has methods to select various attributes based on various input parameters (named getXXX() and findXXX() by convention) and a save() and delete() method that inserts and deletes the bean from the database. The additional things a transactional DAO must have is the reference and setter for the Transaction Manager, code to build a Transaction Template from the Transaction Manager, and a TransactionCallback to wrap the transactional bit of code. This is shown below:

 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
public class MyDao extends JdbcDaoSupport {

  private TransactionTemplate txTemplate;
  ...

  public void setTransactionManager(PlatformTransactionManager txManager) {
    this.txTemplate = new TransactionTemplate(txManager);
    this.txTemplate.setPropagationBehavior(DefaultTransactionDefinition.PROPAGATION_REQUIRED);
  }

  ...
  public int save(MyBean bean) throws Exception {
    final int savedBeanId = (beanId == 0 ? getNextBeanId() : beanId);
    txTemplate.execute(new TransactionCallbackWithoutResult() {
      public void doInTransactionWithoutResult(TransactionStatus status) {
        if (beanId == 0) {
          getJdbcTemplate().update("insert into beans(id,name) values (?,?)",
            new Object[] {savedBeanId, bean.getName()});
        } else {
          getJdbcTemplate().update("update partner set name=? where id=?",
            new Object[] {bean.getName(), savedBeanId});
        }
        for (String beanProp1 : bean.getProps1()) {
          getJdbcTemplate().update(
            "insert into bean_props1(bean_id,prop_value) values (?,?)",
             new Object[] {savedBeanId, beanProp1});
        }
        for (String beanProp2 : bean.getProps2()) {
          getJdbcTemplate().update(
            "insert into bean_props2(bean_id,prop_value) values (?,?)",
             new Object[] {savedBeanId, beanProp2});
        }
        for (String beanProp3 : bean.getProps3()) {
          getJdbcTemplate().update(
            "insert into bean_props3(bean_id,prop_value) values (?,?)",
            new Object[] {savedBeanId, beanProp3});
        }
      }
    });
    return savedBeanId;
  }
  ...
}

Note that I did not want to return anything from my TransactionCallback, so I used a TransactionCallbackWithoutResult object. If there is a need to pass a result back from the callback, you can use TransactionCallback instead.

The Declarative Approach

Spring and most authors recommend using the Declarative approach. With this approach, the DAO code has no reference to the TransactionTemplate. It is identical to a DAO which would have been written to work without transactions. The DAO itself is wrapped inside a TransactionProxyFactoryBean which provides the transactional behavior for the bean. The transactional behavior can be configured in the application context using XML. Our configuration to make the save() and delete() methods work within a transaction but leave the getXXX() methods alone would look like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
  <bean id="myDaoTarget" class="com.mycompany.daos.MyDao">
    <property name="dataSource" ref="dataSource"/>
  </bean>

  <bean id="myDao" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager" ref="transactionManager"/>
    <property name="target" ref="myDaoTarget"/>
    <property name="proxyTargetClass" value="true"/>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED,-Exception</prop>
        <prop key="get*">PROPAGATION_SUPPORTS</prop>
      </props>
    </property>
  </bean>

I used CGLIB proxying because I did not want to define an interface for MyDao, but if an interface was defined, I could have used standard Java interface based proxying as well. The resulting DAO code now looks identical to one without transactions.

 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
public class MyDao extends JdbcDaoSupport {
  ...
  public int save(MyBean bean) throws Exception {
    final int savedBeanId = (beanId == 0 ? getNextBeanId() : beanId);
    if (beanId == 0) {
      getJdbcTemplate().update("insert into beans(id,name) values (?,?)",
        new Object[] {savedBeanId, bean.getName()});
    } else {
      getJdbcTemplate().update("update partner set name=? where id=?",
        new Object[] {bean.getName(), savedBeanId});
    }
    for (String beanProp1 : bean.getProps1()) {
      getJdbcTemplate().update(
        "insert into bean_props1(bean_id,prop_value) values (?,?)",
        new Object[] {savedBeanId, beanProp1});
    }
    for (String beanProp2 : bean.getProps2()) {
      getJdbcTemplate().update(
        "insert into bean_props2(bean_id,prop_value) values (?,?)",
         new Object[] {savedBeanId, beanProp2});
    }
    for (String beanProp3 : bean.getProps3()) {
      getJdbcTemplate().update(
        "insert into bean_props3(bean_id,prop_value) values (?,?)",
        new Object[] {savedBeanId, beanProp3});
    }
    return savedBeanId;
  }
  ...
}

For both the transaction and declarative approaches, note that it is not necessary to have all the JDBC code in a single method. If we wanted to refactor our save() code into multiple doSaveMyBeanMain(), doSaveMyBeanProps1(), etc, methods, using either of the above approaches without any modification will retain the transactional behavior.

Conclusion

This post described two approaches to making the DAO transactional with Spring and JDBC. The approaches described here are not new, they are based on approaches detailed in Spring documentation and other web pages, but I do it in more of a prescriptive style, and in more detail. Hopefully, it will be helpful to people using transactions with JDBC and Spring.

References

The following documents helped me to understand transactions and how they are used within Spring and EJB3.

30 comments (moderated to prevent spam):

Kyle W. Cartmell said...

Great information, thank you!

Sujit Pal said...

Hi Kyle, thanks for the feedback and you are welcome.

test said...

nicely summarized.

Sujit Pal said...

thank you.

Radsa said...

Very informative and well presented. Thanks.

Sujit Pal said...

Thanks Radsa.

Dimitri said...

Thank you very much for sharing. I'm actually facing the same issue, and your post helps a lot.

Sujit Pal said...

Thanks for the comment Dimitri, and you are welcome.

Anonymous said...

Nice post can you share your example in zip file
thanks

Sujit Pal said...

Hi, thanks, and sorry, don't have a zip file of this stuff anymore.

milto said...

Hi!

It's a nice post which can help a lot but I don't understand one thing..

Let's consider declarative transactions and that sample code which you've presented. If we refactor this method to multiple methods as you suggested then each of these methods will have a propagation required attribute set. So if we call these dao methods by turns from our controller and if there is an exception in one of the methods so the transaction will be rollbacked. The next methods won't be executed because of exception. But the previuos ones are executed! And they can insert data and it won't be rollbacked. I understand it in that manner, am I wrong? In my opinion the only way is to keep all of these jdbcTemplate updates in dao's "save" method. Or if we want to refactor to multiple save methods we should set transaction scope on controller method which invokes these multipled save methods.

Any explanation would be welcomed :)

regards

milto

Sujit Pal said...

Hi Milto, yes and no... So if you are bundling multiple db calls together into a single "logical" method call, then I would expose that logical method in my DAO and make it transactional - this method would then call private methods in the DAO to do the CRUD operations. But in case your DAO provides a CRUD interface, then yes, you probably want to have the transaction in the controller layer.

Narayana Enaganti said...

It's very Use full............But can u send it me some samples to my email id : narayana49@gmail.com

Sujit Pal said...

Hi Narayana, all the code is here on the post. You can copy-paste to local files if you want.

Anonymous said...

Very cool, thanks. Could you provide a tutorial about the same subject but using annotations instead? :)

I did this



























PROPAGATION_SUPPORTS




package net.developpez.irnbru.tutorial.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import net.developpez.irnbru.tutorial.domain.Article;
import net.developpez.irnbru.tutorial.domain.Articletranslated;
import net.developpez.irnbru.tutorial.domain.Author;
import net.developpez.irnbru.tutorial.domain.Country;
import net.developpez.irnbru.tutorial.domain.Languages;

@Repository("MonService")
public class PersistenceDaoImpl implements PersistenceDao {

@Autowired
private JdbcTemplate jdbcTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}

@SuppressWarnings("unchecked")
@Transactional
@Override
public List findall() {
// TODO Auto-generated method stub
return jdbcTemplate.query("SELECT * from article",
new RowMapper() {

@Override
public Object mapRow(ResultSet result, int rowNum) throws SQLException {



}
}); }



}

and in my controller

public class WelcomeController implements Controller {

@Autowired
@Qualifier("nico")
private PersistenceDao articleDao;

.....

}

irnbru said...

Great tutorial, it helps to find your way when you start :) But it is even easier with annotations.






















with the service


@Service
public class ServiceDaoImpl implements ServiceDao {

@Autowired
private PersistenceDao dao;

@Override
public List findall() {
// TODO Auto-generated method stub
return dao.findall();
}

}

and the dao

@Repository
public class PersistenceDaoImpl implements PersistenceDao {

@Autowired
private JdbcTemplate jdbcTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}

@SuppressWarnings("unchecked")
@Transactional(isolation=Isolation.DEFAULT,
propagation=Propagation.REQUIRED,
readOnly=true)
@Override
public List findall() {

}

}

Less heavy :) Maybe we could update your tutorial with annotations part! :)

Sujit Pal said...

Thanks Imbru (I take it that both the comments above are from you, right?). Thanks for the code, and agreed its less heavyweight. I guess your comment here serves to inform people that there is a better way :-).

Anonymous said...

As you have used update() of jdbcTemplate.
getJdbcTemplate().update(
"insert into bean_props3(bean_id,prop_value) values (?,?)",
new Object[] {savedBeanId, beanProp3});

But there are one more argument is there int[] argTypes with this method like

int update(String sql,
Object[] args,
int[] argTypes)
throws DataAccessException

Please tell us what is the use of argTypes and when we should use.

Thanks a lot for nice posting.

Thanks,

Binod Suman

http://ayushsuman.blogspot.com

Sujit Pal said...

Hi Binod, you can use both styles. I think the 3-argument style is for you to explicitly set the parameter types. Spring can use the 2-argument call to figure out from the Java to SQL type mapping for the database.

sdfsdf said...

This is a gr8 article. But i have a different scenario. I have many database calls inside one logical method. When i have huge input data, i want to split the input data and call this logical method from different threads. I use executor service to spawn threads. My problem is the transaction is not rolling back when there is error in any one of the threads. Can you suggest how to handle this?

Sujit Pal said...

Thanks sdfsdf. Since you want to coordinate the transaction across multiple threads, would it make sense to use distributed transactions instead?

vels said...

good article...

Sujit Pal said...

thanks, vels.

Lopez said...

Sujith, good article.

How about "using PlatformTransactionManager implementation directly." as mentioned in this spring documents

This approach is similar to JTA conventions. Do you see any pitfalls in this way to doing?

Anonymous said...

can we save an object in database using JDBC template as we do in hibernate.

something like:
db.save(someObject);
This is just an example.

Sujit Pal said...

@Lopez: apologies for an extremely belated answer, for some reason I published your comment but did not answer, but I think using PlatformTransactionManager is the preferable approach. I have another post later on where I have done this.

@Anonymous: yes, we can, but since Hibernate is an ORM, you can say hibernate.save(MyObject) where Hibernate knows what your MyObject looks like, ie what its members are and how they map to the database table(s). JdbcTemplate is a thin layer over the JDBC interface, so it takes care of some of the boilerplate, but you still have to specify which fields you want to persist and to which database table/column.

Imby said...

Thanks

Sujit Pal said...

You are welcome Imby.

Rajkumar Singh said...

please go through the link for easy undeerstanding

Sujit Pal said...

Hi Rajkumar, did you forget to post the link?