Spring Transaction support with JDBCTemplate and Java 1.5 Annotation

Here is quick tutorial to enable transaction in a Spring project using JdbcTemplate and Java 1.5 Annotations.

I'm using Spring 2.5.2, Java 1.6.
(it should work with Spring 1.2 and Java 1.5 too, considering this blog post, which also gives some usefull info for people new to transaction with Spring)

for example , as simply as this :

@Transactional (propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
public Intervention createEmptyIntervention(int idRegulation) throws Exception
{
//...jdbc Code
}

Let's begin with the beginning, applicationContext.xml :

<?xml version="1.0" encoding="UTF-8"?>
<beans          xmlns= "http://www.springframework.org/schema/beans"
               xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance"

      xsi:schemaLocation= "http://www.springframework.org/schema/beans
                           http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">

 <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
   <property name="location">
       <value>classpath:crf-irp.properties</value>
   </property>
 </bean>

 <import resource="spring/cache.xml"     />
 <import resource="spring/dao.xml"       />
 <import resource="spring/scheduler.xml" />
 <import resource="spring/dwr.xml"       />
 <import resource="spring/services.xml"  />
 <import resource="spring/servlet.xml"   />

 <bean id="SecurityService" class="fr.croixrouge.irp.services.authentification.SecurityServiceImpl" autowire="constructor"/>
</beans>

Here, among other things, I include a dao.xml and a services.xml beans definitions files.
dao.xml defines jdbc related things, and, services.xml some beans that use the jdbc things.

Also, I use XML namespace and XSD instead of DTD, because it will be needed after.

Basically, the <beans ... is equivalent to

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd">
<beans>

here is the dao.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans          xmlns = "http://www.springframework.org/schema/beans"
                xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance"
                xmlns:tx = "http://www.springframework.org/schema/tx"

       xsi:schemaLocation= "http://www.springframework.org/schema/beans
                            http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
                            http://www.springframework.org/schema/tx
                            http://www.springframework.org/schema/tx/spring-tx-2.0.xsd">



  <bean id="crfIrpDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" ><value>${jdbc.driver}</value></property>
      <property name="url"           ><value>${jdbc.url}</value></property>
      <property name="username"      ><value>${jdbc.username}</value></property>
      <property name="password"      ><value>${jdbc.password}</value></property>
  </bean>

  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="crfIrpDataSource"/>
  </bean>

  <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
     <property name="dataSource" ref="crfIrpDataSource"/>
  </bean>

  <tx:annotation-driven transaction-manager="txManager"/>

</beans>

Besides the usual dataSource and JdbcTemplate, I add a transactionManager and the tx:annotation-driven bean.
This last will do the job of examining your beans for annotations.
The transaction Manager is here to actually manage the transaction where tx:annotation-driven tells to do so.
Notice that 1 namespaces and and 2 xsd locations has been addded for the tx namespace prefix.



To be complete, here is the services.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans          xmlns    = "http://www.springframework.org/schema/beans"
                xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance"

       xsi:schemaLocation= "http://www.springframework.org/schema/beans
                            http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">


<!--...-->

    <bean id="interventionService"         class="fr.croixrouge.irp.services.intervention.InterventionServiceImpl"              autowire="constructor" />

</beans>

And finally, the code of the service:

package fr.croixrouge.irp.services.intervention;

import java.sql.Types;
import java.util.Date;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import fr.croixrouge.irp.model.monitor.Intervention;
import fr.croixrouge.irp.services.JDBCHelper;

public class InterventionServiceImpl extends JDBCHelper implements InterventionService
{
  private         JdbcTemplate  jdbcTemplate  = null;
  private static  Log           logger        = LogFactory.getLog(InterventionServiceImpl.class);


  
  public InterventionServiceImpl(JdbcTemplate jdbcTemplate)
  {
    this.jdbcTemplate = jdbcTemplate;
  }
  

  
  /*...other methods skipped...*/ 

  private final static String queryForCreateEmptyIntervention = 
    "INSERT INTO `intervention`\n"+
    "  (`id_dispositif`, `id_origine`, `id_motif`, `id_regulation`, `DH_saisie`, `num_inter`)\n"+
    "VALUES\n"+
    "  ( 0, 0, 0, ?, ?, 0)\n";

  @Transactional (propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
  public Intervention createEmptyIntervention(int idRegulation) throws Exception
  {
    Intervention intervention = new Intervention();
    
    intervention.setIdRegulation(idRegulation);
    intervention.setDhReception (new Date()  );

    Object [] os    = new Object[]{ intervention.getIdRegulation(), intervention.getDhReception()};
    int    [] types = new int   []{ Types.INTEGER                 , Types.TIMESTAMP              };
    
    jdbcTemplate.update(queryForCreateEmptyIntervention, os, types);

    intervention.setIdIntervention  (this.getLastInsertedId());
    
    if(logger.isDebugEnabled())
      logger.debug("Intervention inserted with id="+intervention.getIdIntervention());
    
    return intervention;
  }
  /*...other methods skipped...*/

  private int getLastInsertedId()
  {
    return this.getLastInsertedId(jdbcTemplate, "intervention");
  }
  
  /* herited from JDBCHelper*/
  protected int getLastInsertedId(JdbcTemplate jdbcTemplate, String tableName)
  {
    return jdbcTemplate.queryForInt("SELECT last_insert_id() from `" 
        + 
        tableName
        +"` LIMIT 1", null, null); 
  }
}

I've changed nothing but the Java 1.5 annotation that tells the TransactionManager that a Transaction is mandatory here (Required), and it should rollback on any exception that is derived from java.lang.Exception.

Here, I set up the transaction, in order last_insert_id query can work. Without a transaction, Spring can use a different connection between the insert query and the last_insert_id query, and in that case, this last query would return 0 instead of the last inserted id.

To be sure that your method has been executed within a transaction, put Spring in debug mode, and you should be able to see something like this :

2008-07-06 19:54:18,828 DEBUG [http-8080-Processor25] (DefaultRemoter.java:462) - Exec: MonitorInputIntervention.createEmptyIntervention() Object created,  not stored. id=0
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (AbstractPlatformTransactionManager.java:346) - Using transaction object [org.springframework.jdbc.datasource.DataSourceTransactionManager$DataSourceTransactionObject@139d115]
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (AbstractPlatformTransactionManager.java:374) - Creating new transaction with name [fr.croixrouge.irp.services.intervention.InterventionService.createEmptyIntervention]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,-java.lang.Exception
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (DataSourceTransactionManager.java:202) - Acquired Connection [jdbc:mysql://localhost/crfirp?autoReconnect=true, UserName=root@localhost, MySQL-AB JDBC Driver] for JDBC transaction
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (DataSourceTransactionManager.java:219) - Switching JDBC Connection [jdbc:mysql://localhost/crfirp?autoReconnect=true, UserName=root@localhost, MySQL-AB JDBC Driver] to manual commit
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (TransactionSynchronizationManager.java:186) - Bound value [org.springframework.jdbc.datasource.ConnectionHolder@1bef5e8] for key [org.apache.commons.dbcp.BasicDataSource@14189d0] to thread [http-8080-Processor25]
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (TransactionSynchronizationManager.java:261) - Initializing transaction synchronization
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (JdbcTemplate.java:790) - Executing prepared SQL update
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (JdbcTemplate.java:574) - Executing prepared SQL statement [INSERT INTO `intervention`
  (`id_dispositif`, `id_origine`, `id_motif`, `id_regulation`, `DH_saisie`, `num_inter`)
VALUES
  ( 0, 0, 0, ?, ?, 0)
]
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (TransactionSynchronizationManager.java:142) - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1bef5e8] for key [org.apache.commons.dbcp.BasicDataSource@14189d0] bound to thread [http-8080-Processor25]
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (TransactionSynchronizationManager.java:142) - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1bef5e8] for key [org.apache.commons.dbcp.BasicDataSource@14189d0] bound to thread [http-8080-Processor25]
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (StatementCreatorUtils.java:205) - Setting SQL statement parameter value: column index 1, parameter value [2], value class [java.lang.Integer], SQL type 4
2008-07-06 19:54:18,843 DEBUG [http-8080-Processor25] (StatementCreatorUtils.java:205) - Setting SQL statement parameter value: column index 2, parameter value [Sun Jul 06 19:54:18 CEST 2008], value class [java.util.Date], SQL type 93
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (JdbcTemplate.java:800) - SQL update affected 1 rows
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (TransactionSynchronizationManager.java:142) - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1bef5e8] for key [org.apache.commons.dbcp.BasicDataSource@14189d0] bound to thread [http-8080-Processor25]
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (JdbcTemplate.java:639) - Executing prepared SQL query
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (JdbcTemplate.java:574) - Executing prepared SQL statement [SELECT last_insert_id() from `intervention` LIMIT 1]
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (TransactionSynchronizationManager.java:142) - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1bef5e8] for key [org.apache.commons.dbcp.BasicDataSource@14189d0] bound to thread [http-8080-Processor25]
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (TransactionSynchronizationManager.java:142) - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1bef5e8] for key [org.apache.commons.dbcp.BasicDataSource@14189d0] bound to thread [http-8080-Processor25]
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (TransactionSynchronizationManager.java:142) - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1bef5e8] for key [org.apache.commons.dbcp.BasicDataSource@14189d0] bound to thread [http-8080-Processor25]
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (InterventionServiceImpl.java:152) - Intervention inserted with id=12
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (AbstractPlatformTransactionManager.java:880) - Triggering beforeCommit synchronization
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (AbstractPlatformTransactionManager.java:893) - Triggering beforeCompletion synchronization
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (AbstractPlatformTransactionManager.java:707) - Initiating transaction commit
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (DataSourceTransactionManager.java:259) - Committing JDBC transaction on Connection [jdbc:mysql://localhost/crfirp?autoReconnect=true, UserName=root@localhost, MySQL-AB JDBC Driver]
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (AbstractPlatformTransactionManager.java:906) - Triggering afterCommit synchronization
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (AbstractPlatformTransactionManager.java:922) - Triggering afterCompletion synchronization
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (TransactionSynchronizationManager.java:315) - Clearing transaction synchronization
2008-07-06 19:54:18,859 DEBUG [http-8080-Processor25] (TransactionSynchronizationManager.java:232) - Removed value [org.springframework.jdbc.datasource.ConnectionHolder@1bef5e8] for key [org.apache.commons.dbcp.BasicDataSource@14189d0] from thread [http-8080-Processor25]
2008-07-06 19:54:18,875 DEBUG [http-8080-Processor25] (DataSourceTransactionManager.java:314) - Releasing JDBC Connection [jdbc:mysql://localhost/crfirp?autoReconnect=true, UserName=root@localhost, MySQL-AB JDBC Driver] after transaction
2008-07-06 19:54:18,875 DEBUG [http-8080-Processor25] (DataSourceUtils.java:312) - Returning JDBC Connection to DataSource