Unit testing your services with the help of DBUnit

Unittesting an application is something which can improve the overall quality of the application. It can sometimes be a tedious job to test a service layer which accesses a database because it has to be in a known state when running the tests. To do this, unit tests which need a database often use in-memory databases like H2 or HSQL (HyperSQL) because they run independent from other developers and force the user to initialise the database in a known state. To do this you could drop and re-create your database using SQL scripts before every test or even create a setup-script and a teardown-script  but there is much ‘nicer’ way of doing this: DBUnit

A shameless copy-paste of dbunit.org:

DbUnit is a JUnit extension (also usable with Ant) targeted at database-driven projects that, among other things, puts your database into a known state between test runs. This is an excellent way to avoid the myriad of problems that can occur when one test case corrupts the database and causes subsequent tests to fail or exacerbate the damage.

DbUnit has the ability to export and import your database data to and from XML datasets. Since version 2.0, DbUnit can also work with very large datasets when used in streaming mode. DBUnit can also help you to verify that your database data match an expected set of values.

Why DBUnit

If you continue reading this guide then you’ll quickly notice that DBUnit doesn’t do anything but inserting and deleting data into a database and that’s right. It’s mainly created to get real database instances  into a known state. It was never created to initialize an in-memory database, this should be accomplished via another procedure (read: executing SQL scripts). But when looking at the advantages it might become clear that DBUnit isn’t that bad at all because:

  1. If you normally just drop and re-create the schema: with DBUnit there is no need to do this anymore which will improve the performance of your test suite
  2. If you normally create a setup and teardown script: with DBUnit you have to write less scripts, thus less risk for errors
  3. the XML files are more easy to read and even your functional analyst can create them (or you create it in Excel and then you convert it to XML)
  4. It’s not even that complicated and doesn’t take much time to set up

Getting started

The application I’m testing uses the Hibernate JPA2.0 implementation to use an H2 database which is running in server mode and has a single schema named “dbunit”. Hibernate is configured to used a JDBC connection pool which is retreived via a JNDI lookup. The stateless EJB beans are configured to require a Transaction which is managed by the container (Glassfish). I’m also using the hibernate-validator.

This setup results in the following Maven 2.0 pom.xml file.

xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>be.pw</groupId>
    <artifactId>dbunitTryOut</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>ejb</packaging>
    <name>dbunitTryOut</name>
    <properties>
        <endorsed.dir>${project.build.directory}/endorsed</endorsed.dir>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <netbeans.hint.deploy.server>gfv3ee6</netbeans.hint.deploy.server>
    </properties>
    <dependencyManagement>
          <!-- <span class="hiddenSpellError" pre="">bugfix</span> for <span class="hiddenSpellError" pre="for ">slf4j</span> ... version 1.6 is not compatible with the 1.5.5 binding in hibernate-->
            <dependency>
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-api</artifactId>
                <version>1.5.5</version>
            </dependency>
        </dependencies>
    </dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>3.6.8.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>3.6.8.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>4.2.0.Final</version>
        </dependency>
        <dependency>
            <groupId>javax</groupId>
            <artifactId>javaee-api</artifactId>
            <version>6.0</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>
    <build>
        <resources>
            <resource>
                ${project.basedir}/src/main/resources
            </resource>
        </resources>
        <testResources>
            <testResource>
                ${project.basedir}/src/test/resources
            </testResource>
        </testResources>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.3.2</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                    <compilerArguments>
                        <endorseddirs>${endorsed.dir}</endorseddirs>
                    </compilerArguments>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-ejb-plugin</artifactId>
                <version>2.3</version>
                <configuration>
                    <ejbVersion>3.1</ejbVersion>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-dependency-plugin</artifactId>
                <version>2.1</version>
                <executions>
                    <execution>
                        <phase>validate</phase>
                        <goals>
                            <goal>copy</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>${endorsed.dir}</outputDirectory>
                            <silent>true</silent>
                            <artifactItems>
                                <artifactItem>
                                    <groupId>javax</groupId>
                                    <artifactId>javaee-endorsed-api</artifactId>
                                    <version>6.0</version>
                                    <type>jar</type>
                                </artifactItem>
                            </artifactItems>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
    <repositories>
        <repository>
            <id>jboss</id>
            JBoss for hibernate
            <url>https://repository.jboss.org/nexus/content/groups/public/
        </repository>
    </repositories>
</project>

To test this application using JUnit and DBunit I’ve added the following dependencies:

        <dependency>
            <groupId>org.dbunit</groupId>
            <artifactId>dbunit</artifactId>
            <version>2.4.8</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.8.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.glassfish.extras</groupId>
            <artifactId>glassfish-embedded-all</artifactId>
            <version>3.0</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.3.161</version>
            <scope>test</scope>
        </dependency>

Do notice that you need the glassfish-embedded-all dependency, even when you use another container to run your application on. This dependency has the reference implementation of several components which are required to use the EntityManager.

Exporting your existing database

After bringing my H2 database in a known state (this is the state that I’m expecting the DB to be in for every test that I write) it’s time to export the database to an XML format that DBUnit can interpret. Luckily for us, DBUnit provides an easy way to do this. Create the following Java class in your test folder (src/test/java/…/Export.java) and adapt the connection URL and schema to your needs.


import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatDtdDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;

/**
 * @author pw
 */
public class Export {
 public static void main(String[] args) throws Exception
 {
 // database connection
 Class driverClass = Class.forName("org.h2.Driver");
 Connection jdbcConnection = DriverManager.getConnection("jdbc:h2:tcp://localhost:9101/dbunit", "sa", "");
 IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);

 jdbcConnection.createStatement().execute("set schema dbunit");

 // full database export
 IDataSet fullDataSet = connection.createDataSet();
 FlatXmlDataSet.write(fullDataSet, new FileOutputStream("full.xml"));
 FlatDtdDataSet.write(connection.createDataSet(), new FileOutputStream("test.dtd"));
 }
}

When running this class it will generate two XML files:

  • full.xml which has the current data from your database
  • test.dtd which is a DTD of the XML representation of your database. You don’t need this, but it might come in handy when you want to change the full.xml file

You will also need a SQL file to create the structure of your in-memory database. Just keep in mind that DBUnit doesn’t always respect correct foreign constraints so you either have to exclude these from the SQL file or make sure that the full.xml file respects the foreign keys.

FYI: this is my SQL file:


drop schema dbunit if exists;
create user if not exists mike password 'mike';
create schema dbunit authorization sa;
set schema dbunit;

create table PERSOON (
 N_ID BIGINT NOT NULL AUTO_INCREMENT(0,1),
 T_NAAM VARCHAR(255) NOT NULL,
 T_VOORNAAM VARCHAR(255) NOT NULL,
 PRIMARY KEY(N_ID)
);

create table ADRES (
 N_ID BIGINT NOT NULL AUTO_INCREMENT(0,1),
 T_LIJN1 VARCHAR(255) NOT NULL,
 T_LIJN2 VARCHAR(255) NOT NULL,
 N_LAND_ID BIGINT NOT NULL,
 PRIMARY KEY(N_ID)
);

create table LAND (
 N_ID BIGINT NOT NULL AUTO_INCREMENT(0,1),
 T_LAND VARCHAR(255) NOT NULL,
 PRIMARY KEY(N_ID)
);

create table PERSOON_X_ADRES (
 N_ID BIGINT NOT NULL AUTO_INCREMENT(0,1),
 N_PERSOON_ID BIGINT NOT NULL,
 N_ADRES_ID BIGINT NOT NULL,
 PRIMARY KEY(N_ID)
);

alter table ADRES add constraint FK_ADRES_LAND foreign key (N_LAND_ID) REFERENCES LAND(N_ID) ON DELETE RESTRICT ON UPDATE CASCADE;
alter table PERSOON_X_ADRES add constraint FK_PERSOON_X_ADRES_PERSOON foreign key (N_PERSOON_ID) REFERENCES PERSOON(N_ID) ON DELETE RESTRICT ON UPDATE CASCADE;
alter table PERSOON_X_ADRES add constraint FK_PERSOON_X_ADRES_ADRES foreign key (N_ADRES_ID) REFERENCES ADRES(N_ID) ON DELETE RESTRICT ON UPDATE CASCADE;

Preparing the persistence unit for JUnit

Our beans will get access to the database via a persistence unit which is defined in the src/main/resources/META-INF/persistence.xml file. Since our tests will use a different database we have to define a different persistence unit. This persistence unit will automatically start up the in-memory database (you’ve gotta love H2). This persistence unit will not use a JNDI lookup or a connection pool at all but relies on simple connection parameters:


<?xml version="1.0" encoding="UTF-8"?>
xmlns="http://java.sun.com/xml/ns/persistence"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
 <persistence-unit name="be.pw_dbunitTryOut_junit" transaction-type="RESOURCE_LOCAL">
 <provider>org.hibernate.ejb.HibernatePersistence</provider>
 <non-jta-data-source/>
 <exclude-unlisted-classes>false</exclude-unlisted-classes>
 <properties>
 url" value="jdbc:h2:mem:dbunit"/>
 <property name="javax.persistence.jdbc.driver" value="org.h2.Driver"/>
 <property name="javax.persistence.jdbc.user" value="sa"/>
 <property name="javax.persistence.jdbc.password" value=""/>
 </properties>
 </persistence-unit>
</persistence>

Creating the super class

All the logic to set up the in-memory database will be done in an abstract super class from which all unit tests will extend. This abstract class will extend from DBUnit’s DBTestCase class. Even tough this abstract class will no define a single abstract method, it needs to be abstract for JUnit to ignore it, otherwise your test suite will fail because there are no runable tests in this class.

On calling the constructor of the abstract class, the following steps must be followed:

  1. create the EntityManager (this will at the same time start the H2 in-memory database)
  2. define the connection parameters
  3. create a generic SQL connection
  4. build the database structure
  5. read the DBUnit xml file

Then, before each test method is run (@Before) the database will be cleaned and re-filled using DBUnit.

1. creating the EntityManager

About the easiest step:


em = Persistence.createEntityManagerFactory("be.pw_dbunitTryOut_junit").createEntityManager();

2. define the connection parameters

DBUnit does not rely on JPA to get access to the database but uses instead a simple java.sql.Connection. To configure this connection you’ll need to set some system properties:


url = "jdbc:h2:mem:dbunit";
 username = "sa";
 password = "";
 driver = "org.h2.Driver";
 System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS, driver);
 System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL, url);
 System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME, username);
 System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD, password);
 System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_SCHEMA, "dbunit");

Depending on your JPA implementation you can also recover these properties from the EntityManager (em.getProperties(), which will return a Map). The exact keys are depend on the implementation, you’ll have to take a look at the documentation or just debug and inspect the Map.

3. create a generic SQL connection

No rocket science yet:


Class.forName(driver);
 connection = DriverManager.getConnection(url, username, password);

4. build the database structure

As said before we will rely on a SQL file to build the database structure. Using a simple StringBuffer the SQL file will be read and based on the delimiter (a semicolon in my case the huge String will be split in an array of queries which will be executed one by one). Since I’m using the EntityManager to execute the queries it’s important to verify that a transaction is active and that this transaction is committed afterwards (more on this later).


if (!em.getTransaction().isActive()) {
 em.getTransaction().begin();
 }
 FileReader fr = new FileReader(new File(this.getClass().getResource("/databaseSchema.sql").getFile()));
 BufferedReader br = new BufferedReader(fr);
 StringBuffer sb = new StringBuffer();
 while (br.ready()) {
 sb.append(br.readLine());
 }
 String[] queries = sb.toString().split(";");
 for (String s : queries) {
 Logger.getLogger(getClass().getSimpleName()).log(Level.INFO, s);
 Query q = em.createNativeQuery(s);
 q.executeUpdate();
 }
 em.flush();
 em.getTransaction().commit();

5. read the DBUnit xml file

Untill now we haven’t done much with DBUnit yet (in fact we’ve just exported our database to an XML file using DBUnit). Now it’s time to prepare our unit tests for some DBUnit action:

First we need an FileInputStream referring to the XML file


is = new FileInputStream("Z:/dev/src/git/dbunitTryOut/full.xml"); //I should be ashamed 😉

Then we need to build the IDataSet that must be returned by the getDataSet() method (abstract method in DBTestCase).


dataSet = new FlatXmlDataSetBuilder().build(is);

Last but not least we need a special IDatabaseConnection for DBUnit

conn = new DatabaseConnection(connection);

The @Before and @After method

Now all we need to do is create a method which will be called before and after each test. The @Before method will simply ask DBUnit to cleanup the database and re-insert all the data using the SQL Connection and this is where you have to pay a bit attention; H2 does not support more than one connection when it’s running in-memory thus the EntityManager should not have active transactions (remember that in step 4 the transaction had to be committed). It’s only after inserting the data that we have to begin a transaction. This transaction will be used throughout the unit tests and needs to be committed afterwards (which will happen in the @After method). All this seems a bit strange and in fact it is because it’s normally the container which starts and stops the transactions.


@Override
 @Before
 protected void setUp() throws Exception {
 super.setUp();
 try {
 connection.createStatement().executeUpdate("set schema dbunit");
 DatabaseOperation.CLEAN_INSERT.execute(conn, dataSet);
 em.getTransaction().begin();
 } catch (Exception e) {
 Logger.getLogger(getClass().getSimpleName()).log(Level.SEVERE, e.getMessage());
 }

@After
 protected void tearDown() throws Exception {
 super.tearDown();
 em.getTransaction().commit();

 }
 }

The complete project can be cloned or browsed through.

Advertisements

One thought on “Unit testing your services with the help of DBUnit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s