Exploring DBUnit- Part-1

DBUnit is a handy package for unit testing database driven applications. It provides a wide and rich sets of features for unit testing the data layer operations. Some of the widely used features are:

• Export DB data for creating an XML Dataset


• CRUD operations


• Table creation using column filters


• Dataset creation using table filters


• Table comparision


• Dataset comparision

Creating a dataset from DB data:
DBUnit is an excellent tool for creating xml datasets which would inturn act as input dataset for test cases. Creating an input dataset xml manually is a time consuming task. This feature of dbunit can be used to create dataset xml from some sample database data.
The code to generate the dataset xml goes first:

private static void generateDataSet() { try { String outputFile = "OutputDataXML.xml" ; DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@host:port:DATABASE", "username", "password"); IDatabaseConnection connection = new DatabaseConnection(conn); File dir = new File("resource"); // Dataset for specific sets of tables. Tables specified by user QueryDataSet dataSet = new QueryDataSet(connection); dataSet.addTable("EMPLOYEE", "SELECT * FROM EMPLOYEE"); dataSet.addTable("DEPARTMENT", "SELECT * FROM DEPARTMENT WHERE EMP_ID=1"); // Dataset creation for parent and all its child tables String[] dependentTableNames = TablesDependencyHelper.getAllDependentTables(connection, "parentTableName"); IDataSet dependentDataset = connection.createDataSet(dependentTableNames); // Writes dataset to an xml file FlatXmlDataSet.write(dataSet, new FileOutputStream(dir + "/" + outputFile)); } catch (Exception e) { e.printStackTrace(); } }

1. First step is to establish database connection. I have used Driver manager to create the DB connection. Then created a DBUnit specific connection passing this connection to one of the api methods of DBUnit IDatabaseConnection connection = new DatabaseConnection(conn) ;


2. We can create various types of datasets. First I have highlighted how to create a dataset using user provided tables and queries. This type of dataset creation mechanism gives greater control to the developer as he can choose the tables and the corresponding datas that will go into these tables.
   QueryDataSet dataSet = new QueryDataSet(connection);
   This creates a QueryDataset to which tables and its corresponding queries are added.


3. If a user needs to create a Dataset with a parent table and all its corresponding child table, he can create the same by passing all the tables to createDataSet method. The below code shows how to collect all child tables of a given parent table and then create the dataset.

String[] depTableNames = TablesDependencyHelper.getAllDependentTables(connection, parentTable);
IDataSet depDataset = connection.createDataSet(depTableNames);


4. After the creation of the Dataset, it then can be saved into an xml file using the code:
   
   FlatXmlDataSet.write(depDataset, new FileOutputStream(dir + "/"+outputFile));

The xml dataset can then be used as an input dataset to various testcases.

DBUbit Key Points:

• If your application uses Oracle database, your tables may contain Oracle specific datatypes which dbUnit may not recognize. Running testcases using DBUnit with tables having Oracle or vendor specific data-types may throw exceptions. To overcome this problem use specific adaptors as shown below.
  



JdbcDatabaseTester tester = new PropertiesBasedJdbcDatabaseTester(); IDatabaseConnection connection = tester.getConnection(); DatabaseConfig configuration = connection.getConfig(); configuration.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new OracleDataTypeFactory());


After creating the DBUnit connection, the database configuration instance is retrieved and appropriate vendor specific DataTypeFactory is set. For example incase of oracle the DataTypeFactory is set as:

configuration.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new OracleDataTypeFactory());

• Delete operation on a table with no primarykey will throw exception in DBUnit, if DELETE is used.

DatabaseOperation.DELETE.execute(connection, dataset);
To avoid this, use DELETE_ALL instead of DELETE.

... Rest of DBUnit will continue on part-2