Unit testing a DAO class that uses Spring JDBC

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.

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.

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.