JUnit, DBUnit and Oracle

As previously blogged, DBUnit is a powerful addition to your unit test armoury. Having used it with Oracle, there are a few nuances which are worth writing about and therefore remembering!

When creating a connection to an Oracle database, DBUnit will populate a Map of table names. By default these will not be prefixed with the schema name. As a result, you can get duplicate table names. An example of an exception raised for the duplicate table WWV_FLOW_DUAL100 is shown below:

org.dbunit.database.AmbiguousTableNameException: WWV_FLOW_DUAL100 at org.dbunit.dataset.OrderedTableNameMap.add(OrderedTableNameMap.java:198)
 at org.dbunit.database.DatabaseDataSet.initialize(DatabaseDataSet.java:231)
 at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:281)
 at org.dbunit.operation.AbstractOperation.getOperationMetaData(AbstractOperation.java:80)
 at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:140)

To resolve this, set the database config property FEATURE_QUALIFIED_TABLE_NAMES to be true. This will make sure all tables in the Map are unique. As a consequence of this, the table names in the XML data file will need to be prefixed with the schema name.

Another useful database config property is PROPERTY_DATATYPE_FACTORY. In the XML data file, if there are dates with the time element set then the time element will be ignored unless the database config property PROPERTY_DATATYPE_FACTORY is set to new Oracle10DataTypeFactory() (or the equivalent data factory for the version of Oracle being used.)

An example of setting these values in a @BeforeClass annotated JUnit method is shown below:

@BeforeClass
public static void loadDataset() throws Exception {

    // database connection
    ResourceCache resourceCache = ResourceCache.getInstance();
    String driverClassString = resourceCache.getProperty("datasource.driver.class.name");
    String databaseURL = resourceCache.getProperty("datasource.url");
    String username = resourceCache.getProperty("test.datasource.username");
    String password = resourceCache.getProperty("test.datasource.password");

    Class driverClass = Class.forName(driverClassString);
    Connection jdbcConnection = DriverManager.getConnection(databaseURL, username, password);              
    connection = new DatabaseConnection(jdbcConnection);
    DatabaseConfig config = connection.getConfig();
    config.setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);
    config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new Oracle10DataTypeFactory());         
        
    FlatXmlDataSetBuilder flatXmlDataSetBuilder = new FlatXmlDataSetBuilder();
    flatXmlDataSetBuilder.setColumnSensing(true);
    dataset = flatXmlDataSetBuilder.build(Thread.currentThread()
        .getContextClassLoader()
        .getResourceAsStream("Test-dataset.xml"));
 }