$ 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 $
Using DBUnit from Ant
TweetPosted on Wednesday Sep 23, 2015 at 12:02PM in Technology
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:
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.xmlTo import:
$ ant import -Ddbunit.src=mydb.xmlNote 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
