DbUnit – Ant Integration

Ant Integration

Installation

The steps required to add the DbUnit task to your system are:

1. Add the DbUnit jar to Ant's classpath. You may alternatively use the 'classpath' attribute of <taskdef>.

2. Add a <taskdef> element to your build script as follows:

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

3. Use the task in the rest of the buildfile.

Usage

Executes either a single transaction, or a group of transactions, under the DbUnit database testing framework.

Parameters

Attribute	Description	Required
driver	Class name of the jdbc driver. Must be available on the classpath (either system classpath or nested classpath)	Yes
url	Database connection url	Yes
userid	Database username	Yes
password	Database password	Yes
schema	Database schema	No
classpath	Classpath used to load driver.	No (use system classpath)
useQualifiedTableNames	Set the qualified table namesfeature. Defaults to false. @deprecated since 2.4. Use nested dbconfig element instead.	No
supportBatchStatement	Set the batched statementfeature. Defaults to false. @deprecated since 2.4. Use nested dbconfig element instead.	No
datatypeWarning	Set the data type warningfeature. Defaults to true. @deprecated since 2.4. Use nested dbconfig element instead.	No
escapePattern	Set the escape patternproperty. @deprecated since 2.4. Use nested dbconfig element instead.	No
datatypeFactory	Set the datatype factoryproperty. @deprecated since 2.4. Use nested dbconfig element instead.	No
skipOracleRecycleBinTables	Set the skipOracleRecycleBinTablesfeature. @deprecated since 2.4. Use nested dbconfig element instead.	No
batchSize	Set the batchSizeproperty. @deprecated since 2.4. Use nested dbconfig element instead.	No
fetchSize	Set the fetchSizeproperty. @deprecated since 2.4. Use nested dbconfig element instead.	No
allowEmptyFields	Set the allowEmptyFieldsfeature. @deprecated since 2.4. Use nested dbconfig element instead.	No

Parameters specified as nested elements

classpath

DbUnit's classpath attribute is a PATH like structure and can also be set via a nested classpath element. It is used to load the JDBC classes.

dbconfig

DbUnit's configuration parameters to set any of the properties for the database connection. It supports all properties and features from the DatabaseConfig java class, having the last path component of the property/feature URL as name. A more detailed example is shown at dbconfig-example.

The nested <property> element and the nested <feature> element looks as follows:

Attribute	Description	Required
name	The name of the property/feature which must be the last path component of the property/feature URL (see properties for more). Example: for the property "http://www.dbunit.org/properties/tableType" the name is "tableType".	Yes
value	The value of the property/feature as string. Since feature are boolean values the string can take one of the values "true" or "false".	Yes

operation

Use nested <query>elements to exportdata according to a sql statement.

Attribute	Description	Required
type	Type of Database operation to perform. Supported types are UPDATE, INSERT, DELETE, DELETE_ALL, REFRESH, CLEAN_INSERT, MSSQL_INSERT, MSSQL_REFRESH, MSSQL_CLEAN_INSERT.	Yes
src	The source file upon which the operation is to be performed	Must specify either src or fileset
fileset	The source files upon which the operation is to be performed. The source files make up a composite dataset.	Must specify either src or fileset
format	Format type of supplied source file(s). Possible values are "flat", "xml", "csv", "dtd", "xls", "yml". Defaults to "flat"	No
combine	Boolean indicating if tables in a composite dataset having the same name should be merged into one table. Possible values are "true" or "false". Defaults to "false".	No
transaction	Boolean indicating if this operation should be wrapped in a transaction, ensuring that the entire operation completes or is rolled back. This may also dramatically improve performance of large operations. Possible values are "true" or "false". Defaults to "false".	No
ordered	If set to "true" the tables of the exported dataset are ordered using a database key. Therefore dbunit uses the FilteredDataSet combined with the DatabaseSequenceFilter. Defaults to "false"	No
nullToken	A String used to replace all occurrences of this String in a dataset with <null>. A common value for this is "[NULL]". Defaults to null which means that no replacement occurrs.	No

export

Use nested <export> operation elements to export the database to the supplied filename. The default operation is to <export> the entire database to the destination filename with the supplied dataset type. You can specify individual tables or queries to <export> by nesting them under the <export> step.

Attribute	Description	Required
dest	The xml destination filename	Yes
format	Format type of supplied destination file. Possible values are "flat", "xml" or "dtd". Defaults to "flat"	No
doctype	If set and format is "flat", add DOCTYPE declaration referencing specified DTD to exported dataset. The DTD path can be absolute or relative.	No
ordered	If set to "true" the tables of the exported dataset are ordered using a database key. Therefore dbunit uses the FilteredDataSet combined with the DatabaseSequenceFilter. Defaults to "false"	No
encoding	Desired encoding for XML-Output. Possible values are valid XML-Endings like "UTF-8" or "ISO-8859-1". Defaults to "UTF-8"	No

Parameters specified as nested elements

table

Use nested <table> elements to export specific tables.

Attribute	Description	Required
name	Name of the database table to export.	Yes

query

Use nested <query>elements to export data according to a sql statement.

Attribute	Description	Required
name	Name to reference the sql statement.	Yes
sql	The sql to execute. You can use either SELECT * from Mytable or SELECT col1, col4 from MyTable	Yes

queryset

Use a <queryset> element to establish a group of <query> elements which can be referenced throughout the Ant project. Also use nested <queryset>s to include more than one <query> element for a table. QuerySets are joined together using a CompositeDataSet.

Attribute	Description	Required
id	Unique identifier for this element instance which can be referred to using 'refid'.	No
refid	The reference 'id' of the queryset to refer to.	No

Parameters specified as nested elements

filterset	Tokens can be specified when a <queryset> reference is defined. Nested filtersets specify values to replace when the <queryset> is referenced. Tokens will be replaced within values of <query> sql attributes. (see queryset example)

In order to use <queryset> elements with references, you must add the DbUnit Ant tasks to your project in one of the following ways, to ensure all elements are loaded with the same class loader.

If you are using Ant version > 1.6.0

                  <typedef resource="org/dbunit/ant/antlib.xml"/>

If you are using an Ant version < 1.6

                  
                    <taskdef name="dbunit" classname="org.dbunit.ant.DbUnitTask" loaderref="dbunit"/>
                    <typedef name="queryset" classname="org.dbunit.ant.QuerySet" loaderref="dbunit"/>

compare

Use nested <compare> elements to validate the content of the database against the specified dataset file.

Attribute	Description	Required
src	The xml source upon which the comparison is to be performed	Yes
format	Format type of supplied source file. Possible values are "flat" or "xml". Defaults to "flat"	No
sort	Sorts tables prior comparison. Defaults to "false".	No

Parameters specified as nested elements

table

Use nested <table> elements to compare specific tables.

Attribute	Description	Required
name	Name of the database table to compare.	Yes

query

Use nested <query>elements to compare data according to a sql statement.

Attribute	Description	Required
name	Name of the database table to compare.	Yes
sql	The sql to execute. You can use either SELECT * from Mytable or SELECT col1, col4 from MyTable	Yes

Examples

Update operation with specified JDBC driver jar

      
<dbunit driver="com.vendor.jdbc.Driver"
    url="jdbc:vendor:mydatabase"
    userid="user"
    password="password">
    <classpath>
        <pathelement location="/some/jdbc.jar"/>
    </classpath>
    <operation type="UPDATE" src="updateFile.xml"/>
</dbunit>

Insert and update operations

      
<dbunit driver="com.vendor.jdbc.Driver"
        url="jdbc:vendor:mydatabase"
        userid="user"
        password="password">
    <operation type="INSERT" src="insertFile.xml"/>
    <operation type="UPDATE" src="updateFile.xml"/>
</dbunit>

Database data export to XML

      
<dbunit driver="com.vendor.jdbc.Driver"
        url="jdbc:vendor:mydatabase"
        userid="user"
        password="password">
    <export dest="export.xml"/>
</dbunit>

Database structure export to DTD

      
<dbunit driver="com.vendor.jdbc.Driver"
        url="jdbc:vendor:mydatabase"
        userid="user"
        password="password">
    <export dest="export.dtd" format="dtd"/>
</dbunit>

Partial database data export

Export two tables: FOO, resulting from specified query and BAR entire content

      
<dbunit driver="com.vendor.jdbc.Driver"
        url="jdbc:vendor:mydatabase"
        userid="user"
        password="password">
    <export dest="partial.xml">
        <query name="FOO" sql="SELECT COL1, COL2 FROM FOO WHERE COL1=4"/>
        <table name="BAR"/>
    </export>
</dbunit>

Database data comparison

      
<dbunit driver="com.vendor.jdbc.Driver"
        url="jdbc:vendor:mydatabase"
        userid="user"
        password="password">
    <compare src="expectedData.xml"/>
</dbunit>

Partial database data comparison

      
<dbunit driver="com.vendor.jdbc.Driver"
        url="jdbc:vendor:mydatabase"
        userid="user"
        password="password">
    <compare src="expectedData.xml">
        <query name="FOO" sql="SELECT COL1, COL2 FROM FOO WHERE COL1=4"/>
        <table name="BAR"/>
    </compare>
</dbunit>

Partial CSV export using queryset reference.

Export employees with specified userNames, as well as all managers.

      
<!-- ======== Define the reference. Note that before ant 1.6
              this would have to be within a target. ====== -->
<queryset id="employees">
   <query name="EMPLOYEE"
     sql="SELECT * FROM EMPLOYEE WHERE EMP_ID IN (@subQuery@)"/>
   <query name="EMP_ADDRESS" sql="
       SELECT B.* FROM EMPLOYEE A, EMP_ADDRESS B
       WHERE A.EMP_ID IN (@subQuery@)
       AND B.EMP_ID = A.EMP_ID"/>
</queryset>
.....
<!-- ========= Use the reference ====================== -->

<dbunit driver="${jdbcDriver}"
        url="${jdbcURL}" 
        userid="${jdbcUser}" 
        password="${jdbcPassword}">
  <export dest="someDir" format="csv">
    <queryset refid="employees">
       <filterset>
         <filter token="subQuery" value="
           SELECT EMP_ID FROM EMPLOYEE WHERE USER_NAME IN('joe', 'schmo')"/>
       </filterset>
    </queryset>
    <queryset refid="employees">
       <filterset>
         <filter token="subQuery" value="
           SELECT EMP_ID FROM EMPLOYEE WHERE IS_MANAGER = 1"/>
       </filterset>
    </queryset>
  </export>
</dbunit>

DatabaseConfig example with nested configuration

      
<dbunit driver="com.vendor.jdbc.Driver"
        url="jdbc:vendor:mydatabase"
        userid="user"
        password="password">
    <dbconfig>
        <property name="datatypeFactory" value="org.dbunit.ext.oracle.OracleDataTypeFactory" />
        <!-- Is internally split to a string array using the comma as separator -->
        <property name="tableType" value="TABLE,SYNONYM" />
        <feature name="batchedStatements" value="true" />
    </dbconfig>

    ...
</dbunit>