Home » Enterprise Java » spring » JDBC » Spring Transaction Management Example with JDBC Example

About Ram Mokkapaty

Ram Mokkapaty
Ram holds a master's degree in Machine Design from IT B.H.U. His expertise lies in test driven development and re-factoring. He is passionate about open source technologies and actively blogs on various java and open-source technologies like spring. He works as a principal Engineer in the logistics domain.

Spring Transaction Management Example with JDBC Example

In this article, I will show you an example of spring transaction management using JDBC. Let me first touch base on what is transaction and how spring facilitates transaction management.

  • A transaction is a series of actions that are treated as a single unit of work. These actions should either complete in its entirety or not at all.
  • Spring, provides an abstract layer on top of different transaction management APIs. As an application developer, it helps us to focus on the business problem, without having to know much about the underlying transaction management APIs.

PlatformTransactionManager is a general interface for all Spring transaction managers.
Spring has several built-in implementations of this interface for use with different transaction management APIs like DataSourceTransactionManager, HibernateTransactionManager, JpaTransactionManager etc. Since the current example is about JDBC with a single data source, we will be using DataSourceTransactionManager.

 
I am going to show you two examples:

  1. In the first example, we manage the transaction programmatically by embedding transaction management code in the business method like starting a new transaction, commit or rollback of transaction. The main advantage of this method is that we get a precise control on when to commit or rollback the transaction.
  2. In the second example, we will avoid the boilerplate transaction management code, by delegating the execution to TransactionTemplate class. All we have to do is encapsulate our business method in form of a callback method and pass it to the TransactionTemplate.execute. TransactionTemplate provides a template method around which a transactional boundary is started, the callback method is called and then transaction is committed. In case of any exception (checked or unchecked) or error during the execution of the business method, rollback of the transaction happens.

Before I get started with the examples, a bit about the tools I have used:

  • Eclipse Luna (4.4.1) version, along with Maven Integration plugin.
  • Spring version 3.2.3
  • JDK1.7.0_67
  • MySQL Database Server 5.6

1. Create a new Maven project

You need to create a new Maven project in eclipse. Details are here.

2. Add Spring 3.2.3 dependencies

Our project is dependent on following spring modules:

  1. spring-core
  2. spring-context
  3. spring-jdbc

So add the above spring dependencies to Maven’s pom.xml file as shown below.
pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.javacodegeeks.springtx.jdbc.examples</groupId>
	<artifactId>springTxJdbcExample</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>
	</dependencies>

	<properties>
		<spring.version>3.2.3.RELEASE</spring.version>
	</properties>
</project>

3. Add JDBC driver to the dependencies

Lastly, you also need to add mysql driver dependency to pom.xml.

pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.javacodegeeks.springtx.jdbc.examples</groupId>
	<artifactId>springTxJdbcExample</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.26</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>
	</dependencies>

	<properties>
		<spring.version>3.2.3.RELEASE</spring.version>
	</properties>
</project>

4. Transaction Example

We will look into an example of transferring money from one account to another. To do that you have to first withdraw the amount from the source account, and then deposit it to the destination account. The operation has to succeed in full or fail in case of any business issue, in which case it should rollback the entire transaction.

5. Setting up the schema

We just need one table for maintaining the account balance. If the table already exists, we will drop it and re-create.

db-schema.sql:

drop table if exists `Account`;
CREATE TABLE `Account` (
  `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(100) NOT NULL,
  `BALANCE` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

We also need some sample data for the two accounts.

db-test-data.sql:

insert into account(id, name, balance) values (1, "Joe", 2000);
insert into account(id, name, balance) values (2, "Jim", 1000);

In order to simplify the setup process, we will configure the scripts using spring’s custom element jdbc:initialize-database so that the scripts are automatically run as we load the application context.

applicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc" 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.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost/test" />
		<property name="username" value="root" />
		<property name="password" value="mnrpass" />
	</bean>

	<jdbc:initialize-database data-source="dataSource"
		enabled="true">
		<jdbc:script location="classpath:db-schema.sql" />
		<jdbc:script location="classpath:db-test-data.sql" />
	</jdbc:initialize-database>

</beans>

6. JdbcTemplate operations

In our fund transfer example, we need APIs to verify the validity of an account, to retrieve the balance amount and to transfer the amount from one account to another. We will define these APIs in an interface.

FundManager.java:

package com.javacodegeeks.springtx.jdbc.examples;

public interface FundManager {
	void transfer(int accountNbr1, int accountNbr2, int amount) throws Exception;
	int getBalance(int accountNbr);
	void verifyAccount(int accountNbr);
}

The implementation will depend on JdbcTemplate helper class to execute the database operations so we will make it extend JdbcDaoSupport. In doTransfer, we withdraw amount from account1 and deposit it in account2. There are a couple of checks to validate the data like verifying whether account exists and making sure account1 has enough funds. If the account doesn’t exist or hasn’t got enough funds, we throw a RuntimeException.

FundManagerDao.java:

package com.javacodegeeks.springtx.jdbc.examples;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

public abstract class FundManagerDao extends JdbcDaoSupport implements FundManager {

	void doTransfer(int accountNbr1, int accountNbr2, int amount) {		
		verifyAccount(accountNbr1);				
		System.out.println("Transfer amount: " + amount);
		Integer account1Balance = getJdbcTemplate().queryForObject(
				"SELECT BALANCE FROM ACCOUNT WHERE ID = ?",
				new Object[] { accountNbr1 }, Integer.class);
		if (account1Balance < amount) {
			throw new RuntimeException("Cannot transfer, account doesn't have enough funds!");
		}
		int account1NewBalance = account1Balance - amount;			
		getJdbcTemplate().update(
				"UPDATE ACCOUNT SET BALANCE = ? WHERE ID = ?",
				account1NewBalance, accountNbr1);
		
		verifyAccount(accountNbr2);
		Integer account2Balance = getJdbcTemplate().queryForObject(
				"SELECT BALANCE FROM ACCOUNT WHERE ID = ?",
				new Object[] { accountNbr2 }, Integer.class);
		int account2NewBalance = account2Balance + amount;
		getJdbcTemplate().update(
				"UPDATE ACCOUNT SET BALANCE = ? WHERE ID = ?",
				account2NewBalance, accountNbr2);	
	}

	public int getBalance(int accountNbr) {
		return getJdbcTemplate().queryForObject("SELECT BALANCE FROM ACCOUNT WHERE ID = ?", new Object[]{accountNbr}, Integer.class);
	}

	public void verifyAccount(int accountNbr) {
		boolean acountExists = getJdbcTemplate().queryForObject("SELECT 1 FROM ACCOUNT WHERE ID = ?", new Object[]{accountNbr}, Integer.class) == 1;
		if (!acountExists) {
			throw new RuntimeException("Account " + accountNbr + " doesn't exists");
		}
	}
}

7. DataSourceTransactionManager Example

Notice that FundManagerDao class is abstract as we haven’t injected the DataSource. We will have two concrete classes to do so. The first one will demonstrate the case where we manage the transaction ourselves like starting a new transaction by calling the getTransaction() method, calling commit() on successful completion or rollback() in case of any exception. The second concrete class will demonstrate the use of TransactionTemplate.

Let’s begin with our first example.

Class SpringTranManagerFundTransferTransaction depends directly on a transaction manager to manage the transaction. Since we don’t want to depend on a specific type of transaction manager, member variable transactionManager is of interface type PlatformTransactionManager.

SpringTranManagerFundTransferTransaction.java:

package com.javacodegeeks.springtx.jdbc.examples;

import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

public class SpringTranManagerFundTransferTransaction extends FundManagerDao implements FundManager {
	private PlatformTransactionManager transactionManager;

	public void setTransactionManager(PlatformTransactionManager txManager) {
		this.transactionManager = txManager;
	}

	public void transfer(int accountNbr1, int accountNbr2, int amount) throws Exception {
		TransactionDefinition txDef = new DefaultTransactionDefinition();
		TransactionStatus txStatus = transactionManager.getTransaction(txDef);
		try {
			doTransfer(accountNbr1, accountNbr2, amount);
			transactionManager.commit(txStatus);
		} catch (Exception e) {
			transactionManager.rollback(txStatus);
			throw e;
		}
	}
}

Let’s go through the implementation of transfer method. Note that before we start a new transaction, we will have to specify the transaction attributes in a transaction definition object of type TransactionDefinition. Since we don’t have any specific transaction attributes, we will use the default one that spring provides DefaultTransactionDefinition.

Once we have a transaction definition, we will have to ask transactionManager to start a new transaction with that definition by calling the getTransaction() method. The return value is TransactionStatus object to keep track of the transaction status. Next, we will call doTransfer to transfer the amount from one account to another. If the transaction is successfully, we will ask the transactionManager to commit this transaction by passing in the transaction status. If there is any error during transfer, doTransfer method will throw a RuntimeException in which case we will catch the exception and rollback the transaction.

Note that transactionManager is of interface type PlatformTransactionManager and we need to inject an appropriate transaction manager. Since we are dealing with only a single data source and accessing it with JDBC, we will inject DataSourceTransactionManager implementation.

We also need to inject a DataSource because the class SpringTranManagerFundTransferTransaction is a subclass of Spring’s JdbcDaoSupport, which requires it.

We will modify our applicationContext.xml to configure data source and transaction manager.

applicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc" 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.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">

	<jdbc:initialize-database data-source="dataSource"
		enabled="true">
		<jdbc:script location="classpath:db-schema.sql" />
		<jdbc:script location="classpath:db-test-data.sql" />
	</jdbc:initialize-database>


	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost/test" />
		<property name="username" value="root" />
		<property name="password" value="mnrpass" />
	</bean>


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


	<bean id="fundTransferTranManager"
		class="com.javacodegeeks.springtx.jdbc.examples.SpringTranManagerFundTransferTransaction">
		<property name="dataSource" ref="dataSource" />
		<property name="transactionManager" ref="transactionManager" />
	</bean>

</beans>

8. TransactionTemplate Example

In our second example, we will use TransactionTemplate instead of directly relying on PlatformTransactionManager. Note that we still need a transaction manager, instead of directly managing the transactions, we will rely on TransactionTemplate which in turn will use an implementation of PlatformTransactionManager to manage the transactions.

SpringTranTemplateFundTransferTransaction.java:

package com.javacodegeeks.springtx.jdbc.examples;

import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

public class SpringTranTemplateFundTransferTransaction extends FundManagerDao implements FundManager {
	private TransactionTemplate transactionTemplate;

	public void setTransactionTemplate(TransactionTemplate transactionTemplate) {
		this.transactionTemplate = transactionTemplate;
	}

	public void transfer(final int accountNbr1, final int accountNbr2, final int amount)
			throws Exception {
		transactionTemplate.execute(new TransactionCallbackWithoutResult() {			
			@Override
			protected void doInTransactionWithoutResult(TransactionStatus status) {
				doTransfer(accountNbr1, accountNbr2, amount);
			}
		});
	}
}

Instead of calling doTransfer directly, we will encapsulate it within an object of TransactionCallbackWithoutResult and then pass it to TransactionTemplate.execute.

We will now modify our application context xml to add SpringTranTemplateFundTransferTransaction bean. Since it depends on TransactionTemplate, we will have to define a transaction template in the bean configuration file and inject it.

applicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc" 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.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd">

	<jdbc:initialize-database data-source="dataSource"
		enabled="true">
		<jdbc:script location="classpath:db-schema.sql" />
		<jdbc:script location="classpath:db-test-data.sql" />
	</jdbc:initialize-database>


	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost/test" />
		<property name="username" value="root" />
		<property name="password" value="mnrpass" />
	</bean>


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


	<bean id="fundTransferTranManager"
		class="com.javacodegeeks.springtx.jdbc.examples.SpringTranManagerFundTransferTransaction">
		<property name="dataSource" ref="dataSource" />
		<property name="transactionManager" ref="transactionManager" />
	</bean>

	<bean id="transactionTemplate"
		class="org.springframework.transaction.support.TransactionTemplate">
		<property name="transactionManager" ref="transactionManager" />
	</bean>

	<bean id="fundTransferTranTemplate"
		class="com.javacodegeeks.springtx.jdbc.examples.SpringTranTemplateFundTransferTransaction">
		<property name="dataSource" ref="dataSource" />
		<property name="transactionTemplate" ref="transactionTemplate" />
	</bean>
</beans>

9. Run the Example

Now let’s run our example.
Since our beans are spring managed, we will have to first load the applicationContext.xml. Once we have the ApplicationContext object, we will ask it to return us FundManager bean. Note that FundManager is our main interface to manage the funds.

We need some test data to test our example. Recall that we have already created a couple of sample accounts to use.

Tip
To view the sample accounts click here: Sample accounts

We will test both the happy scenario and the failing one. In the happy scenario, we transfer amount $200 from account1 to account2. If the amount is transferred, you will see the message Fund Transferred.
In the failing one, we try to transfer a dollar more that the current balance of account1 and the transaction fails with message Cannot transfer, account doesn’t have enough funds!.

SpringTxJdbcExample.java:

package com.javacodegeeks.springtx.jdbc.examples;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class SpringTxJdbcExample {
	public static void main(String[] args) {
		ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
		FundManager fundManager = (FundManager) context.getBean("fundTransferTranTemplate");
		try {
			int amount = 200;
			printAccountDetails(fundManager);
			fundManager.transfer(1, 2, amount);
			System.out.println("Fund transfered");
			printAccountDetails(fundManager);
			fundManager.transfer(1, 2, fundManager.getBalance(1) + 1);
			printAccountDetails(fundManager);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		printAccountDetails(fundManager);
	}
	
	private static void printAccountDetails(FundManager fundManager) {
		System.out.println("Account 1 has " + fundManager.getBalance(1) + ", account 2 has " + fundManager.getBalance(2));
	}
}

Output:

Account 1 has 2000, account 2 has 1000
Transfer amount: 200
Fund transferred
Account 1 has 1800, account 2 has 1200
Transfer amount: 1801
Cannot transfer, account doesn't have enough funds!
Account 1 has 1800, account 2 has 1200

Download the Eclipse project of this tutorial

This was an example of Spring Transaction Management Example with JDBC.

Download
You can download the full source code of this example here: springTxJdbcExample.zip
(No Ratings Yet)
Start the discussion Views Tweet it!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

avatar
  Subscribe  
Notify of