Spring From the Trenches: Using Null Values in DbUnit Datasets

If we are writing integration tests for an application that uses Spring Framework, we can integrate DbUnit with the Spring testing framework by using Spring Test DbUnit.

However, this integration is not problem free.

Often we have to insert null values to the database before our tests are run or verify that the value saved to the specific table column is null. These are very basic use cases, but it is tricky to write integration tests that support them.

This blog post identifies the problems related to null values and describes how we can solve them. Let’s start by taking a quick look at the system under test.

If you don’t know how you can write integration tests for your repositories, you should read my blog post titled: Spring Data JPA Tutorial: Integration Testing.

It explains how you can write integration tests for Spring Data JPA repositories, but you can use the same approach for writing test for other Spring powered repositories that use a relational database.

The System Under Test

The tested "application" has one entity and one Spring Data JPA repository that provides CRUD operations for that entity.

Our entity class is called Todo and the relevant part of its source code looks as follows:

import javax.persistence.*;

@Entity
@Table(name="todos")
public class Todo {

    private static final int MAX_LENGTH_DESCRIPTION = 500;
    private static final int MAX_LENGTH_TITLE = 100;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "description", nullable = true, length = MAX_LENGTH_DESCRIPTION)
    private String description;

    @Column(name = "title", nullable = false, length = MAX_LENGTH_TITLE)
    private String title;

    @Version
    private long version;
	
	//Constructors, builder class, and getters are omitted.
}
You can get the full source code of the Todo class from Github.

Also, we should not to use the builder pattern because our entity has only two String fields that are set when a new Todo object is created. However, I used it here because it makes our tests easier to read.

Our Spring Data JPA repository interface is called TodoRepository, and it extends the CrudRepository<T, ID extends Serializable> interface. This repository provides CRUD operations for Todo objects. It also declares one query method which returns all todo entries whose description matches with the given search term.

The source code of the TodoRepository interface looks as follows:

import org.springframework.data.repository.CrudRepository;

public interface TodoRepository extends CrudRepository<Todo, Long> {

    List<Todo> findByDescription(String description);
}

Let's move on and find out how we can deal with null values when we write integration tests for code that either reads information from a relational database or saves information to it.

Dealing with Null Values

When we write integration tests for our data access code, we have to initialize the database into a known state before each test case and ensure that the correct data is written to the database.

This section identifies the problems we face when we are writing integration tests that

  • Use flat XML datasets.
  • Write null values to the database or ensure that the value of a table column is null.

We will also learn how we can solve these problems.

Inserting Null Values to the Database

When we write integration tests that read information from the database, we have to initialize that database into a known state before our tests are invoked, and sometimes we have to insert null values to the database.

Because we use flat XML datasets, we can insert null value to a table column by omitting the corresponding the attribute value. This means that if we want to insert null value to the description column of the todos table, we can do this by using the following the following DbUnit dataset:

<dataset>
    <todos id="1" title="FooBar" version="0"/>
</dataset>

However, often we have to insert more than one row to the used database table. The following DbUnit dataset (todo-entries.xml) inserts two rows to the todos table:

<dataset>
    <todos id="1" title="FooBar" version="0"/>
    <todos id="2" description="description" title="title" version="0"/>
</dataset>

Let’s find out what happens when we write an integration test to the findByDescription() method of the TodoRepository interface and initialize our database by using the previous dataset (todo-entries.xml). The source code of our integration test looks as follows:

import com.github.springtestdbunit.DbUnitTestExecutionListener;
import com.github.springtestdbunit.annotation.DatabaseSetup;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
import org.springframework.test.context.support.DirtiesContextTestExecutionListener;
import org.springframework.test.context.transaction.TransactionalTestExecutionListener;

import static org.assertj.core.api.Assertions.assertThat;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {PersistenceContext.class})
@TestExecutionListeners({ DependencyInjectionTestExecutionListener.class,
        DirtiesContextTestExecutionListener.class,
        TransactionalTestExecutionListener.class,
        DbUnitTestExecutionListener.class })
public class ITTodoRepositoryTest {

    private static final Long ID = 2L;
    private static final String DESCRIPTION = "description";
    private static final String TITLE = "title";
    private static final long VERSION = 0L;

    @Autowired
    private TodoRepository repository;

	@Test
	@DatabaseSetup("todo-entries.xml")
	public void findByDescription_ShouldReturnOneTodoEntry() {
		List<Todo> todoEntries = repository.findByDescription(DESCRIPTION);
		assertThat(todoEntries).hasSize(1);

		Todo found = todoEntries.get(0);
		assertThat(found.getId()).isEqualTo(ID);
		assertThat(found.getTitle()).isEqualTo(TITLE);
		assertThat(found.getDescription()).isEqualTo(DESCRIPTION);
		assertThat(found.getVersion()).isEqualTo(VERSION);
	}
}

When we run this integration test, we get the following assertion error:

java.lang.AssertionError: 
Expected size:<1> but was:<0> in: <[]>

This means that the correct todo entry was not found from the database. What happened? Our query method is so simple that it should have worked, especially since we inserted the correct data to the database before our test case was invoked.

Well, actually the description columns of both rows are null. The DbUnit FAQ describes why this happened:

DbUnit uses the first tag for a table to define the columns to be populated. If the following records for this table contain extra columns, these ones will therefore not be populated.

It also provides a solution to this problem:

Since DBUnit 2.3.0 there is a functionality called "column sensing" which basically reads in the whole XML into a buffer and dynamically adds new columns as they appear.

We could solve this problem by reversing the order of todos elements but this is cumbersome because we would have to remember to do every time when we create new datasets. We should use column sensing because it eliminates the possibility of a human error.

We can enable column sensing by following these steps:

  1. Create a dataset loader class that extends the AbstractDataSetLoader class.
  2. Override the protected IDateSet createDataSet(Resource resource) method of the AbstractDataSetLoader class.
  3. Implement this method by enabling column sensing and returning a new FlatXmlDataSet object.

The source code of the ColumnSensingFlatXmlDataSetLoader class looks as follows:

import com.github.springtestdbunit.dataset.AbstractDataSetLoader;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.springframework.core.io.Resource;
import java.io.InputStream;

public class ColumnSensingFlatXMLDataSetLoader extends AbstractDataSetLoader {
	@Override
	protected IDataSet createDataSet(Resource resource) throws Exception {
		FlatXmlDataSetBuilder builder = new FlatXmlDataSetBuilder();
		builder.setColumnSensing(true);
		try (InputStream inputStream = resource.getInputStream()) {
			return builder.build(inputStream);
		}
	}
}
If you use Spring Test DbUnit 1.2 (or newer), your dataset loader class should extend the FlatXmlDataSetLoader class. Because the FlatXmlDataSetLoader class enables column sensing, you don't have to enable it yourself. Chris was kind enough to provide the source code of a dataset loader class which extends the FlatXmlDataSetLoader class. Thanks Chris!

Additional Reading:

We can now configure our test class to use this data et loader by annotating our test class with the @DbUnitConfiguration annotation and setting the value of its loader attribute to ColumnSensingFlatXmlDataSetLoader.class.

The source code of our fixed integration test looks as follows:

import com.github.springtestdbunit.DbUnitTestExecutionListener;
import com.github.springtestdbunit.annotation.DatabaseSetup;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
import org.springframework.test.context.support.DirtiesContextTestExecutionListener;
import org.springframework.test.context.transaction.TransactionalTestExecutionListener;

import static org.assertj.core.api.Assertions.assertThat;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {PersistenceContext.class})
@TestExecutionListeners({ DependencyInjectionTestExecutionListener.class,
        DirtiesContextTestExecutionListener.class,
        TransactionalTestExecutionListener.class,
        DbUnitTestExecutionListener.class })
@DbUnitConfiguration(dataSetLoader = ColumnSensingFlatXMLDataSetLoader.class)
public class ITTodoRepositoryTest {

    private static final Long ID = 2L;
    private static final String DESCRIPTION = "description";
    private static final String TITLE = "title";
    private static final long VERSION = 0L;

    @Autowired
    private TodoRepository repository;

	@Test
	@DatabaseSetup("todo-entries.xml")
	public void findByDescription_ShouldReturnOneTodoEntry() {
		List<Todo> todoEntries = repository.findByDescription(DESCRIPTION);
		assertThat(todoEntries).hasSize(1);

		Todo found = todoEntries.get(0);
		assertThat(found.getId()).isEqualTo(ID);
		assertThat(found.getTitle()).isEqualTo(TITLE);
		assertThat(found.getDescription()).isEqualTo(DESCRIPTION);
		assertThat(found.getVersion()).isEqualTo(VERSION);
	}
}

When we run our integration test for the second time, it passes.

Let’s find out how we can verify that null values are saved to the database.

Verifying that the Value of a Table Column Is Null

When we write integration tests that save information to the database, we have to ensure that the correct information is really saved to the database, and sometimes we have to verify that the value of a table column is null.

For example, if we write an integration test which verifies that the correct information is saved to the database when we create a todo entry that has no description, we have to ensure that a null value is inserted to the description column of the todos table.

The source code of our integration test looks as follows:

import com.github.springtestdbunit.DbUnitTestExecutionListener;
import com.github.springtestdbunit.annotation.DatabaseSetup;
import com.github.springtestdbunit.annotation.DbUnitConfiguration;
import com.github.springtestdbunit.annotation.ExpectedDatabase;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
import org.springframework.test.context.support.DirtiesContextTestExecutionListener;
import org.springframework.test.context.transaction.TransactionalTestExecutionListener;

import static org.assertj.core.api.Assertions.assertThat;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {PersistenceContext.class})
@TestExecutionListeners({ DependencyInjectionTestExecutionListener.class,
        DirtiesContextTestExecutionListener.class,
        TransactionalTestExecutionListener.class,
        DbUnitTestExecutionListener.class })
@DbUnitConfiguration(dataSetLoader = ColumnSensingFlatXMLDataSetLoader.class)
public class ITTodoRepositoryTest {

    private static final String DESCRIPTION = "description";
    private static final String TITLE = "title";

    @Autowired
    private TodoRepository repository;

    @Test
    @DatabaseSetup("no-todo-entries.xml")
    @ExpectedDatabase("save-todo-entry-without-description-expected.xml")
    public void save_WithoutDescription_ShouldSaveTodoEntryToDatabase() {
        Todo todoEntry = Todo.getBuilder()
                .title(TITLE)
                .description(null)
                .build();

        repository.save(todoEntry);
    }
}
This is not a good integration test because it only tests that Spring Data JPA and Hibernate are working correctly. We shouldn't waste our time by writing tests for frameworks. If we don't trust a framework, we shouldn't use it.

If you want to learn to write good integration tests for your data access code, you should read my tutorial titled: Writing Tests for Data Access Code.

The DbUnit dataset (no-todo-entries.xml) that is used to initialize our database looks as follows:

<dataset>
    <todos/>
</dataset>

Because we don’t set the description of the saved todo entry, the description column of the todos table should be null. This means that we should omit it from the dataset which verifies that the correct information is saved to the database.

This dataset (save-todo-entry-without-description-expected.xml) looks as follows:

<dataset>
    <todos id="1" title="title" version="0"/>
</dataset>

When we run our integration test, it fails and we see the following error message:

junit.framework.ComparisonFailure: column count (table=todos, expectedColCount=3, actualColCount=4) 
Expected :[id, title, version]
Actual   :[DESCRIPTION, ID, TITLE, VERSION]

The problem is that DbUnit expects that the todos table has only id, title, and version columns. The reason for this is that these columns are the only columns that are found from the first (and the only) row of our dataset.

We can solve this problem by using a ReplacementDataSet. A ReplacementDataSet is a decorator that replaces the placeholders found from a flat XML dataset file with the replacement objects. Let's modify our custom dataset loader class to return a ReplacementDataSet object that replaces '[null]' strings with null.

We can do this by making the following changes to our custom dataset loader:

  1. Add a private createReplacementDataSet() method to the dataset loader class. This method returns a ReplacementDataSet object and takes a FlatXmlDataSet object as a method parameter.
  2. Implement this method by creating a new ReplacementDataSet object and returning the created object.
  3. Modify the createDataSet() method to invoke the private createReplacementDataSet() method and return the created ReplacementDataSet object.

The source code of the ColumnSensingReplacementDataSetLoader class looks as follows:

import com.github.springtestdbunit.dataset.AbstractDataSetLoader;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ReplacementDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.springframework.core.io.Resource;

import java.io.InputStream;

public class ColumnSensingReplacementDataSetLoader extends AbstractDataSetLoader {

    @Override
    protected IDataSet createDataSet(Resource resource) throws Exception {
        FlatXmlDataSetBuilder builder = new FlatXmlDataSetBuilder();
        builder.setColumnSensing(true);
        try (InputStream inputStream = resource.getInputStream()) {
            return createReplacementDataSet(builder.build(inputStream));
        }
    }

    private ReplacementDataSet createReplacementDataSet(FlatXmlDataSet dataSet) {
        ReplacementDataSet replacementDataSet = new ReplacementDataSet(dataSet);
		
		//Configure the replacement dataset to replace '[null]' strings with null.
        replacementDataSet.addReplacementObject("[null]", null);
        
		return replacementDataSet;
    }
}
If you use Spring Test DbUnit 1.2 (or newer), your dataset loader class should extend the FlatXmlDataSetLoader class. Because the FlatXmlDataSetLoader enables column sensing, the only thing that you have to do is to wrap a FlatXmlDataSet object in a ReplacementDataSet object. Chris was kind enough to provide the source code of a dataset loader class which extends the FlatXmlDataSetLoader class. Thanks Chris!

Additional Reading:

We can fix our integration test by following these steps:

  1. Configure our test class to load the used DbUnit datasets by using the ColumnSensingReplacementDataSetLoader class.
  2. Modify our dataset to verify that the value of the description column is null.

First, we have to configure our test class to load the DbUnit datasets by using the ColumnSensingReplacementDataSetLoader class. Because we have already annotated our test class with the @DbUnitConfiguration, we have to change the value of its loader attribute to ColumnSensingReplacementDataSetLoader.class.

The source code of the fixed test class looks as follows:

import com.github.springtestdbunit.DbUnitTestExecutionListener;
import com.github.springtestdbunit.annotation.DatabaseSetup;
import com.github.springtestdbunit.annotation.DbUnitConfiguration;
import com.github.springtestdbunit.annotation.ExpectedDatabase;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
import org.springframework.test.context.support.DirtiesContextTestExecutionListener;
import org.springframework.test.context.transaction.TransactionalTestExecutionListener;

import static org.assertj.core.api.Assertions.assertThat;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {PersistenceContext.class})
@TestExecutionListeners({ DependencyInjectionTestExecutionListener.class,
        DirtiesContextTestExecutionListener.class,
        TransactionalTestExecutionListener.class,
        DbUnitTestExecutionListener.class })
@DbUnitConfiguration(dataSetLoader = ColumnSensingReplacementDataSetLoader.class)
public class ITTodoRepositoryTest {

    private static final String DESCRIPTION = "description";
    private static final String TITLE = "title";

    @Autowired
    private TodoRepository repository;

    @Test
    @DatabaseSetup("no-todo-entries.xml")
    @ExpectedDatabase("save-todo-entry-without-description-expected.xml")
    public void save_WithoutDescription_ShouldSaveTodoEntryToDatabase() {
        Todo todoEntry = Todo.getBuilder()
                .title(TITLE)
                .description(null)
                .build();

        repository.save(todoEntry);
    }
}

Second, we have to verify that a null value is saved to the description column of the todos table. We can do this by adding a description attribute to the only todos element of our dataset, and setting the value of the description attribute to '[null]'.

Our fixed dataset (save-todo-entry-without-description-expected.xml) looks as follows:

<dataset>
    <todos id="1" description="[null]" title="title" version="0"/>
</dataset>

When we run our integration test, it passes.

Let’s move on and summarize what we learned from this blog post.

Summary

This blog post has taught us four things:

  • DbUnit assumes that a database table contains only those columns that are found from the first tag that specifies the columns of a table row. If we want to override this behavior, we have to enable the column sensing feature of DbUnit.
  • If we want to ensure that the a null value is saved to the database, we have to use replacement datasets.
  • We learned how we can create a custom dataset loader that creates replacement datasets and uses column sensing.
  • We learned how we can configure the dataset loader that is used to load our DbUnit datasets.
6 comments… add one
  • Chris Pimlott May 1, 2015 @ 20:39

    Very helpful article, thanks Petri!

    I found that Spring Test DBUnit already has a FlatXmlDataSetLoader which includes enabling column sensing, so I was able to reduce my custom loader to the follow:

    
    public class NullReplacementDataSetLoader extends FlatXmlDataSetLoader {
    
        @Override
        protected IDataSet createDataSet(Resource resource) throws Exception {
            return createReplacementDataSet(super.createDataSet(resource));
        }
    
        private ReplacementDataSet createReplacementDataSet(IDataSet dataSet) {
            ReplacementDataSet replacementDataSet = new ReplacementDataSet(dataSet);
    
            //Configure the replacement dataset to replace '[NULL]' strings with null.
            replacementDataSet.addReplacementObject("[NULL]", null);
    
            return replacementDataSet;
        }
    }
    
    
    • Petri May 1, 2015 @ 21:04

      Hi Chris,

      Thank you for your comment! Like you said, if you use Spring Test DbUnit 1.2 or newer, you can simply extend the FlatXmlDataSetLoader and enjoy the benefits of column sensing. If you use older versions (< 1.2.0), you still have to enable it manually. I will update this blog post when I have time to do it. Again, thank you for sharing this tip!

  • Udhay Mar 22, 2019 @ 7:33

    Hi Chris,
    I am getting No bean '' defined exception when I used FlatXmlDataSetLoader. Kindly advise on the same.

    Thanks,
    Udhay

    • Petri Mar 30, 2019 @ 16:23

      Hi,

      Can I see the configuration of your integration tests?

      • Hilde May 11, 2020 @ 21:28

        I had the same problem and solved it by using
        @DbUnitConfiguration(databaseConnection = "dataSource", dataSetLoader = ColumnSensingReplacementDataSetLoader.class)

Leave a Reply