13

I have several DAO objects that are used to retrieve information from a database and I really want to write some automated tests for them but I'm having a hard time figuring out how to do it.

I'm using Spring's JdbcTemplate to run the actual query (via a prepared statement) and map the results to the model object (via the RowMapper class).

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.

Here's the (simplified) code for the simplest DAO of the batch:

/**
 * Implementation of the {@link BusinessSegmentDAO} interface using JDBC.
 */
public class GPLBusinessSegmentDAO implements BusinessSegmentDAO {
    private JdbcTemplate jdbcTemplate;

    private static class BusinessSegmentRowMapper implements RowMapper<BusinessSegment>  {
        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<BusinessSegment> getBusinessSegments(String cc, String ll) {
        return jdbcTemplate.query(
                new GetBusinessSegmentsPreparedStatementCreator(cc, ll), 
                new BusinessSegmentRowMapper());
    }

}

Any idea would be appreciated.

Thanks!

Alex Ciminian
  • 11,398
  • 15
  • 60
  • 94
  • You could have a look at [Acolyte](http://acolyte.eu.org/), a framework to unit test the JDBC persistence, with real isolation – cchantep Jul 05 '17 at 00:17

3 Answers3

11

Please have a look at below links:

  1. Testing SQL queries with Spring and DbUnit
  2. MockObjects or DBUnit for testing Code using JdbcTemplate

Hope that helps.

EDIT:

Here is the GitHub version of RowMapperTests for easy reference.

Tony R
  • 7,136
  • 4
  • 21
  • 12
Nilesh
  • 4,137
  • 6
  • 39
  • 53
  • Now that SpringSource has moved to github, you can find RowMapperTests **[here](https://github.com/SpringSource/spring-framework/tree/3.1.x/org.springframework.jdbc/src/test/java/org/springframework/jdbc/core)**. EDIT: I have updated the answer with the new link. – Tony R Dec 23 '11 at 16:13
5

I recommend breaking your dependency on JdbcTemplate class, and using the JdbcOperations interface instead, e.g.

public class GPLBusinessSegmentDAO implements BusinessSegmentDAO {
    private final JdbcOperations jdbc;

    public GPLBusinessSegmentDAO(DataSource dataSource) {
        this(new JdbcTemplate(dataSource));
    }

    public GPLBusinessSegmentDAO(JdbcOperations jdbc) {
        this.jdbc = jdbc;
    }

    // ... DAO methods here
}

Your unit test can invoke the second constructor, passing in a mock JdbcOperations object. Since all DB operations are performed via the jdbc object, you can mock that easily enough.

Your live code can call the first constructor as before.

skaffman
  • 398,947
  • 96
  • 818
  • 769
2

To write a true unit test for this, you would not be touching a real database. You may however find it more practical to pass in a real DataSource to your underlying db, and test the getBusinessSegments() method returns 0, 1 and many results depending on the cc and ll values you pass in.

Another option worth investigating would be to pass in a DataSource of an embedded Java DB that was initialised with your schema in a setUp/@Before method. I guess what you really want to test is that the SELECT... query maps correctly to the schema, so such a test would catch any errors that arise at runtime when the schema, say, changes.

npellow
  • 1,985
  • 1
  • 16
  • 23