Manage test data for integration tests using Spring and DBunit

Introduction

Most enterprise applications rely on the database as the persistence mechanism. Integration tests for these applications require data in the database to run correctly. For integration tests to be repeatable, the tests should carry the test data they need with them and insert it before running the tests and delete it after the tests, since the data in the database can change with time.

DBUnit is a tool which provides this functionality.

This article will look at configuring integration tests using Spring and DBUnit so that test data is inserted into the database before every test. This article also looks at a utility to export/import test data in the database using DBunit.

It is assumed that the reader is familiar with the following concepts and tools:

• Unit testing and integration testing
• Spring 2 and JUnit 3.8
• XML
• General database concepts

Unit tests should run in isolation without relying on any other external dependency. Business layer classes should also be unit tested in isolation without relying on the database.

Tests which rely on another dependency (e.g. database) are integration tests. Testing Data Access Objects (DAOs) is integration testing as it tests the integration between database and the DAOs. Business layer classes which use DAOs also indirectly depend on the database.

This article deals with integration testing of DAOs and integration testing of business layer classes which use DAOs to read/write data.

See further reading links at the end for an interesting discussion of the unit tests, integration testing and DBUnit.

The test data could be managed manually in several ways:

The ideal approach is to:

• Export a subset of the data in the database to a flat file (XML preferred)
• Developers use this flat file as template and change the values some of the interesting values they want to change. This flat file now has the test data
• Before the integration tests run, the data in the flat file is inserted into the database
• After the integration tests run, the data is deleted from the database

DBunit

This is an offline task which is done before integration tests are written. The steps involved in exporting a subset of data in the database to XML are:

• Connect to the database
• Specify the SQL to run to retrieve the data
• Specify the location of the flat file(XML)
• Export the data

This is shown in the code below:

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.database.QueryDataSet;
import org.dbunit.dataset.xml.FlatXmlWriter;

public class TestDBUnit
{
 public static void main(String[] args)
  throws Exception
 {
  //Connect to the database
  DriverManager.registerDriver(new net.sourceforge.jtds.jdbc.Driver());
  Connection conn = DriverManager.getConnection("URL_TO_CONNECT", "username", "password");

  IDatabaseConnection connection = new DatabaseConnection( conn );

  QueryDataSet partialDataSet = new QueryDataSet(connection);
  //Specify the SQL to run to retrieve the data
  partialDataSet.addTable("person", " SELECT * FROM person WHERE name='Test name' ");
  partialDataSet.addTable("address", " SELECT * FROM address WHERE addressid=1 ");

  //Specify the location of the flat file(XML)
  FlatXmlWriter datasetWriter = new FlatXmlWriter(new FileOutputStream("temp.xml"));

  //Export the data
     datasetWriter.write( partialDataSet );
 }
}

The data in the 2 tables is shown below:

Table ‘Person’

The SQL to create this table on Microsoft SQL Server is ‘ create table person (personid int not null primary key identity , name varchar (20 ), dob datetime )’

Table ‘Address’

The SQL to create this table on Microsoft SQL Server is ‘ create table address (addressid int not null primary key identity , address varchar (20 ))‘.

The XML file produced by running the program above is shown below:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <person personid="1" name="Test name" dob="2007-04-20 00:00:00.0"/>
  <address addressid="1" address="23 Some street"/>
</dataset>

The developer can now edit the file or add new rows. This is now the test data and is ready to be inserted by DBUnit when the tests are run.

The export of data in the database to XML is an offline process which is done before (or during) writing integration tests.

The import of data from XML to database is done when the integration tests are run.

For a JUnit test, the test data is inserted in the ‘setUp’ method and removed in the ‘tearDown’ method as shown below:

public class DBUnitTests extends TestCase
{
 public void setUp()
 {
  System.out.println("In setup");
  Connection conn = null;
  try
  {
   DriverManager.registerDriver(new net.sourceforge.jtds.jdbc.Driver());
   conn = DriverManager.getConnection("URL", "username", "password");
   IDatabaseConnection connection = new DatabaseConnection( conn );
   DatabaseOperation.INSERT.execute(connection, new FlatXmlDataSet(new FileInputStream("temp.xml")));
   conn.close();
  }
  catch(Exception exc)
  {
   exc.printStackTrace();
  }
 }

 public void tearDown()
 {
  System.out.println("In tearDown");
  Connection conn = null;
  try
  {
   DriverManager.registerDriver(new net.sourceforge.jtds.jdbc.Driver());
   conn = DriverManager.getConnection("URL", "username", "password");
   IDatabaseConnection connection = new DatabaseConnection( conn );
      DatabaseOperation.DELETE.execute(connection, new FlatXmlDataSet(new FileInputStream("temp.xml")));
      conn.close();
  }
  catch(Exception exc)
  {
   exc.printStackTrace();
  }
 }

 public void testDBUnitImport1()
 {
  System.out.println("In testDBUnitImport1");
  //Run the test
 }

 public void testDBUnitImport2()
 {
  System.out.println("In testDBUnitImport2");
  //Run the test
 }
}

Note: The IdentityInsertOperation.INSERT operation is required for MS-SQL Server instead of DatabaseOperation.INSERT.

Enhance Junit tests using Spring and DBUnit

The code shown in section above has data access logic mixed in the integration test. This is common to all tests and can be moved to a super class. We also would need some mechanism to make it easier for integration tests to access DAO and service layer classes.

Spring provides support for both of these.

Spring provides ‘ AbstractTransactionalDataSourceSpringContextTests’ class which extends JUnit TestCase class. This class has ‘ onSetUpInTransaction’ and ‘ onTearDownInTransaction’ methods which run inside a transaction and can be used to insert test data. We can extend this class and override these 2 methods to insert and delete test data.

This is shown below:

public class AbstractTransactionalHibernateTests extends AbstractTransactionalDataSourceSpringContextTests
{
 private static String TEST_DATA_FILE = "dbunit-test-data.xml";

 protected void onSetUpInTransaction()
  throws Exception
 {
  logger.info("*** Inserting test data ***");
  //Use spring to get the datasource
  DataSource ds = this.jdbcTemplate.getDataSource();
  Connection conn = ds.getConnection();
  try
  {
   IDatabaseConnection connection = new DatabaseConnection( conn );
   DatabaseOperation.INSERT.execute(connection, new FlatXmlDataSet(new FileInputStream(TEST_DATA_FILE)));
  }
  finally
  {
   DataSourceUtils.releaseConnection(conn, ds);
   logger.info("*** Finished inserting test data ***");
  }
 }

 protected void onTearDownInTransaction()
  throws Exception
 {
  //Commit or rollback the transaction
  endTransaction();

  //Delete the data
  DataSource ds = this.jdbcTemplate.getDataSource();
  Connection conn = ds.getConnection();
  try
  {
   IDatabaseConnection connection = new DatabaseConnection( conn );
      DatabaseOperation.DELETE.execute(connection, new FlatXmlDataSet(new FileInputStream(TEST_DATA_FILE)));
  }
  finally
  {
   DataSourceUtils.releaseConnection(conn, ds);
   logger.info("*** Finished removing test data ***");
  }
 }
}

Note:

• The test data is in file ‘dbunit-test-data.xml’ for the example above
• This class relies on spring to setup database connection
• The IdentityInsertOperation.INSERT is required for MS-SQL Server instead of DatabaseOperation.INSERT

The Spring provided class ‘ AbstractTransactionalDataSourceSpringContextTests’ extends JUnit ‘TestCase’ class. The developer writes integration tests by extending ‘ AbstractTransactionalDataSourceSpringContextTests’ class. All the features provided by JUnit are available to him.

If the integration tests extend the Spring-provided ‘ AbstractTransactionalDataSourceSpringContextTests’ class, then any Spring-managed beans defined in the integration tests are injected into the integration tests.

Here is a class which extends ‘ AbstractTransactionalDataSourceSpringContextTests’. We have overridden ‘ protected String[] getConfigLocations()’ to specify the location of Spring config files. For integration tests which extend ‘ AbstractTransactionalDataSourceSpringContextTests’ spring will inject Spring managed bean defined in the config files into the integration test. This class is shown below:

public class AbstractTransactionalHibernateTests extends AbstractTransactionalDataSourceSpringContextTests
{
    protected String[] getConfigLocations()
    {
        return new String[] {"classpath: springConfig.xml"};
    }
}

Sample Spring config file (springconfig.xml) is shown below:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
    "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
 <bean id="testDao" class="test.TestDaoImpl" parent="abstractHibernateDao" />
</beans>

An integration test is shown below:

public class TestDaoTests extends AbstractTransactionalHibernateTests
{
 private TestDao testDao = null;

 public void testGetUsingId()
 {
  //do something
 }

//Called by spring to inject the testDao
 public void setTestDao(TestDao testDao)
 {
  this.testDao = testDao;
 }
}

Summing up implementation details

A quick summary of what is discussed in the previous sections is shown below:

• Use DBUnit to export data from the database. This is done offline before or during writing integration tests. The XML file holds the test data and is stored in version control system along with source code. Developers use this as a template and modify this file as required to setup their test data.
• Write your own class ‘ AbstractTransactionalHibernateTests’ which extends Spring ‘ AbstractTransactionalDataSourceSpringContextTests’. Override ‘ onSetUpInTransaction’ and ‘ onTearDownInTransaction ’ to insert and delete test data held in the XML file. These 2 methods run within the context of a transaction. Also override ‘ protected String[] getConfigLocations()’ to specify the location of Spring config file
• Integration tests extend ‘ AbstractTransactionalHibernateTests’ class which we developed. Any Spring managed beans(DAOs or business layer classes) defined in the spring config are auto injected into the integration tests if there is a setter present for the bean.

Advantages and disadvantages of using Spring and DBUnit

• Simple, elegant and easy to implement.
• Test data is inserted during the test and removed after the test. So test data is always in a known state for every test and changes to test data when running a test won’t affect any other test.
• Dependencies injected automatically by Spring if they are defined in the Spring configuration file.
• Can be used for integration testing of DAOs and business layer classes
• Integration tests written by developers do not have to deal with inserting and deleting data. Test data is available to them at start of the test
• DBUnit provides facility to export subset of data in the database to XML. This is used as a template and developers can modify this XML file to setup their test data.

• Test data is inserted before each test. This is inefficient in some cases where the integration tests do not change the test data. It should be inserted once at the start of running the test suite and deleted once the test suite completes. This should be easier with Junit 4 and TestNG but is more difficult with Junit 3.8

Other tools

JUnit 4 introduces a more sophisticated JUnit unit test lifecycle. Any method in a POJO class can be marked as a JUnit test case method. Any method can be marked as a special method to run it once for the whole test suite (class scoped setup() using @BeforeClass annotation – no equivalent method in Junit 3.8), or for the test case(using @Before) or for the test methods(using @Test).

However the annotations used in Junit4 interfere with spring dependency injection and Spring cannot be used with Junit4.

The Gienah project ( https://code.google.com/p/gienah-testing/ ) provides a workaround but has not been tried by the author.

Also see another article at http://dmy999.com/article/21/adapting-a-springs-junit-3x-base-classes-to-junit-4 which deals with using Junit 4 and Spring.

TestNG provides sophisticated features such as parallel text execution, test classes as POJOs and annotations.

DBUnit should work just as well with TestNG. TestNG uses annotations in POJOs. Any method can be used as a test method ( by annotating it with @Test). TestNG gives the developer choice of inserting test data once for each test(using @BeforeMethod and @AfterMethod) or before each test case (using @BeforeClass and @AfterClass) or once per text execution (using @BeforeSuite and @AfterSuite).

See further reading section at the end for a ServerSide article on running Junit 3 tests in TestNG

Although the author has not tested it, Spring seems to work with TestNG annotations. Please see link in further reading section

Other resources required for integration tests

Integration tests could require other test data e.g. some service layer classes might be involved with processing of excel or csv files. On production system, binary files can be uploaded using a browser and processed by the service layer classes as byte arrays.

Binary files representing test data can be stored in version control system along with the test data. The disadvantage of this approach is this takes up space and they don’t belong in version control systems.

They can be stored in a file server and retrieved using FTP. The Apache commons-net library provides a FTP client which can be incorporated into a java application to retrieve a file from an FTP server. Apache commons-net library provides facility to read data in files on a FTP server into a byte array.

Conclusion

Unit testing is testing code in isolation without relying on any dependencies. Integration testing typically depends on presence of test data in the database.

Test data can be managed using DBUnit. Spring can be used with DBUnit to ensure test data is inserted into the database before every test and deleted after the test.

DBUnit also provides a facility to export a subset of data in the database to an XML flat file. This file is used by DBUnit to insert data before running each test.

JUnit 4 and TestNG provide a more sophisticated lifecycle. Test data can be inserted once at the start and deleted at the end. This makes running tests more efficient and is suitable for cases where the integration tests do not change the test data.

Further reading

See https://www.theserverside.com/discussions/thread/33215.html for an interesting discussion of unit tests, integration tests and DBUnit
See https://code.google.com/p/gienah-testing/ for Gienah project for using Junit4 with Spring
See http://dmy999.com/article/21/adapting-a-springs-junit-3x-base-classes-to-junit-4 for using Junit4 and Spring
See http://www.springframework.org/docs/reference/testing.html for Spring support for testing
See https://www.theserverside.com/news/1365203/Migrating-to-TestNG for migrating Junit 3 tests to TestNG
See http://www.thearcmind.com/confluence/display/SHJFT/TestNG,+JMock+and+Spring+integration+tests for using TestNG and Spring for writing integration tests

Biography

Avneet Mangat has 5 years experience in Java/J2EE. He is currently working as Lead developer at Active Health Partners ( www.ahp.co.uk ). He has a Bachelors degree in Software Engineering, Sun Certified Java Programmer, Sun Certified Web Component Developer, Adobe certified Flash Designer and Prince2 certified (foundation). Lead developer of open source tool DBBrowser, please see http://databasebrowser.sourceforge.net/ Outside interests include photography and travelling. Please contact me at [email protected] or [email protected] for more information.

Author of ‘Setting up maven repository’ article on serverside - https://www.theserverside.com/news/1364121/Setting-Up-a-Maven-Repository