3.1. Defining the Initial Database Contents

DBUnit lets us define and load our test dataset in a simpledeclarative 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:

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.

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:

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:

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:

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.

Java bottom

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2: