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.

• Expert One-on-One J2EE Design and Development - Rod Johnson's original book on the thinking and motivation behind the Spring framework. It has a nice chapter on Spring's transactional capabilities.
• Spring framework online reference documentation - Obviously the final word on anything Spring related.
• Java Transaction Strategies - a free e-book by Mark Richards from InfoQ (membership required). Details various types of transaction strategies for both EJB3 and Spring. However, my code example above uses a 2-layer model of DAO-Controller as opposed to his recommendation of the 3-layer DAO-Service-Controller.