Custom Search

Tuesday, July 10, 2007

A Primer on Spring's Data Access Object (DAO) Framework

07/10/2007

Abstract

The business components in J2EE applications typically use the JDBC API to access and change persistent data in relational databases. This often leads to the mixing of persistence code with business logic—a bad idea. The Data Access Object (DAO) design pattern addresses this problem by separating the persistence logic into data access classes.

This article is a primer on DAO design pattern, highlighting its merits and demerits. It then introduces the Spring 2.0 JDBC/DAO framework and demonstrates how it elegantly addresses the drawbacks in traditional DAO design.

Traditional DAO Design

Data Access Object (DAO) is an integration tier design pattern as cataloged in the book Core J2EE Design Pattern. It encapsulates persistence store access and manipulation code into a separate layer. The persistent store in the context of this article is an RDBMS.

This pattern introduces an abstraction layer between the business logic tier and the persistent storage tier, as shown in Figure 1. Business objects access the RDBMS (data source) through the data access objects. This abstraction layer streamlines application code and introduces flexibility. Ideally, changes made to the data source, such as switching database vendors or type, would require changes only to the data access objects and should have minimal impact on the business objects.

Changes in code structure after introduction of DAO classes
Figure 1. Application structure, before and after DAO

Now that I've explained the basics of the DAO design pattern, it's time to write some code. The examples below are from a company domain model. To put it simply, the company has several employees working in various departments such as sales, marketing, and HR. For the sake of simplicity, I will concentrate on a single entity called "Employee."

Program to an interface

The flexibility the DAO design pattern provides is attributed primarily to a best practice for object design: Program to an Interface (P2I). This principle states that concrete objects must implement an interface that is used in the caller program rather than the concrete object itself. Therefore, you can easily substitute a different implementation with little impact on client code.

Going by this mantra I will define the Employee DAO interface, IEmployeeDAO, with a behavior findBySalaryRange(). The business components will interact with the DAOs through this interface:

import java.util.Map;
public interface IEmployeeDAO {
//SQL String that will be executed
public String FIND_BY_SAL_RNG = "SELECT EMP_NO, EMP_NAME, "
+ "SALARY FROM EMP WHERE SALARY >= ? AND SALARY <= ?";   //Returns the list of employees who fall into the given salary  //range. The input parameter is the immutable map object  //obtained from the HttpServletRequest. This is an early  //refactoring based on "Introduce Parameter Object"   public List findBySalaryRange(Map salaryMap); }

Providing the DAO implementation classes

Having defined the interface, now I must provide a concrete implementation of the Employee DAO, EmployeeDAOImpl:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import com.bea.dev2dev.to.EmployeeTO;

public class EmployeeDAOImpl implements IEmployeeDAO{

public List findBySalaryRange(Map salaryMap)
{
 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 List empList = new ArrayList();
 //Transfer Object for inter-tier data transfer
 EmployeeTO tempEmpTO = null;
 try{
 //DBUtil - helper classes that retrieve connection from pool
   conn = DBUtil.getConnection();
   pstmt = conn.prepareStatement(FIND_BY_SAL_RNG);
   pstmt.setDouble(1, Double.valueOf( (String)
       salaryMap.get("MIN_SALARY") );
   pstmt.setDouble(2, Double.valueOf( (String)
       salaryMap.get("MIN_SALARY") );
   rs = pstmt.executeQuery();
   int tmpEmpNo = 0;
   String tmpEmpName = "";
   double tmpSalary = 0.0D;
   while (rs.next()){
     tmpEmpNo = rs.getInt("EMP_NO");
     tmpEmpName = rs.getString("EMP_NAME");
     tmpSalary = rs.getDouble("SALARY");
     tempEmpTO = new EmployeeTO(tmpEmpNo,
           tmpEmpName,
           tmpSalary);
     empList.add(tempEmpTO);
   }//end while
 }//end try
 catch (SQLException sqle){
   throw new DBException(sqle);
 }//end catch
 finally{
   try{
     if (rs != null){
       rs.close();
     }
   }
   catch (SQLException sqle){
     throw new DBException(sqle);
   }
   try{
     if (pstmt != null){
       pstmt.close();
     }     
   }
   catch (SQLException sqle){
     throw new DBException(sqle);
   }
   try{
     if (conn != null){
       conn.close();
     }     
   }
   catch (SQLException sqle){
     throw new DBException(sqle);
   }
 }//end of finally block
 return empList;
}//end method findBySalaryRange
}

The above listing illustrates these key points about DAO methods:

  • They encapsulate all interactions with the JDBC API. If an O/R mapping solution like Kodo or Hibernate were being used, the DAO classes can wrap the proprietary APIs of these products.
  • They wrap the retrieved data in a JDBC API-neutral transfer object and returns it to the business tier for further processing.
  • They are stateless in nature. Their sole aim is to access and change persistent data for the business objects.
  • They trap any errors (for example, database is unavailable, wrong SQL syntax) reported in the process by the underlying JDBC API or database as SQLException. The DAO objects notify the business objects of such errors again by a JDBC-neutral, custom build runtime exception class DBException.
  • They release database resources like Connection and PreparedStatement objects back to the pool and relinquish memory held by ResultSet cursors after they have been used.

Therefore, the DAO layer provides a consistent data access API for the business tier abstracting the low level data access API.

Building the DAO Factory

The DAO Factory is a typical factory design pattern implementation for creating and serving concrete DAO implementations to the business objects. The business objects use the DAO interfaces and are not aware of the implementation classes. This dependency inversion due to the DAO factory provides enormous flexibility. It is easy to alter DAO implementation (for example, from straight JDBC to Kodo-based O/R mapping) without affecting the client business objects, as long as the contract established by the DAO interfaces remains unchanged:

public class DAOFactory {
private static DAOFactory daoFac;

static{
 daoFac = new DAOFactory();
}

private DAOFactory(){}

public DAOFactory getInstance(){
 return daoFac;
}

public IEmployeeDAO getEmployeeDAO(){
 return new EmployeeDAOImpl();
}
}

Collaborating with the business components

Now it's time to see how DAOs fit into the bigger picture. As mentioned in previous sections, the DAOs collaborate with the business tier components to fetch and change persistent business data. This listing shows the business service component and its interaction with the DAO tier:

public class EmployeeBusinessServiceImpl implements
                                    IEmployeeBusinessService {

public List getEmployeesWithinSalaryRange(Map salaryMap){

 IEmployeeDAO empDAO = DAOFactory.getInstance()
                                 .getEmployeeDAO();
 List empList = empDAO.findBySalaryRange(salaryMap);
 return empList;
}
}

This is nice and clean, with no reliance on any persistence interfaces (including JDBC) at all.

Problems

The DAO design pattern is not devoid of shortcomings:

  • Code Repetition: As evident from the EmployeeDAOImpl listing, code repetition (shown in bold above) is a major problem with JDBC-based, traditional database access. Writing boilerplate code over and over is a clear violation of the basic OO principle of code reuse. This has obvious side effects in terms of project cost, timelines, and effort.
  • Coupling: The DAO code is very tightly coupled with the JDBC interfaces and core collections. This is evident from the number of import statements per DAO class.
  • Resource Leakage: Following the design of the EmployeeDAOImpl class, all DAO methods must relinquish control of acquired database resources like connection, statements, and result sets. This is a risky proposition because a novice programmer can very easily skip those bits. As a result, resources would run out and bring the system to a halt.
  • Error Handling: JDBC drivers report all error situations by raising the SQLException. SQLException is a checked exception, therefore developers are forced to handle it—even though it isn't possible to recover from the majority of these exceptions, which results in cluttering the code. Moreover, the error code and message obtained from the SQLException object are database vendor-specific, so it's not possible to write portable DAO error messaging code.
  • Fragile Code: The setting of the bind variables for the statement object, and the retrieval of the data using the result set getter methods are two frequently used tasks in JDBC-based DAO. If the number of columns in the SQL where clause is changed, or the column positions are altered, the code has to go through the rigorous cycle of change, test, and redeployment.

Let's look at how to maintain the majority of the benefits of DAO while doing away with these problems.



Enter Spring DAO

The problems listed above can be solved by identifying the portions of the code that vary, and then separating or encapsulating them from the code that remains fixed. The designers of Spring have done exactly that and have come out with an ultra-thin, robust, and highly extensible JDBC framework. The fixed parts (like retrieving connection, preparing the statement object, executing the query, and releasing the database resources) have been written once and correctly—so part of this framework helps to eliminate the shortcomings found in traditional JDBC-based DAO.

Figure 2 shows the major building blocks of the Spring JDBC framework. The business service objects continue to use the DAO implementation classes through appropriate interfaces. JdbcDaoSupport is the super class for JDBC data access objects. It is associated with a particular datasource. The Spring Inversion of Control (IOC) container, or the BeanFactory, is responsible for getting the appropriate datasource configuration details and associating them with JdbcDaoSupport. The most important functionality of this class is to make the JdbcTemplate object available to the subclasses.

Spring DAO Architecture
Figure 2. Major components of the Spring JDBC framework

JdbcTemplate is the most important class in the Spring JDBC framework. To quote the documentation, "it simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results." This class helps separate the static parts of JDBC DAO code by performing these boilerplate tasks:

  • Retrieves connections from the datasource.
  • Prepares appropriate statement object.
  • Executes SQL CRUD operations.
  • Iterates over result sets and populates the results in standard collection objects.
  • Handles SQLException exceptions and translates them to a more error-specific exception hierarchy.

Rewriting Using Spring DAO

Now that you have a basic understanding of the Spring JDBC framework, it is time to rewrite the existing code. I will take a step-by-step approach and, in the process, discuss how to overcome the problems stated in the previous section.

Step 1: Alter DAO Implementation class - The EmployeeDAOImpl now extends from JdbcDaoSupport to get hold of JdbcTemplate.

import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.core.JdbcTemplate;

public class EmployeeDAOImpl extends JdbcDaoSupport
                                  implements IEmployeeDAO{

public List findBySalaryRange(Map salaryMap){

 Double dblParams [] = {Double.valueOf((String)
         salaryMap.get("MIN_SALARY"))
           ,Double.valueOf((String)
         salaryMap.get("MAX_SALARY"))
       };
 //The getJdbcTemplate method of JdbcDaoSupport returns an
 //instance of JdbcTemplate initialized with a datasource by the
 //Spring Bean Factory
 JdbcTemplate daoTmplt = this.getJdbcTemplate();
 return daoTmplt.queryForList(FIND_BY_SAL_RNG,dblParams);
}
}

In the listing above, the values from the incoming parameter map are stored in a double array, in the same order as the positional parameters in the SQL string. The query results are returned by the queryForList() method as a List (one entry for each row) of Maps (one entry for each column, using the column name as the key). Later I'll show you how to return a list of transfer objects.

From the simplified code, it is evident that JdbcTemplate fosters reuse, and this has resulted in significant code reduction in the DAO implementation. The tight coupling with the JDBC and collection packages has been removed. Leakage of JDBC resource is no longer a problem as JdbcTemplate methods ensure that database resources are released in the proper sequence after they have been used.

In addition, you are not forced to handle exceptions while using Spring DAO. The JdbcTemplate class handles the SQLException , and translates it into a Spring-specific exception hierarchy based on SQL error codes or the error states. For example, DataIntegrityViolationException is raised when there is an attempt to insert duplicate values in a primary key column. However, you need not handle this exception if you cannot recover from this error. This is possible because the root exception class in Spring DAO, DataAccessException is a runtime exception. It is noteworthy that the Spring DAO exceptions are independent of the data access implementation. The same exceptions are raised if the implementation is provided by O/R mapping solutions.

Step 2: Alter Business Service - The business service now implements a new method setDao(), which is used by the Spring container to pass a reference of the DAO implementation class. This process is called "setter injection," and the Spring container is informed of this via the configuration file shown in Step 3. Note that it is no longer necessary to use the DAOFactory as the Spring BeanFactory compliments that functionality:

public class EmployeeBusinessServiceImpl
                      implements IEmployeeBusinessService {

IEmployeeDAO empDAO;

public List getEmployeesWithinSalaryRange(Map salaryMap){

 List empList = empDAO.findBySalaryRange(salaryMap);
 return empList;
}
public void setDao(IEmployeeDAO empDAO){
 this.empDAO = empDAO;
}
}

You see the flexibility of P2I; there is only a minimal change in the business service implementation, even though I have overhauled the DAO implementation. The minimal impact is due to the business services now being managed by the Spring container.

Step 3: Configure Bean Factory - The Spring bean factory needs a configuration file to initialize and start the Spring framework. This configuration file enlists all the business service and DAO implementation classes with the Spring bean container. Apart from this, it also has the information to initialize the datasource and JdbcDaoSupport:




"http://www.springframework.org/dtd/spring-beans.dtd">


  
  
    class="org.springframework.jndi.JndiObjectFactoryBean"> 
     
      
        
          weblogic.jndi.WLInitialContextFactory
        
        
          t3://localhost:7001
        
      
     
     
      
        jdbc/DBPool
       
    
  

  
  
    
      
    
  

  
  
  class="com.bea.dev2dev.sampleapp.business.EmployeeBusinessServiceImpl">

The Spring bean container sets the datasource object with the DAO implementation, by invoking the setDataSource() method available from JdbcDaoSupport. It also takes the responsibility of supplying the business service with the DAO implementation.

Step 4: Test - Finally it's time to write the JUnit test class. Going by Spring philosophy, I will test it outside the container. However, as evident from the configuration file in Step 3, I have been using the WebLogic Server connection pool.

package com.bea.dev2dev.business;

import java.util.*;
import junit.framework.*;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

public class EmployeeBusinessServiceImplTest extends TestCase {
  private IEmployeeBusinessService empBusiness;
  private Map salaryMap;
  List expResult;

  protected void setUp() throws Exception {
      initSpringFramework();
      initSalaryMap();
      initExpectedResult();
  }
  private void initExpectedResult() {
      expResult = new ArrayList();
      Map tempMap = new HashMap();
      tempMap.put("EMP_NO",new Integer(1));
      tempMap.put("EMP_NAME","John");
      tempMap.put("SALARY",new Double(46.11));
      expResult.add(tempMap);
  }
  private void initSalaryMap() {
      salaryMap = new HashMap();
      salaryMap.put("MIN_SALARY","1");
      salaryMap.put("MAX_SALARY","50");
  }
  private void initSpringFramework() {
    ApplicationContext ac = new FileSystemXmlApplicationContext
 ("C:/SpringConfig/Spring-Config.xml");
    empBusiness =
           (IEmployeeBusinessService)ac.getBean("EMP_BUSINESS");
  }
  protected void tearDown() throws Exception {
  }

  /**
   * Test of getEmployeesWithinSalaryRange method,
   * of class
   * com.bea.dev2dev.business.EmployeeBusinessServiceImpl.
   */
  public void testGetEmployeesWithinSalaryRange() {
    List result = empBusiness.getEmployeesWithinSalaryRange
    (salaryMap);
    assertEquals(expResult, result);      
  }   




}


Using Bind Variables

So far, I have been searching employees between a minimum and maximum salary range. Let's assume a hypothetical scenario where the business users want that range to be reversed. The DAO code is fragile and would have to change to cater to modified requirements. The problem lies with the use of positional bind variables (denoted by "?") that are static. Spring DAO comes to the rescue by providing support for named bind variables. The modified listing of IEmployeeDAO introduces named bind variables (denoted by ":"). Note the change in query, as shown below:

import java.util.Map; public interface IEmployeeDAO { //SQL String that will be executed public String FIND_BY_SAL_RNG = "SELECT EMP_NO, EMP_NAME, " + "SALARY FROM EMP WHERE SALARY >= :max AND SALARY <= :min"; //Returns the list of employees falling into the given salary range //The input parameter is the immutable map object obtained from //the HttpServletRequest. This is an early refactoring based on //- "Introduce Parameter Object" public List findBySalaryRange(Map salaryMap); }

The majority of JDBC drivers support only positional bind variables. Therefore, at runtime Spring DAO transforms this query to a positional bind, variable-based query and sets the appropriate bind variables. In order to perform these tasks, you now need to use the NamedParameterJdbcDaoSupport and NamedParameterJdbcTemplate classes, instead of JdbcDaoSupport and JdbcTemplate . Here is the modified DAO implementation class:

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; public class EmployeeDAOImpl extends NamedParameterJdbcDaoSupport implements IEmployeeDAO{ public List findBySalaryRange(Map salaryMap){ NamedParameterJdbcTemplate tmplt = this.getNamedParameterJdbcTemplate(); return tmplt.queryForList(IEmployeeDAO.FIND_BY_SAL_RNG ,salaryMap); } }

The getNamedParameterJdbcTemplate() method of NamedParameterJdbcDaoSupport returns an instance of NamedParameterJdbcTemplate, pre-initialized with the datasource handle. The Spring Beanfactory performs the initialization tasks, getting all the details from the configuration file. NamedParameterJdbcTemplate delegates the operation to JdbcTemplate once the substitution from the named parameters to positional placeholders is done at execution time. You can see that the use of named parameters makes the DAO methods immune to any kind of change in the underlying SQL statement.

Finally, the initSalaryMap() method in the JUnit test class needs a slight modification, as shown below, in case your database does not support automatic type conversion.

private void initSalaryMap() { salaryMap = new HashMap(); salaryMap.put("MIN_SALARY",new Double(1)); salaryMap.put("MAX_SALARY",new Double(50)); }

Spring DAO Callbacks

So far I've shown how the static parts of JDBC code have been encapsulated and generalized in the JdbcTemplate class to solve the problems in traditional DAO design. Now let's turn the focus to the variable aspects, like setting bind variables and iterating over ResultSets. Although Spring DAO has generalized solutions to these problems, in certain SQL-based conditions, you may need to set the bind variables.

In the endeavor to switch over to Spring DAO, I have introduced a subtle runtime error, due to a broken contract between the business service and its clients. The source of this error can be traced back to the original DAO. It no longer returns a list of EmployeeTO instances. Rather, the dbcTemplate.queryForList() method returns a list of maps (each map being a row in the result set).

As you know by now, the JdbcTemplate is based on the template method design pattern that defines the SQL execution workflow using the JDBC API. This workflow has to be altered to fix the broken contract. The first option is to alter/extend the workflow in a subclass. You can iterate over the list returned by JdbcTemplate.queryForList() and replace the map objects with EmployeeTO instances. However, this leads to the mingling of static and dynamic code, which I have been so desperately trying to avoid. The second option is to plug the code into various workflow alteration hooks provided by JdbcTemplate. It would be wise to encapsulate the transfer object population code in a different class, and then link it through the hook. Any alteration in the population logic will still leave the DAO unchanged.

The second option is achieved through classes that implement a method defined in a Spring framework-specific interface. These methods are called callbacks and are registered with the framework through JdbcTemplate. These methods are invoked by the framework when an appropriate event (for example, ResultSet has to be iterated and populated in framework-independent transfer objects) occurs.

Step 1: The transfer object

Here is the transfer object that you may be interested in. Note that the transfer object presented below is fixed:

package com.bea.dev2dev.to; public final class EmployeeTO implements Serializable{ private int empNo; private String empName; private double salary; /** Creates a new instance of EmployeeTO */ public EmployeeTO(int empNo,String empName,double salary) { this.empNo = empNo; this.empName = empName; this.salary = salary; } public String getEmpName() { return this.empName; } public int getEmpNo() { return this.empNo; } public double getSalary() { return this.salary; } public boolean equals(EmployeeTO empTO){ return empTO.empNo == this.empNo; } }

Step 2: Implement the callback interface

The RowMapper interface is implemented to populate the transfer objects from the result set. Here's an example:

package com.bea.dev2dev.dao.mapper; import com.bea.dev2dev.to.EmployeeTO; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; public class EmployeeTOMapper implements RowMapper{ public Object mapRow(ResultSet rs, int rowNum) throws SQLException{ int empNo = rs.getInt(1); String empName = rs.getString(2); double salary = rs.getDouble(3); EmployeeTO empTo = new EmployeeTO(empNo,empName,salary); return empTo; } }

Note that the implementation class should not call the next() method on the supplied ResultSet object. This is taken care of by the framework, and it should extract only values from the current row of the result set. Any SQLException raised by the callback implementation is also handled by the Spring framework.

Step 3: Plug in the callback interface

JdbcTemplate uses a default RowMapper implementation to produce a list of maps when a SQL query is executed. I now need to register the custom callback implementation to alter this behavior of JdbcTemplate. Note that I am now using the query() method of NamedParameterJdbcTemplate, instead of the queryForList() method:

public class EmployeeDAOImpl extends NamedParameterJdbcDaoSupport implements IEmployeeDAO{ public List findBySalaryRange(Map salaryMap){ NamedParameterJdbcTemplate daoTmplt = getNamedParameterJdbcTemplate(); return daoTmplt.query(IEmployeeDAO.FIND_BY_SAL_RNG, salaryMap, new EmployeeTOMapper()); } }

The Spring DAO framework iterates through the results returned after the query execution. At each step of the iteration, it invokes the mapRow() method implemented by the EmployeeTOMapper class to populate each row of the final result with a EmployeeTO transfer object.

Step 4: Modified JUnit class

The results will now be tested against the returned transfer objects. Therefore, there are modifications in the test methods.

public class EmployeeBusinessServiceImplTest extends TestCase { private IEmployeeBusinessService empBusiness; private Map salaryMap; List expResult; // all methods not shown in the listing remain the // same as in the previous example private void initExpectedResult() { expResult = new ArrayList(); EmployeeTO to = new EmployeeTO(2,"John",46.11); expResult.add(to); } /** * Test of getEmployeesWithinSalaryRange method, of * class com.bea.dev2dev.business. * EmployeeBusinessServiceImpl */ public void testGetEmployeesWithinSalaryRange() { List result = empBusiness. getEmployeesWithinSalaryRange(salaryMap); assertEquals(expResult, result); } public void assertEquals(List expResult, List result){ EmployeeTO expTO = (EmployeeTO) expResult.get(0); EmployeeTO actualTO = (EmployeeTO) result.get(0); if(!expTO.equals(actualTO)){ throw new RuntimeException("** Test Failed **"); } } }

Benefits

The advantages of Spring's JDBC framework are clear. I have made significant gains and reduced the DAO methods to only a few lines of code. The code is no longer fragile thanks to out-of-the-box support for named parameter bind variables and the separation of transfer object population logic in mappers. The perennial problems of resource leakage and error handling are no longer cause for concern.

The benefits of Spring JDBC should encourage you to move your existing code to this framework. This article hopefully will help guide you in this endeavor. It will help you to be armed with some tools and knowledge on Refactoring. For example, in case you have not adopted the P2I Extract Interface, refactoring can be used to create interfaces from existing DAO implementation classes. Apart from this, take a look at this article's references for additional pointers.

Download

  • You can download the source code used in this article.

Summary

In this article I have shown you the basics of the Data Access Object(DAO) design pattern and discussed the pros and cons associated with it. Spring DAO or the JDBC framework was introduced to address the demerits of traditional DAO. Then, the fragile DAO code was amended with named parameter support provided out of the box by the Spring framework. Finally, the callback feature demonstrated how the behavior of the framework can be altered at specified points.

References


Posted by Arun at 6:20 PM
Subscribe to: Post Comments (Atom)
 

Share/Bookmark