DBUNIT

Database Testing with DbUnit

DbUnit is an open source Framework created by Manuel Laflamme. This is a powerful tool for simplifying Unit Testing of the database operations. This tutorial explains the concepts of this testing tool with proper example.
The DbUnit is an extension of JUnit which is used data-driven Java applications. With the help of DbUnit you can repopulate your database with sample data and perform unit testing of the Java application.
This helps the developers to quickly Unit test database driven applications. You can completely test persistence layer of your application.
In this tutorial we have given examples which will help you in learning DbUnit quickly.
The list of topics is given below:

Introduction to DbUnitDbUnit is an open source Framework created by Manuel Laflamme. This is a powerful tool for simplifying Unit Testing of the database operations. It extends the popular JUnit test framework that puts the database into a known state while the test executes. This strategy helps to avoid the problem that can occur when one test corrupts the database and causes subsequent test to fail. DbUnit provides a very simple XML based mechanism for loading the test data, in the form of data set in XML file, before a test runs. Moreover the database can be placed back into its pre-test state at the completion of the test.

Advantages of DbUnit

The reasons to use this testing tool can be summarized as follows :

1. A framework which simplifies operations for each stage in the life cycle of individual database tests.



2. It provides a very simple XML based mechanism for loading test data.


3. It provides equally a simple mechanism to export existing test data into the XML format for subsequent use.


4. It can work with very large datasets.


5. It can help verify your data matches an expected set of values.


6. It provides methods for comparing data between flat files, queries and database tables.

Setting up the environment
To run the example of DbUnit in Eclipse, we need jar files of  DbUnit, JUnit and Jakarta Commons IO. DbUnit is available from DbUnit web site, http://www.dbunit.org. JUnit is available from http://www.junit.org.  commons-io-1.3.2.jar can be found from  http://mirrors.kahuki.com/apache/commons/io/binaries/commons-io-1.3.2-bin.zip. Now these jar files are required to be added in the referenced library of your testing directory.

DbUnit test Life Cycle

DbUnit framework follows some steps in its life cycle :

1.  Removing the old data left in the database from previous tests.


2.  Loading some data from XML file into the database.


3.  Running the test.

DatabaseTestCase class provides two methods setUp() and TearDown() which internally call getSetUpOperation() and getTearDownOperation() methods respectively. setUp() method provides the setup operation DatabaseOperation.CLEAN_INSERT or DatabaseOperation.REFRESH. DatabaseOperation.CLEAN_INSERT operation is the combination of two operations DELETE_ALL and INSERT. So data defined in the XML file is loaded in the database by this operation. First two steps of the life cycle are executed when executing the setUp() method before running the test. These steps allow you not to waste time in writing code to restore state in the database. DatabaseOperation.REFRESH updates the desired database with the data found in the XML file. The getTearDownOperation() performs a NONE operation which does nothing.

protected void setUp() throws Exception{   super.setUp();   executeOperation(getSetUpOperation());   } protected void tearDown() throws Exception{    super.tearDown();    executeOperation(getTearDownOperation());   } protected DatabaseOperation getSetUpOperation() throws Exception{   return DatabaseOperation.CLEAN_INSERT;   } protected DatabaseOperation getTearDownOperation() throws Exception{   return DatabaseOperation.NONE; }

DbUnit can work with default behavior, however, you can override the methods according to the s requirement.

Getting Started

We have used MySQL for database purpose. Now follow the steps below:
Create a table "login" in the database "hrapptest"  in MySQL like below :
login Table in database:

Field Type Collation Null Key Default ------------ ----------- ----------------- ------ ------ ------- id bigint(20) (NULL) NO PRI empcode varchar(15) latin1_swedish_ci YES (NULL) loginname varchar(30) latin1_swedish_ci NO password varchar(30) latin1_swedish_ci NO loginenabled varchar(1) latin1_swedish_ci NO

 
Create XML file (for example, "input.xml") representing the database tables and the data within it. Put a data set in this file like below. In this file "login" is the table name in the database and "id", "empcode" etc are the columns in the table. Put values for the fields in this file.
input.xml :

<?xml version='1.0' encoding='UTF-8'?>

<dataset>

<!--Login Table -->

<login id="1" empcode="E005" loginname="chandan" 

   password="chandan" loginenabled="y"/>

<login id="2" empcode="E006" loginname="deepak" 

    password="deepak" loginenabled="n"/>

</dataset>

DbUnit framework provides an abstract class named "DatabaseTestCase" which is a sub class of JUnit's "TestCase" class. So instead of creating a subclass of TestCase class we need to extend DatabaseTestCase class. This class provides two abstract methods "getConnection()" and "getDataSet()".

IDatabaseConnection getConnection() throws Exception

protected IDataSet getDataSet() throws Exception.

 Because of its being an abstract class we need to implement these two methods:
TestDbUnit.java :

........................... ........................... // Provide a connection to the database protected IDatabaseConnection getConnection() throws Exception{ Class driverClass = Class.forName("com.mysql.jdbc.Driver"); Connection jdbcConnection = DriverManager.getConnection("jdbc:mysql://localhost: 3306/hrapptest", "root", "root"); return new DatabaseConnection(jdbcConnection); } // Load the data which will be inserted for the test protected IDataSet getDataSet() throws Exception{ loadedDataSet = new FlatXmlDataSet(this.getClass().getClassLoader(). getResourceAsStream("input.xml")); return loadedDataSet; } ............................ ............................

getConnection() method returns IDatabaseConnection object that represents database connection created using DriverManager class. In the above code, IDatabaseConnection represents MySQL database where hrapptest is the name of database where username and password both are "deepak".
getDataSet() method uses the FlatXmlDataSet class to load "input.xml" file and return this loaded data set as an object implementing IDataSet interface. IDataSet provides many useful methods to return data sets.
 
Writing Test :
Now, write test to check that the data has been loaded in TestDbUnit.java file:

............................ ............................ public void testCheckLoginDataLoaded() throws Exception{ assertNotNull(loadedDataSet); int rowCount = loadedDataSet.getTable(TABLE_LOGIN).getRowCount(); assertEquals(2, rowCount); } ............................ ............................

Combining all of the above functionalities into one TestDbUnit.java file, you will find it as follows :
TestDbUnit.java :

package test; import java.sql.Connection; import java.sql.DriverManager; import org.dbunit.DatabaseTestCase; import org.dbunit.database.DatabaseConnection; import org.dbunit.database.IDatabaseConnection; import org.dbunit.dataset.IDataSet; import org.dbunit.dataset.xml.FlatXmlDataSet; public class TestDbUnit extends DatabaseTestCase{ public static final String TABLE_LOGIN = "login"; private FlatXmlDataSet loadedDataSet; // Provide a connection to the database protected IDatabaseConnection getConnection() throws Exception{ Class.forName("com.mysql.jdbc.Driver"); Connection jdbcConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/hrapptest", "root", "root"); return new DatabaseConnection(jdbcConnection); } // Load the data which will be inserted for the test protected IDataSet getDataSet() throws Exception{ loadedDataSet = new FlatXmlDataSet(this.getClass().getClassLoader() .getResourceAsStream("input.xml")); return loadedDataSet; } // Check that the data has been loaded. public void testCheckLoginDataLoaded() throws Exception{ assertNotNull(loadedDataSet); int rowCount = loadedDataSet.getTable(TABLE_LOGIN).getRowCount(); assertEquals(2, rowCount); } }

Running Test :
Now, in Eclipse, go to Run->Run As and click "JUnit Test" to run tests. If testing is successful then a green strip appears at the left of the eclipse window. If any of the test fails then it turns into a red strip indicating failure of any test.