Testing services with DBUnit and HyperSQL

     Let’s assume that you have a tests that use database and you want to set up the database to be sure about the state before each test run. Otherwise we will get a headaches to maintain a lot of test manually(different configuration and database contents), because we want that out test always run successful. We need a testing tool that provides easy way to initialise database and create/restore consistent state for our test. DBUnit will help us with that. It’s a great integration tool that can export and import your database data to and from XML datasets.

     This post shows how easily can be used DBUnit and HSQL to write database tests and testing your persistence layer services. So, getting started. Suppose that we have a service UserService that returns User objects from the database. And we want to test services method to make sure that everything is working properly.

     Well, the plan is to initialize the database before execute test, execute the database schema and read the DBUnit xml file. And then build the IDataSet that represents a collections of tables.

jdbcConnection = DriverManager.getConnection("jdbc:hsqldb:mem:hsql://localhost/test;user=sa");
dbConnection = new DatabaseConnection(jdbcConnection, "create_database.sql");
dbConnection.getConfig().setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);
.....
IDataSet  dataSet = new XmlDataSet(getClass().getResourceAsStream("hsqldb_users.dbunit.xml"));

     The create_database.sql schema file look like this.

create table if not exist users (
    id varchar(30) not null,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    email varchar(30),
    primary key (id)
);

     And here is a simple dataset xml file.

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <table name="users">
        <column>id</column>
        <column>first_name</column>
        <column>last_name</column>
        <column>email</column>
        <row>
            <value>123</value>
            <value>Tom</value>
            <value>Jenkins</value>
            <value>tom.jenkins@gmail.com</value>
        </row>
        <row>
            <value>456</value>
            <value>Red</value>
            <value>Balloon</value>
            <value>red.balloon@gmail.com</value>
        </row>
        <row>
            <value>789</value>
            <value>Max</value>
            <value>Liano</value>
            <value>maxliano@gmail.com</value>
        </row>
    </table>
</dataset>

     Now, we are ready to test our service methods. We can get the necessary data table as an ITable object from IDataSet.

@Test
public void testFindUserByid() throws Exception {
    final ITable users = getDataSet().getTable("users");
    
    String firstName = users.getValue(0, "first_name").toString(); // Tom
    String lastName = users.getValue(0, "last_name").toString(); // Jenkins
    String email = users.getValue(0, "email").toString(); // tom.jenkins@gmail.com
    
    User user = userService.findUserByid("123");
    
    Assert.assertEquals(firstName, user.getFirstName());
    Assert.assertEquals(lastName, user.getLastName());
    Assert.assertEquals(email, user.getEmail());
}

@Test
public void testFindUserByEmail() throws Exception {
     final ITable users = getDataSet().getTable("users");
     
     String id = users.getValue(0, "id").toString(); // 123
     String firstName = users.getValue(0, "first_name").toString(); // Tom
     String lastName = users.getValue(0, "last_name").toString(); // Jenkins
     
     User user = userService.findUserByEmail("tom.jenkins@gmail.com");
     
     Assert.assertEquals(id, user.getId());
     Assert.assertEquals(firstName, user.getFirstName());
     Assert.assertEquals(lastName, user.getLastName());
}

     And here is a Maven pom.xml file with necessary dependencies.

<dependencies>
    .....
    <dependency>
      <groupId>org.dbunit</groupId>
      <artifactId>dbunit</artifactId>
      <version>2.4.9</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.hsqldb</groupId>
      <artifactId>hsqldb</artifactId>
      <version>2.3.0</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.5</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-nop</artifactId>
      <version>1.7.5</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    .....
</dependencies>

     Finally, the complete DbUnitDatabaseTest file with @Before and @After methods which will be called before and after test accordingly and other important pieces of code.

import java.sql.Connection;
import java.sql.DriverManager;

import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.xml.XmlDataSet;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;

/**
 * Example database test with DBUnit and HSQLDB.
 * 
 * @author Dmitry Nikolaenko
 *
 */
public class DbUnitDatabaseTest {
    private Connection jdbcConnection;
    private IDatabaseConnection dbConnection;
    private IDataSet dataSet;
    
    @Before
    public void setUp() throws Exception {
        Thread.sleep(1000L);
        Class.forName("org.hsqldb.jdbcDriver");
        jdbcConnection = DriverManager.getConnection("jdbc:hsqldb:mem:hsql://localhost/test;user=sa");
        dbConnection = new DatabaseConnection(jdbcConnection, "create_database.sql");
        dbConnection.getConfig().setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);
    }

    @After
    public void tearDown() throws Exception {
        dbConnection.close();
        jdbcConnection.close();
    }
    
    private IDataSet getDataSet() throws Exception {
     if (dataSet == null) {
      dataSet = new XmlDataSet(getClass().getResourceAsStream("hsqldb_users.dbunit.xml"));
     }
     return dataSet;
    }

    @Test
    public void testFindUserByid() throws Exception {
        final ITable users = getDataSet().getTable("users");
        
        String firstName = users.getValue(0, "first_name").toString(); // Tom
        String lastName = users.getValue(0, "last_name").toString(); // Jenkins
        String email = users.getValue(0, "email").toString(); // tom.jenkins@gmail.com
        
        User user = userService.findUserByid("123");
        
        Assert.assertEquals(firstName, user.getFirstName());
        Assert.assertEquals(lastName, user.getLastName());
        Assert.assertEquals(email, user.getEmail());
    }
    
    @Test
    public void testFindUserByEmail() throws Exception {
         final ITable users = getDataSet().getTable("users");
      
         String id = users.getValue(0, "id").toString(); // 123
         String firstName = users.getValue(0, "first_name").toString(); // Tom
         String lastName = users.getValue(0, "last_name").toString(); // Jenkins
         
         User user = userService.findUserByEmail("tom.jenkins@gmail.com");
         
         Assert.assertEquals(id, user.getId());
         Assert.assertEquals(firstName, user.getFirstName());
         Assert.assertEquals(lastName, user.getLastName());
    }
}

     Also example can be found on Github with all additional files. So, keep calm and write code :)