Thursday, May 21, 2020

Spring Transaction Management JDBC Example Using @Transactional Annotation

In the post Transaction Management in Spring we have already seen the details about transaction management in Spring. In this post, building on that knowledge we’ll see an example of transaction management in Spring with JDBC. This Spring transaction management example uses declarative transaction using @Transactional annotation.

Technologies used

  • Spring 5.0.4
  • Apache DBCP2
  • MYSQL 5.1.39
  • Java 8
  • Apache Maven 3.3.3

In the transaction management example, Apache DBCP is used for providing pooled JDBC datasource and MYSQL is used as the back end.


Maven dependencies

If you are using Maven then you can provide dependencies in your pom.xml.

Refer Creating a Maven Project in Eclipse to see how to set up Maven project.

With all the required dependencies your pom.xml should look something like this -

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

<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>org.netjs.spring</groupId>
  <artifactId>SpringProj</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>
  
  <name>SpringProj</name>
  <url>http://maven.apache.org</url>

  <properties>
    <java.version>1.8</java.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <spring.version>5.0.4.RELEASE</spring.version>
  </properties>

  <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>javax.inject</groupId>
        <artifactId>javax.inject</artifactId>
        <version>1</version>
    </dependency>
    
    <!-- Spring JDBC Support -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
    </dependency>
    
   <!-- MySQL Driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.39</version>
    </dependency>
    
    <!--  Apache DBCP connection pool -->
    <dependency>
       <groupId>org.apache.commons</groupId>
       <artifactId>commons-dbcp2</artifactId>
       <version>2.1</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
  </dependencies>
</project>

Alternatively you can download the jars and add them to the class path.

Spring declarative transaction management JDBC example

Now the basic project structure is in place and we should create DB tables and classes for the project. In this Spring transaction management example we’ll have two DB tables employee and address and when employee record and employee’s address records are inserted with in a transaction, either both of them should be inserted correctly or none of them.

Database tables

Here is the table structure for both employee and address tables.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 
CREATE TABLE `address` (
  `id` int(11) NOT NULL,
  `address` varchar(20) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  KEY `id_idx` (`id`),
  CONSTRAINT `id` FOREIGN KEY (`id`) REFERENCES `employee` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

JDBC Data Source

For this example Apache DBCP is used and DB properties are read from a properties file.

Bean definition for data source is as follows-
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
    <property name="driverClassName" value = "${db.driverClassName}" />
    <property name="url" value = "${db.url}" />
    <property name="username" value = "${db.username}" />
    <property name="password" value = "${db.password}" />
    <property name="initialSize" value = "${pool.initialSize}" />
</bean>

db.properties file

db.driverClassName=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/netjs
db.username=
db.password=
pool.initialSize=5

Spring transaction management example – Java classes

The flow in the example is; first service bean is called which in turn calls a DAO class and from there the DB call.

The classes needed for this flow are as follows-

  1. Employee and Address Beans
  2. Service interface and Service implementation class
  3. DAO interface and DAO implementation class.
With all these classes project structure will look like as follows-
spring transaction management example

Employee.java

public class Employee {
 private int empId;
 private String empName;
 private int age;
 private Address address;
 public int getEmpId() {
  return empId;
 }
 public void setEmpId(int empId) {
  this.empId = empId;
 }
 public String getEmpName() {
  return empName;
 }
 public void setEmpName(String empName) {
  this.empName = empName;
 }
 public int getAge() {
  return age;
 }
 public void setAge(int age) {
  this.age = age;
 }
 public Address getAddress() {
  return address;
 }
 public void setAddress(Address address) {
  this.address = address;
 }
}

Address.java

public class Address {
 private int id;
 private String addrLine; 
 private String city;
 public int getId() {
  return id;
 }
 public void setId(int id) {
  this.id = id;
 }
 public String getAddrLine() {
  return addrLine;
 }
 public void setAddrLine(String addrLine) {
  this.addrLine = addrLine;
 }
 public String getCity() {
  return city;
 }
 public void setCity(String city) {
  this.city = city;
 } 
}

EmployeeDAO.java interface

public interface EmployeeDAO {
  public Employee findEmployee(int empId);
  public void insertEmployee(Employee emp);
}

EmployeeDAOImpl.java

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
    @Autowired
    private JdbcTemplate jdbcTemplate; 
    @Override
    public Employee findEmployee(int empId) {
        final String SELECT_BY_ID_QUERY = "select emp.id, name, age, address, city from employee emp, address adr "
                + "where emp.id = adr.id and emp.id = ?";
         return this.jdbcTemplate.queryForObject(SELECT_BY_ID_QUERY, new EmployeeMapper()
         , empId);
    }
    
    private static final class EmployeeMapper implements RowMapper<Employee> {
        public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
            Employee emp = new Employee();
            Address addr = new Address();
            emp.setEmpId(rs.getInt("id"));
            emp.setEmpName(rs.getString("name"));
            emp.setAge(rs.getInt("age"));
            addr.setAddrLine("address");
            addr.setCity("city");
            emp.setAddress(addr);
            return emp;
        }
    }
    
    @Override
    public void insertEmployee(Employee emp) {
         final String INSERT_EMP_QUERY = "insert into employee (id, name, age) values (?, ?, ?)";
         final String INSERT_ADDR_QUERY = "insert into address (id, address, city) values (?, ?, ?)";
         
         jdbcTemplate.update(INSERT_EMP_QUERY, emp.getEmpId(), emp.getEmpName(), emp.getAge());
         System.out.println("Employee record inserted");
         jdbcTemplate.update(INSERT_ADDR_QUERY, emp.getEmpId(), emp.getAddress().getAddrLine(), emp.getAddress().getCity());
         System.out.println("Employee address record inserted");

    }
}

In the EmployeeDAOImpl class there are two methods one for inserting records and one for finding an employee. Note that Spring jdbcTemplate is used here for data access. In the insertEmployee() method there are two insert queries one for inserting employee record and another one for address of the employee. If this method is running in a transaction and some thing goes wrong while inserting address record, employee record which is already inserted should also roll back.

EmployeeService.java interface

public interface EmployeeService {
  public Employee getEmployee(int empId);
  public void addEmployee(Employee emp);
}

EmployeeServiceImpl.java

import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.netjs.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional(propagation=Propagation.SUPPORTS,readOnly=true)
public class EmployeeServiceImpl implements EmployeeService {
 @Autowired
 private EmployeeDAO empDAO;

 @Override
 public Employee getEmployee(int empId) {
  return empDAO.findEmployee(empId);
 }

 @Override
 @Transactional(propagation=Propagation.REQUIRED,readOnly=false)
 public void addEmployee(Employee emp) {
  empDAO.insertEmployee(emp);
 }
}

As far as the Spring transaction management example goes this is the class where all the transaction related activities happen. As you can see @Transactional annotation is used for declarative transaction. For the whole class transaction management setting is to support transaction if exists and read only optimizations should be applied. In the method addEmployee() transaction settings are overridden and for this method transaction is required and read only is false. Default settings are used for roll back of the transaction where the roll back happens if RunTime exception is thrown.

Also notice the convenience of using Spring declarative transaction. You don’t need to write with in the code where transaction starts, where it ends and when to roll back, you just have to declare the transaction settings for the method and the Spring transaction management will take care of all the boiler plate code for transaction.

XML configuration for the Spring transaction management example

Here is the XML configuration for the example (appContext.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:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd
    http://www.springframework.org/schema/tx 
    http://www.springframework.org/schema/tx/spring-tx.xsd">
    
    <context:component-scan base-package="org.netjs.serviceImpl, org.netjs.DAOImpl" />
    <!--  For reading properties files --> 
    <context:property-placeholder location="classpath:config/db.properties" />
    
     <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value = "${db.driverClassName}" />
        <property name="url" value = "${db.url}" />
        <property name="username" value = "${db.username}" />
        <property name="password" value = "${db.password}" />
        <property name="initialSize" value = "${pool.initialSize}" />
    </bean>
    <!--  Transaction Manager --> 
    <bean id="txManager" class= "org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
        <property name="dataSource" ref="dataSource"></property>  
    </bean>  
    <tx:annotation-driven proxy-target-class="true" transaction-manager="txManager" />
</beans>

In the XML, Spring transaction management related configuration is listed below-

  1. You need to define the correct Transaction manager implementation class which in case of JDBC is org.springframework.jdbc.datasource.DataSourceTransactionManager.
  2. <tx:annotation-driven/> tag is required to let Spring container know that annotation is used for transaction.
  3. Internally @Transactional annotation uses AOP proxies and proxy-target-class attribute controls what type of transactional proxies are created for classes annotated with the @Transactional annotation. If the proxy-target-class attribute is set to true, then class-based proxies are created. If proxy-target-class is false or if the attribute is omitted, then standard JDK interface based proxies are created.
  4. xmlns:tx="http://www.springframework.org/schema/tx" is needed for tx namespace.
  5. Apart from that Datasource and JDBCTemplate are defined for connecting to DB and executing queries.
To run the Spring transaction management example you can use the following class.
public class App {

 public static void main(String[] args) {
   ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext
         ("appcontext.xml");   
   EmployeeService empService = context.getBean("employeeServiceImpl", EmployeeServiceImpl.class); 
   empService.addEmployee(createEmployee());
   context.registerShutdownHook();   
 }
 
 // Method to create Employee object
 private static Employee createEmployee(){
  Employee emp = new Employee();
  Address addr = new Address();
  emp.setEmpId(101);
  emp.setEmpName("John");
  emp.setAge(25);
  // same as employee ID
  addr.setId(emp.getEmpId());
  addr.setAddrLine("Hunters Lane");
  addr.setCity("Princeton");
  emp.setAddress(addr);
  return emp;
 }
}

Output

INFO: Loading XML bean definitions from class path resource [appcontext.xml]
Aug 03, 2018 2:11:56 PM org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor <init>
INFO: JSR-330 'javax.inject.Inject' annotation found and supported for autowiring
Employee record inserted
Employee address record inserted
Aug 03, 2018 2:11:58 PM org.springframework.context.support.AbstractApplicationContext doClose

For checking that full transaction is rolled back or not, you can give some value while inserting address record which causes error. That should roll back the insertion to employee record too.

That's all for this topic Spring Transaction Management JDBC Example Using @Transactional Annotation. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Spring Tutorial Page


Related Topics

  1. Data Access in Spring Framework
  2. Insert\Update Using NamedParameterJDBCTemplate in Spring Framework
  3. Autowiring Using Annotations in Spring
  4. Autodiscovery of Bean Using componenent-scan in Spring
  5. Spring Example Program Using JavaConfig And Annotations

You may also like-

  1. Using Spring Profiles to Switch Environment
  2. Difference Between component-scan And annotation-config in Spring
  3. Excluding Bean From Autowiring in Spring
  4. registerShutdownHook() Method in Spring Framework
  5. Invoking Getters And Setters Using Reflection - Java Program
  6. Deadlock in Java Multi-Threading
  7. BigDecimal in Java
  8. YARN in Hadoop