Testing the database layer
The database is an integral part of many applications and writing queries is often hard. For that reason I have always written integration tests for the data access objects or DAO’s that I use to access the database. The way I write my DAO tests has changed a lot over the years and in this post I’d like to document the ways in which it has changed and why.
DbUnit
The first integration tests I wrote for the database used DbUnit (www.dbunit.org). DbUnit lets you to export a pre-populated database to an XML file. In the setup method of your integration test you then call a utility method that clears the database and initializes the database with the exported data. This worked relatively well most of the time but still I noticed a number of problems with this approach. First of all XML is a terrible format to export data to. It’s overly verbose and XML’s escaping rules make manual editing of the exported data a non trivial task. Of course this doesn’t matter if you always use tooling to recreate the dataset. However at least at the time the tooling left somewhat to be desired. In particular I was having problems with the ordering of the exported data. For instance if you had order lines that contained a foreign key that pointed to an order, DbUnit would sometimes place the order lines at the top of the file, leading to constraint violations when re-importing the file. At some later point the authors added something that I believe is called a DatabaseSequenceFilter that supposedly solves this issue. However it never really worked well for me. The second issue I had with it is that because DbUnit actually commits all the data to the database it is a) rather slow and b) opens up the possibility of inadvertently leaving data in the database that can break other tests if you do not practice proper testing hygiene.
AbstractTransactionalDataSourceSpringContextTests
A number of years ago a collegue at JTeam introduced me to a class called AbstractTransactionalDataSourceSpringContextTests (nowadays deprecated in favor of AbstractJUnit38SpringContextTests and AbstractTransactionalJUnit4SpringContextTests), which is part of Spring (www.springframework.org) the purpose of this class is twofold. First of all it contains a number of convenience methods such as executeSqlScript and deleteFromTables that make it easy to initialize a database to a known state. In a way this is a simpler alternative to some of the functionality that DbUnit offers.
Second and more importantly it makes sure that for each test method a database transaction is started. All your testing code operates within this transaction and upon completion of the test the transaction is rolled back. This means that your modifications never get committed and your database stays clean. Because a rollback is a lot faster than actually committing the data and then removing it, this affects performance in a noticeable way.
Note that AbstractTransactionalDataSourceSpringContextTests does in fact not rule out the use of DbUnit. If for some reason you like DbUnit you can still use DbUnit to import your data from within the onSetUpInTransaction method in the classic AbstractTransactionalDataSourceSpringContextTests or from a method annotated with @Before if you use one of the newer annotation based base classes.
Personally not liking the XML format that DbUnit uses all that much, I never seriously considered it, rather than using DbUnit I initially created one inline fixture per method.
Fixture per method
The class below shows an example of this
public class HibernateOrderDaoTest extends AbstractTransactionalDataSourceSpringContextTests {
....
public void testGetById() {
// ... many insert statements for all the related data, countries, shipping
jdbcTemplate.execute("INSERT INTO order (id, version, shipping_address_street, shipping_address_city, shipping_address_state, shipping_address_postcode, shipping_address_country_id, billing_address_street, billing_address_city, billing_address_state, billing_address_postcode, billing_address_country_id, account_id, submitted_on, total_price_currency, total_price_amount, total_price_amount_inclusive, active_status_type_name, last_exported, shipping_service_id)" +
"VALUES (1, 0, 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', '100', 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', '100', 1, '2005-9-27', 'EUR', 100, 120, 'created', '2005-9-27', 1)");
Order order = dao.getById(1L);
// verify all the fields
}
}
What I liked about this is that the SQL is in-lined in the test method. No more switching between an external file that contains the data and the test code.
However there are also problems. Often there are interdependencies between tables. For instance, an order is placed by a user who lives in a country. So before I can create the order I must first have created the country and user. This quickly adds up for non-trivial domains.
What more because I am using a fixture per method I find that I am repeating myself over and over again. A fixture per method only works well if the object you’re testing has no interdependencies.
One way to solve this for many scenarios is to move the SQL for the interdependencies to a method that gets called before any test code is run
Fixture per method + shared setup per class
The class below shows an example of this
public class HibernateOrderDaoTest extends AbstractTransactionalDataSourceSpringContextTests {
protected void doSetUpInTransaction() throws Exception {
jdbcTemplate.execute("INSERT INTO country(id, version, name, short_name) VALUES (100, 0, 'Netherlands', 'NL');");
jdbcTemplate.execute("INSERT INTO account (id, version, username, password, first_name, last_name, email, phone_number, billing_address_street, billing_address_city, billing_address_state, billing_address_postcode, billing_address_country_id, shipping_address_street, shipping_address_city, shipping_address_state, shipping_address_postcode, shipping_address_country_id, last_signin, active, agreed_to_latest_terms) " +
"VALUES (1, 0, 'leonard', 'c539eadb15ce2243242196e428986d70', 'Leonard', 'Wolters', 'leonard@jteam.nl', '+31 020-1234567', 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', 100, 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', 100, '2005-9-30', false, true);");
// etc etc..
}
public void testFindOrdersAboveOneHundredEuros() {
jdbcTemplate.execute("INSERT INTO order (id, version, shipping_address_street, shipping_address_city, shipping_address_state, shipping_address_postcode, shipping_address_country_id, billing_address_street, billing_address_city, billing_address_state, billing_address_postcode, billing_address_country_id, account_id, submitted_on, total_price_currency, total_price_amount, total_price_amount_inclusive, active_status_type_name, last_exported, shipping_service_id)" +
"VALUES (1, 0, 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', '100', 'Straatnaam 23', 'Eindhoven', 'Noord Brabant', '1234AB', '100', 1, '2005-9-27', 'EUR', 100, 120, 'created', '2005-9-27', 1)");
// insert a few more orders with varying
List<Order> orders = dao.FindOrdersAboveOneHundredEuros();
}
}
While this solves the problem outlined above there is still a lot of room for improvement
First of all, every time you insert a row in the database you’re effectively duplicating all the column names. This means that if at some point a database column name changes you will have to go over each insert statement and update this statement by hand. This same problem arises when you introduce a new required column or remove a column. The removal of a column I found to be particularly time consuming and error prone in this approach because of the difficulty you will have figuring out which column belongs to which value. It caused me to format my insert statements like this when I was using this approach.
INSERT INTO order (id, version, shipping_address_street)
values (1, 0, 'Straatnaam 23');
Second, often a lot of the information contained in the insert statements is irrelevant to many tests. If you’re testing a method that returns all orders for 100 euros or more you are not interested in the customer’s shipping details. It just clutters the code and distracts from the actual purpose of the test.
The way we solved this initially was by creating a DatabaseHelper class that was used to insert rows into the database.
Helper classes
The above example we could rewrite as:
public class HibernateOrderDaoTest extends AbstractTransactionalDataSourceSpringContextTests {
private long countryId;
private long accountId;
protected void doSetUpInTransaction() throws Exception {
countryId = databasehelper.createCountry("Netherlands", "NL");
accountId = databasehelper.createAccount("username", "c539eadb15ce2243242196e428986d70", countryId);
// etc etc..
}
public void testFindOrdersAboveOneHundredEuros() {
databasehelper.createOrderWithAmount(accountId, 101);
databasehelper.createOrderWithAmount(accountId, 99);
// insert a few more orders
List<Order> orders = dao.FindOrdersAboveOneHundredEuros();
}
}
This looks much, much cleaner. The testFindOrdersAboveOneHundredEuros method is very concise and understandable. The fields that are not relevant are defaulted to some arbitrary valid value so you do not have to think about them.
However in different test scenario’s different information is relevant. Suppose we also have a method on our DAO that returns all orders shipped to a particular city.
In that case we either create a new method on the helper called createOrderWithCity(accountId, cityName) Or we rename the createOrderWithAmount method to createOrder and add an additional argument to this method
createOrder(accountId, cityName, amount);
Values will then be set to null depending on the test scenario.
The former scenario has the potential of growing out of control when the number of test cases grows, while the latter scenario is less concise because it requires you to think about information that is not relevant to your testcase (eg. the values you have to null out) Also if you change the method signature of this method you will have to update all the code that references this method.
Additionally i found that a single class that contains all your database inserts becomes a point of contention when working in a team.
To remedy these problems, recently I have been using builder classes for creating my test data.
Statement builders
These builder statements implement the following interface.
public interface InsertStatement<T> {
T execute(SimpleJdbcTemplate template) throws DataAccessException;
}
Here’s an example of an implementation
public class ProjectInsertStatement implements InsertStatement<Long> {
private long organizationId;
private String name;
private String goal = "goal";
private String description = "description";
private String externalRisks = "external risks";
public ProjectInsertStatement(long organizationId, String name) {
Assert.notNull(name);
this.organizationId = organizationId;
this.name = name;
}
public ProjectInsertStatement setGoal(String goal) {
this.goal = goal;
return this;
}
public ProjectInsertStatement setDescription(String description) {
this.description = description;
return this;
}
public ProjectInsertStatement setExternalRisks(String externalRisks) {
this.externalRisks = externalRisks;
return this;
}
public Long execute(SimpleJdbcTemplate template) {
JdbcInsertStatementBuilder builder = new JdbcInsertStatementBuilder("project");
builder.addParameter("name", name);
builder.addParameter("organization_id", organizationId);
builder.addParameter("goal", goal);
builder.addParameter("description", description);
builder.addParameter("external_risks", externalRisks);
builder.build().execute(template);
return template.queryForLong("select max(id) from project");
}
}
And here’s an example of how to use this class:
new ProjectInsertStatement(organizationId, "my project")
.setGoal("my goal")
.execute(template);
As you can see the statement has two required constructor arguments, the organisationId because a project always has to be associated with an organization, and a name, which in this case has to be unique so it cannot be defaulted. Goal, description and externalRisks are required but do not have to be unique so we can default these. A setter is provided for each of these fields so if you want to deviate from the default this is possible.
Some have commented on the fact that it is a lot of work to create these insert statement. While this is certainly true I do think the extra work its worth it in the long run.
Do not rely on any of the defaults.
Suppose you wanted to test a method that counts all projects whose goal is 4 letters long you could write it like this:
new ProjectInsertStatement(organizationId, "my project 1")
.setGoal("my goal")
.execute(template);
new ProjectInsertStatement(organizationId, "my project 2")
.execute(template);
assertEquals(1, dao.countProjectsWithFourLetterGoal());
While this test would pass because that default goal is “goal” (4 letters) its not self documenting and it would break if someone changed the default in the insert statement If something is relevant to what you are testing, be explicit about it.
Use fixture classes
Often a fixture can be shared across multiple classes so these days I tend to move fixtures to an external class
Here’s an example of a fixture class:
public class DonationFixture {
private long organizationId;
private long projectId;
private DonationFixture(SimpleJdbcTemplate template) {
organizationId = new OrganizationInsertStatement("Greenpeace")
.execute(template);
projectId = new ProjectInsertStatement(organizationId, "Save the monkey")
.execute(template);
}
public long getProjectId() {
return projectId;
}
public long getOrganizationId() {
return organization1Id;
}
public static DonationFixture create(SimpleJdbcTemplate jdbcTemplate) {
Assert.notNull(jdbcTemplate);
return new DonationFixture(jdbcTemplate);
}
}
And some code that uses it:
@Test
public void testFindById() {
DonationFixture fixture = DonationFixture.create(simpleJdbcTemplate);
String comment = "comment";
long id = new DonationInsertStatement(fixture.getProject1Id(), 10)
.setComment(comment)
.execute(simpleJdbcTemplate);
Donation donation = donationDao.findById(id);
assertEquals(id, (long) donation.getId());
assertEquals(comment, donation.getComment());
assertEquals(fixture.getProject1Id(), (long) donation.getProject().getId());
}
The above code is taken from an example project that you can download from here.
It uses JPA for the DAO implementation and runs against an in memory H2 database.
So you do not have to setup and external database to run the code.
