Tutorial

Spring Transaction Management Example JDBC

Published on August 3, 2022

Spring

Pankaj

Spring Transaction Management is one of the most widely used and important feature of Spring framework. Transaction Management is a trivial task in any enterprise application. We have already learned how to use JDBC API for Transaction Management. Spring provides extensive support for transaction management and help developers to focus more on business logic rather than worrying about the integrity of data incase of any system failures.

Spring Transaction Management

Some of the benefits of using Spring Transaction Management are:

Support for Declarative Transaction Management. In this model, Spring uses AOP over the transactional methods to provide data integrity. This is the preferred approach and works in most of the cases.
Support for most of the transaction APIs such as JDBC, Hibernate, JPA, JDO, JTA etc. All we need to do is use proper transaction manager implementation class. For example org.springframework.jdbc.datasource.DriverManagerDataSource for JDBC transaction management and org.springframework.orm.hibernate3.HibernateTransactionManager if we are using Hibernate as ORM tool.
Support for programmatic transaction management by using TransactionTemplate or PlatformTransactionManager implementation.

Most of the features that we would want in a transaction manager is supported by Declarative transaction management, so we would use this approach for our example project.

Spring Transaction Management JDBC Example

We will create a simple Spring JDBC project where we will update multiple tables in a single transaction. The transaction should commit only when all the JDBC statements execute successfully otherwise it should rollback to avoid data inconsistency. If you know JDBC transaction management, you might argue that we can get do it easily by setting auto-commit to false for the connection and based on the result of all the statements, either commit or rollback the transaction. Obviously we can do it, but that will result in a lot of boiler-plate code just for transaction management. Also the same code will present in all the places where we are looking for transaction management, causing tightly coupled and non-maintainable code. Spring declarative transaction management addresses these concerns by using Aspect Oriented Programming to achieve loose coupling and avoid boiler-plate code in our application. Let’s see how Spring does it with a simple example. Before we jump into our Spring project, let’s do some database setup for our use.

Spring Transaction Management - Database Setup

We will create two tables for our use and update both of them in a single transaction.

CREATE TABLE `Customer` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Address` (
  `id` int(11) unsigned NOT NULL,
  `address` varchar(20) DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We could define foreign-key relationship here from Address id column to Customer id column, but for simplicity I am not having any constraint defined here. Our Database setup is ready for spring transaction management project, lets create a simple Spring Maven Project in the Spring Tool Suite. Our final project structure will look like below image. Let’s look into each of the pieces one by one, together they will provide a simple spring transaction management example with JDBC.

Spring Transaction Management - Maven Dependencies

Since we are using JDBC API, we would have to include spring-jdbc dependency in our application. We would also need MySQL database driver to connect to mysql database, so we will include mysql-connector-java dependency too. spring-tx artifact provides transaction management dependencies, usually it’s included automatically by STS but if it’s not then you need to include it too. You might see some other dependencies for logging and unit testing, however we will not be using any of them. Our final pom.xml file looks like below code.

<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>org.springframework.samples</groupId>
	<artifactId>SpringJDBCTransactionManagement</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>

		<!-- Generic properties -->
		<java.version>1.7</java.version>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

		<!-- Spring -->
		<spring-framework.version>4.0.2.RELEASE</spring-framework.version>

		<!-- Logging -->
		<logback.version>1.0.13</logback.version>
		<slf4j.version>1.7.5</slf4j.version>

		<!-- Test -->
		<junit.version>4.11</junit.version>

	</properties>

	<dependencies>
		<!-- Spring and Transactions -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring-framework.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>${spring-framework.version}</version>
		</dependency>

		<!-- Spring JDBC and MySQL Driver -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring-framework.version}</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.0.5</version>
		</dependency>

		<!-- Logging with SLF4J & LogBack -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>${slf4j.version}</version>
			<scope>compile</scope>
		</dependency>
		<dependency>
			<groupId>ch.qos.logback</groupId>
			<artifactId>logback-classic</artifactId>
			<version>${logback.version}</version>
			<scope>runtime</scope>
		</dependency>

		<!-- Test Artifacts -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${spring-framework.version}</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>${junit.version}</version>
			<scope>test</scope>
		</dependency>

	</dependencies>
</project>

I have updated the Spring versions to the latest one as of today. Make sure MySQL database driver is compatible with your mysql installation.

Spring Transaction Management - Model Classes

We will create two Java Beans, Customer and Address that will map to our tables.

package com.journaldev.spring.jdbc.model;

public class Address {

	private int id;
	private String address;
	private String country;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getCountry() {
		return country;
	}
	public void setCountry(String country) {
		this.country = country;
	}
	
}

package com.journaldev.spring.jdbc.model;

public class Customer {

	private int id;
	private String name;
	private Address address;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Address getAddress() {
		return address;
	}
	public void setAddress(Address address) {
		this.address = address;
	}
	
}

Notice that Customer bean has Address as one of it’s variables. When we will implement DAO for Customer, we will get data for both customer and address table and we will execute two separate insert queries for these tables and that’s why we need transaction management to avoid data inconsistency.

Spring Transaction Management - DAO Implementation

Let’s implement the DAO for Customer bean, for simplicity we will just have one method to insert record in both customer and address tables.

package com.journaldev.spring.jdbc.dao;

import com.journaldev.spring.jdbc.model.Customer;

public interface CustomerDAO {

	public void create(Customer customer);
}

package com.journaldev.spring.jdbc.dao;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.journaldev.spring.jdbc.model.Customer;

public class CustomerDAOImpl implements CustomerDAO {

	private DataSource dataSource;

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

	@Override
	public void create(Customer customer) {
		String queryCustomer = "insert into Customer (id, name) values (?,?)";
		String queryAddress = "insert into Address (id, address,country) values (?,?,?)";

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		jdbcTemplate.update(queryCustomer, new Object[] { customer.getId(),
				customer.getName() });
		System.out.println("Inserted into Customer Table Successfully");
		jdbcTemplate.update(queryAddress, new Object[] { customer.getId(),
				customer.getAddress().getAddress(),
				customer.getAddress().getCountry() });
		System.out.println("Inserted into Address Table Successfully");
	}

}

Notice that CustomerDAO implementation is not taking care of transaction management. This way we are achieving separation of concerns because sometimes we get DAO implementations from third party and we don’t have control on these classes.

Spring Declarative Transaction Management - Service

Let’s create a Customer Service that will use the CustomerDAO implementation and provide transaction management when inserting records in the customer and address tables in a single method.

package com.journaldev.spring.jdbc.service;

import com.journaldev.spring.jdbc.model.Customer;

public interface CustomerManager {

	public void createCustomer(Customer cust);
}

package com.journaldev.spring.jdbc.service;

import org.springframework.transaction.annotation.Transactional;

import com.journaldev.spring.jdbc.dao.CustomerDAO;
import com.journaldev.spring.jdbc.model.Customer;

public class CustomerManagerImpl implements CustomerManager {

	private CustomerDAO customerDAO;

	public void setCustomerDAO(CustomerDAO customerDAO) {
		this.customerDAO = customerDAO;
	}

	@Override
	@Transactional
	public void createCustomer(Customer cust) {
		customerDAO.create(cust);
	}

}

If you notice the CustomerManager implementation, it’s just using CustomerDAO implementation to create the customer but provide declarative transaction management through annotating createCustomer() method with @Transactional annotation. That’s all we need to do in our code to get the benefits of Spring transaction management. @Transactional annotation can be applied over methods as well as whole class. If you want all your methods to have transaction management features, you should annotate your class with this annotation. Read more about annotations at Java Annotations Tutorial. The only part remaining is wiring spring beans to get spring transaction management example to work.

Spring Transaction Management - Bean Configuration

Create a Spring Bean Configuration file with name as “spring.xml”. We will use this in our test program to wire spring beans and execute our JDBC program to test transaction management.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="https://www.springframework.org/schema/beans"
	xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:context="https://www.springframework.org/schema/context"
	xmlns:tx="https://www.springframework.org/schema/tx"
	xsi:schemaLocation="https://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
		https://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context-4.0.xsd
		https://www.springframework.org/schema/tx https://www.springframework.org/schema/tx/spring-tx-4.0.xsd">

	<!-- Enable Annotation based Declarative Transaction Management -->
	<tx:annotation-driven proxy-target-class="true"
		transaction-manager="transactionManager" />

	<!-- Creating TransactionManager Bean, since JDBC we are creating of type 
		DataSourceTransactionManager -->
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>
	
	<!-- MySQL DB DataSource -->
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">

		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/TestDB" />
		<property name="username" value="pankaj" />
		<property name="password" value="pankaj123" />
	</bean>

	<bean id="customerDAO" class="com.journaldev.spring.jdbc.dao.CustomerDAOImpl">
		<property name="dataSource" ref="dataSource"></property>
	</bean>

	<bean id="customerManager" class="com.journaldev.spring.jdbc.service.CustomerManagerImpl">
		<property name="customerDAO" ref="customerDAO"></property>
	</bean>

</beans>

Important points to note in the spring bean configuration file are:

tx:annotation-driven element is used to tell Spring context that we are using annotation based transaction management configuration. transaction-manager attribute is used to provide the transaction manager bean name. transaction-manager default value is transactionManager but I am still having it to avoid confusion. proxy-target-class attribute is used to tell Spring context to use class based proxies, without it you will get runtime exception with message such as Exception in thread “main” org.springframework.beans.factory.BeanNotOfRequiredTypeException: Bean named ‘customerManager’ must be of type [com.journaldev.spring.jdbc.service.CustomerManagerImpl], but was actually of type [com.sun.proxy.$Proxy6]
Since we are using JDBC, we are creating transactionManager bean of type org.springframework.jdbc.datasource.DataSourceTransactionManager. This is very important and we should use proper transaction manager implementation class based on our transaction API use.
dataSource bean is used to create the DataSource object and we are required to provide the database configuration properties such as driverClassName, url, username and password. Change these values based on your local settings.
We are injecting dataSource into customerDAO bean. Similarly we are injecting customerDAO bean into customerManager bean definition.

Our setup is ready, let’s create a simple test class to test our transaction management implementation.

package com.journaldev.spring.jdbc.main;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.journaldev.spring.jdbc.model.Address;
import com.journaldev.spring.jdbc.model.Customer;
import com.journaldev.spring.jdbc.service.CustomerManager;
import com.journaldev.spring.jdbc.service.CustomerManagerImpl;

public class TransactionManagerMain {

	public static void main(String[] args) {
		ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext(
				"spring.xml");

		CustomerManager customerManager = ctx.getBean("customerManager",
				CustomerManagerImpl.class);

		Customer cust = createDummyCustomer();
		customerManager.createCustomer(cust);

		ctx.close();
	}

	private static Customer createDummyCustomer() {
		Customer customer = new Customer();
		customer.setId(2);
		customer.setName("Pankaj");
		Address address = new Address();
		address.setId(2);
		address.setCountry("India");
		// setting value more than 20 chars, so that SQLException occurs
		address.setAddress("Albany Dr, San Jose, CA 95129");
		customer.setAddress(address);
		return customer;
	}

}

Notice that I am explicitly setting address column value too long so that we will get exception while inserting data into Address table. Now when we run our test program, we get following output.

Mar 29, 2014 7:59:32 PM org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@3fa99295: startup date [Sat Mar 29 19:59:32 PDT 2014]; root of context hierarchy
Mar 29, 2014 7:59:32 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring.xml]
Mar 29, 2014 7:59:32 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Inserted into Customer Table Successfully
Mar 29, 2014 7:59:32 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
Mar 29, 2014 7:59:32 PM org.springframework.jdbc.support.SQLErrorCodesFactory <init>
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
Exception in thread "main" org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into Address (id, address,country) values (?,?,?)]; Data truncation: Data too long for column 'address' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'address' at row 1
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:907)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:968)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:978)
	at com.journaldev.spring.jdbc.dao.CustomerDAOImpl.create(CustomerDAOImpl.java:27)
	at com.journaldev.spring.jdbc.service.CustomerManagerImpl.createCustomer(CustomerManagerImpl.java:19)
	at com.journaldev.spring.jdbc.service.CustomerManagerImpl$$FastClassBySpringCGLIB$$84f71441.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:711)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
	at com.journaldev.spring.jdbc.service.CustomerManagerImpl$$EnhancerBySpringCGLIB$$891ec7ac.createCustomer(<generated>)
	at com.journaldev.spring.jdbc.main.TransactionManagerMain.main(TransactionManagerMain.java:20)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'address' at row 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2939)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1541)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1455)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1440)
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:914)
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:907)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:642)
	... 16 more

Notice the log message says that data inserted into customer table successfully but exception thrown by MySQL database driver clearly says that value is too long for the address column. Now if you will check the Customer table, you won’t find any row there that means that transaction is rolled back completely. If you are wondering where the transaction management magic is happening, look at the logs carefully and notice the AOP and Proxy classes created by Spring framework. Spring framework is using Around advice to generate a proxy class for CustomerManagerImpl and only committing the transaction if the method returns successfully. If there is any exception, it’s just rolling back the whole transaction. I would suggest you to read Spring AOP Example to learn more about Aspect Oriented Programming model. That’s all for Spring Transaction Management Example, download the sample project from below link and play around with it to learn more.

Download Spring JDBC Transaction Management Project

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

About the authors

Pankaj

author

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

Still looking for an answer?

Ask a question Search for more help

Was this helpful?

JournalDev

DigitalOcean Employee

• December 15, 2021

I am getting below Error : any help is appreciated {“resourceType”:“OperationOutcome”,“issue”:[{“severity”:“error”,“code”:“exception”,“diagnostics”:“Could not open JDBC Connection for transaction; nested exception is java.lang.NullPointerException”}]}

- Sanjay

JournalDev

DigitalOcean Employee

• October 25, 2021

I’m having multiple dao calls in same class and all dao calls are interdependent on each others and I want to run all dao calls in same transaction? Transaction should rollback if any error happens.

- mayur Vinod Patil

JournalDev

DigitalOcean Employee

• February 19, 2020

Simply Super !!!

- Nissi

JournalDev

DigitalOcean Employee

• August 23, 2019

It will be good if you can provide both annotation & xml based configurations whenever you are explaining any spring related modules.

- Akshay

JournalDev

DigitalOcean Employee

• July 30, 2019

Could you please explain the spring boot + transaction with example …

- Mahesh Katkam

JournalDev

DigitalOcean Employee

• February 4, 2019

I regularly visit your blog and find your solutions accurate and really helpful. Thanks for this amazing post.

- Gajendra

JournalDev

DigitalOcean Employee

• September 13, 2018

Good post buddy.

- Raju

JournalDev

DigitalOcean Employee

• June 13, 2018

Hi Pankaj, My data source is like <jee:jndi-lookup id=“dataSource” jndi-name="jdbc/… For this what changes is required in transaction manager class ? Thanks,

- Tuhin subhra Dey

JournalDev

DigitalOcean Employee

• June 2, 2018

Hi, even though i have commented the following line in spring.xml. If i check the logs getting the same like before. Creating TransactionManager Bean, since JDBC we are creating of type DataSourceTransactionManager

- vijaya

JournalDev

DigitalOcean Employee

• May 23, 2018

Thank you so much, Very well explained.

- Bhavani Shankar Reddy