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

by Dhrubojyoti Kayal
11/29/2006

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.

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.