1. Introduction
In this tutorial, we’ll take a look at DBUnit, a unit testing tool used to test relational database interactions in Java.
We’ll see how it helps us get our database to a known state and assert against an expected state.
2. Dependencies
First, we can add DBUnit to our project from Maven Central by adding the dbunit dependency to our pom.xml:
<dependency>
<groupId>org.dbunit</groupId>
<artifactId>dbunit</artifactId>
<version>2.7.0</version>
<scope>test</scope>
</dependency>
We can look up the most recent version on Maven Central.
3. Hello World Example
Next, let’s define a database schema:
schema.sql:
CREATE TABLE IF NOT EXISTS CLIENTS
(
`id` int AUTO_INCREMENT NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS ITEMS
(
`id` int AUTO_INCREMENT NOT NULL,
`title` varchar(100) NOT NULL,
`produced` date,
`price` float,
PRIMARY KEY (`id`)
);
3.1. Defining the Initial Database Contents
DBUnit lets us define and load our test dataset in a simple declarative way.
We define each table row with one XML element, where the tag name is a table name, and attribute names and values map to column names and values respectively. The row data can be created for multiple tables. We have to implement the getDataSet() method of DataSourceBasedDBTestCase to define the initial data set, where we can use the FlatXmlDataSetBuilder to refer to our XML file:
data.xml:
<?xml version="1.0" encoding="UTF-8"?>
<dataset>
<CLIENTS id='1' first_name='Charles' last_name='Xavier'/>
<ITEMS id='1' title='Grey T-Shirt' price='17.99' produced='2019-03-20'/>
<ITEMS id='2' title='Fitted Hat' price='29.99' produced='2019-03-21'/>
<ITEMS id='3' title='Backpack' price='54.99' produced='2019-03-22'/>
<ITEMS id='4' title='Earrings' price='14.99' produced='2019-03-23'/>
<ITEMS id='5' title='Socks' price='9.99'/>
</dataset>
3.2. Initializing the Database Connection and Schema
Now that we’ve got our schema, we have to initialize our database.
We have to extend the DataSourceBasedDBTestCase class and initialize the database schema in its getDataSource() method:
DataSourceDBUnitTest.java:
public class DataSourceDBUnitTest extends DataSourceBasedDBTestCase {
@Override
protected DataSource getDataSource() {
JdbcDataSource dataSource = new JdbcDataSource();
dataSource.setURL(
"jdbc:h2:mem:default;MODE=LEGACY;DB_CLOSE_DELAY=-1;init=runscript from
'classpath:schema.sql'");
dataSource.setUser("sa");
dataSource.setPassword("sa");
return dataSource;
}
@Override
protected IDataSet getDataSet() throws Exception {
return new FlatXmlDataSetBuilder().build(getClass().getClassLoader()
.getResourceAsStream("data.xml"));
}
}
Here, we passed a SQL file to an H2 in-memory database in its connection string. If we want to test on other databases we will need to provide our custom implementation for it.
Keep in mind that**,** in our example, DBUnit will reinitialize the database with the given test data before each test method execution.
There are multiple ways to configure this via get**SetUpOperation and get**TearDownOperation:
@Override
protected DatabaseOperation getSetUpOperation() {
return DatabaseOperation.REFRESH;
}
@Override
protected DatabaseOperation getTearDownOperation() {
return DatabaseOperation.DELETE_ALL;
}
The REFRESH operation, tells DBUnit to refresh all its data. This will ensure that all caches are cleared up and our unit test gets no influence from another unit test. The DELETE_ALL operation ensures that all the data gets removed at the end of each unit test. In our case, we are telling DBUnit that during set up, using the getSetUpOperation method implementation we will refresh all caches. Finally, we tell DBUnit to remove all data during the teardown operation using the getTearDownOperation method implementation.
3.3. Comparing the Expected State and the Actual State
Now, let’s examine our actual test case. For this first test, we’ll keep it simple – we’ll load our expected dataset and compare it to the dataset retrieved from our DB connection:
@Test
public void givenDataSetEmptySchema_whenDataSetCreated_thenTablesAreEqual() throws Exception {
IDataSet expectedDataSet = getDataSet();
ITable expectedTable = expectedDataSet.getTable("CLIENTS");
IDataSet databaseDataSet = getConnection().createDataSet();
ITable actualTable = databaseDataSet.getTable("CLIENTS");
assertEquals(expectedTable, actualTable);
}
4. Deep Dive Into Assertions
In the previous section, we saw a basic example of comparing the actual contents of a table with an expected data set. Now we’re going to discover DBUnit’s support for customizing data assertions.
4.1. Asserting with a SQL Query
A straightforward way to check the actual state is with a SQL query.
In this example, we’ll insert a new record into the CLIENTS table, then verify the contents of the newly created row. We defined the expected output in a separate XML file, and extracted the actual row value by an SQL query:
@Test
public void givenDataSet_whenInsert_thenTableHasNewClient() throws Exception {
try (InputStream is = getClass().getClassLoader().getResourceAsStream("dbunit/expected-user.xml")) {
IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(is);
ITable expectedTable = expectedDataSet.getTable("CLIENTS");
Connection conn = getDataSource().getConnection();
conn.createStatement()
.executeUpdate(
"INSERT INTO CLIENTS (first_name, last_name) VALUES ('John', 'Jansen')");
ITable actualData = getConnection()
.createQueryTable(
"result_name",
"SELECT * FROM CLIENTS WHERE last_name='Jansen'");
assertEqualsIgnoreCols(expectedTable, actualData, new String[] { "id" });
}
}
The getConnection() method of the DBTestCase ancestor class returns a DBUnit-specific representation of the data source connection (an IDatabaseConnection instance). The createQueryTable() method of the IDatabaseConnection can be used to fetch actual data from the database, for comparison with the expected database state, using the Assertion.assertEquals() method. The SQL query passed onto createQueryTable() is the query we want to test. It returns a Table instance that we use to make our assert.
4.2. Ignoring Columns
Sometimes in database tests, we want to ignore some columns of the actual tables. These are usually auto-generated values that we can’t strictly control, like generated primary keys or current timestamps.
We could do this by omitting the columns from the SELECT clauses in the SQL queries, but DBUnit provides a more convenient utility for achieving this. With the static methods of the DefaultColumnFilter class we can create a new ITable instance from an existing one by excluding some of the columns, as shown here:
@Test
public void givenDataSet_whenInsert_thenGetResultsAreStillEqualIfIgnoringColumnsWithDifferentProduced()
throws Exception {
Connection connection = tester.getConnection().getConnection();
String[] excludedColumns = { "id", "produced" };
try (InputStream is = getClass().getClassLoader()
.getResourceAsStream("dbunit/expected-ignoring-registered_at.xml")) {
IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(is);
ITable expectedTable = excludedColumnsTable(expectedDataSet.getTable("ITEMS"), excludedColumns);
connection.createStatement()
.executeUpdate("INSERT INTO ITEMS (title, price, produced) VALUES('Necklace', 199.99, now())");
IDataSet databaseDataSet = tester.getConnection().createDataSet();
ITable actualTable = excludedColumnsTable(databaseDataSet.getTable("ITEMS"), excludedColumns);
assertEquals(expectedTable, actualTable);
}
}
4.3. Investigating Multiple Failures
If DBUnit finds an incorrect value, then it immediately throws an AssertionError.
In specific cases, we can use the DiffCollectingFailureHandler class, which we can pass to the Assertion.assertEquals() method as a third argument.
This failure handler will collect all failures instead of stopping on the first one, meaning that the Assertion.assertEquals() method will always succeed if we use the DiffCollectingFailureHandler. Therefore, we’ll have to programmatically check if the handler found any errors:
@Test
public void givenDataSet_whenInsertUnexpectedData_thenFailOnAllUnexpectedValues() throws Exception {
try (InputStream is = getClass().getClassLoader()
.getResourceAsStream("dbunit/expected-multiple-failures.xml")) {
IDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(is);
ITable expectedTable = expectedDataSet.getTable("ITEMS");
Connection conn = getDataSource().getConnection();
DiffCollectingFailureHandler collectingHandler = new DiffCollectingFailureHandler();
conn.createStatement()
.executeUpdate("INSERT INTO ITEMS (title, price) VALUES ('Battery', '1000000')");
ITable actualData = getConnection().createDataSet().getTable("ITEMS");
assertEquals(expectedTable, actualData, collectingHandler);
if (!collectingHandler.getDiffList().isEmpty()) {
String message = (String) collectingHandler.getDiffList()
.stream()
.map(d -> formatDifference((Difference) d))
.collect(joining("\n"));
logger.error(() -> message);
}
}
}
private static String formatDifference(Difference diff) {
return "expected value in " + diff.getExpectedTable()
.getTableMetaData()
.getTableName() + "." +
diff.getColumnName() + " row " +
diff.getRowIndex() + ":" +
diff.getExpectedValue() + ", but was: " +
diff.getActualValue();
}
Furthermore, the handler provides the failures in the form of Difference instances, which lets us format the errors.
After running the test we get a formatted report:
java.lang.AssertionError: expected value in ITEMS.price row 5:199.99, but was: 1000000.0
expected value in ITEMS.produced row 5:2019-03-23, but was: null
expected value in ITEMS.title row 5:Necklace, but was: Battery
at com.baeldung.dbunit.DataSourceDBUnitTest.givenDataSet_whenInsertUnexpectedData_thenFailOnAllUnexpectedValues(DataSourceDBUnitTest.java:91)
It’s important to notice that at this point we expected the new item to have a price of 199.99 but it was 1000000.0. Then we see that the production date to be 2019-03-23, but in the end, it was null. Finally, the expected item was a Necklace and instead we got a Battery.
5. Conclusion
In this article, we saw how DBUnit provides a declarative way of defining test data to test data access layers of Java applications.
As always, the full source code for the examples is available over on GitHub.