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.

Sub Queries vs. Outer Joins : (SELECT of a SELECT) vs. (LEFT OUTER JOINS)

One performance bottleneck that as Java developers we may encounter may be our poor use of SQL.

I often see a Nested SELECT like:

SELECT name FROM bbc
WHERE population >
(SELECT population FROM bbc
WHERE name='Russia')


Implemented as a LEFT OUTER JOIN
SELECT name FROM BBC LEFT OUTER JOIN ON BBC

Dependant on the query, the Nested SELECT technique may force the subquery to be evaluated for every row in the left-hand table.
A LEFT OUTER join, by contrast, can often use a much more efficient query plan.

This is not always the case as they are mathematically equivalent and a good query optimizer may generate the same query plan, but this is not always the case.

Why you should look into ORM, if you haven't already?

In my last posting, I got clobbered by the blogosphere concerning my posting "Data Access: Plain JDBC vs. ORM".

I feel that the title was misleading, however, I do think that I do make some valid points about the shortcomings of authoring a data tier without the assistance of some type of ORM.

It is my opinion that, yes, there are many applications out there that still use raw JDBC as the primary method of accessing a data source in their data tier.

With that being said, I was targeting this article to those programmers that still write data tiers using raw JDBC, and was thinking of following up with some simple posts and tutorials on ORM's like Spring JDBC and Hibernate.

Without further ado, I am going to repost my thoughts and concerns.

Why you should look into ORM, if you haven't already?

A developer's goal when writing data access code is to properly contain the responsibilities of all data access operations. Leaking data access operations into the application is referred to as leakage of data-access details and has some adverse affects when the application has to adapt to changes on the data side.

Let's take a look at a method that uses raw JDBC:

public class UserRegistrationJDBC {

   private javax.sql.DataSource dataSource;

   public int getUserRegistrationCount() throws MyDataAccessException {

      java.sql.Connection connection = null;
      java.sql.Statement statement = null;
      java.sql.ResultSet resultSet = null;
      try{
         connection = dataSource.getConnection();
         statement = connection.createStatement();
         resultSet = statement.executeQuery(
            "SELECT COUNT(*) FROM USER_REGISTRATION");

         rs.next();
         return rs.getInt(1);
      }catch(java.sql.SQLException sqle){
         throw new MyDataAccessException(e);
      }finally{
         if(resultSet != null){ 
            try{ resultSet.close() }
            catch (java.sql.SQLException sqle){ sqle.printStackTrace(); }
         }
         if(statement != null){ 
            try{ statement.close() }
            catch (java.sql.SQLException sqle){ sqle.printStackTrace(); }
         }
         if(connection != null){ 
            try{ connection.close() }
            catch (java.sql.SQLException sqle){ sqle.printStackTrace(); }
         }
      }
   }
}

Let's look at some technical details contained within the code above:

• The call to getConnection() on the javax.sql.DataSource object can be problematic because it obtains its own database connection. For example, suppose we wrote all our data access methods like the one above, even for inserts and updates. Imagine attempting to persist multiple records, perhaps on the completion of an order, something with an invoice, shipping details and billing details. If one of those transactions fail for some unexpected reason, how would you rollback the transactions? Obtaining a connection from the pool will only rollback the last transaction, leaving you to hand implement the rollback. It becomes even worse if the set of transactions spans multiple data sources.
• The call to createStatement() is problematic as well. All sql should be rendered using a java.sql.PreparedStatement() for improved performance via caching. While this is more of a practice and learned through experience, using raw JDBC leaves the programmer open to these types of mistakes.
• Catching a java.sql.SQLException is required by the JDBC API as it is it's only exception type and typically reveals little about the root cause of your error. Wrapping it with the unchecked exception MyDataAccessException, adds little value, only that your calling code does not have to catch it. Your application code now is restricted in how it can deal with specific data access errors.
• The finally block contains the closing of all resources used in the method, which makes it impossible for other methods to reuse it.

Poor handling of the concerns above can lead to:

• Resource exhaustion - loss of connection availability and loss of memory from improperly closed result sets and statements.
• Poor performance - due to Statement vs. PreparedStatement as mentioned above.
• Inappropriate connection life cycles - any operation where more than one SQL statement is executed can reuse connections and other resources for more appropriate life cycles. Even with connection pools, we should be careful not to spend CPU cycles and memory on obtaining and releasing connections.

To elaborate even further the inappropriate connection life cycles point, consider where a user registration is added with a payment for the registration. We would want to ensure that all the data access operations occurred or none at all. In a more appropriate world, we want to demarcate these operations at a transaction level, enabling the reuse of all related resources.

The goal of many ORM's are to effectively handle the leakage of data access details into the view tier. In addition, they support:

• Transaction Demarcation - a mechanism to declare when transactions start and end, and support rollback.
• Transaction Management API - an API to keep contracts clear and concise on transactions

It should be obvious, that writing a Transaction Management API is out of scope for most developers and only an adequately defined API can guarantee that applications remain flexible and adaptive.

The benefits of ORM's are too many for you not to have looked heavily into their proposed benefits.

In my next article, I'll present ways on how Spring provides a constructive and flexible programming model which addresses the concerns above and much more.

Tomcat DBCP DataSource falls idle overnight

If you have a low activity web server using Tomcat DBCP to retrieve connections to your database, the database server might time out connections in the pool.

Adding the following to your <Resource> configuration can address the problem.

• testOnBorrow="true"
• validationQuery="SELECT 1 /* ping */"

Branching in CVS with Eclipse

Sometimes you find such a well written article on a specific topic that you feel that it just needs to be shared.

I have gone to this article on branching and merging in CVS with Eclipse numerous times for more confidence, particularly before a merge.

http://www.eclipse.org/articles/article.php?file=Article-BranchingWithEclipseAndCVS/article1.html

Using Static Helper Classes - Java Memory and the Initialization on Demand Holder Idiom

A recent discussion about design patterns and data delegator classes was brought up recently in the tomcat-users mailing list. It is common to find that many web applications use several delegate classes providing access to some data source. Delegate classes are often implemented using the singleton-design pattern, which proposes that at any time there can only be one instance of a singleton (object) created by the JVM.

Theoretically, questions come into mind concerning the governance surrounding instantiation and synchronization of these singleton classes, especially in a multi-threaded environment.

In this post, we'll go over some approaches posted in the thread and provide a best option.

• Static Initizialization
• Double Checked Locking (later only on post 1.5 jvm)
• Initialize-On-Demand Holder Class Idiom (MOST PREFERRED)

Static Initialization

class BeanBag {
private static final SomeBean someBean = new SomeBean();
private static final AnotherBean anotherBean = new AnotherBean();

public static SomeBean getSomeBean() { return someBean; }
public static AnotherBean getAnotherBean() { return anotherBean; }
}

The initialization happens at the loading time of the class but there is no guarantee to be synchronized. The static class property someBean will be available at first call to getSomeBean(). This works 99% of the time, I have never had a problem with it.

Double Lock Checking

class BeanBag {
private static volatile SomeBean someBean = null;

public static SomeBean getSomeBean() {
if (someBean==null){
synchronized(BeanBag.class){
if (someBean==null){
someBean = new SomeBean();
}
}
}
return someBean;
}
}

The difference here, is the singleton instance is not actually instantiated until the getSomeBean() method is called. This provides a more lazy instantiation paradigm if needed. Here we can synchronize the block when SomeBean is instantiated.

Initialize-On-Demand Holder Class Idiom

class BeanBag {
//Inner Class
private static class BeanBagHolder {
public static SomeBean someBean = new SomeBean();
}

public static Something getInstance() {
return BeanBagHolder.someBean;
}
}

This idiom derives its thread safety from the fact that operations that are part of class initialization, which is guaranteed by the JVM. It derives its lazy initialization from the fact that the inner class is not loaded until some thread references one of its fields or methods. This provides the best of both options and guaranteed to be thread-safe by the JVM.

The last option is the most preferred and this article goes into greater detail about Java Memory Model.

MySQL Import and Export

Two useful commands below to help you import and export data to and from MySQL databases.

Export
To export data from a MySQL database, use the mysqldump utility.

mysqldump -u(your_username) -p(password) (database_name) > (BACKUPFILE.sql) 

Import
The file produced can be used to pipe back into a different MySQL database.

mysql -u(username) -p(password) (database_name) < (BACKUPFILE.sql)

Remove the parenthesis and replace the values in both commands above.

java.util.List to Object array (Object []) of a specified type

The tip below can help you in the data access tier or the application tier.

The java.lang.reflect.Array class provides static methods to dynamically create and access Java arrays. Any java collection or list can easily be converted into an Object array. There are two method calls, that I preferably squish into one line to get the job done. The first method call is:

Array.newInstance(Class componentType, int length) throws NegativeArraySizeException 

The method creates a new array with the specified component type and length. You can also cast down to have returned an array of your specified component type.

The second method call is defined in the java.util.List interface.

public Object[] toArray(Object[] a)

The method returns an array containing all of the elements in the List in the correct order where the runtime type of the returned array is that of the specified array.

Coupling the two calls above can allow you to quickly transform your ArrayList of objects into an object array of the specified object's component type.

Have a look at the sample code below for an ArrayList of strings to be converted into a String [].

ArrayList theStrings = new ArrayList();
theStrings.add("Hello");
theStrings.add("Aloha");
theStrings.add("Bonjour");

//Return String [].
String [] theStringArray = 
(String[])theStrings.toArray(
(String[])Array.newInstance(java.lang.String.class, theStrings.size())
);

With one line, you'll be able to turn your list of Java objects into an array of objects of the object's component type.

Questions, just comment and I'll be sure to get back to you.

Spring JDBC Tutorial

Below is a quick tutorial on how to get things up and running with Spring and JDBC. The code in the tutorial is intended to utilize a MySQL database as its data source.

Let's start the tutorial off with getting MySQL database installed. If you do not have it installed, follow this link to the MySQL download site. Follow the installation instructions and remember your database username and password.

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

• JDBC driver for MySQL, MySQL Connector/J.
• The latest spring framework. 3.0.0.M1.
• ANTLR 3.0.1
• Commons Logging.

Because you won't need the whole spring framework, below is a list of all the required Spriong libraries to build and execute the code in this tutorial:

• spring beans
• spring context
• spring core
• spring expression
• spring jdbc
• spring transaction
• mysql connector/j
• commons logging
• antlr

Make sure you add these dependencies to your build path.

CREATE THE DATABASE
Next, we'll need to create a database along with a table to act as the data source for this tutorial.

For this example, we'll create a table called siteAdmin:

CREATE TABLE siteAdmin (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
username VARCHAR (20) NOT NULL,
password VARCHAR (20) NOT NULL,
firstName VARCHAR (20) NOT NULL,
lastName VARCHAR (20) NOT NULL,
email VARCHAR (40) NOT NULL,
created TIMESTAMP NOT NULL,
lastModified TIMESTAMP NOT NULL,
status INT NOT NULL,
PRIMARY KEY (id)
);

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

package com.edwardwebnerd.persistence.model;

import java.sql.Date;

public class SiteAdmin{

private int id;
private String username;
private String password;
private String firstname;
private String lastname;
private String email;
private Date created;
private Date lastModified;
private int status;

/** @return the id */
public int getId() {
return id;
}
/** @param id the id to set */
public void setId(int id) {
this.id = id;
}
/** @return the username */
public String getUsername() {
return username;
}
/** @param username the username to set */
public void setUsername(String username) {
this.username = username;
}
/** @return the password */
public String getPassword() {
return password;
}
/** @param password the password to set */
public void setPassword(String password) {
this.password = password;
}
/** @return the firstname */
public String getFirstname() {
return firstname;
}
/** @param firstname the firstname to set */
public void setFirstname(String firstname) {
this.firstname = firstname;
}
/** @return the lastname */
public String getLastname() {
return lastname;
}
/** @param lastname the lastname to set */
public void setLastname(String lastname) {
this.lastname = lastname;
}
/** @return the email */
public String getEmail() {
return email;
}
/** @param email the email to set */
public void setEmail(String email) {
this.email = email;
}
/** @return the created */
public Date getCreated() {
return created;
}
/** @param created the created to set */
public void setCreated(Date created) {
this.created = created;
}
/** @return the lastModified */
public Date getLastModified() {
return lastModified;
}
/** @param lastModified the lastModified to set */
public void setLastModified(Date lastModified) {
this.lastModified = lastModified;
}
/** @return the status */
public int getStatus() {
return status;
}
/** @param status the status to set */
public void setStatus(int status) {
this.status = status;
}
}

CREATE YOUR DAO INTERFACE
Next, generate your DAO interface.

package com.edwardwebnerd.persistence.jdbc.dao;

import java.util.List;
import javax.sql.DataSource;
import com.edwardwebnerd.persistence.model.SiteAdmin;


public interface SiteAdminDAO {

void setDataSource(DataSource ds);

List selectAll();

void create(String username, String password, String firstName, String lastName,
String email, int status);

List select(String username, String password);

List select(int id);

List selectAll();

void delete(int id);
}

IMPLEMENT YOUR DAO

The implementing DAO class.

package com.edwardwebnerd.persistence.jdbc.dao.impl;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.edwardwebnerd.persistence.model.SiteAdmin;
import com.edwardwebnerd.persistence.jdbc.dao.SiteAdminDAO;
import com.edwardwebnerd.persistence.model.SiteAdmin;
import com.edwardwebnerd.persistence.jdbc.dao.impl.mapper.SiteAdminMapper;


public class SiteAdminDAOImpl implements SiteAdminDAO {

private DataSource dataSource;

@Override
public void setDataSource(DataSource ds) { dataSource = ds; }


@Override
public void create(String username, String password, String firstName, 
String lastName, String email, int status)
{
JdbcTemplate insert = new JdbcTemplate(dataSource);
insert.update("INSERT INTO siteAdmin " +
"(username, password, firstName, lastName, email, status)" +
" VALUES(?,?,?,?,?)",
new Object[] { username, password, firstName, lastName, email,
status});
}


@Override
public void delete(int id) 
{
JdbcTemplate delete = new JdbcTemplate(dataSource);
delete.update("DELETE from siteAdmin where id= ?",
new Object[] { id });
}


@Override
public List select(String username, String password) 
{
JdbcTemplate select = new JdbcTemplate(dataSource);
return select.query(
"select * from siteAdmin where username = ? AND password = ?",
new Object[] { username, password },
new SiteAdminMapper());
}


@Override
public List select(int id) 
{
JdbcTemplate select = new JdbcTemplate(dataSource);
return select.query(
"select * from siteAdmin where id = ?",
new Object[] { id },
new SiteAdminMapper());
}


@Override
public List selectAll() 
{
JdbcTemplate select = new JdbcTemplate(dataSource);
return select.query("select * from siteAdmin",
new SiteAdminMapper());
}
}

The implementation also uses a RowMapper class to help shorten the implementation and provide a single point of managing the mapping between the siteAdmin's table column names and the domain model's class members.

package com.edwardwebnerd.persistence.jdbc.dao.impl.mapper;

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

import org.springframework.jdbc.core.RowMapper;

import com.edwardwebnerd.persistence.model.SiteAdmin;

public class SiteAdminMapper implements RowMapper {

public SiteAdminMapper(){}


@Override
public Object mapRow(ResultSet resultSet, int arg1) throws SQLException {
SiteAdmin siteAdmin = new SiteAdmin();
siteAdmin.setId(resultSet.getInt("id"));
siteAdmin.setUsername(resultSet.getString("username"));
siteAdmin.setPassword(resultSet.getString("password"));
siteAdmin.setFirstname(resultSet.getString("firstname"));
siteAdmin.setLastname(resultSet.getString("lastname"));
siteAdmin.setEmail(resultSet.getString("email"));
siteAdmin.setCreated(resultSet.getDate("created"));
siteAdmin.setLastModified(resultSet.getDate("lastModified"));
siteAdmin.setStatus(resultSet.getInt("status"));
return siteAdmin;
}
}

CREATE A SERVICE LAYER INTERFACE OVER YOUR DAO IMPLEMENTATION
Next, generate your Service interface.

package com.edwardwebnerd.persistence;

import java.util.List;

import com.edwardwebnerd.persistence.jdbc.dao.SiteAdminDAO;
import com.edwardwebnerd.persistence.model.SiteAdmin;


public interface LittleLeagueServices {

void setSiteAdminDAO(SiteAdminDAO siteAdminDAO);

List siteAdminSelectAll();
}

CREATE A SERVICE LAYER INTERFACE OVER YOUR DAO IMPLEMENTATION
Next, generate your implementing Service class.

package com.edwardwebnerd.persistence.jdbc;

import java.util.List;

import com.edwardwebnerd.persistence.LittleLeagueServices;
import com.edwardwebnerd.persistence.jdbc.dao.SiteAdminDAO;
import com.edwardwebnerd.persistence.model.SiteAdmin;


public class LittleLeagueServicesImpl implements LittleLeagueServices {

private SiteAdminDAO siteAdminDAO;

@Override
public void setSiteAdminDAO(SiteAdminDAO siteAdminDao) 
{    
siteAdminDAO = siteAdminDao;    
}

@Override
public List siteAdminSelectAll(){
return siteAdminDAO.selectAll();
}   
}

CONFIGURE SPRING
Next, we'll define an XML config file to have Spring inject the DAO Implementation into our services and inject a MySQL data source into the DAO Implmentation. Remember to replace the connection information appropriately.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans 
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

<bean id="littleLeagueServices"
class="com.edwardwebnerd.persistence.jdbc.LittleLeagueServicesImpl">
<property name="siteAdminDAO" ref="siteAdminDAO"/>
</bean>

<bean id="siteAdminDAO" 
class="com.edwardwebnerd.persistence.jdbc.dao.impl.SiteAdminDAOImpl">
<property name="dataSource" ref="dataSource"/>
</bean>

<bean id="dataSource" destroy-method="close" 
class="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
<property name="url" 
value="jdbc:mysql://localhost:3306/?autoReconnect=true"/>
<property name="user" value=""/>
<property name="password" value=""/>
</bean>
</beans> 

TRY A TEST RUN
Next, test your services with a test run class.

package com;

import java.util.List;

import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.context.ApplicationContext;

import com.edwardwebnerd.persistence.LittleLeagueServices;
import com.edwardwebnerd.persistence.model.SiteAdmin;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Logger;

public class SpringTest {

static Logger logger = Logger.getLogger(SpringTest.class );

public static void main(String [] args){
BasicConfigurator.configure();

ApplicationContext applicationContext = 
new ClassPathXmlApplicationContext("config.xml");
BeanFactory factory = applicationContext;

logger.info("LittleLeagueServices Initializing");
LittleLeagueServices littleLeagueServices = 
(LittleLeagueServices) factory.getBean("littleLeagueServices");
logger.info("LittleLeagueServices Initialized");

logger.info("LittleLeagueServices: Retrieving SiteAdmins");
List siteAdmins = littleLeagueServices.siteAdminSelectAll();
for(int i = 0; i < siteAdmins.size(); i++){
SiteAdmin siteAdmin = siteAdmins.get(i);
logger.info(siteAdmin.toString());
}
}
} 

If you have any questions on this tutorial, just comment on the blog and I'll be sure to get back to you.