Introduction

Spring framework has a full-fledge support for the Database Access Objects(DAO). Spring’s support of DAO is not limited with the relational database like MySQL , Oracle , etc . It has support for Object Relational Databases like , HSQL , JDO etc. Whan I talk about suppot it means a lot of things.

Before going into the details , let us ask a question to ourselves .What is the real problem in coding a JDBC without a framework support ?

All of us , who are JDBC programmers know these clearly :

• DBC is redundant .
• Lots of Coding to be done .
• Lots of Coding to be repeated (Yes , even after following all the OO academic theories !!).
• Extra headache of Opening a connection and Closing a connection .
• Maintaining a Connection Pool .
• You find yourself doing the same things over and over again .
• Yes , you must have to propagate the exception and handle it .
• You have to handle transactions .
• You have to write statements .
• You have to execute the statements .
• You have to get the results after executing the statement and iterate through it.

Waoh !! So many things to take care. Isn’t it ? What do the framework like Spring do then ? How does it help ?
Here is the list of what it does (We will see the HOW shortly) :

• Define connection parameters .
• Open the connection .
• Specify the statement . – Work of an Application Developer.
  
• Prepare and execute the statement .
• Set up the loop to iterate through the results (if any) .
• Do the work for each iteration . - Work of an Application Developer.
• Process any exception .
• Handle transactions .
• Close the connection .

In the above list , Except specifying the statement and writing the business logic for each iteration , all other services are taken care by the SPRING Framework. Isn’t it cool ? You don’t have to worry about many things that would have taken most of your development time otherwise.

Know the Spring DAO Module :

Packages

The Spring Framework’s JDBC abstraction framework consists of four different packages, namely core, datasource, object, and support. Spring community documentation helps you with number of options for selecting an approach to form the basis for your JDBC database access.

• JdbcTemplate – this is the classic Spring JDBC approach and the most widely used. Works well in a JDK 1.4 and higher environment.
• NamedParameterJdbcTemplate – wraps a JdbcTemplate to provide more convenient usage with named parameters instead of the traditional JDBC “?” place holders. This provides better documentation and ease of use when you have multiple parameters for an SQL statement. Works with JDK 1.4 and up.

• SimpleJdbcTemplate – this class combines the most frequently used features of both JdbcTemplate and NamedParameterJdbcTemplate plus it adds additional convenience by taking advantage of some Java 5 features like varargs, autoboxing and generics to provide an easier to use API. Requires JDK 5 or higher.
• SimpleJdbcInsert and SimpleJdbcCall – designed to take advantage of database metadata to limit the amount of configuration needed. This will simplify the coding to a point where you only need to provide the name of the table or procedure and provide a Map of parameters matching the column names. Designed to work together with the SimpleJdbcTemplate. Requires JDK 5 or higher and a database that provides adequate metadata.
• RDBMS Objects including MappingSqlQuery, SqlUpdate and StoredProcedure – an approach where you create reusable and thread safe objects during initialization of your data access layer. This approach is modeled after JDO Query where you define your query string, declare parameters and compile the query. Once that is done any execute methods can be called multiple times with various parameter values passed in. Works with JDK 1.4 and higher.

Lets see one example to learn and understand Spring-jdbc configuration. We will use jdbcTemplate style of JDBC approach. Reader of this post is expected to try other approaches as well.

Setup Details

DataBase used : MySQL 5
IDE Used : Eclipse 3.3.1
Jar files needed : Please inclusde the followings jar into project classpath.

1. commons-dbcp.jar – For DataSource Support.
2. commons-pool.jar – For Connection Pool Support.
3. mysql-connector-java-5.1.5.jar – Java Connector for MYSQL.
4. ojdbc14.jar – Query Support
5. spring.jar – The core framework library

All the above jar files should be found under lib/ and dist/ directory of the Spring Distribution with Dependencies.

Lsts take an example of a table called User which is created under the data base called demo. The description of the demo.User table is as follows,

describe table

Let us create a domain class called User which is a POJO(Plain old java object) class having the properties and setter / getters for the properties. A User.java look like ,

Listing 1 : User.java

public class User {

          private Integer uid;
          private String userName;
          private String password;

          public User() {

          }
          public User(Integer uid, String userName, String password) {
                  this.uid = uid;
                  this.userName = userName;
                  this.password = password;
          }
          public Integer getUid() {
                  return uid;
          }
          public void setUid(Integer uid) {
              this.uid = uid;
          }
          public String getUserName() {
             return userName;
          }
          public void setUserName(String userName) {
            this.userName = userName;
          }
          public String getPassword() {
              return password;
          }
          public void setPassword(String password) {
                   this.password = password;
          }

         public String toString(){
              StringBuilder sb = new StringBuilder();
              sb.append("User ID :"+getUid()+"\n");
              sb.append("User Name :"+getUserName());
              return sb.toString();
         }

    }

As the design pattern teaches us, we should do all the programming to interfaces and not to implementation . let us create an interface called UserDAO which will declare all the methods to be implemented for a User database operation.

Listing 2 : UserDAO.java

public interface UserDAO {
    public boolean insertUser(User userRecord);
    public boolean updateUser(User userRecord);
    public boolean deleteUser(int uid);
    public User selectUser(int uid);
}

Before going to the implementation of UserDAO interface , we should know the following :
– Spring Framework will provide a mechanism to inject TransactionManager and DataSource instance to our implementation class.
– A Datasource can contain information like, driverClassName,url,username,password etc and all this information (parameters) will be injected by container to the implementation class.
We will achieve these by coding our ApplicationContext.xml . Here it is .

Listing 3 : ApplicationContext.xml

<?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.0.xsd">

    <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/demo"/>
        <property name="username" value="root"/>
        <property name="password" value="abcdef"/>
        <property name="maxActive" value="10"></property>
        <property name="maxIdle" value="5"></property>
        <property name="minIdle" value="1"></property>
        <property name="poolPreparedStatements" value="true"></property>
        <property name="initialSize" value="1"></property>
    </bean>

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

    <bean id="userDao" class="user.DAO.Impl.UserDaoImpl">
   <property name="transactionManager"ref="transactionManager"></property>
    </bean>

</beans>

Look at the last bean , userDao. This bean instantiate UserDaoImpl which is an implementation os UserDAO interface. As per the setter injection logic , UserDaoImpl.java should have a setter method for the reference property transactionManager. Now , have a look at the transactionManager bean. This is a type of (say , instance of) class org.springframework.jdbc.datasource.DataSourceTransactionManager . This is a class provided by the Spring Framework to the application developer. This class has a public setDataSource(DataSource dataSource) method. Hence you have the scope to inject dataSource object as a refrence to the transactionManager bean.

The dataSource bean is of type org.apache.commons.dbcp.BasicDataSource which has setter for all the properties defined under the bean. Additionaly , the bean described theat close() method will be called as a destroy-method to close the connection.

The Sequence is like this ,
- Get a TransactionManager instance,
- Get a DataSource from TransactionManager.
- Form a jdbcTemplate from the Datasource instance.
- Specify the Query.

All the above steps are taken care in the UserDaoImpl.java class. Have a look.

Listing 4 : UserDaoImpl.java

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

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import user.DAO.UserDao;
import user.Domain.User;

public class UserDaoImpl implements UserDao {

    private JdbcTemplate jdbcTemplate;

    // Setter Injection happens here
    public void setTransactionManager(
            DataSourceTransactionManager transactionManager) {
        DataSource dataSource= transactionManager.getDataSource();
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public boolean deleteUser(int uid) {
        String sql = "delete from demo.user where UID=?";
        Object[] params = new Object[]{uid};
        int deleted = jdbcTemplate.update(sql,params);
        if(deleted > 0){
            return true;
        }else{
            return false;
        }
    }

    @Override
    public boolean insertUser(User userRecord) {

		String sql = "insert into demo.user(UID,USERNAME,PASSWORD)"
				+ " values(?,?,?)";
		Object[] params = new Object[] { userRecord.getUid(),
				userRecord.getUserName(), userRecord.getPassword() };
		int[] types = new int[] { Types.INTEGER, Types.VARCHAR,
                            Types.VARCHAR };
		int inserted = jdbcTemplate.update(sql, params, types);
		if (inserted > 0)
			return true;
		else
			return false;
    }

    @Override
    public User selectUser(int uid) {
        String sql = "select UID,USERNAME from demo.user where UID=?";
        Object[] params = new Object[]{uid};
        final User user = new User();

        jdbcTemplate.query(sql,params,new RowCallbackHandler(){

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                user.setUid(new Integer(rs.getInt("UID")));
                user.setUserName(rs.getString("USERNAME"));
            }
        });
        return user;
    }

    @Override
    public boolean updateUser(User userRecord) {
        // You Implement
        return false;
    }

}

Inspect the public void setTransactionManager(DataSourceTransactionManager transactionManager) method . The Dependency Injection takes place here. TransactionManager instance will be injected by Spring IOC container using the setter injection methodology. Once the TransactionManager is populated , a datasource instance can be created as ,
DataSource dataSource= transactionManager.getDataSource();
and a jdbcTemplate can be created as,
jdbcTemplate = new JdbcTemplate(dataSource);

This jdbcTemplate should be used for all type of query execution. To understand different types of query execution , please refer to the Spring Source Doc on JDO (from section 11.2) after running the above program successfully.