Testing your JDBC data access layer with DBUnit and H2
In most server-side applications there will be a requirement to persist your data to some storage layer. Spring provides a neat abstraction in the form of the JdbcTemplate to achieve this, and in combination with a Spring-injected DataSource you can easily switch your data source between testing and production databases. Unfortunately the recommended and well-publicised test strategies for *integration* testing of your applications involving Apache Derby, HSQL (HSQLDB), and MySQL don’t enable rapid-fire *unit* testing of your data access code, since they require a real database to be at some known location on your disk or on a server (or in the case of HSQL don’t completely support all of the JDBC-3.0 spec).
Today, I’ll take you through combining some free components that enable you to run unit tests on your JDBC data access layer in a completely in-memory fashion, which allows you to more easily run regular complete and predictable tests of your data access layer and get consistent results. In fact, this solution isn’t limited to just Spring applications; any JDBC code can be tested like this!
I’ll assume through this article that you’ve already written yourself a JDBC DAO implementation to reach through to your database. We’ll use this trivial one (nb: it’s deliberately trivial because the DAO is not the focus of this article …
public class JdbcUserDao implements UserDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
public List<User> getAllUsers() {
final String sql = "SELECT username, email FROM USERS";
return jdbcTemplate.query(sql, new UserRowMapper());
}
}
And the UserRowMapper:
public class UserRowMapper implements RowMapper {
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
return new User(rs.getString("username"), rs.getString("email"));
}
}
And of course the User object:
public class User {
private String username;
private String email;
public User(String username, String email) {
this.username = username;
this.email = email;
}
public String getUsername() { return username; }
public String getEmail() { return email; }
}
To start the testing, we’ll need to ensure we have DBUnit and H2 database available. Download these, unzip them, and ensure JAR files are available on your projects test build and test run path. DBUnit is a great tool that leverages JUnit to insert data into and clean data out of a database automatically when your JUnit tests run. The H2 database is a light footprint database which has grown out of the Hypersonic database, which supports both JDBC 3.0 and complete in-memory databases (which we will take advantage of).
Generally speaking, each unit test will run as follows (notice we don’t use Spring for this):
- ant or your other test wrapper will call JUnit to run unit tests
- your test class will call create a new in-memory H2 Database, and run the CREATE TABLE statements
- your test class will defer to DBUnit to populate the database
- your test class will then create a new instance of your data layer object
- tests will run and your tests will pass (hopefully!)
First step is to set up a fake dataset that will be used for testing your data layer. Put the contents into an XML document in the same location as your test class will go — we will name it UserTestData.xml. DBUnit will load this file each time our unit tests run and put it into the database.
<!xml version="1.0" encoding="UTF-8-->
<dataset>
<users username="jane" email="janesem@yourhost.com.x"/>
<users username="joe" email="joe@yourhost.com.x"/>
</dataset>
We will make a helper class to create the datasource for DBUnit and run the “CREATE TABLE”s on the database. There is an additional method to create a new DataFieldMaxValueIncrementer, which is how I’d suggest getting your primary key values for your INSERT statements if you’re using Spring. If you’re not using Spring, it will pay to remove this method.
public class H2TestingUtils {// don't instantiate
private H2TestingUtils() { }/*
* Create a new H2 in-process in-memory database
*/
public static DataSource getNewDataSource() {
DataSource ds = new SingleConnectionDataSource("org.h2.Driver", "jdbc:h2:mem:", "sa", "", true);
return ds;
}/**
* Creates a DataFieldMaxValueIncrementer tied to a h2 driver and sequence (Spring
* @param ds the DataSource the incrementer uses
* @return an incrementer to use
*/
public static DataFieldMaxValueIncrementer getNewIncrementer(final DataSource ds, final String sequenceName) {
return new H2SequenceMaxValueIncrementer(ds, sequenceName);
}/**All your CREATE TABLE, CREATE SEQUENCE, etc statements should go into the createTables() method. Note that in this current incarnation the createTables method will be called once for each unit test that runs. This could be corrected by making the DataSource; either by doing a DROP or by using a Singleton and calling createTables() inside the H2TestingUtils.getNewDataSource() method.
* Creates the tables in the database
* @param ds the DataSource to CREATE into
*/
public static void createTables(final DataSource ds) {
try {
final String stmt = "create table USERS(username varchar(50), email varchar(50), Primary Key (username));";
ds.getConnection().prepareStatement(stmt).execute();
} catch (Exception e) {
// do nothing -- probably just called twice as part of a unit tests
}
}
}
Regardless, for now we have our database layer set up. Two steps remaining!
Set up the basics of our unit tests class:
public class JdbcUserDaoTest extends DataSourceBasedDBTestCase {
private JdbcUserDao instance;
private DataSource dataSource;
public JdbcUserDaoTest(String testName) {
super(testName);
}/**
* setUp is called by JUnit before every test method is run
*/
protected void setUp() throws Exception {
// create the database
dataSource = H2TestingUtils.getNewDataSource();
H2TestingUtils.createTables(dataSource);
// defer to dbunit
super.setUp();
// create the instance to test
instance = new JdbcUserDao();
instance.setDataSource(dataSource);
}
/**
* tearDown is called by JUnit after each test and needs to be present for the data to be cleared
*/
protected void tearDown() throws Exception {
super.tearDown();
}
/**
* DBUnit will insert into the dataSource by retrieving it through method
*/
protected DataSource getDataSource() {
return dataSource;
}/**The above sets out how DBUnit will set up our data source. Since we’ve pointed it to an in-memory H2 database, DBUnit will read the UserTestData.xml file and populate the database. We’re very close … just add the test code!
* DBUnit calls this to get the data to inject to the database
*/
protected IDataSet getDataSet() throws Exception {
return new FlatXmlDataSet(getClass().getResource("UserTestData.xml"));
}
/* What DbUnit does with the existing data and with data in the XML file */
protected DatabaseOperation getSetUpOperation() {
return DatabaseOperation.CLEAN_INSERT;
}
/* What DbUnit does with the data after the test is done. */
protected DatabaseOperation getTearDownOperation() throws Exception {
return DatabaseOperation.DELETE_ALL;
}
}
public class JdbcUserDaoTest extends DataSourceBasedDBTestCase {...public void testGetAllUsers() {
List<User> users = instance.getAllUsers();
assertEquals(2, users.size());
// more testing ...
}
}
In this article I’ve covered how to unit test with DBUnit and H2 to test your JDBC-based data access layer, and in the process we’ve learnt a little about how DBUnit and Spring fit together. I’d love to hear your feedback on the blog.
Leave a Reply