Spring Transaction Management with Jdbc Template Example

Faisal / Java,Spring

Transaction Management in simple terms means inserting / updating related data in the tables without loss. For example, we want to insert the data into 2 or 3 tables which are dependent on one another, then in such a case, it is very important to keep in mind data consistency. Every table needs to be updated without error, and if any table does not get updated due to any technical reasons whatsoever, then a rollback (removing inserted data) should be performed quickly.

In Spring, the framework allows us to achieve such feature very easily, which we can see in following example.

We will be using Spring JdbcTemplate with datasouce to insert data in 2 related tables (employee and address).

Before we jump into our Spring project, let’s do some database setup for our use.

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

CREATE TABLE `employee` (

`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;

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;

Now let’s create a simple maven java project in Netbeans IDE, any IDE you are comfortable with. Our final project structure will look like below.

project_structure

We will be using spring jdbc API, which will be handled by spring-jdbc dependency injection. Along with spring core and mysql database dependencies. Let’s look at the pom.xml file.

Spring Maven Dependencies

<?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>com.faisal</groupId>
    <artifactId>JdbcTemplate</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.7</maven.compiler.source>
        <maven.compiler.target>1.7</maven.compiler.target>
        
        <spring.version>4.0.4.RELEASE</spring.version>
        <!-- Logging -->
        <logback.version>1.0.13</logback.version>
        <slf4j.version>1.7.5</slf4j.version>
    </properties>
    
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        
        <!-- Spring and Transactions -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- Spring JDBC and MySQL Driver -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.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>
        
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

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

<groupId>com.faisal</groupId>

<artifactId>JdbcTemplate</artifactId>

<version>1.0-SNAPSHOT</version>

<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

<maven.compiler.source>1.7</maven.compiler.source>

<maven.compiler.target>1.7</maven.compiler.target>

<spring.version>4.0.4.RELEASE</spring.version>

<logback.version>1.0.13</logback.version>

<slf4j.version>1.7.5</slf4j.version>

</properties>

<groupId>org.springframework</groupId>

<artifactId>spring-core</artifactId>

<version>${spring.version}</version>

</dependency>

<groupId>org.springframework</groupId>

<artifactId>spring-context</artifactId>

<version>${spring.version}</version>

</dependency>

<groupId>org.springframework</groupId>

<artifactId>spring-tx</artifactId>

<version>${spring.version}</version>

</dependency>

<groupId>org.springframework</groupId>

<artifactId>spring-jdbc</artifactId>

<version>${spring.version}</version>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

<version>${slf4j.version}</version>

<scope>compile</scope>

</dependency>

<groupId>ch.qos.logback</groupId>

<artifactId>logback-classic</artifactId>

<version>${logback.version}</version>

<scope>runtime</scope>

</dependency>

<groupId>junit</groupId>

<artifactId>junit</artifactId>

</dependency>

</dependencies>

</project>

Entity Model Classes

We have 2 classes, Employee and Address according to our database.

Employee.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.faisal.jdbc.model;

/**
 *
 * @author hotplugin
 */
public class Employee {

    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;
    }

}

* To change this license header, choose License Headers in Project Properties.

* To change this template file, choose Tools | Templates

* and open the template in the editor.

package com.faisal.jdbc.model;

/**

* @author hotplugin

public class Employee {

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;

}

Address.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.faisal.jdbc.model;

/**
 *
 * @author hotplugin
 */
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;
    }

}

* To change this license header, choose License Headers in Project Properties.

* To change this template file, choose Tools | Templates

* and open the template in the editor.

package com.faisal.jdbc.model;

/**

* @author hotplugin

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;

}

Now we need our DAO and IMPL classes.

EmployeeDaoImpl.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.faisal.jdbc.dao;

import com.faisal.jdbc.model.Employee;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author hotplugin
 */
public class EmployeeDAOImpl implements EmployeeDAO {

    private DataSource dataSource;

     // setter injection of datasource
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    @Transactional
    public void create(Employee employee) {

        String queryEmployee = "insert into Employee (id, name) values (?,?)";
        String queryAddress = "insert into Address (id, address,country) values (?,?,?)";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        jdbcTemplate.update(queryEmployee, new Object[]{employee.getId(),
            employee.getName()});
        System.out.println("Inserted into Employee Table");
        
        
        jdbcTemplate.update(queryAddress, new Object[]{employee.getId(),
            employee.getAddress().getAddress(),
            employee.getAddress().getCountry()});
        System.out.println("Inserted into Address Table");

    }

}

* To change this license header, choose License Headers in Project Properties.

* To change this template file, choose Tools | Templates

* and open the template in the editor.

package com.faisal.jdbc.dao;

import com.faisal.jdbc.model.Employee;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.transaction.annotation.Transactional;

/**

* @author hotplugin

public class EmployeeDAOImpl implements EmployeeDAO {

private DataSource dataSource;

// setter injection of datasource

public void setDataSource(DataSource dataSource) {

this.dataSource = dataSource;

}

@Override

@Transactional

public void create(Employee employee) {

String queryEmployee = "insert into Employee (id, name) values (?,?)";

String queryAddress = "insert into Address (id, address,country) values (?,?,?)";

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

jdbcTemplate.update(queryEmployee, new Object[]{employee.getId(),

employee.getName()});

System.out.println("Inserted into Employee Table");

jdbcTemplate.update(queryAddress, new Object[]{employee.getId(),

employee.getAddress().getAddress(),

employee.getAddress().getCountry()});

System.out.println("Inserted into Address Table");

}

EmployeeDAO.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.faisal.jdbc.dao;

import com.faisal.jdbc.model.Employee;


/**
 *
 * @author hotplugin
 */
public interface EmployeeDAO {

    public void create(Employee customer);
}

* To change this license header, choose License Headers in Project Properties.

* To change this template file, choose Tools | Templates

* and open the template in the editor.

package com.faisal.jdbc.dao;

import com.faisal.jdbc.model.Employee;

/**

* @author hotplugin

public interface EmployeeDAO {

public void create(Employee customer);

}

Now final Run Class to test it all out:

Test.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.faisal.jdbctemplate;

import com.faisal.jdbc.dao.EmployeeDAOImpl;
import com.faisal.jdbc.model.Address;
import com.faisal.jdbc.model.Employee;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 *
 * @author hotplugin
 */
public class Test {
    
    public static void main(String[] args) {
        ClassPathXmlApplicationContext cpac = new ClassPathXmlApplicationContext("spring.xml");
        EmployeeDAOImpl impl = cpac.getBean("employeeDAO",EmployeeDAOImpl.class);
        Employee employee = createDummyEmployee();
        impl.create(employee);
    }
    
    private static Employee createDummyEmployee() {
		Employee employee = new Employee();
		employee.setName("faisal");
		Address address = new Address();
		address.setCountry("Nepal");
		address.setAddress("newroad ");
		employee.setAddress(address);
		return employee;
	}
}

* To change this license header, choose License Headers in Project Properties.

* To change this template file, choose Tools | Templates

* and open the template in the editor.

package com.faisal.jdbctemplate;

import com.faisal.jdbc.dao.EmployeeDAOImpl;

import com.faisal.jdbc.model.Address;

import com.faisal.jdbc.model.Employee;

import org.springframework.context.support.ClassPathXmlApplicationContext;

/**

* @author hotplugin

public class Test {

public static void main(String[] args) {

ClassPathXmlApplicationContext cpac = new ClassPathXmlApplicationContext("spring.xml");

EmployeeDAOImpl impl = cpac.getBean("employeeDAO",EmployeeDAOImpl.class);

Employee employee = createDummyEmployee();

impl.create(employee);

}

private static Employee createDummyEmployee() {

Employee employee = new Employee();

employee.setName("faisal");

Address address = new Address();

address.setCountry("Nepal");

address.setAddress("newroad ");

employee.setAddress(address);

return employee;

}

springJdbcTemplate 6.09 KB

Download Source Code