Theme: 

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:

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:

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.37.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.37.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
IMPORTANT: Since v1.8.0 you can also use com.github.database.rider.cdi.api.DBRider annotation to enable database rider, both activate the DBUnitInterceptor.
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.37.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.
TIP: The same works for SpringBoot projects using JUnit5, see an example project here.
If you use SpringBoot extension for JUnit5 you don’t need to declare the field or method, see an example here.
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.37.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 
USER
TWEET
FOLLOWER
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

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 ExpectedException exceptionRule = ExpectedException.none();

    @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
@DataSet(cleanBefore = true)
@ExpectedDataSet(value = "yml/expectedUsers.yml", ignoreCols = "id")
public void shouldNotMatchExpectedDataSet() {
    exceptionRule.expect(ComparisonFailure.class);
    exceptionRule.expectMessage("value (table=USER, row=0, col=NAME) expected:<[]expected user1> but was:<[non ]expected user1>");
    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)
@Ignore("`shouldFindTwoConnectionLeaks` is failing randomly, seems one of the connections in being closed by another test or DriverManager is returning the same connection.")
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 shouldFindTwoConnectionLeaks() throws SQLException {
        exception.expect(LeakHunterException.class);
        exception.expectMessage("Execution of method shouldFindTwoConnectionLeaks left 2 open connection(s).");
        createLeak();
        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: " + u1.getId() + NEW_LINE +
                            " NAME: \"u1\"" + NEW_LINE
            );
}

@Test
@DataSet(cleanBefore = true)
public void shouldExportYMLDataSetWithExplicitSchemaProgrammatically() 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"), "public");
    File ymlDataSet = new File("target/user.yml");
    assertThat(ymlDataSet).exists();
    assertThat(contentOf(ymlDataSet)).
            contains("USER:" + NEW_LINE +
                            " - ID: " + u1.getId() + 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.

""

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

  1. Setup database connection

    Setup command

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

    Export command
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).
1. Contexts and dependency for the Java EE
2. Scripting for the Java Platform, for more information access the official docs here