Testing SQL queries with Spring and DbUnit, part 1

SQL queries or any sort of persistence components are very common in enterprise applications. More generally, persistence is a critical part in applications and that’s why it should be thoroughly tested, whether it uses plain SQL or object/relational mapping tools like Hibernate. This article covers how to leverage the Spring TestContext Framework and DbUnit to test the persistence layer of an application. This first part shows a straightforward yet functional integration of both frameworks. A second part will then show how to integrate DbUnit into the Spring test framework in a more flexible way.

The code to be tested

The code to be tested is a simple repository (a.k.a DAO) which is based on Spring’s SimpleJdbcTemplate:

1. @Transactional
2. public class JdbcContactRepository implements ContactRepository {
4. private SimpleJdbcTemplate jdbcTemplate;
6. public JdbcContactRepository(DataSource dataSource) {
7. this.jdbcTemplate = new SimpleJdbcTemplate(dataSource);
8. }
10. @Override
11. public Contact create(Contact contact) {
12. long nextId = jdbcTemplate.queryForLong(« select nextval(‘contact_seq’) »);
13. jdbcTemplate.update(
14. « insert into contact (id,firstname,lastname) values (?,?,?) »,
15. nextId,contact.getFirstname(),contact.getLastname()
16. );
17. contact.setId(nextId);
18. return contact;
19. }
21. @Override
22. public List<Contact> selectByLastname(String lastname) {
23. return jdbcTemplate.query(
24. « select * from contact where lastname like ? »,
25. new ContactRowMapper(),
26. « % »+lastname+« % »
27. );
28. }
30. private static class ContactRowMapper implements RowMapper<Contact> {
32. @Override
33. public Contact mapRow(ResultSet rs, int rowNum) throws SQLException {
34. return new Contact(rs.getLong(« id »), rs.getString(« firstname »),rs.getString(« lastname »));
35. }
37. }
39. }

Nothing fancy so far. Just note that this repository class acts as our guinea pig for the persistence test demonstration. What we are about to see is valid for any persistence technology (plain JDBC, but also Hibernate, JPA, iBatis, etc.)

The application configuration

Here is a simple Spring configuration for declaring the repository and the transaction policy :

1. <beans (…)>
3. <bean id=« contactRepository » class=« com.zenika.contact.repository.jdbc.JdbcContactRepository »>
4. <constructor-arg ref=« dataSource » />
5. </bean>
7. <bean id=« transactionManager » class=« org.springframework.jdbc.datasource.DataSourceTransactionManager »>
8. <property name=« dataSource » ref=« dataSource » />
9. </bean>
11. <tx:annotation-driven />
13. </beans>

The previous snippet shows what we usually call the « application configuration », as this configuration is all about application components and can be re-used across environments (production, testing). Note that this configuration is not self-sufficient, as it relies on a dataSource bean, which is typically defined in a « infrastructure configuration » file. In production, the dataSource bean can be defined as a JNDI lookup and for tests, it can be an embedded database.
Last note: the repositories and the transaction manager should also be defined in their own configuration files (that’s how it’s done in the code samples), but we gathered them here for brevity.

The test configuration

The test configuration re-uses the application configuration and defines a in-memory DataSource and a SimpleJdbcTemplate (useful for assertions in the test) :

1. <beans (…)>
3. <import resource=« classpath:/contact-configuration.xml » />
5. <jdbc:embedded-database id=« dataSource » type=« H2 »>
6. <jdbc:script location=« classpath:/contact-create.sql »/>
7. </jdbc:embedded-database>
9. <bean class=« org.springframework.jdbc.core.simple.SimpleJdbcTemplate »>
10. <constructor-arg ref=« dataSource » />
11. </bean>
13. </beans>

We use the Spring 3.0’s jdbc namespace, which creates a in-memory database and can also execute scripts on it after its creation. The contact-create.sql file creates the table of the application, here is its content :

1. DROP sequence contact_seq IF EXISTS;
2. CREATE sequence contact_seq;
3. CREATE TABLE contact (
4. id int,
5. firstname varchar(255),
6. lastname varchar(255),
7. constraint contact_pk PRIMARY KEY (id)
8. );

Let’s see now how to write the test with the Spring TestContext Framework.

Writing a test with the Spring TestContext Framework

The Spring TestContext Framework allows to bootstrap a Spring application context for a test. It’s thus useful for integration tests, when you want to assert that several of your components integrate correctly with each other and/or with an external system like a database. That’s exactly what we want for testing our repository: to check that the queries work properly when run on a database.
Using the Spring TestContext Framework is easy as it consists mainly in annotating a JUnit 4 test. Here is how to leverage the test framework :

1. package com.zenika.contact.repository.jdbc;
3. import org.junit.Test;
4. import org.junit.runner.RunWith;
5. import org.springframework.beans.factory.annotation.Autowired;
6. import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
7. import org.springframework.test.context.ContextConfiguration;
8. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
9. import com.zenika.contact.repository.ContactRepository;
11. @RunWith(SpringJUnit4ClassRunner.class)
12. @ContextConfiguration(locations={
13. « classpath:/repository-test-context.xml »
14. })
15. public class JdbcContactRepositoryTest {
17. @Autowired
18. ContactRepository contactRepository;
20. @Autowired
21. SimpleJdbcTemplate jdbcTemplate;
23. @Test public void create() {
24. // test method
25. }
27. }

Some explanations about how the test works :

• the RunWith annotation is from JUnit and it tells the test launcher (Eclipse, Maven, etc) which runner to use to execute the test. We hand off the execution to a Spring runner, which is the entry point of the Spring TestContext Framework.
• with the ContextConfiguration annotation, we tell the runner to boostrap a Spring application context from the file(s) in the locations attribute of the annotation.
• as the Spring test runner is
  in charge of the test lifecycle, it can inject some dependencies from the application context into it. We use this feature to inject the to-be-tested repository and the SimpleJdbcTemplate declared in the test infrastructure file.

The dependency injection feature is not the only thing the test framework brings. It also caches Spring application contexts in case they are used across tests. This is especially useful when you run a test suite that uses the same application context (e.g. the whole repository test suite): the application context is instantiated only once, which speeds up the execution of the tests (some beans like a Hibernate SessionFactory or a JPA EntityManagerFactory take some time to bootstrap as they have to compute all the mapping metadata).
The Spring TestContext Framework comes also with several annotations – to manage transactions across the test methods for instance – but we won’t cover all of them in this article. Let’s focus on the test and see how to test the create method of the repository.

Testing the create method

What we want to check is the insert query. For brevity’s sake, we’ll do this in a quick’n’dirty way, meaning we’ll only check a new row has been inserted in the database (we won’t check that this new row contains the exact same values we sent). Here is the content of the test of the create method :

1. @RunWith(SpringJUnit4ClassRunner.class)
2. @ContextConfiguration(locations={
3. « classpath:/repository-test-context.xml »
4. })
5. public class JdbcContactRepositoryTest {
7. @Autowired
8. ContactRepository contactRepository;
10. @Autowired
11. SimpleJdbcTemplate jdbcTemplate;
13. @Autowired
14. DataSource dataSource;
16. @Test public void create() {
17. int initialCount = jdbcTemplate.queryForInt(« select count(1) from contact »);
18. Contact contact = new Contact(« Arnaud », « Cogoluegnes »);
19. contactRepository.create(contact);
20. Assert.assertEquals(initialCount+1,jdbcTemplate.queryForInt(« select count(1) from contact »));
21. }
23. }

Note the use of the SimpleJdbcTemplate as a test support class – its straightforward API is particularly suited for these cases (as well as for more advanced uses in applications). Now you can run the test with your favorite test launcher.
That’s it for the create method, let’s see now how to test the selectByLastname method.

Testing the selectByLastname method

Testing this method is more interesting as common testing problems show up. If
we want to test this method correctly, the database needs to be in a known state. But if we don’t do anything, we have no guaranty about such a known state, as any test than ran previously can have altered the database. So we are on our own: we need to put the database in a known state before testing the selectByLastname. This can be done manually, thanks to an helper class like the SimpleJdbcTemplate, but this solution doesn’t scale when test data sets become more complex.
So we are going to use DbUnit, a framework dedicated to database tests. DbUnit allows to define data set into files (usually XML), comes with a API to inject these data sets into databases in different ways. DbUnit provides also helper classes to check the content of the database (mainly for assertion) and some base test classes.
Let’s start immediatly with DbUnit by writing the data set (we’re using DbUnit’s « flat XML » format) :

1. <dataset>
3. <contact id=« -1 » firstname=« Carl » lastname=« Azoury » />
4. <contact id=« -2 » firstname=« Olivier » lastname=« Croisier » />
6. </dataset>

In the flat XML data set format, the tag and attribute names are mapped with tables and columns, respectively. This makes writing the data sets really simple. Note that DbUnit can create data sets from an existing database and that data sets are not meant to contain the whole content of the database (just the data needed for the test case).
Once the dataset is created, we need to inject it in the database before each method of the test. This is a job for a JUnit setUp method. In JUnit 4, these methods need to be annotated with Before:

1. @RunWith(SpringJUnit4ClassRunner.class)
2. @ContextConfiguration(locations={
3. « classpath:/repository-test-context.xml »
4. })
5. public class JdbcContactRepositoryTest {
7. @Autowired
8. ContactRepository contactRepository;
10. @Autowired
11. SimpleJdbcTemplate jdbcTemplate;
13. @Autowired
14. DataSource dataSource;
16. @Before public void setUp() throws Exception {
17. IDataSet dataSet = new FlatXmlDataSetBuilder().build(new File(
18. « ./src/test/resources/dataset.xml »
19. ));
20. IDatabaseConnection dbConn = new DatabaseDataSourceConnection(dataSource);
21. DatabaseOperation.CLEAN_INSERT.execute(dbConn, dataSet);
22. }
23. (…)
24. }

We use DbUnit « clean insert » operation: it means that every table referenced in the data set is cleant before data are injected. Thanks to this, the create test method doesn’t interfere with the selectByLastname method. The following snippet shows the whole test, with the selectByLastname test method :

1. @RunWith(SpringJUnit4ClassRunner.class)
2. @ContextConfiguration(locations={
3. « classpath:/repository-test-context.xml »
4. })
5. public class JdbcContactRepositoryTest {
7. @Autowired
8. ContactRepository contactRepository;
10. @Autowired
11. SimpleJdbcTemplate jdbcTemplate;
13. @Autowired
14. DataSource dataSource;
16. @Before public void setUp() throws Exception {
17. IDataSet dataSet = new FlatXmlDataSetBuilder().build(new File(
18. « ./src/test/resources/dataset.xml »
19. ));
20. IDatabaseConnection dbConn = new DatabaseDataSourceConnection(dataSource);
21. DatabaseOperation.CLEAN_INSERT.execute(dbConn, dataSet);
22. }
24. @Test public void create() {
25. int initialCount = jdbcTemplate.queryForInt(« select count(1) from contact »);
26. Contact contact = new Contact(« Arnaud », « Cogoluegnes »);
27. contactRepository.create(contact);
28. Assert.assertEquals(initialCount+1,jdbcTemplate.queryForInt(« select count(1) from contact »));
29. }
31. @Test public void selectByLastname() {
32. Assert.assertEquals(0,contactRepository.selectByLastname(« cogo »).size());
33. Assert.assertEquals(1,contactRepository.selectByLastname(« our »).size());
34. Assert.assertEquals(2,contactRepository.selectByLastname(« o »).size());
35. }
37. }

Summary

We saw how to leverage the Spring TestContext Framework and DbUnit for testing a repository. DbUnit is a very useful toolkit for database testing and we integrate some of its features into our test. This works quite nice: each method of our test is isolated from the others and can run its tests effectively as the database is in a known state. But our DbUnit integration is too simple: it needs some specific code in each test and this code will be scattered all over our test suite, each time we need to inject data in the database. That’s why we’ll see in the second part of this article how to integrate DbUnit in a more effective and flexible way, thanks to an extension of the Spring TestContext Framework. Thanks to this integration, tests won’t depend on the DbUnit API any more and will be able to add the data set injection feature thanks to a couple of annotations.