Loading test data sets

Let's start with an example of a UserDAO with a simple findByName method for retrieving a user based on its first and last name. A typical unit test looks as follows:

@DataSet
public class UserDAOTest extends UnitilsJUnit4 {

    @Test
    public void testFindByName() {
        User result = userDao.findByName("doe", "john");
        assertPropertyLenientEquals("userName", "jdoe", result);
    }

    @Test
    public void testFindByMinimalAge() {
        List<User> result = userDao.findByMinimalAge(18);        
        assertPropertyLenientEquals("firstName", Arrays.asList("jack"), result);
    }
}

The @DataSet annotation in the test instructs Unitils to look for DbUnit data files that need to be loaded for the test. If no file name is specified, Unitils automatically looks for a data set file that is in the same directory as the test class and has following name pattern: className.xml

The data set file should be in DbUnit's FlatXMLDataSet file format and should contain all data needed for the test. All existing content of tables in the data set will first be deleted, then all data of the data set will be inserted. Tables that are not in the data set will not be cleared. You can explicitly clear a table by adding an empty table element, e.g. <MY_TABLE /> to the data set file. If you explicitly want to specify a null value, you can do so by using the value [null].

For the UserDAOTest we could for example create a class level data set file named UserDAOTest.xml and put it in the same directory as the UserDAOTest class:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>

    <usergroup name="admin" />  
    <user userName="jdoe"  name="doe"   firstname="john"   userGroup="admin" />
    
    <usergroup name="sales" />    
    <user userName="smith" name="smith" userGroup="sales" />
    
</dataset>

This will clear the user and usergroup tables and insert the user groups and user records. The first name of the user named 'smith' will be set to a null value.

Suppose the testFindByMinimalAge() method needs a specific data set instead of the class-level data set. You could create a file named UserDAOTest.testFindByMinimalAge.xml and put that file in the same directory as the test class:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
    <user userName="jack" age="18" />
    <user userName="jim"  age="17" />
</dataset>

You can then use this data set by adding an @DataSet annotation to the method, overriding the default data set file of the class.

public class UserDAOTest extends UnitilsJUnit4 {

    @Test
    @DataSet("UserDAOTest.testFindByMinimalAge.xml")
    public void testFindByMinimalAge() {
        List<User> result = userDao.findByMinimalAge(18);        
        assertPropertyLenientEquals("firstName", Arrays.asList("jack"), result);
    }
}

Method-level data sets should not be overused, since having more data files means you have to do more maintenance. You should first try to reuse the data in the class level data set - in most cases a small set of test records can be reused for multiple tests. If this would result in a big and incoherent data set, it may be better to use a method specific one, or to try to split the test in 2 separate tests, each having their own data set.

By setting the @DataSet annotation on class or superclass level a data set is loaded for every test in the class. If a data set should only be loaded for some of the tests, you should remove the class level annotation and annotate the test methods instead. Data sets will then only be loaded for the annotated tests. If your data set file does not comply with the naming conventions as described above, you can also specify a file name explicitly by passing the name as an argument to the @DataSet annotation. You can also specify multiple data set file names, in case you want to use more than 1 data set:

@DataSet({"UserDAOTest_general.xml", "ConfigSettings.xml"})
public class UserDAOTest extends UnitilsJUnit4 {

    @Test
    public void testFindByName() {
        User result = userDao.findByName("doe", "john");
        assertPropertyLenientEquals("userName", "jdoe", result);
    }

    @Test 
    @DataSet("UserDAOTest_ages.xml")
    public void testFindByMinimalAge() {
        List<User> result = userDao.findByMinimalAge(18);
        assertPropertyLenientEquals("firstName", Arrays.asList("jack"), result);
    }
}

Configuring the dataset load strategy

By default, data sets are loaded into the database using a clean insert strategy. This means that all data in the tables that are present in the dataset is deleted, after which the test data records are inserted. This behavior is configurable, it can be modified by changing the value of the property DbUnitModule.DataSet.loadStrategy.default. Suppose we add following in unitils.properties:

DbUnitModule.DataSet.loadStrategy.default=org.unitils.dbunit.datasetloadstrategy.InsertLoadStrategy

This sets the load strategy to insert instead of clean insert. The result is that data already available in the tables present in the dataset is not deleted, and test data records are simply inserted.

The loadStrategy that is used can also be configured for specific tests using an attribute of the @DataSet annotation. E.g.:

@DataSet(loadStrategy = InsertLoadStrategy.class)

For those familiar with DbUnit, configuring the load strategy is equivalent to using a different DatabaseOperation. Following are load strategies that are supported by default:

• CleanInsertLoadStrategy: Insert the dataset, after removal of all data currently present in the tables specified in the dataset
• InsertLoadStrategy: Simply insert the dataset into the database
• RefreshLoadStrategy: 'Refresh' the contents of the database with the contents of the dataset. This means that data of existing rows is updated and non-existing rows are inserted. Any rows that are in the database but not in the dataset stay unaffected
• UpdateLoadStrategy: Update the contents of the database with the contents of the dataset. This means that data of existing rows is updated. Fails if the dataset contains records that are not in the database (i.e. a records having the same value for the primary key column).

Configuring the dataset factory

Dataset files in Unitils have the multischema xml format, which is an extended version of DbUnits FlatXmlDataSet format. Configuration of the file format and file extension is handled by a DataSetFactory.

Although Unitils currently only supports one dataset format, the possibility is offered to implement a custom implementation of DataSetFactory to use a different file format. This can be done by specifying the value of the property DbUnitModule.DataSet.factory.default in unitils.properties or by using the factory attribute of the @DataSet annotation. Such a custom factory could e.g. be implemented to create an instance of DbUnit's XlsDataSet, if you want to use Excel files instead of XML.

Verifying test results

Sometimes it can be useful to use data sets for checking the contents of a database after a test was run. For example when you want to check the result of a bulk update method or a stored procedure.

Following example tests a method that disables all user accounts that haven't been used for an entire year:

public class UserDAOTest extends UnitilsJUnit4 {

    @Test @ExpectedDataSet
    public void testInactivateOldAccounts() {
        userDao.inactivateOldAccounts();
    }
}

Note that we have added the @ExpectedDataSet to the test method. This will instruct Unitils to look for a data set file named UserDAOTest.testInactivateOldAccounts-result.xml and compare the contents of the database with the contents of the data set:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
    <user userName="jack" active="true" />
    <user userName="jim"  active="false" />
</dataset>

For this data set, it will check whether there are 2 different user records with corresponding values in the User table in the database. Other records and other tables are not taken into account.

As with the @DataSet annotation, a file name can explicitly be specified. If no name is specified, following naming pattern will be used: className.methodName-result.xml

The use of result data sets should be kept to a minimum. Adding new data sets means more maintenance. As an alternative, you should always try to perform the same check in the test code (e.g. by using a method findActiveUsers()).

Using multi-schema data sets

Some applications connect to more than one database schema. To facilitate this, Unitils extends the data set xml definition to enable it to contain data for multiple schemas. Following example loads data for tables in 2 different schemas:

<?xml version='1.0' encoding='UTF-8'?>
<dataset xmlns="SCHEMA_A" xmlns:b="SCHEMA_B">
    <user id="1" userName="jack" />    
    <b:role id="1" roleName="admin" />
</dataset>

In this case we defined 2 schemas, SCHEMA_A and SCHEMA_B. The first schema, SCHEMA_A, is linked to the default xml namespace, the second schema, SCHEMA_B, is linked to xml namespace b. If a table xml element is prefixed with namespace b, the table is expected to be in schema SCHEMA_B, if it doesn't have a namespace prefix it is considered to be in SCHEMA_A. In the example, test data is defined for tables SCHEMA_A.user and SCHEMA_B.role.

If no default namespace is specified, it is by default set to the first of the list of schema names defined by the property database.schemaNames. So suppose you have defined following schema names:

database.schemaNames=SCHEMA_A, SCHEMA_B

This will make SCHEMA_A the default schema. You can then simplify the above data set example by leaving out the default namespace declaration:

<?xml version='1.0' encoding='UTF-8'?>
<dataset xmlns:b="SCHEMA_B">
    <user id="1" userName="jack" />    
    <b:role id="1" roleName="admin" />
</dataset>

Transactions

For different reasons, it can be useful to run tests that access a test database in a transaction. The most important reasons are the following:

• Database actions exist that only work properly when executed in a transaction, such as when using SELECT FOR UPDATE or triggers that execute ON COMMIT.
• A lot of projects like to run their test on a database that is pre-filled with some general-purpose data. During each test, data may be inserted or modified. To make sure the database is in the same known state before every test, a transaction is started before and rolled back after each test.
• When using hibernate or JPA, since these require you to run each test in a transaction to make the system work properly.

By default every test is executed in a transaction, which is committed at the end of the test.

This default behavior can be changed by by setting a property. Transaction management can for example be disabled (= auto-commit behavior) as follows:

DatabaseModule.Transactional.value.default=disabled

Other supported values for this property are commit, rollback and disabled.

The transactional behavior can also be modified at the level of a test class, by annotating the test class with @Transactional. For example:

@Transactional(TransactionMode.ROLLBACK)
public class UserDaoTest extends UnitilsJUnit4 {

This will roll back the transaction after each test in the test class. The @Transactional annotation is inheritable, so it can be moved to a shared superclass instead of specifying it for each test separately.

Under the hoods, unitils depends on spring to implement transaction management. This doesn't mean you that need to use spring in your application code for transaction management. The fact that spring is used, is completely transparent.

If you are using unitils' spring support (see Testing with spring), and you've configured a bean of type PlatformTransactionManager in your spring configuration, unitils will make use of this transaction manager.

Multi-database

Multi-database was already possible in dbmaintain but not yet in Unitils. This feature is introduced in Unitils 3.4.

You should configure it like this:

database.names=database1, database2
database.userName=sa
database.password=
database.schemaNames=public
database.driverClassName.database1=org.hsqldb.jdbcDriver
database.driverClassName.database2=org.h2.Driver
database.url.database1=jdbc:hsqldb:mem:unitils1
database.url.database2=jdbc:h2:~/test
database.dialect.database1=hsqldb
database.dialect.database2=h2

When you want to use multiple databases, you should take the old property and add the name of the datbase. The first database defined in the property 'database.names' will be the default database. In this case the default database will be 'database1'.

In your test you can choose to define a @TestDataSource with the name of the database or without it. If there is no database name defined in the annotation, than the default database will be chosen.

@TestDataSource("database2")
private DataSource dataSource;

@TestDataSource
private DataSource defaultDataSource;

The same with the @DataSet and @ExpectedDataSet annotation. In Unitils 3.4 there is an attribute 'databaseName' in those annotations.

@Test
@DataSet(value = "MultiDatabaseIntTest.testOneDataSetDatabase1.xml")
public void testOneDataSetDatabase1() {
    //This test uses the default database.
}
   
@Test
@DataSet(value = "MultiDatabaseIntTest.testOneDataSetDatabase2.xml",  databaseName="database2")
public void testOneDataSetDatabase2() {
    //'database2' is used in this test.
}

If you want to add multiple datasets to multiple databases in one test, than you can use the @DataSets and @ExpectedDataSets feature.

@Test
@DataSets({
        @DataSet(value = "MultiDatabaseIntTest.testMultipleDataSetsDatabase1_1.xml", databaseName="database1"), 
    @DataSet(value = "MultiDatabaseIntTest.testMultipleDataSetsDatabase1_2.xml", databaseName="database2")})
public void testMultipleDataSetsMultipleDatabases() throws Exception {
    //do some asserts
}
    
@Test
@ExpectedDataSets({
        @ExpectedDataSet(databaseName="database1", value = "MultiDatabaseIntTest.testMultipleExpectedDataSetsOnMultipleDatabases_1.xml"), 
    @ExpectedDataSet(databaseName="database2", value= "MultiDatabaseIntTest.testMultipleExpectedDataSetsOnMultipleDatabases_2.xml")})
public void testMultipleExpectedDataSetsOnMultipleDatabases() {
        //do some asserts
}

@nameDatabase_nameScript.sql

01_@nameDatabase_nameScript.sql