A Primer on Spring's Data Access Object (DAO) Framework
Pages: 1, 2, 3

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 ":<some name>"). Note the change in query, as shown below:

import java.util.Map;

public interface IEmployeeDAO {

  //SQL String that will be executed


                         :max AND SALARY <=  

  //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 = 


    return tmplt.queryForList(IEmployeeDAO.FIND_BY_SAL_RNG




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 = 


    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);




       * Test of getEmployeesWithinSalaryRange method, of 

       * class com.bea.dev2dev.business.

       * EmployeeBusinessServiceImpl


      public void testGetEmployeesWithinSalaryRange() {

          List result = empBusiness.


          assertEquals(expResult, result);        


      public void assertEquals(List expResult, List result){

          EmployeeTO expTO = (EmployeeTO) expResult.get(0);

          EmployeeTO actualTO = (EmployeeTO) result.get(0);


               throw new RuntimeException("** Test Failed **");





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.


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


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.


  • Core J2EE Patterns: Data Access Object (Sun Developer Network) - provides a detailed description of the DAO design pattern
  • Spring DAO Framework - the official Spring DAO documentation
  • Refactoring - this site explains refactoring basics and catalogs all the refactorings detailed in Martin Fowler's book, Refactoring: Improving the Design of the Existing Code; this site also contains a list of tools that can be used for refactoring

Dhrubojyoti Kayal works as a Senior Consultant with Capgemini Consulting. He has more than five years of experience developing and desigining applications and products leveraging Enterprise Java technologies.