Data AccessCore JavaApp FrameworksViewTestingBuildingDeploymentDev ToolsSecurityOpinions

Monday, January 11, 2010

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:


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.

20 comments:

  1. sql injection possible in getUsers()

    ReplyDelete
    Replies
    1. Thiên mà tu luyện, hiệu quả quả nhiên là tốt hơn rất nhiều, Nhạc Thành cảm nhận linh khí ở đây vô cùng nồng đậm.

      Mang thẻ Linh cắm vào, Nhạc Thành bày ra một cấm chế rồi khoanh chân ngồi xuống, lẩm bẩm nói:

      - Nửa năm sau không biết ta có thể đột phá tới Tích Cốc hậu kỳ không.

      Không suy nghĩ nhiều nữa, Nhạc Thành liền đắm chìm trong tu luyện, vận chuyển Hỗn Nguyên Chân Kinh tâm pháp, nồng đậm linh khí nhanh chóng chuyển thành chân khí tinh thuần nhất đầu nhập vào trong đan điền của Nhạc Thành.
      đồng tâm
      game mu
      cho thuê phòng trọ
      cho thuê phòng trọ
      nhac san cuc manh
      tư vấn pháp luật qua điện thoại
      văn phòng luật
      số điện thoại tư vấn luật
      dịch vụ thành lập doanh nghiệp
      Trong đan điền của Nhạc Thành không ngừng hấp thu chân khí.

      Cứ như vậy, Nhạc Thành hoàn toàn tiến nhập vào trong trạng thái tu luyện, bây giờ Nhạc Thành đã trở thành một hài nhi cực đói, những linh khí kia giống như là sữa tươi của mẫu thân vậy.

      Hôm nay đã là ngày thứ ba Nhạc Thành bế quan, Khiếu Thiếu Hổ hiện tại đã

      Delete
  2. Thanks for the comment.

    I would say that sql injection is not prevented in the data tier, but should be prevented at the application or web tier that interfaces with the data tier.

    So if you had a login form in a web application, your application should prevent sql injection at the application tier and not the data tier as another application may be accessing the service in a different way.

    ReplyDelete
  3. (Not putting you down or anything - just trying to learn other approaches.)
    I don't think sql injection validation belongs in the web app or anywhere else except the dao.
    1) The code that knows all the specific escape characters/reserved words for a particular database is the jdbc driver itself(via prepared staements)
    2) You'd make your form/ui/web/sevice tier aware of database specifics.

    ReplyDelete
    Replies
    1. You can appreciate lot of things in this article than finding trivial issues and complaining abt that

      Delete
  4. Not a problem at all and no offense taken. Your participation is greatly appreciated and to answer your question....

    Spring JDBC essentially uses PreparedStatement to execute all SQL using JDBC. It would be a violation if I had concatenated portions of the string with parameter input to build the SQL. Using PreparedStatements is the primary defense option we have against SQL injection.

    Another option would be to essentially escape all bad character sequences, but that really is a last option and utilized primarily when you have an existing app and do not want to refactor all your data access code. It also becomes an issue when your application needs to be SQL agnostic.

    I have a previous post that explains why you should move to Spring JDBC or an ORM, rather than straight JDBC. Defending against SQL Injection was one of the primary benefits you gain.

    To your second point, often your application/web tier rules will filter and validate input that should also provide as another line of defense to SQL Injection.

    To your second point, escaping character reserved words at the application level is not a valid rule. I agree with that and is not what I meant.

    It should also be pointed out that escaping these characters or reserved words is not your only option in defending against SQL injection. PreparedStatements are good enough in most instances.

    ReplyDelete
  5. thanks for the article. I have question;
    I am using an action framework to develop web application. In my actions , i am calling more than one manager methods to perform my business logic.How can i make action level transaction management? OR i am calling several manager methods from the simple main method, how can i make transaction management without the spring conf. file?

    ReplyDelete
  6. Hi Edward,

    I am beginner to the Spring and transactional management, can you please post the step by step process of execution.

    Thanks,
    Nish

    ReplyDelete
  7. Hi Erhan,

    The design of having multiple calls in more than one action should most likely be refactored, if at all possible.

    If that is not possible, I would create a new manager to handle multiple db transactions in one call, or even write a SQL proc.

    If you require multiple return objects, you could easily return a map of objects in the one call.

    In the end, my advice would be to really approach the problem in a more RESTful way.

    ReplyDelete
  8. Hi Nish,

    Manager -> DAO -> Manager....

    Nish the @Transactional annotation will work like AOP and intercept calls before and after.

    Before method call, establishing a connection from your configured data source.

    After method call, close all necessary objects after.

    Have a look at this post on my blog...
    http://j2eewebprogrammer.blogspot.com/2009/12/why-you-should-look-into-orm-if-you.html

    ReplyDelete
  9. Thank you. Very useful

    ReplyDelete
  10. Very useful.However how do the code use datasource .I am not able to relate the adapter to the datasource.

    ReplyDelete
  11. I tried with as you mentioned.

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

    But after throwing nullpointer exception in second line. First line user is not rollbacked.

    Will it be rollllback?? please help me.

    ReplyDelete
  12. In the event that you auto needs repairs and you just don't have the money to cover it, you might need to look for a payday credit to get your auto up and running.

    ReplyDelete
  13. Now, individuals regularly swing to payday advances keeping in mind the end goal to get cool, hard trade out their pockets.

    ReplyDelete
  14. Just on the off chance that you acquire through payday advances, ensure that you pay on time with the goal that you won't have issues later on, particularly that concerning your credit records.

    ReplyDelete
  15. Subsequent to deciding whether you are qualified or not, you can act properly so you would now be able to back your money issue. Simply be constant in your hunt and you can locate the correct payday credit on the web.

    ReplyDelete