Which relational database systems are supported?

See the publicly maintained list of compatible RDBMS at the DbUnit Wiki. Don't hesitate to contribute to this list, particularly for database not listed yet.

How to build DbUnit?

BuildingDbUnit detailed instruction.

How to see SQL statements issued by DbUnit using P6Spy?

P6Spy is a transparent JDBC proxy-driver specialized to log the statements performed against the actual driver you normally would use. Using p6spy would allow to log the SQL statements issued by DbUnit.

To install P6Spy, complete the following steps:

  1. Put the p6spy.jar file in your classpath.
  2. Move into a directory listed in your classpath. Unlike JAR files, you do not directly reference your property file in the classpath (e.g.: if you have the file c:\r\proj\ make sure you put c:\r\proj\conf in the classpath).
  3. Modify your application to use the P6Spy database driver i.e. com.p6spy.engine.spy.P6SpyDriver.
  4. Modify the realdriver line in the file to reflect the wrapped database driver. An example of a modified realdriver line follows: realdriver = oracle.jdbc.driver.OracleDriver
Installation is complete. When you run your application, a spy.log file is generated in the same directory from where you run the application. The log file contains a list of all of the database statements executed. You can change both the destination of spy.log and what it logs by editing the file.

How to learn more about known and fixed issues?

Look at ChangesSinceLastRelease and at Issue Tracking .

Using DbUnit

How to extract a flat XML dataset from my database?

The following sample demonstrates how you can export one or many tables from a database to an flat XML dataset file.

public class DatabaseExportSample
    public static void main(String[] args) throws Exception
        // database connection
        Class driverClass = Class.forName("org.hsqldb.jdbcDriver");
        Connection jdbcConnection = DriverManager.getConnection(
                "jdbc:hsqldb:sample", "sa", "");
        IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);

        // partial database export
        QueryDataSet partialDataSet = new QueryDataSet(connection);
        partialDataSet.addTable("FOO", "SELECT * FROM TABLE WHERE COL='VALUE'");
        FlatXmlDataSet.write(partialDataSet, new FileOutputStream("partial.xml"));

        // full database export
        IDataSet fullDataSet = connection.createDataSet();
        FlatXmlDataSet.write(fullDataSet, new FileOutputStream("full.xml"));

How to generate a DTD representing my database schema?

The following sample demonstrates how you can generate a flat xml dataset DTD from a database.

public class DatabaseExportSample
    public static void main(String[] args) throws Exception
        // database connection
        Class driverClass = Class.forName("org.hsqldb.jdbcDriver");
        Connection jdbcConnection = DriverManager.getConnection(
                "jdbc:hsqldb:sample", "sa", "");
        IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);

        // write DTD file
                new FileOutputStream("test.dtd"));
Can I work with multiple database schemas having tables with identical name?
Yes, see Why I get a "AmbiguousTableNameException"? .
Can I use DbUnit with IDENTITY or auto-increment columns?

Many RDBMS allow IDENTITY and auto-increment columns to be overwritten with client values implicitly. DbUnit can be used with these RDBMS natively. Some databases, like MS SQL Server and Sybase, need to explicitly activate client values writing. The way to activate this feature is vendor specific.

DbUni t provides this functionality for MS SQL Server with the IdentityInsertOperation class.

How to specify NULL values with flat XML dataset?

See FlatXmlDataSet documentation

Can I use DbUnit with database views?

Yes. By default DbUnit is configured to only recognize normal tables. Modify the table type property to work with other table types. For example, use {"TABLE", "VIEW"} for views.

Be aware that REFRESH, DELETE and UPDATE operations are not compatible with tables without primary keys. They are not usable with views without overriding primary keys detection . CLEAN_INSERT, INSERT and DELETE_ALL operations are compatible with views.

How to enable batched statement?
See batched statement feature.
What are the dates formats supported by DbUnit?

DbUnit use the JDBC escape formats for string representation.

Type Format
DATE yyyy-mm-dd
TIME hh:mm:ss
TIMESTAMP yyyy-mm-dd hh:mm:ss.fffffffff

How to replace the default data type factory?

You can replace the default DbUnit data type factory to get support for custom data type. DbUnit provides extended factories for some vendors, which are located in org.dbunit.ext subpackages.

Here is how to setup the Oracle factory:

    IDatabaseConnection connection = new DatabaseConnection(
            jdbcConnection, schema);
    DatabaseConfig config = connection.getConfig();
            new OracleDataTypeFactory());

Don't hesitate to submit your own implementation if you encounter types not currently supported by DbUnit.

How to perform streamed import and export?

Historically, DbUnit has memory consumption issues when working with very large dataset files. DbUnit 2.0 includes many improvements, like using SAX2 instead of the Electric XML parser and and streamed XML dataset writing, to overcome the memory consumption problems.

For compatibility reason, streamed export and import are not enabled by default. When working with large dataset, using this feature can make a huge difference.

Database Export:

Configure your DbUnit connection to use ForwardOnlyResultSetTable when exporting very large dataset. ForwardOnlyResultSetTable is a very efficient database table implemtation useful when random data access is not required. By default, DbUnit uses CachedResultSetTable which consume more memory but provides random data access.

Following sample shows how to configure your DbUnit connection to use ForwardOnlyResultSetTable:

    IDatabaseConnection connection = new DatabaseConnection(
            jdbcConnection, schema);
    DatabaseConfig config = connection.getConfig();

            new ForwardOnlyResultSetTableFactory());

Database Import:

Use the very efficient StreamingDataSet to load your XML dataset when working with forward only database operations like UPDATE, INSERT, REFRESH.

How to enable flat XML dataset validation?

Flat XML validation is disabled by default even if you are using a DTD. Following sample demonstrate how to load a flat XML dataset with DTD validation enabled:

    FlatXmlProducer producer = new FlatXmlProducer(
            new InputSource("dataset.xml"));
    IDataSet dataSet = new CachedDataSet(producer);

How to improve the performance of my DbUnit tests?

It is normal that testing with a real database is slower than testing with MockObjects . Here are few tricks that will help to speedup your DbUnit tests.

1. Reuse the same connection thorough your test suite

Creating a new DbUnit connection every time has a cost. The overhead is much more than just creating a new JDBC connection. DbUnit need to fetches tables' metadata to determine columns data types. This information is cached in the DbUnit connection. So this is highly recommended to reuse the same DbUnit connection thorough your test suite; more you have tables greater are the benefits.

2. Specify the database schema name

If your database server supports multiple schemas, like Oracle, you should always specify the schema name you want to use when creating the DbUnit connection. DbUnit can potentially fetch the metadata of all tables it have access to. This include tables from other schemas if you are using a god JDBC connection . So in this situation, specifying a schema name can dramatically improve DbUnit performance.

3. Test with little data

Unit testing require relatively little data. So try to keep your setup datasets as small as possible . There is no necessity to reset the entire database content at the beginning of every test. Try to use only the data you need for a particular test case.

4. Setup stale data once for the entire test suite

If most of your tests are using the same read-only data, you should consider initializing this data once for an entire test class or test suite.

5. Enable the batched statement feature

The batched statements feature is disabled by default because there are many JDBC drivers incompatible with it. This is recommended to enable this feature if your driver supports it. The performance gain may not be very significant when testing with small datasets .

How to automatically orders tables according their foreign keys?
DbUnit operations insert and update tables' rows in the same order they are found in your dataset and delete are done in reverse order. You must order your tables and rows appropriately in your datasets to prevent foreign keys constraint violation.

Since version 2.0, the DatabaseSequenceFilter can now be used to automatically determine the tables order using foreign/exported keys information.

The following sample demonstrate how to use this class to export a flat XML dataset:
IDatabaseConnection conn = new DatabaseConnection(jdbcConn);

ITableFilter filter = new DatabaseTableFilter(conn);
IDataSet dataset = new FilteredDataSet(filter, 

FlatXmlDataSet.write(dataset, new File(fileName));
How to add the DOCTYPE declaration when writing a flat XML dataset?

Use the setDocType() method of the FlatXmlWriter class like this:

    FlatXmlWriter datasetWriter = new FlatXmlWriter( 
            new FileOutputStream("dataset.xml")); 
This can also be done with the DbUnit Ant task .

How to exclude some table columns at runtime?

The FilteredTableMetaData class, introduced in DbUnit 2.1, can be used in combination with the IColumnFilter interface to decide the inclusion or exclusion of table columns at runtime.

    FilteredTableMetaData metaData = new FilteredTableMetaData(
        originalTable.getTableMetaData(), new MyColumnFilter());
    ITable filteredTable = new CompositeTable(metaData, originalTable);

You can use your own IColumnFilter implementation or use the DefaultColumnFilter class provided by DbUnit. DefaultColumnFilter supports wildcards. This class also offers some convenience methods, includedColumnsTable() and excludedColumnsTable(), to ease creation of column filtered table.

The following sample demonstrates the usage of DefaultColumnFilter to exclude all columns prefixed with "PK" or suffixed by "TIME".

    DefaultColumnFilter columnFilter = new DefaultColumnFilter();

    FilteredTableMetaData metaData = new FilteredTableMetaData(
            originalTable.getTableMetaData(), columnFilter);

Same than above but using the excludedColumnsTable() convenience method.

    ITable filteredTable = DefaultColumnFilter.excludedColumnsTable(
            originalTable, new String[]{"PK*", "*TIME"});

See also Ignoring some columns in comparison .

How to use InsertIdentityOperation with user defined types?

The IColumnFilter interface is now used by InsertIdentityOperation to detect identity columns. The default implementation assumes that type name of identity columns end with "identity". If you are using user defined types that does not follow this assumption you can now provide your own implementation via the MS SQL identity column filter property .

    IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
        new MyIndentityFilter());

How to customize primary keys detection?

The IColumnFilter interface can also be used to determine which columns are primary keys instead of using DatabaseMetaData.getPrimaryKeys(). This can be useful if your primary keys are not explicitly defined in your database model.

    IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
        new MyPrimaryKeyFilter());


Why I get a "AmbiguousTableNameException"?

This error occurs when no schema is specified and that DbUnit detect that it is getting columns information from multiple tables having the same name and located in different schemas.

You can solve this problem in three different ways:

  1. Provide the schema name when creating the database connection. Note that for Oracle you must specify the schema name in uppercase.
  2. Ensure that the connection is restricted to access only one schema.
  3. Enable the qualified table names feature.

Why I get a "Foreign keys constraint violation" error?
See How to automatically orders table according their foreign keys?
Why I get a "data type not recognized" warning?

By default, DbUnit only support standard JDBC data types. You will get this warning message if you are using vendor specific data types.

Read how to replace the default data type factory and how to disable this warning message .

Why I get a "Can't start a cloned connection" exception when I use InsertIdentityOperation?

If you are using the Microsoft driver (i.e. ), you'll need to use the SelectMethod=cursor parameter in the JDBC connection string (as outlined by this JDC thread ). Your database Url would look something like the following:


Why I get an "UnsatisfiedLinkError" with the DB2 driver?

DbUnit uses JDBC 2.0 features (batch updates). By default, DB2 in stalls the JDBC 1.0 driver. You have to install the JDBC 2.0 driver in order for DbUnit to work or you will get an UnsatisfiedLinkError from the DB2 JDBC 1.0 driver.

The steps for installing the DB2 JDBC 2.0 driver are covered in the DB2 documentation.