Spring Framework Data Access

Any non trivial enterprise application usually have a persistence mechanism and usually it’s a SQL compliant database. Please note that Spring does not provide any native support for database access. Spring does not provides any SQL like language to connect to database. What Spring provides is integration to various data access framework which includes

• JDBC
• Hibernate
• iBatis
• Apache Object Relationship Bridge
• JPA
• Toplink

Spring provides the following features to handle the database:

• Spring provides easy way to handle database interaction using one of the data access mechanisms. It will handle the boiler plate code so that application developer can concentrate on application logic.
• Managing connection is a non trivial process which Spring handles in an easy way.
• Transaction boundaries can be handle in an easy way using annotations.
• Spring promotes unchecked exceptions philosophy. Remember in your JDBC interaction code how many times you have done anything useful with the SQLException. Pretty much nothing apart putting a message in console. Spring catches the SQL exception and converts into unchecked exceptions.  Another thing Spring does is to check the vendor specific errors in exceptions and converts it into a uniform exception hierarchy.

Let's create a Student class and persist it.
Student.java

public class Student {    
  protected String name;

  //Getters and setters

Let's use ((Hypersonic)) database to persist the data. Bring the hsqldb.jar in the application path and start it as mentioned in ((Hypersonic)).
SQL for creating Student
CREATE TABLE STUDENT ( NAME VARCHAR )

Let's first look into the classic way of persisting the data.

Student student = new Student();
student.setName("Amitabh");
        
try{
   Class.forName("org.hsqldb.jdbcDriver");
   }catch(ClassNotFoundException cfe){
       System.out.println("Driver not found");
       System.exit(0);
  }
        
Connection conn = null;
PreparedStatement stmt = null;
try{
   conn = DriverManager.getConnection ("jdbc:hsqldb:hsql://localhost","sa","");
   stmt = conn.prepareStatement ("insert into STUDENT (name) values (?) ");
   stmt.setString(1, student.getName());
   stmt.execute();
   }catch(SQLException se){
        System.out.println("Problem with data insert");
   } finally{
       try{
           if(stmt != null) {stmt.close();}
       if(conn != null) {conn.close();}
       }catch(SQLException se) {}    

Look how much verbose is the code. The program in statement is:

• Define connection parameters
• Open the connection
• Specify the statement
• Prepare and execute the statement
• Process any exception
• Handle transactions
• Close the connection

Note that apart from the step of specifying the statement, rest all is boiler plate code which is repeated endlessly.Now let's see how Spring handles this situation. Before we move further let's look into Spring DAO philosophy. Spring promotes a pattern called Data Access Object(DAO) pattern, where Spring promotes interface based approach. This leads to flexibility in the programming model as it facilitates change of concrete implementation with ease. Let's write the interface
StudentDao

public interface StudentDao {
 
   public void saveStudent(Student student);    }

And if we are dealing with JDBC based interaction to database, the implementation class will look like

public class StudentJdbcDao implements StudentDao{

   public void saveStudent(Student student) {
   //Here we have to provide the implementation.
   //We can pull our JDBC interaction code here
   //but we do not achieve much with this apart from an indirection because of interface.
   }
}

Let's take a step back and understand how Spring handles this kind of situation. Spring adopts a template based approach. The role of template is to wrap up all the boiler plate code and provide interfaces so that the developers can focus on business specific code.Spring handles the data access using templates and callbacks.Any data access technology is used it has a fixed part(boiler plate code) and a programmer defined part. The fixed part like opening closing connection is handled by template and the variable part like how to handle the result is handled by callbacks.
Spring comes with several data access template for different persistence mechanism:

• JdbcTemplate
• NamedParameterJdbcTemplate
• SimpleJdbcTemplate
• HibernateTemplate
• JpaTemplate
• …

At the moment we are looking into JDBC way of interacting to database so Let's see how templates related to JDBC come to action. In this regard we will use JdbcTemplate first.JdbcTemplate class simplifies working with JDBC. It  automatically handles resource management, exception handling and transaction management.It is a thread safe class so you can use a single instance that many classes can use. Underlying Connection can also be accessed.So now our implementation class for JdbcDao looks like
StudentJdbcDao.java

public class StudentJdbcDao implements StudentDao{

   private JdbcTemplate jdbcTemplate;
   
   public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
        this.jdbcTemplate = jdbcTemplate;
   }

   public void saveStudent(Student student) {
        jdbcTemplate.update
        ("insert into STUDENT (name) values (?)",new Object[] {student.getName()} );
   }
}

Now the question is how StudentJdbcDao gets the jdbcTemplate. In true Spring fashion let's wire the relationship.
In context.xml

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource" />
</bean>

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource“
    destroy-method="close">
  <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
  <property name="url" value="jdbc:hsqldb:hsql://localhost" />
  <property name="username" value="sa" />
  <property name="password" value="" />
</bean>

dataSource is the bean in which we provide all the database connection properties. Than we inject dataSource in jdbcTemplate and than let's inject jdbcTemplate in StudentJdbcDao.

<bean id="studentDao" class="StudentJdbcDao">
   <property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

Note that we have given the id as studentDao but the actual class is StudentJdbcDao(Use full package name). We can now access our dao in calling code:

Student student = new Student();
student.setName("AmitabhDao");
StudentDao studentDao = (StudentDao)appContext.getBean("studentDao");
studentDao.saveStudent(student);

The jars required for this are:

• lib/log4j/log4j-1.2.14.jar
• lib/jakarta-commons/commons-logging.jar
• dist/spring.jar
• lib/hsqldb/hsqldb.jar

The source files and configuration file can be downloaded from attachment.
It's possible that we might have many dao classes to handle different database interactions. So that will result in writing a lot of code to handle jdbcTemplate. To solve this Spring comes with a convenient class JdbcDaoSupport which gives access to JdbcTemplate

public class StudentJdbcDao extends JdbcDaoSupport implements StudentDao {
    
   public void saveStudent(Student student) {
    this.getJdbcTemplate().update
        ("insert into STUDENT (name) values (?)",
                     new Object[] {student.getName()} );        
  }
}

The configuration code in context.xml remains same.

Configuring Data Sources

Spring provides a number of ways to configure data source.

• Using JDBC driver
• JNDI Lookup
• Pool connections

We saw earlier how to handle the connection using connection properties when we are working with JDBC directly. Spring provides two basic JDBC driver based datasource

• DriverManagerDataSource –  A new connection is returned every time. Connections are not pooled.
• SingleConnectionDataSource – It returns always the same connection. It acts like a pool of one connection.

<bean id =“datasource”   class=“org.springframework.jdbc.datasource.DriverManagerDataSource”>
   <property name="driverClassName“ value="org.hsqldb.jdbcDriver" />
   <property name="url" value="jdbc:hsqldb:hsql://localhost" />
   <property name="username" value="sa" />
   <property name="password" value="" />
</bean>

For JNDI datasource, the datasource is feteched using JNDI lookup

<bean id ="datasource" class="org.springframework.jndi.JndiObjectFactoryBean">
   <property name=“jndiName” value=/jdbc/ds”/>
   <property name=“resourceRef” value=“true”/>
</bean>

With jee namespace, it can be written more succinctly

<jee:jndi-lookup id=“datasource” jndi-name=“jdbc/ds” resource-ref=“true” />

Spring provides the capability to pool the datasource in the application itself using Jakarta’s Commons Database Connection Pools (DBCP)

<bean id ="datasource" class="org.apache.commons.dbcp.BasicDataSource">
   <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
   <property name="url" value="jdbc:hsqldb:hsql://localhost" />
   <property name="username" value="sa" />
   <property name="password" value="" />
   <property name=“initialSize” value=“5”/>
   <property name=“maxActive” value=“10”/>
</bean>

JDBCTemplate

Spring provides three types of template class to work with JDBC

• JdbcTemplate: This is the way we did earlier. It's the basic way of doing the database access. The parameters to SQL are passed as indexes parameters.
• NamedParameterJdbcTemplate: The parameters are send as named value pair in a Map.
• SimpleJdbcTemplate: It uses features like autoboxing and ((Generics)). Brings type safety and casting need not be done.
• Each template classs has supporting DaoSupport class for easy wiring of templates.We saw the usage of JdbcTemplate earlier. Let's look how NamedParameterJdbcTemplate works. In this case the dao class looks like
•  

public class StudentJdbcDao implements StudentDao {
    
    protected NamedParameterJdbcTemplate namedJdbcTemplate;

    //Method to inject NamedParameterJdbcTemplate  
    public void setNamedJdbcTemplate (NamedParameterJdbcTemplate namedJdbcTemplate) {
    this.namedJdbcTemplate = namedJdbcTemplate;
    }

    public void saveStudent(Student student) {
    Map parameters = new HashMap();
    parameters.put("name",student.getName());
    namedJdbcTemplate.update
        ("insert into STUDENT (name) values (:name)",
          parameters);        
    }
...
}

In configuration XML

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
    <constructor-arg ref="dataSource" />
</bean>

Note that the datasource is injected as constructor argument.

In case of SimpleJdbcTemplate the Dao class looks like:

protected SimpleJdbcTemplate simpleJdbcTemplate;

    //Setter method for SimpleJdbcTemplate
    ...

    public void saveStudent(Student student) {
         //Note how the arguments are passed. It uses varargs feature.
    simpleJdbcTemplate.update
        ("insert into STUDENT (name,percentage) values (?,?)",
         student.getName(),student.getPercentage());

Configuration XML

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
    <constructor-arg ref="dataSource" />
</bean>

Fetching Collections

Now let's look how we can fetch the collection from database. We will use the SimpleJdbcTemplate to do that

public List<Student> getAllStudents() {
   return simpleJdbcTemplate.query
    ("Select name as Name from Student",
               new ParameterizedRowMapper<Student>(){
              public Student mapRow(ResultSet rs,int rowNum)
                  throws SQLException {
                    Student student = new Student();
                student.setName(rs.getString("Name"));
                return student;                      
          }
        }        
   );
}

Note how anonymous inner class ParameterizedRowMapper<Student> is registered as a parameter to the query method. Now to understand that again think in terms of moving part and fixed part. The moving part is the query which is passed as first argument. The fixed part is the boiler plate code which handles opening and closing connection, making and executing statement and iterating through the result. Again the moving part is how to handle each iteration. This is provided by the mapRow method. The mapRow method is called as a callback for every iteration.

Transactions

Spring supports the following way of defining transaction

• Declarative
• Programmatic

Spring has no capability to manage transactions directly. The transaction management is delegated to platform specific transaction implementation provided by either JTA or the persistence mechanism.Spring supports wide variety of transaction manager.
Plain JDBC transaction is handled by DataSourceTransactionManager.DataSourceTransactionManager manages the transaction automatically by calling commit on success and in the case of failure calling the rollback.Define the transaction manager and transaction template

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

<bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate" >
   <property name="transactionManager" ref="transactionManager"/>
</bean>

Controlling transaction programmatically

public void saveUser(final User user) {
   transactionTemplate.execute(
    new TransactionCallback(){
    public Object doInTransaction(TransactionStatus ts){
        try{
                     userDao.saveUser(user);
                }catch(Exception ex){
                  ts.setRollbackOnly();
                }
    return null;
});}

Spring defines declarative transaction using attributes:

• Propagation behavior - This tells how the transaction boundaries need to be handled.
• Isolation level - The isolation level decides how the common data between two transactions are shared.
• Read-only - This is an optimization hint to the database. The database can optimize if it comes to know that the particular sql call is a read only call. For example fetching the list of students todisplay only. Not all database support this feature.
• Transaction timeout - For long running transaction the timeout tells the time limit to which the transaction can run. After timeout the transaction is aborted by the database.
• Rollback Rules -Tell which exception will result in a rollback.By default transactions are rolled back only on runtime exceptions and not on checked exceptions.In Spring, the behaviour can be changed by doing rollback on checked exception and not to do rollback on unchecked exceptions.

Annotation Driven Transaction
With annotations introduced in Java 5+, handling transaction has been made very easy. Add the transaction manager by registering the transaction manager in configuration XML. This also has been made easy by using tx namespace

<tx:annotation-driven/>

Add the annotation

@Transactional(propagation=Propagation.SUPPORTS, readOnly=true)
public class UserListService {

   @Transactional(propagation=Propagation.REQUIRED, readOnly=false)
   public void insertUser(final User user) {
      userDao.saveUser(user);
   }

The annotation drives the behavior for all methods in the class. The behavior for individual method can be overridden by putting annotation on individual method.