Sunday, 6 July 2008

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

5 comments:

Fer nando Franzini said...

Hi Dude

Very cool post....I have some ask for you.. I need use put JDBC Transaction in the JPA transaction inside spring !! Do you how a configure that ?

Manson Thomas said...

I'm sorry I don't know nothing about JPA (I try to stay away from these persisting framework) ;)

Asen said...

This is fine for single data source.
Do you know how to synchronise between two data sources. The Spring DataSourceTransactionManager is for single resource only and works great with it, but how about the two phase commits?
If you have any examples for this, please let us know.

Manson Thomas said...

Asen, sorry for the delay to publish your comment, I just forgot ;)

And I never had to work with a transaction that use 2 JDBC connection so I've no Idea... If you've found how to do this, please share !

Ondrej Medek said...

IMHO just @Transactional without anu parameters should do the job, too.