Using DBUnit from Ant

It’s a common usecase that using DBUnit from JUnit testcases but sometimes I need to use DBUnit in a standalone application manner to achive tasks such as exporting the data from a database to a XML file for creating test data from an existing table. In such case, I feel using DBUnit Ant task is better rather than launching it from Maven plugin or write a standalone Java application uses DBUnit API. in this entry, I introduce you a complete working example of an Ant script that uses DBUnit Ant task.

Environment

dbunit-2.5.1.jar (Not available on the download page on SourceForge. Obtain it from Maven Repository instead)
- With dependencies: slf4j-api-1.7.12.jar, slf4j-jdk14-1.7.12.jar
postgresql-9.4-1203.jdbc42.jar
Apache Ant™ version 1.9.6
PostgreSQL 9.3.5
Oracle JDK8u40

Complete JAR file list in my ~/.ant/lib:

$ ls -l ~/.ant/lib
total 1236
-rw-rw-r--. 1 kyle kyle 560755 Sep 23 10:56 dbunit-2.5.1.jar
-rw-rw-r--. 1 kyle kyle 660126 Sep 23 10:54 postgresql-9.4-1203.jdbc42.jar
-rw-rw-r--. 1 kyle kyle  32127 Sep 23 11:15 slf4j-api-1.7.12.jar
-rw-rw-r--. 1 kyle kyle   7892 Sep 23 11:16 slf4j-jdk14-1.7.12.jar
$

Exporting example

build.xml

This script exports the result of a SQL to a flat XML file. The format can be specified in dbunit.format property that has set to flat as default. You can add additional query or table elements inside the dbunit element to include multiple dataset in single XML file. JDBC connection information such as credentials or URL is expected to be stored as a separate file named dbunit.properties that is placed under the same directory to this file.

<project name="dbunit-export" basedir=".">
    <taskdef name="dbunit" classname="org.dbunit.ant.DbUnitTask"/>

    <loadproperties srcFile="dbunit.properties"/>

    <fail unless="dbunit.driver"/>
    <fail unless="dbunit.url"/>
    <fail unless="dbunit.userid"/>
    <fail unless="dbunit.password"/>
    <fail unless="dbunit.datatypeFactory"/>

    <property name="dbunit.format" value="flat"/> <!-- Possible values are "flat", "xml", "csv", "dtd", "xls". Defaults to "flat" -->

    <target name="export">
        <fail unless="dbunit.sql"/>
        <fail unless="dbunit.dest"/>
        <fail unless="dbunit.format"/>
        <fail unless="dbunit.query"/>

        <dbunit driver="${dbunit.driver}" url="${dbunit.url}" userid="${dbunit.userid}" password="${dbunit.password}">
            <dbconfig>
                <property name="datatypeFactory" value="${dbunit.datatypeFactory}" />
            </dbconfig>
            <export dest="${dbunit.dest}">
                <query name="${dbunit.query}" sql="${dbunit.sql}"/>
            </export>
        </dbunit>
    </target>
</project>

dbunit.properties

Set each properties to suit your environment. See this page to check what datatypeFactory are available.

dbunit.driver=org.postgresql.Driver
dbunit.url=jdbc:postgresql://localhost:5432/mydb
dbunit.userid=someone
dbunit.password=somepass
dbunit.datatypeFactory=org.dbunit.ext.postgresql.PostgresqlDataTypeFactory

How to run

The following command exports a XML file from a SQL.

$ ant export "-Ddbunit.sql=select * from job_instance where jobinstanceid >= 399341 order by jobinstanceid" -Ddbunit.query=job_instance -Ddbunit.dest=job_instance.xml
Buildfile: /home/kyle/dbunit-ant-example/build.xml

export:
   [dbunit] Executing export:
   [dbunit]       in format: flat to datafile: /home/kyle/dbunit-ant-example/job_instance.xml
Successfully wrote file '/home/kyle/dbunit-ant-example/job_instance.xml'

BUILD SUCCESSFUL
Total time: 0 seconds

This produces a flat XML dataset named job_instance.xml something like:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <job_instance jobinstanceid="399341" jobname="somejob1" applicationname="someapp"/>
  <job_instance jobinstanceid="399342" jobname="somejob2" applicationname="someapp"/>
  <job_instance jobinstanceid="399343" jobname="somejob3" applicationname="someapp"/>
...
  <job_instance jobinstanceid="400004" jobname="somejob4" applicationname="someapp"/>
</dataset>

Exporting / importing an entire database

<project name="mydbunit" basedir=".">
    <taskdef name="dbunit" classname="org.dbunit.ant.DbUnitTask"/>

    <loadproperties srcFile="dbunit.properties"/>

    <fail unless="dbunit.driver"/>
    <fail unless="dbunit.url"/>
    <fail unless="dbunit.userid"/>
    <fail unless="dbunit.password"/>
    <fail unless="dbunit.datatypeFactory"/>

    <property name="dbunit.format" value="flat"/> <!-- Possible values are "flat", "xml", "csv", "dtd", "xls". Defaults to "flat" -->

    <target name="export">
        <fail unless="dbunit.dest"/>
        <fail unless="dbunit.format"/>

        <dbunit driver="${dbunit.driver}" url="${dbunit.url}" userid="${dbunit.userid}" password="${dbunit.password}">
            <dbconfig>
                <property name="datatypeFactory" value="${dbunit.datatypeFactory}" />
                <!-- <property name="escapePattern" value="`?`" /> -->
            </dbconfig>
            <export dest="${dbunit.dest}" ordered="true"/>
        </dbunit>
    </target>

    <target name="import">
        <fail unless="dbunit.src"/>
        <fail unless="dbunit.format"/>

        <dbunit driver="${dbunit.driver}" url="${dbunit.url}" userid="${dbunit.userid}" password="${dbunit.password}">
            <dbconfig>
                <property name="datatypeFactory" value="${dbunit.datatypeFactory}" />
                <!-- <property name="escapePattern" value="`?`" /> -->
            </dbconfig>
            <operation type="CLEAN_INSERT" src="${dbunit.src}" ordered="true"/>
        </dbunit>
    </target>

</project>

To export:

$ ant export -Ddbunit.dest=mydb.xml

To import:

$ ant import -Ddbunit.src=mydb.xml

Note that you need commons-collections3 jar file in your classpath.

Other tasks

There are more operations that can be achieved through Ant tasks available such as:

Data comparison
INSERT or UPDATE based on the file

References

http://dbunit.sourceforge.net/anttask.html

Kohei Nozaki's blog