Writing a domain integration test against database

In episode 22 we how to seed a database with spring's @Sql annotation so lets extend it and find out how to test a domain with database interactions.

Detailed Video Notes

Getting started

For our exercise we have been given a user story to "Update an existing person's password" which will require us to create a Person table, POJO and a data access object. We will use spring's jdbcTemplate to interact with the database but it could be substituted by injecting mybatis or hibernate data access beans. Once those pieces are created we will put it together an integration test.

This process may vary depending what techniques and methodologies you are using such as TDD.

Project set up

[0:21]

Let's get started by creating a spring boot project from the starter web site selecting spring-boot-starter-jdbc and spring-boot-starter-web. We will then download and import the maven project into eclipse while adding derby as additional dependency in the pom.xml. Spring-boot recognizes derby is on the classpath and auto configures an embedded database which means you do not need to specify any connection URLs or driver class names (pretty neat). If you are wanting to dig into the specifics check out EmbeddedDatabaseConnection and EmbeddedDataSourceConfiguration.

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derby</artifactId>
    <version>10.11.1.1</version>
</dependency>

Derby could be substituted with other databases such as oracle, mysql or another database by specifying properties in application.properties or application.yml.

spring.datasource.url=jdbc:mysql://<hostname>[<:3306>]/<dbname>
spring.datasource.username=me
spring.datasource.password=me
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Creating Person domain and DAO

[1:2]

Next lets create a Person POJO and PersonDAO. As we mentioned earlier the PersonDAO will use JdbcTemplate abstraction to access the database. We implemented a PersonRowMapper that implements RowMapper to map result set row to a Person object, a getPerson and updatePassword method.

public class Person {

    private int personId;
    private String firstName;
    private String middleName;
    private String lastName;
    private String userId;
    private String password;
}

@Component
public class PersonDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public class PersonRowMapper implements RowMapper<Person> {

        @Override
        public Person mapRow(ResultSet rs, int rowNum) throws SQLException {

            Person person = new Person();

            person.setPersonId(rs.getInt("PERSONID"));
            person.setFirstName(rs.getString("firstname"));
            person.setMiddleName(rs.getString("MIDDLENAME"));
            person.setLastName(rs.getString("LASTNAME"));
            person.setUserId(rs.getString("USERID"));
            person.setPassword(rs.getString("PASSWORD"));

            return person;
        }

    }

    public Person getPerson(int personId) {
        String sql = "SELECT * from PERSON WHERE PERSONID = " + personId;
        return jdbcTemplate.queryForObject(sql, new PersonRowMapper());
    }

    public int updatePassword (Person person) {

        String updateSql = "UPDATE PERSON SET PASSWORD = ? WHERE PERSONID = ?";

        return jdbcTemplate.update(
                updateSql,
                new Object[]{person.getPassword(), person.getPersonId()},
                new int[]{Types.VARCHAR, Types.INTEGER});
    }
}

Setting up the database

[1:25]

Once the foundation is created, we will want to write a test to validate that a person's password was updated. An important thing to mention is when running integration tests against a database is that is in a known state before each test to have consistent reproducible tests. To achieve this we will use spring's @Sql and @SqlGroup annotation.

We covered this in detail in episode 22 so at a high level using these annotations we create a person table, preload the entry of "Fred" and drop the table upon completion of the test all against our embedded database.

...
@SqlGroup({
        @Sql(executionPhase = ExecutionPhase.BEFORE_TEST_METHOD,
                scripts = "classpath:beforeScripts.sql"),
        @Sql(executionPhase = ExecutionPhase.AFTER_TEST_METHOD,
        scripts = "classpath:afterScripts.sql") })
public class ApplicationTests {
...

Writing the test

[1:50]

After the database and data is set up we are ready to write our unit test. Since we know we seeded the database with a person and their id equaling one, we will create a Person object setting the id to 1 and the password to "wilma". In the event that we didn't know which person existed we could first queried the database to get a random person and followed the same steps.

Next calling the personDAO.updatePassword and passing the Person object we just created will execute an update statement which we can see by examining the logs.

2015-01-25 07:47:20.585 DEBUG 49978 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [UPDATE PERSON SET PASSWORD = ? WHERE PERSONID = ?]
2015-01-25 07:47:20.585 DEBUG 49978 --- [           main] o.s.jdbc.datasource.DataSourceUtils      : Fetching JDBC Connection from DataSource
2015-01-25 07:47:20.618 DEBUG 49978 --- [           main] o.s.jdbc.core.JdbcTemplate               : SQL update affected 1 rows

Finally we can validate the update occured by calling personDAO.getPerson.

2015-01-25 07:47:20.621 DEBUG 49978 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL query [SELECT * from PERSON WHERE PERSONID = 1]

Putting it all together

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = Application.class)
@WebAppConfiguration
@SqlGroup({
        @Sql(executionPhase = ExecutionPhase.BEFORE_TEST_METHOD,
                scripts = "classpath:beforeScripts.sql"),
        @Sql(executionPhase = ExecutionPhase.AFTER_TEST_METHOD,
        scripts = "classpath:afterScripts.sql") })
public class ApplicationTests {

    @Autowired
    private PersonDAO personDAO;

    @Test
    public void should_update_password() {

        Person person = new Person();
        person.setPersonId(1);
        person.setPassword("wilma");

        personDAO.updatePassword(person);

        // validate update occurred
        Person updatedPerson = personDAO.getPerson(1);
        assertEquals("wilma", updatedPerson.getPassword());
    }
}

Testing interactions between a domain and a database can be challenging and spring has made it easy to set up, tear down and execute scripts against a database. It is important to find the balance between what an integration test can offer and interactions you can test with mocking frameworks such as mockito.

Thanks for joining in today's level up, have a great day!