Database Rider Documentation

1. Introduction

Database Rider aims for bringing DBUnit closer to your JUnit tests so database testing will feel like a breeze!. Here are the main features:

JUnit rule to integrate with DBUnit via annotations:

    @Rule
    public DBUnitRule dbUnitRule = DBUnitRule.instance(jdbcConnection);(1)

    @Test
    @DataSet(value = "datasets/yml/users.yml")
    public void shouldSeedDataSet(){
        //database is seed with users.yml dataset
    }

1	The rule depends on a JDBC connection.

CDI integration via interceptor to seed database without rule instantiation;
JSON, YAML, XML, XLS, and CSV support;
Configuration via annotations or yml files;
Cucumber integration;
Multiple database support;
Date/time support in datasets;
Scriptable datasets with groovy and javascript;
Regular expressions in expected datasets;
JUnit 5 integration;
DataSet export;
Connection leak detection;
Lot of examples.

The project is composed by 5 modules:

Core: Contains the dataset executor and JUnit rule;
CDI: provides the DBUnit interceptor;
Cucumber: a CDI aware cucumber runner;
JUnit5: Comes with an extension for JUnit5.
Examples module.

2. Seeding database

In order to insert data into database before test execution
As a developer
I want to easily use DBUnit in JUnit tests.

Database Rider brings DBUnit to your JUnit tests by means of:

JUnit rules (for JUnit4 tests);
CDI interceptor (for CDI based tests)
JUnit5 extension (for JUnit5 tests).

2.1. Seed database with `DBUnit Rule`

JUnit4 integrates with DBUnit through a JUnit rule called DBUnitRule which reads @Dataset annotations in order to prepare the database state using DBUnit behind the scenes.

The rule just needs a JDBC connection in order to be created.

Dependencies

To use it add the following maven dependency:

<dependency>
   <groupId>com.github.database-rider</groupId>
   <artifactId>rider-core</artifactId>
    <version>1.8.0</version>
   <scope>test</scope>
</dependency>

Given: The following junit rules

@RunWith(JUnit4.class)
public class DatabaseRiderIt {

    @Rule
    public EntityManagerProvider emProvider = EntityManagerProvider.instance("rules-it"); (1)

    @Rule
    public DBUnitRule dbUnitRule = DBUnitRule.instance(emProvider.connection()); (2)
}

1	`EntityManagerProvider` is a simple Junit rule that creates a JPA entityManager for each test. DBUnit rule don’t depend on EntityManagerProvider, it only needs a JDBC connection.
2	DBUnit rule is responsible for reading `@DataSet` annotation and prepare the database for each test.

And: The following dataset

src/test/resources/dataset/yml/users.yml

USER:
  - ID: 1
    NAME: "@realpestano"
  - ID: 2
    NAME: "@dbunit"
TWEET:
  - ID: abcdef12345
    CONTENT: "dbunit rules!"
    DATE: "[DAY,NOW]"
    USER_ID: 1
FOLLOWER:
  - ID: 1
    USER_ID: 1
    FOLLOWER_ID: 2

When: The following test is executed:

    @Test
    @DataSet(value = "datasets/yml/users.yml", useSequenceFiltering = true)
    public void shouldSeedUserDataSet() {
        User user = (User) EntityManagerProvider.em().
                createQuery("select u from User u join fetch u.tweets join fetch u.followers where u.id = 1").getSingleResult();
        assertThat(user).isNotNull();
        assertThat(user.getId()).isEqualTo(1);
        assertThat(user.getTweets()).isNotNull().hasSize(1);
        Tweet tweet = user.getTweets().get(0);
        assertThat(tweet).isNotNull();
        Calendar date = tweet.getDate();
        Calendar now = Calendar.getInstance();
        assertThat(date.get(Calendar.DAY_OF_MONTH)).
                isEqualTo(now.get(Calendar.DAY_OF_MONTH));
    }

Source code of the above example can be found here.

Then: The database should be seeded with the dataset content before test execution

2.2. Seed database with `DBUnit Interceptor`

DBUnit CDI^[1] integration is done through a CDI interceptor which reads @DataSet to prepare database in CDI tests.

Dependencies

To use this module just add the following maven dependency:

<dependency>
   <groupId>com.github.database-rider</groupId>
   <artifactId>rider-cdi</artifactId>
    <version>1.8.0</version>
   <scope>test</scope>
</dependency>

Given: DBUnit interceptor is enabled in your test beans.xml:

src/test/resources/META-INF/beans.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://java.sun.com/xml/ns/javaee"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/beans_1_0.xsd">
     <interceptors>
            <class>com.github.database.rider.cdi.DBUnitInterceptorImpl</class>
     </interceptors>
</beans>

Your test itself must be a CDI bean to be intercepted. if you’re using Deltaspike test control just enable the following property in test/resources/META-INF/apache-deltaspike.properties:

deltaspike.testcontrol.use_test_class_as_cdi_bean=true

When using above configuration the JUnit @Before will not work as expected, see discussion here.

And: The following dataset

src/test/resources/dataset/yml/users.yml

USER:
  - ID: 1
    NAME: "@realpestano"
  - ID: 2
    NAME: "@dbunit"
TWEET:
  - ID: abcdef12345
    CONTENT: "dbunit rules!"
    USER_ID: 1
  - ID: abcdef12233
    CONTENT: "dbunit rules!"
    USER_ID: 2
  - ID: abcdef1343
    CONTENT: "CDI for the win!"
    USER_ID: 2
FOLLOWER:
  - ID: 1
    USER_ID: 1
    FOLLOWER_ID: 2

When: The following test is executed:

@RunWith(CdiTestRunner.class) (1)
@DBUnitInterceptor (2)
@DataSet(value = "yml/users.yml")
public class DBUnitCDIIt {
    @Test
    @DataSet("yml/users.yml")
    public void shouldSeedUserDataSetUsingCdiInterceptor() {
        List<User> users = em.createQuery("select u from User u order by u.id asc").getResultList();
        User user1 = new User(1);
        User user2 = new User(2);
        Tweet tweetUser1 = new Tweet();
        tweetUser1.setId("abcdef12345");
        assertThat(users).isNotNull().hasSize(2).contains(user1, user2);
        List<Tweet> tweetsUser1 = users.get(0).getTweets();
        assertThat(tweetsUser1).isNotNull().hasSize(1).contains(tweetUser1);
    }

1	CdiTestRunner is provided by Apache Deltaspike but you should be able to use other CDI test runners.
2	Needed to activate DBUnit interceptor

Source code of the above example can be found here.

Since v1.8.0 you can also use com.github.database.rider.cdi.api.DBRider annotation to enable database rider, both activate the DBUnitInterceptor.

Then: The database should be seeded with the dataset content before test execution

2.3. Seed database with `JUnit 5 extension`

DBUnit is enabled in JUnit 5 tests through an extension named DBUnitExtension.

Dependencies

To use the extension just add the following maven dependency:

<dependency>
   <groupId>com.github.database-rider</groupId>
   <artifactId>rider-junit5</artifactId>
    <version>1.8.0</version>
   <scope>test</scope>
</dependency>

Given: The following dataset

src/test/resources/dataset/users.yml

USER:
  - ID: 1
    NAME: "@realpestano"
  - ID: 2
    NAME: "@dbunit"

When: The following junit5 test is executed

@ExtendWith(DBUnitExtension.class) (1)
@RunWith(JUnitPlatform.class) (2)
@DataSet(cleanBefore = true)
public class DBUnitJUnit5It {

    private ConnectionHolder connectionHolder = () -> (3)
            EntityManagerProvider.instance("junit5-pu").clear().connection();(4)

    @Test
    @DataSet(value = "usersWithTweet.yml")
    public void shouldListUsers() {
        List<User> users = EntityManagerProvider.em().createQuery("select u from User u").getResultList();
        assertThat(users).isNotNull().isNotEmpty().hasSize(2);
    }

1	Enables DBUnit.
2	JUnit 5 runner;
3	As JUnit5 requires Java8 you can use lambdas in your tests;
4	DBUnitExtension will get connection by reflection so just declare a field or a method with `ConnectionHolder` as return type.

Source code of the above example can be found here.

Another way to activate DBUnit in JUnits 5 test is using @DBRider annotation (at method or class level):

    @DBRider //shortcut for @ExtendWith(DBUnitExtension.class) and @Test
    @DataSet(value = "usersWithTweet.yml")
    public void shouldListUsers() {
        List users = EntityManagerProvider.em().
                createQuery("select u from User u").getResultList();
        assertThat(users).isNotNull().isNotEmpty().hasSize(2);
        assertThat(users.get(0)).isEqualTo(new User(1));
    }

1	Shortcut for `@Test` and `@ExtendWith(DBUnitExtension.class)`

The same works for SpringBoot projects using JUnit5, see an example project here.

Then: The database should be seeded with the dataset content before test execution

2.4. Seeding database in BDD tests with `Rider Cucumber`

DBUnit enters the BDD world through a dedicated JUNit runner which is based on Cucumber and Apache DeltaSpike.

This runner just starts CDI within your BDD tests so you just have to use Database Rider CDI interceptor on Cucumber steps, here is the so called Cucumber CDI runner declaration:

package com.github.database.rider.examples.cucumber;

import com.github.database.rider.cucumber.CdiCucumberTestRunner;
import cucumber.api.CucumberOptions;
import org.junit.runner.RunWith;


@RunWith(CdiCucumberTestRunner.class)
@CucumberOptions(
        features = {"src/test/resources/features/contacts.feature"},
        plugin = {"json:target/cucumber.json"}
        //glue = "com.github.database.rider.examples.glues"
)
public class ContactFeature {
}

As cucumber doesn’t work with JUnit Rules, see this issue, you won’t be able to use Cucumber runner with DBUnit Rule, but you can use DataSetExecutor in @Before, see example here.

Dependencies

Here is a set of maven dependencies needed by Database Rider Cucumber:

Most of the dependencies, except CDI container implementation, are brought by Database Rider Cucumber module transitively.

<dependency>
     <groupId>com.github.database-rider</groupId>
     <artifactId>rider-cucumber</artifactId>
    <version>1.8.0</version>
     <scope>test</scope>
</dependency>

Cucumber dependencies

<dependency> (1)
  <groupId>info.cukes</groupId>
  <artifactId>cucumber-junit</artifactId>
  <version>1.2.4</version>
  <scope>test</scope>
</dependency>
<dependency> (1)
  <groupId>info.cukes</groupId>
  <artifactId>cucumber-java</artifactId>
  <version>1.2.4</version>
  <scope>test</scope>
</dependency>

1	You don’t need to declare because it comes with Database Rider Cucumber module dependency.

DeltaSpike and CDI dependency

<dependency> (1)
  <groupId>org.apache.deltaspike.modules</groupId>
  <artifactId>deltaspike-test-control-module-api</artifactId>
  <version>${ds.version}</version>
  <scope>test</scope>
</dependency>

<dependency> (1)
  <groupId>org.apache.deltaspike.core</groupId>
  <artifactId>deltaspike-core-impl</artifactId>
  <version>${ds.version}</version>
  <scope>test</scope>
</dependency>

<dependency> (1)
  <groupId>org.apache.deltaspike.modules</groupId>
  <artifactId>deltaspike-test-control-module-impl</artifactId>
  <version>${ds.version}</version>
  <scope>test</scope>
</dependency>

<dependency> (2)
  <groupId>org.apache.deltaspike.cdictrl</groupId>
  <artifactId>deltaspike-cdictrl-owb</artifactId>
  <version>${ds.version}</version>
  <scope>test</scope>
</dependency>

<dependency>  (2)
  <groupId>org.apache.openwebbeans</groupId>
  <artifactId>openwebbeans-impl</artifactId>
  <version>1.6.2</version>
  <scope>test</scope>
</dependency>

1	Also comes with Rider Cucumber.
2	You can use CDI implementation of your choice.

Given: The following feature

Feature: Contacts test
  As a user of contacts repository
  I want to crud contacts
  So that I can expose contacts service


  Scenario Outline: search contacts
    Given we have a list of contacts
    When we search contacts by name "<name>"
    Then we should find <result> contacts

    Examples: examples1
      | name     | result |
      | delta    | 1      |
      | sp       | 2      |
      | querydsl | 1      |
      | abcd     | 0      |


  Scenario: delete a contact

    Given we have a list of contacts
    When we delete contact by id 1
    Then we should not find contact 1

And: The following dataset

CONTACT:
  - ID: 1
    NAME: "deltaspike"
    EMAIL: "users@deltaspike.apache.org"
    COMPANY_ID: 1
  - ID: 2
    NAME: "querydsl"
    EMAIL: "info@mysema.com"
    COMPANY_ID: 2
  - ID: 3
    NAME: "Spring"
    EMAIL: "spring@pivotal.io"
    COMPANY_ID: 3

COMPANY:
  - ID: 1
    NAME: "Apache"
  - ID: 2
    NAME: "Mysema"
  - ID: 3
    NAME: "Pivotal"
  - ID: 4
    NAME: "Google"

And: The following Cucumber test

package com.github.database.rider.examples.cucumber;

import com.github.database.rider.cucumber.CdiCucumberTestRunner;
import cucumber.api.CucumberOptions;
import org.junit.runner.RunWith;


@RunWith(CdiCucumberTestRunner.class)
@CucumberOptions(
        features = {"src/test/resources/features/contacts.feature"},
        plugin = {"json:target/cucumber.json"}
        //glue = "com.github.database.rider.examples.glues"
)
public class ContactFeature {
}

When: The following cucumber steps are executed

package com.github.database.rider.examples.cucumber;

import com.github.database.rider.core.api.dataset.DataSet;
import com.github.database.rider.cdi.api.DBUnitInterceptor;
import cucumber.api.java.en.Given;
import cucumber.api.java.en.Then;
import cucumber.api.java.en.When;
import org.example.jpadomain.Contact;
import org.example.jpadomain.Contact_;
import org.example.service.deltaspike.ContactRepository;

import javax.inject.Inject;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNull;

@DBUnitInterceptor (1)
public class ContactSteps {

    @Inject
    ContactRepository contactRepository; (2)

    Long count;


    @Given("^we have a list of contacts$")
    @DataSet("datasets/contacts.yml") (3)
    public void given() {
        assertEquals(contactRepository.count(), new Long(3));
    }

    @When("^we delete contact by id (\\d+)$")
    public void we_delete_contact_by_id(long id) throws Throwable {
        contactRepository.remove(contactRepository.findBy(id));
    }

    @Then("^we should not find contact (\\d+)$")
    public void we_should_not_find_contacts_in_database(long id) throws Throwable {
        assertNull(contactRepository.findBy(id));
    }

    @When("^^we search contacts by name \"([^\"]*)\"$")
    public void we_search_contacts_by_name_(String name) throws Throwable {
        Contact contact = new Contact();
        contact.setName(name);
        count = contactRepository.countLike(contact, Contact_.name);
    }

    @Then("^we should find (\\d+) contacts$")
    public void we_should_find_result_contacts(Long result) throws Throwable {
        assertEquals(result, count);
    }
}

1	Activates DBUnit CDI interceptor
2	As the Cucumber cdi runner enables CDI, you can use injection into your Cucumber steps.
3	Dataset is prepared before step execution by `@DBUnitInterceptor`.

Source code for the example above can be found here.

Then: The database should be seeded with the dataset content before step execution

3. DataSet creation

In order to create datasets to feed tables
As a developer
I want to declare database state in external files.

It is a good practice to move database preparation or any infrastructure code outside test logic, it increases test maintainability.

3.1. Creating a `YAML` dataset

YAML stands for yet another markup language and is a very simple, lightweight yet powerful format.

YAML is based on spaces indentation so be careful because any missing or additional space can lead to an incorrect dataset.

Source code of the examples below can be found here.

Given: The following dataset

src/test/resources/dataset/yml/users.yml

USER:
  - ID: 1
    NAME: "@realpestano"
  - ID: 2
    NAME: "@dbunit"
TWEET:
  - ID: abcdef12345
    CONTENT: "dbunit rules!"
    DATE: "[DAY,NOW]"
    USER_ID: 1
FOLLOWER:
  - ID: 1
    USER_ID: 1
    FOLLOWER_ID: 2

When: The following test is executed:

@Test
@DataSet("yml/users.yml")
public void shouldSeedDatabaseWithYAMLDataSet() {
   List<User> users = em().createQuery("select u from User u").getResultList();
   assertThat(users).isNotNull().isNotEmpty().hasSize(2);
}

Then: The database should be seeded with the dataset content before test execution

3.2. Creating a `JSON` dataset

Given: The following dataset

src/test/resources/dataset/json/users.json

{
  "USER": [
    {
      "id": 1,
      "name": "@realpestano"
    },
    {
      "id": 2,
      "name": "@dbunit"
    }
  ],
  "TWEET": [
    {
      "id": "abcdef12345",
      "content": "dbunit rules json example",
      "date": "2013-01-20",
      "user_id": 1
    }
  ],
  "FOLLOWER": [
    {
      "id": 1,
      "user_id": 1,
      "follower_id": 2
    }
  ]
}

When: The following test is executed:

@Test
@DataSet("json/users.json")
public void shouldSeedDatabaseWithJSONDataSet() {
   List<User> users = em().createQuery("select u from User u").getResultList();
   assertThat(users).isNotNull().isNotEmpty().hasSize(2);
}

Then: The database should be seeded with the dataset content before test execution

3.3. Creating a `XML` dataset

Given: The following dataset

src/test/resources/dataset/xml/users.xml

<dataset>
    <USER id="1" name="@realpestano" />
    <USER id="2" name="@dbunit" />
    <TWEET id="abcdef12345" content="dbunit rules flat xml example" user_id="1"/>
    <FOLLOWER id="1" user_id="1" follower_id="2"/>
</dataset>

When: The following test is executed:

@Test
@DataSet("xml/users.xml")
public void shouldSeedDatabaseWithXMLDataSet() {
   List<User> users = em().createQuery("select u from User u").getResultList();
   assertThat(users).isNotNull().isNotEmpty().hasSize(2);
}

Then: The database should be seeded with the dataset content before test execution

3.4. Creating a `XLS` dataset

Given: The following dataset

src/test/resources/dataset/xls/users.xls

ID	NAME
1	@realpestano
2	@dbunit

Each Excell sheet name is the table name, first row is columns names and remaining rows/cells are values.

When: The following test is executed:

@Test
@DataSet("xls/users.xls")
public void shouldSeedDatabaseWithXLSDataSet() {
    List<User> users = em().createQuery("select u from User u").getResultList();
    assertThat(users).isNotNull().isNotEmpty().hasSize(2);
}

Then: The database should be seeded with the dataset content before test execution

3.5. Creating a `CSV` dataset

Given: The following dataset

src/test/resources/dataset/csv/USER.csv

ID, NAME
"1","@realpestano"
"2","@dbunit"

src/test/resources/dataset/csv/TWEET.csv

ID, CONTENT, DATE, LIKES, USER_ID
"abcdef12345","dbunit rules!","2016-09-12 22:46:20.0",null,"1"

File name is table name and first row is column names.

src/test/resources/dataset/csv/table-ordering.txt

FOLLOWER
TWEET
USER

CSV datasets are composed by multiple files (one per table) and a table ordering descriptor declaring the order of creation.

Also note that each csv dataset must be declared in its own folder because DBUnit will read all csv files present in dataset folder.

When: The following test is executed:

@Test
@DataSet("datasets/csv/USER.csv") (1)
public void shouldSeedDatabaseWithCSVDataSet() {
    List<User> users = em().createQuery("select u from User u").getResultList();
    assertThat(users).isNotNull().isNotEmpty().hasSize(2);
}

1	You need to declare just one csv dataset file. Database rider will take parent folder as dataset folder.

Then: The database should be seeded with the dataset content before test execution

4. Configuration

In order to handle various use cases
As a developer
I want to be able to configure DataBase Rider

4.1. DataSet configuration

DataSet configuration is done via @DataSet annotation at class or method level:

@Test
@DataSet(value ="users.yml", strategy = SeedStrategy.UPDATE,
  disableConstraints = true,cleanAfter = true,
  useSequenceFiltering = true, tableOrdering = {"TWEET","USER"},
  executeScriptsBefore = "script.sql", executeStatementsBefore = "DELETE from USER where 1=1"
  transactional = true, cleanAfter=true)
public void shouldCreateDataSet(){

}

Table below illustrate the possible configurations:

Name Description Default

Name	Description	Default
value	Dataset file name using test resources folder as root directory. Multiple, comma separated, dataset file names can be provided.	""
executorId	Name of dataset executor for the given dataset.	DataSetExecutorImpl.DEFAULT_EXECUTOR_ID
strategy	DataSet seed strategy. Possible values are: CLEAN_INSERT, INSERT, REFRESH and UPDATE.	CLEAN_INSERT, meaning that DBUnit will clean and then insert data in tables present in provided dataset.
useSequenceFiltering	If true dbunit will look at constraints and dataset to try to determine the correct ordering for the SQL statements.	true
tableOrdering	A list of table names used to reorder DELETE operations to prevent failures due to circular dependencies.	""
disableConstraints	Disable database constraints.	false
cleanBefore	If true Database Rider will try to delete database before test in a smart way by using table ordering and brute force.	false
cleanAfter	If true Database Rider will try to delete database after test in a smart way by using table ordering and brute force.	false
transactional	If true a transaction will be started before and committed after test execution.	false
executeStatementsBefore	A list of jdbc statements to execute before test.	{}
executeStatementsAfter	A list of jdbc statements to execute after test.	{}
executeScriptsBefore	A list of sql script files to execute before test. Note that commands inside sql file must be separated by `;`.	{}
executeScriptsAfter	A list of sql script files to execute after test. Note that commands inside sql file must be separated by `;`.	{}

value

Dataset file name using test resources folder as root directory. Multiple, comma separated, dataset file names can be provided.

""

executorId

Name of dataset executor for the given dataset.

DataSetExecutorImpl.DEFAULT_EXECUTOR_ID

strategy

DataSet seed strategy. Possible values are: CLEAN_INSERT, INSERT, REFRESH and UPDATE.

CLEAN_INSERT, meaning that DBUnit will clean and then insert data in tables present in provided dataset.

useSequenceFiltering

If true dbunit will look at constraints and dataset to try to determine the correct ordering for the SQL statements.

true

tableOrdering

A list of table names used to reorder DELETE operations to prevent failures due to circular dependencies.

""

disableConstraints

Disable database constraints.

false

cleanBefore

If true Database Rider will try to delete database before test in a smart way by using table ordering and brute force.

false

cleanAfter

If true Database Rider will try to delete database after test in a smart way by using table ordering and brute force.

false

transactional

If true a transaction will be started before and committed after test execution.

false

executeStatementsBefore

A list of jdbc statements to execute before test.

{}

executeStatementsAfter

A list of jdbc statements to execute after test.

{}

executeScriptsBefore

A list of sql script files to execute before test. Note that commands inside sql file must be separated by ;.

{}

executeScriptsAfter

A list of sql script files to execute after test. Note that commands inside sql file must be separated by ;.

{}

4.2. DBUnit configuration

DBUnit, the tool doing the dirty work the scenes, can be configured by @DBUnit annotation (class or method level) and dbunit.yml file present in test resources folder.

@Test
@DBUnit(cacheConnection = true, cacheTableNames = false, allowEmptyFields = true,batchSize = 50)
public void shouldLoadDBUnitConfigViaAnnotation() {

}

Here is a dbunit.yml example, also the default values:

src/test/resources/dbunit.yml

cacheConnection: true (1)
cacheTableNames: true (2)
leakHunter: false (3)
caseInsensitiveStrategy: !!com.github.database.rider.core.api.configuration.Orthography 'UPPERCASE' (4)
properties:
  batchedStatements:  false (5)
  qualifiedTableNames: false (6)
  caseSensitiveTableNames: false (7)
  batchSize: 100 (8)
  fetchSize: 100 (9)
  allowEmptyFields: false (10)
  escapePattern: (11)
connectionConfig: (12)
  driver: ""
  url: ""
  user: ""
  password: ""

1	Database connection will be reused among tests
2	Caches table names to avoid query connection metadata unnecessarily
3	Activate connection leak detection. In case a leak (open JDBC connections is increased after test execution) is found an exception is thrown and test fails.
4	When `caseSensitiveTableNames` is `false` will apply letter case based on configured strategy. Valid values are `UPPERCASE` and `LOWERCASE`.
5	Enables usage of JDBC batched statement
6	Enable or disable multiple schemas support. If enabled, Dbunit access tables with names fully qualified by schema using this format: SCHEMA.TABLE.
7	Enable or disable case sensitive table names. If enabled, Dbunit handles all table names in a case sensitive way.
8	Specifies the size of JDBC batch updates
9	Specifies statement fetch size for loading data into a result set table.
10	Allow to call INSERT/UPDATE with empty strings ('').
11	Allows schema, table and column names escaping. The property value is an escape pattern where the ? is replaced by the name. For example, the pattern "[?]" is expanded as "[MY_TABLE]" for a table named "MY_TABLE". The most common escape pattern is ""?"" which surrounds the table name with quotes (for the above example it would result in ""MY_TABLE""). As a fallback if no questionmark is in the given String and its length is one it is used to surround the table name on the left and right side. For example the escape pattern """ will have the same effect as the escape pattern ""?"".
12	JDBC connection configuration, it will be used in case you don’t provide a connection inside test (except in CDI test where connection is inferred from entity manager).

@DBUnit annotation takes precedence over dbunit.yml global configuration which will be used only if the annotation is not present.

Since version 1.1.0 you can define only the properties of your interest, example:

cacheConnection: false
properties:
  caseSensitiveTableNames: true
  escapePattern: ""?""

5. DataSet assertion

In order to verify database state after test execution
As a developer
I want to assert database state with datasets.

Complete source code of examples below can be found here.

5.1. Assertion with yml dataset

Given: The following dataset

expectedUsers.yml

USER:
  - ID: 1
    NAME: "expected user1"
  - ID: 2
    NAME: "expected user2"

When: The following test is executed:

@RunWith(JUnit4.class)
public class ExpectedDataSetIt {

    @Rule
    public EntityManagerProvider emProvider = EntityManagerProvider.instance("rules-it");

    @Rule
    public DBUnitRule dbUnitRule = DBUnitRule.instance(emProvider.connection());


    @Test
    @DataSet(cleanBefore = true)(1)
    @ExpectedDataSet(value = "yml/expectedUsers.yml", ignoreCols = "id")
    public void shouldMatchExpectedDataSet() {
        EntityManagerProvider instance = EntityManagerProvider.newInstance("rules-it");
        User u = new User();
        u.setName("expected user1");
        User u2 = new User();
        u2.setName("expected user2");
        instance.tx().begin();
        instance.em().persist(u);
        instance.em().persist(u2);
        instance.tx().commit();
    }

1	Clear database before to avoid conflict with other tests.

Then: Test must pass because database state is as in expected dataset.

5.2. Assertion with regular expression in expected dataset

Given: The following dataset

expectedUsersRegex.yml

USER:
  - ID: "regex:\\d+"
    NAME: regex:^expected user.* #expected user1
  - ID: "regex:\\d+"
    NAME: regex:.*user2$ #expected user2

When: The following test is executed:

@Test
@DataSet(cleanBefore = true)
@ExpectedDataSet(value = "yml/expectedUsersRegex.yml")
public void shouldMatchExpectedDataSetUsingRegex() {
    User u = new User();
    u.setName("expected user1");
    User u2 = new User();
    u2.setName("expected user2");
    EntityManagerProvider.tx().begin();
    EntityManagerProvider.em().persist(u);
    EntityManagerProvider.em().persist(u2);
    EntityManagerProvider.tx().commit();
}

Then: Test must pass because database state is as in expected dataset.

5.3. Database assertion with seeding before test execution

Given: The following dataset

user.yml

USER:
  - ID: 1
    NAME: "@realpestano"
  - ID: 2
    NAME: "@dbunit"

And: The following dataset

expectedUser.yml

USER:
  - ID: 2
    NAME: "@dbunit"

When: The following test is executed:

@Test
@DataSet(value = "yml/user.yml", disableConstraints = true)
@ExpectedDataSet(value = "yml/expectedUser.yml", ignoreCols = "id")
public void shouldMatchExpectedDataSetAfterSeedingDataBase() {
    tx().begin();
    em().remove(EntityManagerProvider.em().find(User.class, 1L));
    tx().commit();
}

Then: Test must pass because database state is as in expected dataset.

5.4. Failing assertion

Given: The following dataset

expectedUsers.yml

USER:
  - ID: 1
    NAME: "expected user1"
  - ID: 2
    NAME: "expected user2"

When: The following test is executed:

@Test
@ExpectedDataSet(value = "yml/expectedUsers.yml", ignoreCols = "id")
public void shouldNotMatchExpectedDataSet() {
    User u = new User();
    u.setName("non expected user1");
    User u2 = new User();
    u2.setName("non expected user2");
    EntityManagerProvider.tx().begin();
    EntityManagerProvider.em().persist(u);
    EntityManagerProvider.em().persist(u2);
    EntityManagerProvider.tx().commit();
}

Then: Test must fail with following error:

junit.framework.ComparisonFailure: value (table=USER, row=0, col=name) expected:<[]expected user1> but was:<[non ]expected user1> at org.dbunit.assertion.JUnitFailureFactory.createFailure(JUnitFailureFactory.java:39) at org.dbunit.assertion.DefaultFailureHandler.createFailure(DefaultFailureHandler.java:97) at org.dbunit.assertion.DefaultFailureHandler.handle(DefaultFailureHandler.java:223) at …

5.5. Assertion using automatic transaction

Given: The following dataset

expectedUsersRegex.yml

USER:
  - ID: "regex:\\d+"
    NAME: regex:^expected user.* #expected user1
  - ID: "regex:\\d+"
    NAME: regex:.*user2$ #expected user2

When: The following test is executed:

@Test
@DataSet(cleanBefore = true, transactional = true,executorId = "TransactionIt")
@ExpectedDataSet(value = "yml/expectedUsersRegex.yml")
@DBUnit(cacheConnection = true)
public void shouldManageTransactionAutomatically() {
    User u = new User();
    u.setName("expected user1");
    User u2 = new User();
    u2.setName("expected user2");
    EntityManagerProvider.em().persist(u);
    EntityManagerProvider.em().persist(u2);
}

Transactional attribute will make Database Rider start a transaction before test and commit the transaction after test execution but before expected dataset comparison.

Then: Test must pass because inserted users are committed to database and database state matches expected dataset.

6. Dynamic data using scritable datasets

In order to have dynamic data in datasets
As a developer
I want to use scripts in DBUnit datasets.

Scritable datasets are backed by JSR 223.^[2].

Complete source code of examples below can be found here.

6.1. Seed database with groovy script in dataset

Given: Groovy script engine is on test classpath

<dependency>
    <groupId>org.codehaus.groovy</groupId>
    <artifactId>groovy-all</artifactId>
    <version>2.4.6</version>
    <scope>test</scope>
</dependency>

And: The following dataset

TWEET:
  - ID: "1"
    CONTENT: "dbunit rules!"
    DATE: "groovy:new Date()" (1)
    USER_ID: 1

1	Groovy scripting is enabled by `groovy:` string.

When: The following test is executed:

@Test
@DataSet(value = "datasets/yml/groovy-with-date-replacements.yml",cleanBefore = true, disableConstraints = true, executorId = "rider-it")
public void shouldReplaceDateUsingGroovyInDataset() {
    Tweet tweet = (Tweet) emProvider.em().createQuery("select t from Tweet t where t.id = '1'").getSingleResult();
    assertThat(tweet).isNotNull();
    assertThat(tweet.getDate().get(Calendar.DAY_OF_MONTH)).
            isEqualTo(now.get(Calendar.DAY_OF_MONTH));
    assertThat(tweet.getDate(). get(Calendar.HOUR_OF_DAY)).
            isEqualTo(now.get(Calendar.HOUR_OF_DAY));
}

Then: Dataset script should be interpreted while seeding the database

6.2. Seed database with javascript in dataset

Javascript engine comes within JDK so no additional classpath dependency is necessary.

Given: The following dataset

TWEET:
  - ID: "1"
    CONTENT: "dbunit rules!"
    LIKES: "js:(5+5)*10/2" (1)
    USER_ID: 1

1	Javascript scripting is enabled by `js:` string.

When: The following test is executed:

@Test
@DataSet(value = "datasets/yml/js-with-calc-replacements.yml",cleanBefore = true, disableConstraints = true, executorId = "rider-it")
public void shouldReplaceLikesUsingJavaScriptInDataset() {
    Tweet tweet = (Tweet) emProvider.em().createQuery("select t from Tweet t where t.id = '1'").getSingleResult();
    assertThat(tweet).isNotNull();
    assertThat(tweet.getLikes()).isEqualTo(50);
}

Then: Dataset script should be interpreted while seeding the database

7. Database connection leak detection

In order to find JDBC connection leaks
As a developer
I want to make Database Rider monitor connections during tests execution.

Leak hunter is a Database Rider component, based on this blog post, which counts open jdbc connections before and after test execution.

Complete source code of example below can be found here.

7.1. Detecting connection leak

@RunWith(JUnit4.class)
@DBUnit(leakHunter = true) (1)
public class LeakHunterIt {

    @Rule
    public DBUnitRule dbUnitRule = DBUnitRule.instance(new ConnectionHolderImpl(getConnection()));

    @Rule
    public ExpectedException exception = ExpectedException.none();

    @Test
    @DataSet("yml/user.yml")
    public void shouldFindConnectionLeak() throws SQLException {
        exception.expect(LeakHunterException.class);
        exception.expectMessage("Execution of method shouldFindConnectionLeak left 1 open connection(s).");
        createLeak();
    }

    private void createLeak() throws SQLException {
        Connection connection = getConnection();
        try (Statement stmt = connection.createStatement()) {
            ResultSet resultSet = stmt.executeQuery("select count(*) from user");
            assertThat(resultSet.next()).isTrue();
            assertThat(resultSet.getInt(1)).isEqualTo(2);
        }
    }

1	Enables connection leak detection.

If number of connections after test execution are greater than before then a LeakHunterException will be raised.

8. DataSet export

In order to easily create dataset files
As a developer
I want generate datasets based on database state.

Manual creation of datasets is a very error prone task. In order to export database state after test execution into datasets files one can use @ExportDataSet Annotation or use DataSetExporter component or even using a JBoss Forge addon.

Complete source code of examples below can be found here.

8.1. Export dataset with `@ExportDataSet` annotation

@Test
@DataSet("datasets/yml/users.yml") (1)
@ExportDataSet(format = DataSetFormat.XML, outputName = "target/exported/xml/allTables.xml")
public void shouldExportAllTablesInXMLFormat() {
}

1	Used here just to seed database, you could insert data manually or connect to a database which already has data.

After above test execution all tables will be exported to a xml dataset.

XML, YML, JSON, XLS and CSV formats are supported.

8.2. Programmatic export

@Test
@DataSet(cleanBefore = true)
public void shouldExportYMLDataSetProgrammatically() throws SQLException, DatabaseUnitException {
    tx().begin();
    User u1 = new User();
    u1.setName("u1");
    EntityManagerProvider.em().persist(u1);
    tx().commit();
    DataSetExporter.getInstance().export(emProvider.connection(), new DataSetExportConfig().outputFileName("target/user.yml"));
    File ymlDataSet = new File("target/user.yml");
    assertThat(ymlDataSet).exists();
    assertThat(contentOf(ymlDataSet)).
            contains("USER:" + NEW_LINE +
                            " - ID: 1" + NEW_LINE +
                            " NAME: \"u1\"" + NEW_LINE
            );
}

8.3. Configuration

Following table shows all exporter configuration options:

Name	Description	Default
format	Exported dataset file format.	YML
includeTables	A list of table names to include in exported dataset.	Default is empty which means ALL tables.
queryList	A list of select statements which the result will used in exported dataset.	{}
dependentTables	If true will bring dependent tables of declared includeTables.	false
outputName	Name (and path) of output file.	""

Name

Description

Default

format

Exported dataset file format.

YML

includeTables

A list of table names to include in exported dataset.

Default is empty which means ALL tables.

queryList

A list of select statements which the result will used in exported dataset.

{}

dependentTables

If true will bring dependent tables of declared includeTables.

false

outputName

Name (and path) of output file.

""

8.4. Export using DBUnit Addon

DBUnit Addon exports DBUnit datasets based on a database connection.

Pre requisites

You need JBoss Forge installed in your IDE or available at command line.

Installation

Use install addon from git command:

addon-install-from-git --url https://github.com/database-rider/dbunit-addon.git

Usage

Setup database connection
Export database tables into YAML, JSON, XML, XLS and CSV datasets.

Export configuration

Format: Dataset format.
Include tables: Name of tables to include in generated dataset. If empty all tables will be exported.
Dependent tables: If true will bring dependent included tables. Works in conjunction with includeTables.
Query list: A list of SQL statements which resulting rows will be used in generated dataset.
Output dir: directory to generate dataset.
Name: name of resulting dataset. Format can be ommited in dataset name.

9. MetaDataSet

In order to reuse datasets
As a developer
I want to create a custom annotation which holds my dataset and use it among tests.

Complete source code of examples below can be found here.

See Rider Spring, JUnit5 and CDI examples.

9.1. Class level metadataset

Given: The following metataset annotation

MetaDataSet.java

package com.github.database.rider.core.api.dataset;

import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;


@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.METHOD})
@DataSet(value = "yml/users.yml", disableConstraints = true)
public @interface MetaDataSet {

}

When: The following test is executed:

@RunWith(JUnit4.class)
@MetaDataSet
public class MetaDataSetIt {

        @Rule
        public EntityManagerProvider emProvider = EntityManagerProvider.instance("rules-it");

        @Rule
        public DBUnitRule dbUnitRule = DBUnitRule.instance(emProvider.connection());

// end::declaration[]

        @Test
        public void testMetaAnnotationOnClass() {
                List<User> users = em().createQuery("select u from User u").getResultList();
                assertThat(users).isNotNull().isNotEmpty().hasSize(2);
        }

        @Test
        @AnotherMetaDataSet
        public void testMetaAnnotationOnMethod() {
                List<User> users = em().createQuery("select u from User u").getResultList();
                assertThat(users).isNotNull().isNotEmpty().hasSize(1);
        }
}
}

Then: Test must use dataset declared in MetaDataSet annotation.

9.2. Method level metadaset

Given: The following metataset annotation

MetaDataSet.java

package com.github.database.rider.core.api.dataset;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;


@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.METHOD})
@DataSet(value = "yml/expectedUser.yml", disableConstraints = true)
public @interface AnotherMetaDataSet {

}

When: The following test is executed:

@RunWith(JUnit4.class)
@MetaDataSet
public class MetaDataSetIt {

        @Rule
        public EntityManagerProvider emProvider = EntityManagerProvider.instance("rules-it");

        @Rule
        public DBUnitRule dbUnitRule = DBUnitRule.instance(emProvider.connection());

// end::declaration[]

        @Test
        public void testMetaAnnotationOnClass() {
                List<User> users = em().createQuery("select u from User u").getResultList();
                assertThat(users).isNotNull().isNotEmpty().hasSize(2);
        }

        @Test
        @AnotherMetaDataSet
        public void testMetaAnnotationOnMethod() {
                List<User> users = em().createQuery("select u from User u").getResultList();
                assertThat(users).isNotNull().isNotEmpty().hasSize(1);
        }
}
}

Then: Test must use dataset declared in AnotherMetaDataSet annotation.

10. DataSet merging

In order to reuse dataset configuration between test methods
As a developer
I want merge class level with test level dataset configuration

Complete source code of examples below can be found here.

See Rider JUnit5 and CDI examples.

10.1. Merging datasets

Given: The following class level dataset configuration

@RunWith(JUnit4.class)
@DataSet(value = "yml/tweet.yml", executeScriptsAfter = "addUser.sql", executeStatementsBefore = "INSERT INTO USER VALUES (8,'user8')")
@DBUnit(mergeDataSets = true) (1)
public class MergeDataSetsIt {

}

1	Enables dataset merging so @DataSet declared on test class will be merged with test/method one.

When: The following test is executed:

    @Test
    @DataSet(value = "yml/user.yml", executeScriptsAfter = "tweets.sql", executeStatementsBefore = "INSERT INTO USER VALUES (9,'user9')", strategy = SeedStrategy.INSERT)
    public void shouldMergeDataSetsFromClassAndMethod() {
        List<User> users = em().createQuery("select u from User u").getResultList();
        assertThat(users).isNotNull().isNotEmpty().hasSize(4);  //2 users from user.yml plus 1 from class level 'executeStatementsBefore' and 1 user from method level 'executeStatementsBefore'

        User user = (User) em().createQuery("select u from User u where u.id = 9").getSingleResult();//statement before at test level
        assertThat(user).isNotNull();
        assertThat(user.getId()).isEqualTo(9);
        user = (User) em().createQuery("select u from User u where u.id = 1").getSingleResult();

        assertThat(user.getTweets()).isNotEmpty(); //tweets comes from class level annotation merged with method level
        assertThat(user.getTweets().get(0).getContent()).isEqualTo("dbunit rules again!");
    }
    @AfterClass
    public static void afterTest() {
        User user = (User) em().createQuery("select u from User u where u.id = 10").getSingleResult();//scripts after from class level dataset
        assertThat(user).isNotNull();
        assertThat(user.getId()).isEqualTo(10);

        Tweet tweet = (Tweet) em().createQuery("select t from Tweet t where t.id = 10").getSingleResult();//scripts after on test level
        assertThat(tweet).isNotNull();
        assertThat(tweet.getId()).isEqualTo("10");
    }

}

Then: Test and method dataset configuration will be merged in one dataset

Only array properties such as value and executeScriptsBefore of @DataSet will be merged.

Class level dataset configuration will come before method level if a property is defined in both datasets, like executeStatementsBefore in example above.

You can enable dataset merging for all tests with 'mergeDataSets=true` on dbunit.yml configuraton file.

11. DataSet builder

In order to create datasets programmatically
As a developer
I want to use DatasetBuilder API.

Complete source code of examples below can be found here.

11.1. Create dataset using dataset builder

Given: The following method declaration

    @Test
    @DataSet(provider = UserDataSetProvider.class, (1)
              cleanBefore = true)
    public void shouldSeedDatabaseProgrammatically() {
        List<User> users = EntityManagerProvider.em().createQuery("select u from User u ").getResultList();
        assertThat(users).
            isNotNull().
            isNotEmpty().hasSize(2).
            extracting("name").
            contains("@dbunit", "@dbrider");
    }

1	`provider` attribute expects a class which implements DataSetProvider interface.

And: The following dataset provider implementation

    public static class UserDataSetProvider implements DataSetProvider {

        @Override
        public IDataSet provide() {
            DataSetBuilder builder = new DataSetBuilder();
            builder.table("user")(1)
                    .row() (2)
                        .column("id", 1) (3)
                        .column("name", "@dbunit")
                    .row() (4)
                        .column("id", 2)
                        .column("name", "@dbrider");
            return builder.build(); (5)
        }
    }

1	Starts a table on current dataset
2	Starts creating a row for current table
3	Adds a column witn name `id` and value `1` to current row
4	Starts creating another row for table `user`
5	creates the dataset.

For more complex examples of programmatic dataset creation, see here.

When: The test is executed
Then: The following dataset will be used for seeding the database

USER:
 - ID: 1
   NAME: "@dbunit"
 - ID: 2
   NAME: "@dbrider"

You can use DataSet export to generate DatasetBuilder code, for that just set builderType property in @ExportDataSet, ex:

   @Test
   @DataSet("datasets/yml/users.yml")
   @ExportDataSet(format = DataSetFormat.XML, outputName = "target/exported/xml/AllTables.xml", builderType = BuilderType.DEFAULT)
   public void shouldExportDataSetAsBuilderInDefaultSyntax() {
       //AllTables.java file containing DataSetBuilder code will be generated along with AllTables.xml file.
   }

yaml format is used only for illustration here, when using DatasetBuilder the dataset is only created in memory, it is not materialized in any file (unless it is exported).

11.2. Create dataset using dataset builder with `column…values` syntax

DataSetBuilder has an alternative syntax, similar to SQL insert into values, which may be more appropriated to datasets with few columns and lot of rows.

Given: The following method declaration

    @Test
    @DataSet(provider = UserDataSetProviderWithColumnsSyntax.class)
    public void shouldSeedDatabaseUsingDataSetProviderWithColumnsSyntax() {
        List<User> users = EntityManagerProvider.em().createQuery("select u from User u ").getResultList();
        assertThat(users).
                isNotNull().
                isNotEmpty().hasSize(2).
                extracting("name").
                contains("@dbunit", "@dbrider");
    }

And: The following dataset provider implementation

    public static class UserDataSetProviderWithColumnsSyntax implements DataSetProvider {

        @Override
        public IDataSet provide() {
            DataSetBuilder builder = new DataSetBuilder();
            IDataSet iDataSet = builder.table("user") (1)
                    .columns("id", "name") (2)
                    .values(1,"@dbunit") (3)
                    .values(2,"@dbrider").build();
            return iDataSet;
        }
    }

1	Starts a table on current dataset
2	Declares columns involved in current dataset
3	specify values for each column

The columns are specified only one time and the values are 'index' based (first value refers to first column.

When: The test is executed
Then: The following dataset will be used for seeding the database

USER:
 - ID: 1
   NAME: "@dbunit"
 - ID: 2
   NAME: "@dbrider"

yaml format is used only for illustration here, when using DatasetBuilder the dataset is only created in memory, it is not materialized in any file (unless it is exported).

Database Rider Documentation

1. Introduction

2. Seeding database

2.1. Seed database with DBUnit Rule

Dependencies

2.2. Seed database with DBUnit Interceptor

Dependencies

2.3. Seed database with JUnit 5 extension

Dependencies

2.4. Seeding database in BDD tests with Rider Cucumber

Dependencies

3. DataSet creation

3.1. Creating a YAML dataset

3.2. Creating a JSON dataset

3.3. Creating a XML dataset

3.4. Creating a XLS dataset

3.5. Creating a CSV dataset

4. Configuration

4.1. DataSet configuration

4.2. DBUnit configuration

5. DataSet assertion

5.1. Assertion with yml dataset

5.2. Assertion with regular expression in expected dataset

5.3. Database assertion with seeding before test execution

5.4. Failing assertion

5.5. Assertion using automatic transaction

6. Dynamic data using scritable datasets

6.1. Seed database with groovy script in dataset

6.2. Seed database with javascript in dataset

7. Database connection leak detection

7.1. Detecting connection leak

8. DataSet export

8.1. Export dataset with @ExportDataSet annotation

8.2. Programmatic export

8.3. Configuration

8.4. Export using DBUnit Addon

9. MetaDataSet

9.1. Class level metadataset

9.2. Method level metadaset

10. DataSet merging

10.1. Merging datasets

11. DataSet builder

11.1. Create dataset using dataset builder

11.2. Create dataset using dataset builder with column…​values syntax

2.1. Seed database with `DBUnit Rule`

2.2. Seed database with `DBUnit Interceptor`

2.3. Seed database with `JUnit 5 extension`

2.4. Seeding database in BDD tests with `Rider Cucumber`

3.1. Creating a `YAML` dataset

3.2. Creating a `JSON` dataset

3.3. Creating a `XML` dataset

3.4. Creating a `XLS` dataset

3.5. Creating a `CSV` dataset

8.1. Export dataset with `@ExportDataSet` annotation

11.2. Create dataset using dataset builder with `column…values` syntax