DBUNIT : Extracting Test Data into XML file

Many times you need sample data to be used as the set up data for your test cases. An easy way to extract data from a database into an XML file is as follows
The code sample below is mentioned for Postgres database however can be used for any database.

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.xml.FlatXmlDataSet;

/**
* @author vhazrati
*
*/
public class TestDataExtractor {

public static void main(String[] args) throws ClassNotFoundException, SQLException, DataSetException, FileNotFoundException, IOException {
Class.forName("org.postgresql.Driver");

Connection jdbcConnection = DriverManager.getConnection("jdbc:postgresql://localhost/xtime", "username", "password");
IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);

QueryDataSet partialDataSet = new QueryDataSet(connection);

// Mention all the tables here for which you want data to be extracted
// take note of the order to prevent FK constraint violation when re-inserting
partialDataSet.addTable("app_user");
partialDataSet.addTable("role");
partialDataSet.addTable("user_role");
partialDataSet.addTable("project_assignment");
partialDataSet.addTable("timesheet_entry");

// XML file into which data needs to be extracted
FlatXmlDataSet.write(partialDataSet, new FileOutputStream("src/test/resources/test-dataset_temp.xml"));
System.out.println("Dataset written");
}
}

If you have keenly noticed the above code, you would have noticed that we have to add the table names for which we want the data to extracted into the XML. Now let us consider the scenario where you want to take the dump of the entire database [or selected tables filtered by a query].The code represented below would fetch all the data into an XML file where the table names match the criteria specified in the populateTableList method. The implementation below is specific to Oracle but of course you can use it with any database given that you are able to fetch a list of tablenames based on certain criteria.

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.xml.FlatXmlDataSet;

public class TestDataExtractor {

static private List tableList = new ArrayList();

public static void main(String[] args) throws ClassNotFoundException, SQLException, DataSetException, FileNotFoundException, IOException {

Class.forName("oracle.jdbc.OracleDriver");

Connection jdbcConnection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl10", "vdacore", "vdacore");
IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);

populateTableList(jdbcConnection);

QueryDataSet partialDataSet = new QueryDataSet(connection);
addTables(partialDataSet);

// XML file into which data needs to be extracted
FlatXmlDataSet.write(partialDataSet, new FileOutputStream("test-dataset_temp.xml"));
System.out.println("Dataset written");
}

private static void populateTableList(Connection connection) {
ResultSet rs = null;
Statement st = null;
try {
st = connection.createStatement();
rs = st.executeQuery("Select table_name from tabs where table_name like 'UKC%'");
while (rs.next()){
tableList.add(rs.getString("table_name"));
}
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
rs.close();
st.close();
} catch (SQLException e) {
e.printStackTrace();
}

}
}

static private void addTables(QueryDataSet dataSet) {
if (tableList == null) return;
for (Iterator k = tableList.iterator(); k.hasNext(); ) {
String table = (String) k.next();
dataSet.addTable(table);
}
}

}

---

Possibly related posts: (automatically generated)

• DBUNIT, HSQLDB, data type (16, ‘BOOLEAN’) not recognized and will be ig…
• Android - Just use Smack API for XMPP « Show me the code!
• Implementing Inheritance in database with JPA