How to populate an Oracle TIMESTAMP WITH ZONE field using Dbunit FlatXmlDataSet

Question

I am trying to setup data for a DB unit test by populating a table which has columns of type TIMESTAMP(6) WITH TIME ZONE as follows:

<timetable START_TIME="2015-03-01 10.00.00.000000000" END_TIME="2015-03-02 10.00.00.000000000"/>

But I keep getting the following exception when I run the test:

org.dbunit.dataset.NoSuchColumnException: TIMETABLE.START_TIME -  (Non-uppercase input column: START_TIME) in ColumnNameToIndexes cache map. Note that the map's column names are NOT case sensitive.
    at org.dbunit.dataset.AbstractTableMetaData.getColumnIndex(AbstractTableMetaData.java:117)
    at org.dbunit.operation.AbstractOperation.getOperationMetaData(AbstractOperation.java:89)
    at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:143)
    at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
    at com.github.springtestdbunit.DbUnitRunner.setupOrTeardown(DbUnitRunner.java:194)
    at com.github.springtestdbunit.DbUnitRunner.beforeTestMethod(DbUnitRunner.java:66)
    at com.github.springtestdbunit.DbUnitTestExecutionListener.beforeTestMethod(DbUnitTestExecutionListener.java:185)
    at org.springframework.test.context.TestContextManager.beforeTestMethod(TestContextManager.java:249)

I tried different formats for the timestamp field including the addition of the timezone suffix +XX:XX 2015-03-01 10.00.00.000000000 +00.00 to no avail. I also tried running the test with the VM argument -Duser.timezone=UTC but that didn't help either.

Does anyone know how this can be achieved?

EDIT 1

I noticed the following warnings in the console:

2016-05-31 14:54:23 WARN  SQLHelper:429 - TIMETABLE.START_TIME data type (-101, 'TIMESTAMP(6) WITH TIME ZONE') not recognized and will be ignored. See FAQ for more information.
2016-05-31 14:54:23 WARN  SQLHelper:429 - TIMETABLE.END_TIME data type (-101, 'TIMESTAMP(6) WITH TIME ZONE') not recognized and will be ignored. See FAQ for more information.

So it looks like Dbunit does not support TIMESTAMP WITH TIME ZONE data type and ignores it, hence the NoSuchColumnException exception

EDIT 2

Actually dbunit already supports TIMESTAMP data types through OracleDataTypeFactory class. The configuration would then look like:

<bean id="oracleDataTypeFactory" class="org.dbunit.ext.oracle.OracleDataTypeFactory"/>

<bean id="dbUnitDatabaseConfig" class="com.github.springtestdbunit.bean.DatabaseConfigBean">
        <property name="datatypeFactory" ref="oracleDataTypeFactory" />
</bean>

Unfortunately, after these config changes the data type problem was still there because dbunit DatabaseConfig.datatypeFactory property was re-set back by DbUnitTestExecutionListener to the default DefaultDataTypeFactory which doesn't support TIMESTAMP data types

Taoufik Mohdit · Accepted Answer · 2016-06-01T10:46:32.033

I managed to load TIMESTAMP data by removing Spring-dbunit specific annotations (@TestExecutionListeners and @DbUnitConfiguration). I still had to use @DirtiesContext(classMode = ClassMode.AFTER_EACH_TEST_METHOD). I then used the 'old' Dbunit way to setup and teardown data and to check data expectations. This is not as concise as Spring-dbunit annotations @DatabaseSetup, @DatabaseTearDown and @ExpectedDatabase, but it does work (See code snippets below). I also kept OracleDataTypeFactory in my test context; this is essential for the TIMESTAMP datatype to be recognised by Dbunit.

I think the issue I faced might be the manifestation of a bug in spring-dbunit Test Execution Listeners. I am particularly suspecting DbUnitTestExecutionListener

Setup:

@Before
    public void setUp() throws SQLException, IOException, DataSetException, DatabaseUnitException {    
        Resource resource = new ClassPathResource("path/to/data_setup.xml");
        FlatXmlDataSetBuilder builder = new FlatXmlDataSetBuilder();
        builder.setColumnSensing(true);
        FlatXmlDataSet dataSetup = builder.build(resource.getInputStream());
        DatabaseOperation.CLEAN_INSERT.execute(dbUnitDatabaseConnection, dataSetup);    
    }

Teardown:

@After
    public void tearDown() throws SQLException, IOException, DataSetException, DatabaseUnitException {
        Resource resource = new ClassPathResource("path/to/data_teardown.xml");
        FlatXmlDataSet dataTearDown = new FlatXmlDataSetBuilder().build(resource.getInputStream());
        DatabaseOperation.DELETE_ALL.execute(dbUnitDatabaseConnection, dataTearDown);
    }

Expectation (within test method):

QueryDataSet actualDataSet = new QueryDataSet(dbUnitDatabaseConnection);
actualDataSet.addTable("YOUR_TABLE", "<YOUR_SQL_QUERY>");
FlatXmlDataSet expectedDataSet = new FlatXmlDataSetBuilder().build(new ClassPathResource("path/to/data_expectation.xml").getInputStream());
Assertion.assertEquals(expectedDataSet, actualDataSet);

How to populate an Oracle TIMESTAMP WITH ZONE field using Dbunit FlatXmlDataSet

1 Answers1