Spring JDBC transactions example

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

Configure Maven to get the required Spring 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:

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:

CREATE TABLE USER (
  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  USERNAME VARCHAR (32) NOT NULL,
  NAME VARCHAR (64) NOT NULL,
  UNIQUE (USERNAME)
);

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

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:

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

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).

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.

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

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.

Now we define the configuration file used for this example:

<?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.

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

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:

Remember that the Driver should be in the application classpath.

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

• InvalidIsolationLevelException: Standard JPA does not support custom isolation levels - use a special JpaDialect for your JPA implementation
• Spring - Change transaction isolation level example
• Spring + JPA + Hibernate example
• Spring JTA multiple resource transactions in Tomcat with Atomikos example