Spring Transaction using JDBCTemplate, Spring Transaction easy example

Here I am going to show very easy example of Spring jdbcTemplate transaction. First I will write one small example to save data into mysql database without handling any transaction then we will solve problem with use of Spring transation.

Create one project (JDBCTemplateTransaction) in eclipse and add these below mentioned external jar:

1. spring-2.5.6.jar
2. mysql-connector-java-5.1.9.jar
3. log4j-1.2.15.jar
4. commons-logging-1.0.4.jar
5. commons-dbcp-1.2.2.jar
6. commons-pool-1.3.jar

Create one package in src folder say com.suman and add these java files.
1. App.java
2. IDaoOperation.java (Interface)
3. DaoOperation.java

All the code are below.

Create three file inside the src folder
1. project-config.properties
2. project-config.xml
3. log4j.xml

******** code section ************
App.java

package com.suman;

import org.apache.log4j.Logger;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class App
{
public static void main( String[] args )
{
Logger log = Logger.getLogger(App.class);
IDaoOperation daoOperation;
log.info( "Check Spring Transaction using jdbcTemplate" );
ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("project-config.xml");
daoOperation = (IDaoOperation) context.getBean("daoOperation");
daoOperation.saveStudent();
log.info("THIS IS MAIN METHOD OF SPRING TEST");
}
}

IDaoOperation.java

package com.suman;
/**
* @author Binod Suman
*/
public interface IDaoOperation {

public void saveStudent();

}


DaoOperation.java

package com.suman;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Collection;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcOperations;

/**
* @author Binod Suman
*/
public class DaoOperation implements IDaoOperation{

private DataSource dataSource;
private Logger log;
private JdbcOperations jdbc;

public void saveStudent(){
String sql = "insert into student (id,name,roll) values (1,'Binod',110)";
executeSQL(sql);
saveHostel();
}

public void saveHostel(){
String sql = "insert into hostel (id,name,roll) values (1,'Yamuna',1)";
executeSQL(sql);
}


public DataSource getDataSource() {
return dataSource;
}

public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}

public JdbcOperations getJdbc() {
return jdbc;
}

public void setJdbc(JdbcOperations jdbc) {
this.jdbc = jdbc;
}


public void init(){
log = Logger.getLogger(DaoOperation.class);
setDatabase();
}


public void executeSQL(String sql) {
try{
log.debug("exec SQL: " + sql);
jdbc.execute(sql);
}catch(Exception e){
log.error("some problem during executing sql");
}
}

public void executeSQL(Collection<String> sqlList) throws IOException,
SQLException {
for (String sql : sqlList) {
executeSQL(sql);
}
}
public void setDatabase(){
executeSQL("USE sumandb");
}


}


project-config.properties
db.driver = com.mysql.jdbc.Driver
db.url = jdbc:mysql://localhost
db.username = root
db.password = mysql

project-config.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:aop="http://www.springframework.org/schema/aop"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:lang="http://www.springframework.org/schema/lang"
xmlns:context="http://www.springframework.org/schema/context"
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
http://www.springframework.org/schema/lang
http://www.springframework.org/schema/lang/spring-lang-2.5.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-2.5.xsd">
<context:mbean-server/>
<context:property-placeholder location="classpath:/project-config.properties" />

<bean id="daoOperation"
class="com.suman.DaoOperation"
init-method="init" p:dataSource-ref="dataSource"
p:jdbc-ref="jdbc" />

<bean id="jdbc" class="org.springframework.jdbc.core.JdbcTemplate"
p:dataSource-ref="dataSource" />

<!--Infrastructure setup -->

<bean id="dataSource" destroy-method="close"
class="org.apache.commons.dbcp.BasicDataSource" p:driverClassName="${db.driver}"
p:url="${db.url}" p:username="${db.username}" p:password="${db.password}" />

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
p:dataSource-ref="dataSource"/>

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

</beans>

log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/" debug="false">

<!-- logs to system console -->
<appender name="CONSOLE" class="org.apache.log4j.ConsoleAppender">
<param name="Target" value="System.out" />
<param name="Threshold" value="ALL" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d{ABSOLUTE} %-5p [%c{1}] %m%n" />
</layout>
</appender>

<logger name="org.springframework" additivity="false">
<level value="WARN" />
<appender-ref ref="CONSOLE"/>
</logger>

<root>
<level value="ALL" />
<appender-ref ref="CONSOLE" />
</root>

</log4j:configuration>


Create database with name sumandb in mysql in localhost at 3306 port.
Then create the two table

create table
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(90) default NULL,
`roll` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



create table
CREATE TABLE `hostel` (
`id` int(11) NOT NULL,
`name` varchar(200) default NULL,
`roll` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `FK_hostel` (`roll`),
CONSTRAINT `FK_hostel` FOREIGN KEY (`roll`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



RUN THE APPLICATION :

Now run App.java
you will see one record has been inserted into student table and one record into hostel table.

Here student.id is the foreign key for hostel.roll. So without having id (say x) exist in student table you can not save any record into hostel table with roll x.
** IMP **. But I am going to save into both table. I will give id 2 in student table and roll 9 in hostel table.
String sql = "insert into student (id,name,roll) values (2,'Pramod',110)";
String sql = "insert into hostel (id,name,roll) values (1,'Yamuna',9)";
See what is going to happen.

One recrod has been inserted in student table but no new record into hostel table. BUT in student table record should not have been inserted if we will use TRANSACTION.

delete that recrod from student table.
DELETE FROM student WHERE id = 2

NOW I AM GOING TO IMPLEMENT SPRING TRANSACTION.

FIRST WAY TO IMPLEMENT SPRING TRANSACTION.

Change saveStudetn() of DaoOperation.java method and change import:

public void saveStudent(){
TransactionTemplate tt = new TransactionTemplate();
tt.setTransactionManager(new DataSourceTransactionManager(getDataSource()));
tt.execute(new TransactionCallbackWithoutResult() {
protected void doInTransactionWithoutResult(TransactionStatus status) {
String sql = "insert into student (id,name,roll) values (2,'Pramod',110)";
executeSQL(sql);
saveHostel();
}
});
}

Change import

import java.io.IOException;
import java.sql.SQLException;
import java.util.Collection;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;



Now you run App.java, there will NOT be any record will get inserted either in Student or hostel table. Transaction has rolled back all the database opeartion if any operation gets fail.

Now change sql:
String sql = "insert into student (id,name,roll) values (2,'Pramod',110)";
String sql = "insert into hostel (id,name,roll) values (2,'Yamuna',2)";

Now run the application.
Both record have been saved successfully.


SECON WAY TO IMPLEMENT SPRING TRANSACTION.

Change saveStudetn() of DaoOperation.java method and change import:

@Transactional (propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
public void saveStudent(){
String sql = "insert into student (id,name,roll) values (2,'Pramod',110)";
executeSQL(sql);
saveHostel();
}



Change sql to check whether transaction is working correctly:

String sql = "insert into student (id,name,roll) values (3,'Ayush',120)";
String sql = "insert into hostel (id,name,roll) values (3,'Ganga',9)";

no any record should be inserterd.

Now change sql, and both record should be inserted.
String sql = "insert into student (id,name,roll) values (3,'Ayush',120)";
String sql = "insert into hostel (id,name,roll) values (3,'Ganga',3)";

Thats easy ............... :)


Now I checked with huge data. If any record will not be able to insert then all the previouse record will rolled back.

Changed both method of DaoOpeartion.java

public void saveStudent(){
for(int i=0;i<10;i++){
String sqlStudetn = "insert into student (id,name,roll) values ("+i+",'Ayush',120)";
String sqlHostel = "insert into hostel (id,name,roll) values ("+i+",'Ganga',"+i+")";
executeSQL(sqlStudetn);
saveHostel(sqlHostel);
}

}

public void saveHostel(String sql){
executeSQL(sql);
}


Run the App.java

10 record should be inserted into Student table and 10 records should be inserted into hostel table.

Now add last recrod that should not be insert to avoid foreign key constraints. Then all the previous 10 record will be rolled back;

DELETE FROM hostel;
DELETE FROM student;
@Transactional (propagation=Propagation.REQUIRED, rollbackFor=Exception.class)
public void saveStudent(){
for(int i=0;i<10;i++){
String sqlStudetn = "insert into student (id,name,roll) values ("+i+",'Ayush',120)";
String sqlHostel = "insert into hostel (id,name,roll) values ("+i+",'Ganga',"+i+")";
executeSQL(sqlStudetn);
saveHostel(sqlHostel);
}
String sqlHostel = "insert into hostel (id,name,roll) values (10,'Ganga',10)";
saveHostel(sqlHostel);
}


Now run the App.java
and check database, there is no any record has been inserted either in student table or Hostel table.

That's easy .............. :)