Terse Systems

Integration tests are the rule when it comes to testing. It is far more common to run a script to drop and recreate the database, set up some initial data and then manually work through the test case than it is to run a unit test.

So I’ve been playing with DBUnit, trying to get it to work with both Oracle 10g and HSQL. This is what I’ve found out about DBUnit 2.2, especially working with Oracle 10g.

Create a utility class to manage DBUnit operations

Many of the examples given in the DBUnit assume that you’ll be extending the DBTestCase class. You don’t need to do this, and I think it’s a move that will limit your options. Consider that there are only so many things that you can do with DBUnit, and look at writing a utility class that will manage the low level interaction for you, such as this:

public void insertDataSet(String schema, IDataSet dataSet) throws DatabaseUnitException, SQLException, IOException
{


LOGGER.info("inserting dataset: " + dataSet);

DataSource dataSource = getDataSource();
// Using spring-mock here as well, although you can do this directly
Connection connection = DataSourceUtils.getConnection(dataSource);
IDatabaseConnection dbconn = new DatabaseConnection(connection, schema);
try
{
    DatabaseConfig config = dbconn.getConfig();

    // Define the schema using a streaming configuration.
    config.setProperty(DatabaseConfig.PROPERTY_RESULTSET_TABLE_FACTORY, new ForwardOnlyResultSetTableFactory());

    DatabaseOperation.INSERT.execute(dbconn, dataSet);
} finally
{
    dbconn.close();
}


}

or use the IDatabaseTester interface.

Consider using P6Spy

In as much as DBUnit works, it runs JDBC statements. If you’re having problems with DBUnit, P6Spy will show you what JDBC statements are actually being run by DBUnit. This has helped me more than a few times when I’ve forgotten a step.

Purging Recycle Bin

DBUnit will not pick up on “purged” tables in Oracle, and will give you errors like this:

org.dbunit.database.AmbiguousTableNameException: BIN$KWdKkk3jYEvgQAB/AQAa8A==$0

Truncating tables

Oracle does not allow you to truncate tables that have a foreign key constraint. Ever. Even if there is no data in either of the tables. You will get this error:

"ORA-02266: unique/primary keys in table referenced by enabled foreign key"

select constraint_name from user_cons_columns where table_name = 'example_table';

alter table foo disable constraint constraint_name;

Always Specify the Schema

Oracle likes to have the schema defined. If you don’t have the schema defined, then DBUnit will get very confused. You can specify explicit schema names using a property, but then you can’t share XML data sets very easily.

Be Careful with CLOBs and LONGs

DBUnit takes some shortcuts when working with Oracle CLOBs. It assumes that you’re not using a DB Proxy, and it assumes it knows what Oracle class is valid. This assumption will fail if you are using a database proxy such as C3P0. Filed bug 1637073, but I do not know of a non-intrusive workaround at this point.

DBUnit also does not interact well with Oracle LONG objects. The bug is here and there is a workaround in the bug comments.

Use the right tool for the job

As much as DBUnit is useful for importing data, it’s still written for ease of use and generality than it is raw speed. You can use ForwardOnlyResultSetTableFactory to speed it up, but ultimately using the 10g data pump facilities is going to be faster than using DBUnit by an order of magnitude. If the data pump facilities don’t do it, you can use external tables and transportable tablespaces to set up a large database even faster.