Spring JDBC Tutorial with Transaction Management

I recently wrote a post on Spring JDBC with some good and positive feedback from the user community.

In this posting, I will present a more in-depth tutorial and a data access strategy that I use for projects accessing a single data source or database. This tutorial makes use of Spring 2.5.6

In this tutorial, I use MySQL as the data source.
Follow the installation instructions and make note of your username and password.



REQUIRED LIBRARIES
Next, download all the necessary libraries needed to execute this tutorial:

• JDBC driver for MySQL, MySQL Connector/J.
• Spring Framework 2.5.6
• Commons Project
  
  • commons-beanutils-1.8.2
  • commons-collections-3.2.1
  • commons-configuration-1.6
  • commons-dbcp-1.2.2
  • commons-digester-2.0
  • commons-lang-2.4
  • commons-logging-1.1.1
  • commons-pool-1.5.4

PACKAGE STRUCTURE
Next, we'll define our package structure for the data project and we provide a description for each package.

• com.edwardwebnerd.persistence
  
  • adapter - single SQL operations and implementations.
  • dbcp - database connection pool and configurations.
  • domain - domain models
  • exception - exception classes
  • transmanager - business level services

These are very rudimentary package definitions but they do provide us with some structure and conventions in our development.


CREATE THE DATABASE TABLE
First, we'll create a user table in our database. The table is very simple and only meant for illustrative purposes.

CREATE TABLE user (
 id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),
 username VARCHAR(30),
 email VARCHAR(30),
 password VARCHAR(50)
)

We'll provide Spring JDBC access the user table on a single access level, within the adapter package. After that, we'll introduce Spring JDBC transaction management facilities within the transmanager package to provide data services which may be comprised of one or more calls to the adapter package.


CREATE YOUR DOMAIN MODEL
Next, generate the domain model of the user table you just created.

package com.edwardwebnerd.persistence.domain;

/** User Domain Model */
public class User {

 private Integer id;
 public Integer getId() { return id; }
 public void setId(Integer id) { this.id = id; }
 
 private String username;
 public String getUsername() { return username; }
 public void setUsername(String username) { this.username = username; }
 
 private String password;
 public String getPassword() { return password; }
 public void setPassword(String password) { this.password = password; }
 
 private String email;
 public String getEmail() { return email; }
 public void setEmail(String email) { this.email = email; }
}

CREATE YOUR DAO INTERFACE
Next, we create our DAO interface, providing definitions of our single access calls and services to the user table.

package com.edwardwebnerd.persistence.adapter;

import java.util.List;

import com.edwardwebnerd.persistence.domain.User;

public interface UserServiceAdapter {

 public User getByUsername(String username);
 
 public User getByEmail(String username);
 
 public User getById(Integer id);
 
 public void createUser(User user);
 
 public void updateUser(User user);
 
 public void deleteUser(String username);
 
 public List<User> getUsers(Integer pageNumber, Integer pageLength, String sortCriterion, String sortOrder);
 
 public Integer getNumberOfUsers();
}

IMPLEMENT YOUR DAO
Next, we implement our DAO interface using Spring JDBC in a subpackage called springjdbc off of the adapter package.

package com.edwardwebnerd.persistence.adapter.springjdbc;

import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.edwardwebnerd.persistence.adapter.UserServiceAdapter;
import com.edwardwebnerd.persistence.adapter.springjdbc.mapper.UserMapper;
import com.edwardwebnerd.persistence.domain.User;

public class UserServiceAdapterImpl extends JdbcDaoSupport implements UserServiceAdapter {

 @Override
 public User getByUsername(String username) {
  try{
   return (User) getJdbcTemplate().queryForObject(
     "SELECT * FROM user WHERE username = ?", 
     new Object [] {username}, 
     new UserMapper());
  }catch(EmptyResultDataAccessException erdae){
   return null;
  }
 }
 
 
 @Override
 public User getByEmail(String email) {
  try{
   return (User) getJdbcTemplate().queryForObject(
     "SELECT * FROM user WHERE email = ?", 
     new Object [] {email}, 
     new UserMapper());
  }catch(EmptyResultDataAccessException erdae){
   return null;
  }
 }
 
 
 @Override
 public User getById(Integer id) {
  try{
   return (User) getJdbcTemplate().queryForObject(
     "SELECT * FROM user WHERE id = ?", 
     new Object [] {id}, 
     new UserMapper());
  }catch(EmptyResultDataAccessException erdae){
   return null;
  }
 }
 
 
 @Override
 public void createUser(User user) {
  getJdbcTemplate().update(
    "INSERT INTO user (username, email, password) VALUES (?, ?, ?)",
    new Object[] {user.getUsername(), user.getEmail(), user.getPassword()});
 }

 
 @Override
 public void deleteUser(String username) {
  getJdbcTemplate().update(
    "DELETE FROM user WHERE username = ?",
    new Object[] {username});
 }
 

 @Override
 public void updateUser(User user) {
  getJdbcTemplate().update(
    "UPDATE user SET username = ?, email = ?, password = ? WHERE id = ?",
    new Object[] {user.getUsername(), user.getEmail(), user.getPassword(), user.getId()});
 }


 @SuppressWarnings("unchecked")
 @Override
 public List<User> getUsers(Integer pageNumber, Integer pageLength, String sortCriterion, String sortOrder) {
  StringBuffer query = new StringBuffer("SELECT * FROM user");
  if(sortCriterion != null && sortOrder != null)
   query.append(" ORDER BY ").append(sortCriterion).append(" ").append(sortOrder);
  
  query.append(" LIMIT ").append(pageLength).append(" OFFSET ").append((pageNumber-1) * pageLength);
  return getJdbcTemplate().query(query.toString(), new UserMapper());
 }


 @Override
 public Integer getNumberOfUsers() {
  return getJdbcTemplate().queryForInt("SELECT COUNT(*) FROM user");
 }
}

There are a few things to note about the Spring JDBC implementation:

• The class extends org.springframework.jdbc.core.support.JdbcDaoSupport - a convenient superclass for JDBC data access objects, which requires a DataSource to be set, which we will set in our config files.
• Most of the methods in this implementation use a RowMapper implementation, to help transfer data from the ResultSet to the Domain Model. We will provide the RowMapper implementation below.

Below, we provide an implementation of the RowMapper interface for our User table and domain model.

package com.edwardwebnerd.persistence.adapter.springjdbc.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.edwardwebnerd.persistence.domain.User;

public class UserMapper implements RowMapper{

 @Override
 public Object mapRow(ResultSet resultSet, int rowNum) throws SQLException {
  User user = new User();
  user.setEmail(resultSet.getString("email"));
  user.setUsername(resultSet.getString("username"));
  user.setPassword(resultSet.getString("password"));
  user.setId(resultSet.getInt("id"));
  return user;
 } 
}

DEFINE THE USER MANAGER INTERFACE
We use the term 'Manager' to dictate which portion of the package applications should access.
Next, define the User Manager Interface.

package com.edwardwebnerd.persistence.transmanager;

import java.util.List;

import org.springframework.transaction.annotation.Transactional;

import com.edwardwebnerd.persistence.domain.User;
import com.edwardwebnerd.persistence.exception.UserEmailAlreadyExistsException;
import com.edwardwebnerd.persistence.exception.UsernameAlreadyExistsException;

@Transactional
public interface UserManager {
 
 public User getUserByUsername(String username);
 
 public User getUserByEmail(String email);
 
 public User getUserById(Integer id);
 
 public void createUser(User user) 
  throws UsernameAlreadyExistsException, UserEmailAlreadyExistsException;
 
 public void updateUser(User user);
 
 public List<User> getUsers(Integer pageNumber, Integer pageLength, String sortCriterion, String sortOrder);
 
 public Integer getNumberOfUsers();
}

There are a few things to note about interface:

• We use the @Transactional annotation to denote that all methods and implementation should be demarcated at the method level. This annotation also needs to be coupled with a spring configuration which will be provided below.
• We defined a couple of exception classes that our implementations should throw up to the application tier on certain cases. We'll provide the implementations below.

Below we provide the implementation of both exception classes utilized in our UserManager interface.

package com.edwardwebnerd.persistence.exception;

public class UserEmailAlreadyExistsException extends DataAccessException{

 private static final long serialVersionUID = 1L;

 public UserEmailAlreadyExistsException(String email){
  super("A user with the email " + email + " already exists");
 }
}

package com.edwardwebnerd.persistence.exception;

public class UsernameAlreadyExistsException extends DataAccessException{

 private static final long serialVersionUID = 1L;

 public UsernameAlreadyExistsException(String username){
  super("A user with the username " + username + " already exists");
 }
}

Both of these exceptions extend our base DataAccessException class.

package com.edwardwebnerd.persistence.exception;

import java.io.PrintStream;
import java.io.PrintWriter;

public class DataAccessException extends Exception {

 private static final long serialVersionUID = 1L;
 
 
 /** A wrapped Throwable */
 protected Throwable cause;

 public DataAccessException() { super("A data exception occurred"); }

 public DataAccessException(String message) { super(message); }

 public DataAccessException(String message, Throwable cause) {
  super(message);
  this.cause = cause;
 }
 

 public Throwable getCause()  { return cause; }
 
 public Throwable initCause(Throwable cause) {
  this.cause = cause;
  return cause;
 }

 
 /** Builds the message off of its own message + all nested exception messages */
 public String getMessage() {
  // Get the message for this exception
  String msg = super.getMessage();

  
  // Get the message for each nested exception
  Throwable parent = this;
  Throwable child;
  while((child = parent.getCause()) != null) {
   String msg2 = child.getMessage();

   if (msg2 != null) {
    if (msg != null) { msg += ": " + msg2; } 
    else { msg = msg2; }
   }

   if (child instanceof DataAccessException) { break; }
   
   parent = child;
  }
  return msg;
 }

 
 /** Prints the stack trace + all nested stack traces */
 public void printStackTrace() {
  // Print the stack trace for this exception.
  super.printStackTrace();
  
  // Print the stack trace for each nested exception.
  Throwable parent = this;
  Throwable child;
  while((child = parent.getCause()) != null) {
   if (child != null) {
    System.err.print("Caused by: ");
    child.printStackTrace();

    if (child instanceof DataAccessException) { break; }
                   
    parent = child;
   }
  }
 }

 
 /** Prints the stack trace + all nested stack traces */
 public void printStackTrace(PrintStream s) {
  // Print the stack trace for this exception.
  super.printStackTrace(s);
  
  Throwable parent = this;
  Throwable child;

  // Print the stack trace for each nested exception.
  while((child = parent.getCause()) != null) {
   if (child != null) {
    s.print("Caused by: ");
    child.printStackTrace(s);

    if (child instanceof DataAccessException) { break; }
    
    parent = child;
   }
  }
 }


 /** Prints the stack trace + all nested stack traces */
 public void printStackTrace(PrintWriter w) {
  // Print the stack trace for this exception.
  super.printStackTrace(w);

  Throwable parent = this;
  Throwable child;

  // Print the stack trace for each nested exception.
  while((child = parent.getCause()) != null) {
   if (child != null) {
    w.print("Caused by: ");
    child.printStackTrace(w);

    if (child instanceof DataAccessException) {
     break;
    }
    parent = child;
   }
  }
 }
}

IMPLEMENT YOUR MANAGER
Next, we implement the User Manager interface using Spring JDBC, in a subpackage called springjdbc off of the transmanager package.

package com.edwardwebnerd.persistence.transmanager.springjdbc;

import java.util.List;

import com.edwardwebnerd.persistence.adapter.UserServiceAdapter;
import com.edwardwebnerd.persistence.domain.User;
import com.edwardwebnerd.persistence.exception.UserEmailAlreadyExistsException;
import com.edwardwebnerd.persistence.exception.UsernameAlreadyExistsException;
import com.edwardwebnerd.persistence.transmanager.UserManager;

public class UserManagerImpl implements UserManager {

 private UserServiceAdapter userServiceAdapter = null;
 public void setUserServiceAdapter(UserServiceAdapter userServiceAdapter){
  this.userServiceAdapter = userServiceAdapter;
 }
 
 @Override
 public void createUser(User user) 
  throws UsernameAlreadyExistsException, UserEmailAlreadyExistsException
 {
  User usernameTest = userServiceAdapter.getByUsername(user.getUsername());
  if(usernameTest != null){
   throw new UsernameAlreadyExistsException(user.getUsername());
  }
  
  User userEmailTest = userServiceAdapter.getByEmail(user.getEmail());
  if(userEmailTest != null){
   throw new UserEmailAlreadyExistsException(user.getEmail());
  }
  
  userServiceAdapter.createUser(user);
  userServiceAdapter.createUser(null);
 }
 
 @Override
 public List getUsers(Integer pageNumber, Integer pageLength, String sortCriterion, String sortOrder) {
  return userServiceAdapter.getUsers(pageNumber, pageLength, sortCriterion, sortOrder);
 }

 @Override
 public User getUserByUsername(String username) {
  return userServiceAdapter.getByUsername(username);
 }
 
 @Override
 public User getUserByEmail(String email) {
  return userServiceAdapter.getByEmail(email);
 }
 
 @Override
 public User getUserById(Integer id) {
  return userServiceAdapter.getById(id);
 }

 @Override
 public void updateUser(User user) {
  userServiceAdapter.updateUser(user);
 }

 @Override
 public Integer getNumberOfUsers() {
  return userServiceAdapter.getNumberOfUsers();
 }
}

Most of the methods in the manager are just single access operations. As a result many of the implementations are straight mappings to UserServiceAdapter.

Note the last two line in the implementation of the createUser implementation.

userServiceAdapter.createUser(user);
userServiceAdapter.createUser(null);

The very last call will throw a NullPointerException. Any call to this method will always rollback the user created in the second to last call. We'll leave it up to you to test and verify this.


SPRING CONFIGURATIONS
We are now ready to wire our services together using spring.

In almost every environment, there is always a need to support test and production modes. We handle this using a property we set at runtime. Based on the environment property variable 'env' we will load a set of properties file, which will point us to our test or production resources, based on the value set. The property is easily passed during execution through the -D argument. For the property env we accept qa and production as valid values.

Below we provide our spring configuration file, which is stored in our com.edwardwebnerd.persistence.dbcp package.

<?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:tx="http://www.springframework.org/schema/tx"
 xsi:schemaLocation="http://www.springframework.org/schema/beans 
  http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
  http://www.springframework.org/schema/tx
  http://www.springframework.org/schema/tx/spring-tx.xsd
">

 <!--
  You must use a -D JVM level option.
  There are two configurations supported.
    -Denv=qa
    -Denv=production
  based on the configuration loaded the appropriate data source will be loaded. 
 -->
 <bean id="DataAccessProperties" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
  <property name="location" value="classpath:com/edwardwebnerd/persistence/dbcp/${env}.properties"/>
     </bean>
     
 <bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
  <!-- <property name="driverClassName" value="com.ibm.as400.access.AS400JDBCDriver"/> -->
  <property name="driverClassName" value="${jdbc.driver}"/>
  <property name="url" value="${jdbc.url}"/>
  <property name="username" value="${jdbc.username}"/>
  <property name="password" value="${jdbc.password}"/>
  <property name="maxActive" value="30"/>
  <property name="maxIdle" value="2"/>
  <property name="maxWait" value="5000"/>
 </bean>
 
 <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  <property name="dataSource" ref="dataSource"/>
 </bean>
 
 <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="false"/>
 
 <bean id="userManager" class="com.edwardwebnerd.persistence.transmanager.springjdbc.UserManagerImpl">
  <property name="userServiceAdapter" ref="userServiceAdapter"/>
 </bean>
 
 <bean id="userServiceAdapter" class="com.edwardwebnerd.persistence.adapter.springjdbc.UserServiceAdapterImpl">
  <property name="dataSource" ref="dataSource"/>
 </bean>
</beans>

Here we see that we wire together our data sources with our service adapter and manager. There are also some configurations that are required to support the @Transactional annotation used in the UserManager interface. Below, we'll provide an example of one of our properties file located in the com.edwardwebnerd.persistence.dbcp pacakage.

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?autoReconnect=true
jdbc.username=root
jdbc.password=admin

We'll leave it up to you, to test the project above. In our next tutorial, we will expose these services on our web tier, strictly for illustrative purposes.

Please feel free to leave any comments or suggestions on how the code above can be improved. I'll be sure to answer them.