title: Unit testing a DAO class that uses Spring JDBC
body: <p>I have several DAO objects that are used to retrieve information from a database and I <em>really</em> want to write some automated tests for them but I'm having a hard time figuring out how to do it.</p>
  
  <p>I'm using Spring's <code>JdbcTemplate</code> to run the actual query (via a prepared statement) and map the results to the model object (via the <code>RowMapper</code> class).</p>
  
  <p>If I were to write unit tests, I'm not sure how I would/should mock the objects. For example, since there are only reads, I would use the actual database connection and not mock the jdbcTemplate, but I'm not sure that's right.</p>
  
  <p>Here's the (simplified) code for the simplest DAO of the batch:</p>
  
  <pre><code>/**
   * Implementation of the {@link BusinessSegmentDAO} interface using JDBC.
   */
  public class GPLBusinessSegmentDAO implements BusinessSegmentDAO {
      private JdbcTemplate jdbcTemplate;
  
      private static class BusinessSegmentRowMapper implements RowMapper&lt;BusinessSegment&gt;  {
          public BusinessSegment mapRow(ResultSet rs, int arg1) throws SQLException { 
              try {
                  return new BusinessSegment(rs.getString(...));
              } catch (SQLException e) {
                  return null;
              }
          }
      }
  
      private static class GetBusinessSegmentsPreparedStatementCreator 
          implements PreparedStatementCreator {
          private String region, cc, ll;
          private int regionId;
  
          private GetBusinessSegmentsPreparedStatementCreator(String cc, String ll) {
              this.cc = cc;
              this.ll = ll;
          }
  
          public PreparedStatement createPreparedStatement(Connection connection)
                  throws SQLException {           
              String sql = "SELECT ...";
  
              PreparedStatement ps = connection.prepareStatement(sql);
              ps.setString(1, cc);
              ps.setString(2, ll);
              return ps;
          }
      }
  
      public GPLBusinessSegmentDAO(DataSource dataSource) {
          jdbcTemplate = new JdbcTemplate(dataSource);
      }
  
      public Collection&lt;BusinessSegment&gt; getBusinessSegments(String cc, String ll) {
          return jdbcTemplate.query(
                  new GetBusinessSegmentsPreparedStatementCreator(cc, ll), 
                  new BusinessSegmentRowMapper());
      }
  
  }
  </code></pre>
  
  <p>Any idea would be appreciated.</p>
  
  <p>Thanks!</p>