Spring JDBC transactions example

29 December 2012
By Gonçalo Marques
In this tutorial you will learn how to implement JDBC transactions using the Spring framework.

Introduction

In this tutorial we will explore one of the most powerful Spring features: The transaction management. At the end of this tutorial we will have used the JDBC Transaction Manager in a declarative way so Spring manages all transaction related boilerplate synchronization for us.

Comprehensive documentation on this subject can be found at the official Spring website.

This tutorial considers the following software and environment:

  1. Ubuntu 12.04
  2. Maven 3.0.4
  3. JDK 1.7.0.09
  4. Spring 3.2.0
  5. MySQL 5.5.28

Configuration

Configure Maven to get the required Spring dependencies:

Maven pom.xml file referencing required dependencies
<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.byteslounge.spring.tx</groupId>
  <artifactId>com-byteslounge-spring-tx</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>com-byteslounge-spring-tx</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <!-- Define Spring version as a constant -->
    <spring.version>3.2.0.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>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>${spring.version}</version>
    </dependency>
    
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${spring.version}</version>
    </dependency>
    
    <dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>1.2.2</version>
    </dependency>

  </dependencies>
</project>

Now place yourself in the project directory and issue the following command to prepare your project for Eclipse:

mvn eclipse:eclipse

After conclusion you can import the project into Eclipse.


This tutorial will not focus on how to configure a MySQL instance or database but will consider the following table:

MySQL table used in this example
CREATE TABLE USER (
  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  USERNAME VARCHAR (32) NOT NULL,
  NAME VARCHAR (64) NOT NULL,
  UNIQUE (USERNAME)
);

Model and DAO

We will need a simple Java class to represent USER table information. This class will be the model for this example.

User.java class
package com.byteslounge.spring.tx.model;

public class User {

  private int id;
  private String username;
  private String name;
	
  public int getId() {
    return id;
  }
	
  public void setId(int id) {
    this.id = id;
  }

  public String getUsername() {
    return username;
  }
	
  public void setUsername(String username) {
    this.username = username;
  }
	
  public String getName() {
    return name;
  }
	
  public void setName(String name) {
    this.name = name;
  }
	
}

Now we define our DAO interface and implementation:

DAO interface
package com.byteslounge.spring.tx.dao;

import java.util.List;

import com.byteslounge.spring.tx.model.User;

public interface UserDAO {

  void insertUser(User user);
	
  User getUser(String username);
	
  List<User> getUsers();
}



DAO implementation
package com.byteslounge.spring.tx.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Service;

import com.byteslounge.spring.tx.dao.UserDAO;
import com.byteslounge.spring.tx.model.User;

@Service
public class UserDAOImpl extends JdbcDaoSupport implements UserDAO {

  @Autowired
  public UserDAOImpl(DataSource dataSource) {
    setDataSource(dataSource);
  }
  
  @Override
  public void insertUser(User user) {
    getJdbcTemplate().update(
      "INSERT INTO USER (USERNAME, NAME) VALUES (?, ?)",
        new Object[] {
        user.getUsername(),
        user.getName()
      }
    );
  }

  @Override
  public User getUser(String username) {
    User user = getJdbcTemplate().
      queryForObject("SELECT * FROM USER WHERE USERNAME = ?",
      new Object[] { username },
      new UserMapper()
      );
    return user;
  }
  
  @Override
  public List<User> getUsers() {
    List<User> users = getJdbcTemplate().
      query("SELECT * FROM USER",
      new UserMapper()
      );
    return users;
  }
  
  private class UserMapper implements RowMapper<User>{

    @Override
    public User mapRow(ResultSet rs, int rowNum)
        throws SQLException {
      User user = new User();
      user.setId(rs.getInt("ID"));
      user.setUsername(rs.getString("USERNAME"));
      user.setName(rs.getString("NAME"));
      return user;
    }
    
  }

}

We are basically defining three operations that will be executed over our example USER table: Insert a new user, fetching a user by its username and fetching all users. There are a couple of things to note here. The first is the @Service annotation. This DAO will be injected by the Spring container into another managed bean. The second is that we are extending JdbcDaoSupport. This Spring class represents an abstraction layer around JDBC so we don't need to implement the JDBC boilerplate code ourselves but we delegate this wiring to Spring instead. JdbcDaoSupport needs a Datasource so we also inject it using @Autowired annotation at the constructor level (the Datasource will be configured later as a managed bean in Spring configuration file).

The Service bean

Now we need to define the actual service bean that will make use of the DAO we previously defined. You usually implement your business logic in this layer: The service layer. Since this is a very simple example the service layer will just make use of the DAO to interact with the Database and return the results directly to the caller.

Service interface
package com.byteslounge.spring.tx.user;

import java.util.List;

import com.byteslounge.spring.tx.model.User;

public interface UserManager {

  void insertUser(User user);
	
  User getUser(String username);
	
  List<User> getUsers();
}



Service implementation
package com.byteslounge.spring.tx.user.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.byteslounge.spring.tx.dao.UserDAO;
import com.byteslounge.spring.tx.model.User;
import com.byteslounge.spring.tx.user.UserManager;

@Service
public class UserManagerImpl implements UserManager {

  @Autowired
  private UserDAO userDAO;
  
  @Override
  @Transactional
  public void insertUser(User user) {
    userDAO.insertUser(user);
  }

  @Override
  public User getUser(String username) {
    return userDAO.getUser(username);
  }

  @Override
  public List<User> getUsers() {
    return userDAO.getUsers();
  }

}

As we have already stated before it should be in this service layer that the business logic would be implemented. In this simple example we are just using the DAO to interact with the Database and return the results to the caller. Things to note in this class: The service implementation is annotated with @Service which means that this will be a bean managed by Spring. UserDAO is annotated with @Autowired so it will be injected by the Spring container. insertUser(User user) method is annotated with @Transactional so every operations that occur inside this method will be executed in a transactional way by Spring JDBC Transaction Manager.

Spring configuration file

Now we define the configuration file used for this example:

Spring XML configuration file
<?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-3.0.xsd
  http://www.springframework.org/schema/context
  http://www.springframework.org/schema/context/spring-context-3.0.xsd 
  http://www.springframework.org/schema/tx 
  http://www.springframework.org/schema/tx/spring-tx.xsd">

  <tx:annotation-driven />
  
  <context:component-scan 
      base-package="com.byteslounge.spring.tx.dao.impl" />
  <context:component-scan 
      base-package="com.byteslounge.spring.tx.user.impl" />

  <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/TEST" />
    <property name="username" value="testuser" />
    <property name="password" value="testpasswd" />
  </bean>

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

</beans>


Important things to note in the configuration file: We define a datasource bean pointing to our MySQL instance. This datasource bean will be used in our DAO as we have seen previously. There is also a transactionManager bean. This bean is the Spring JDBC transaction manager that will handle transaction related boilerplate code and wiring for us. tx:annotation-driven element defines that we are declaring transactions using annotations in our classes (remember @Transactional annotations in our service layer?). Finally we define the packages where Spring should look for beans using context:component-scan elements.

Note: In this example we used MySQL as the data repository so we need to specify the correct MySQL Driver in the dataSource bean. This Driver must be in the application classpath when you run your application. Drivers can be usually found in the respective vendor websites. In our case we got it from MySQL website.

Testing the application

Let's create a simple class to test our example:

Simple Main testing class
package com.byteslounge.spring.tx;

import java.util.List;

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

import com.byteslounge.spring.tx.model.User;
import com.byteslounge.spring.tx.user.UserManager;

public class Main 
{
  public static void main( String[] args ) {

    ApplicationContext ctx = 
        new ClassPathXmlApplicationContext("spring.xml");
    UserManager userManager = 
        (UserManager) ctx.getBean("userManagerImpl");
   
    User user = new User();
    user.setUsername("johndoe");
    user.setName("John Doe");
   
    userManager.insertUser(user);
   
    System.out.println("User inserted!");
   
    user = userManager.getUser("johndoe");
   
    System.out.println("\nUser fetched!"
    + "\nId: " + user.getId()
    + "\nUsername: " + user.getUsername()
    + "\nName: " + user.getName());
   
    List<User> users = userManager.getUsers();
   
    System.out.println("\nUser list fetched!"
     + "\nUser count: " + users.size());

  }
}

When we run our test the following output will be generated:

User inserted!

User fetched!
Id: 1
Username: johndoe
Name: John Doe

User list fetched!
User count: 1


Remember that the Driver should be in the application classpath.

This tutorial source code can be found at the end of this page.

Download source code from this article

Related Articles

Comments

About the author
Gonçalo Marques is a Software Engineer with several years of experience in software development and architecture definition. During this period his main focus was delivering software solutions in banking, telecommunications and governmental areas. He created the Bytes Lounge website with one ultimate goal: share his knowledge with the software development community. His main area of expertise is Java and open source.

GitHub profile: http://github.com/gonmarques

He is also the author of the WiFi File Browser Android application: