DBUnit:Oracle – Fix issue with inserting DATE Field

Recently in our project, we came across a situation where we are supposed to assert a value of a Date column in Oracle which includes the both date and time details to it. We are using DBUnit to generate the dataset and assert the result with the values inserted through this dataset.

Unfortunately, whenever we tried to insert a value for a java.util.Date type field like ’03-02-2009 09:30:00.0′ through the dataset, we found that the time portion is truncated and the value taken and inserted into the database was always ’03-02-2009 12:00:00′. This bought us a situation where we were unable to assert the time portion of the field.

We finally managed to find the solution.

After some spiking around, we found that the reason for DBUnit ignoring the time details is because of we using DefaultDataTypeFactory instead of  OracleDataTypeFactory (for the latest versions of DBUnit, Oracle1oDataTypeFactory can be used for Oracle 10g).

Once we set the PROPERTY_DATATYPE_FACTORY to OracleDataTypeFactory, DBUnit was able to insert the DATE field values without ignoring the time details. Basically, the PROPERTY_DATATYPE_FACTORY property has to be set on the DBConnection that you use for inserting DBUnit’s dataset something as follows:
Assuming you obtained the DBConnection and assigned it to the variable ‘dbConnection’ add the following line for setting the property:

new OracleDataTypeFactory());

Note: DBUnit gives us choice of choosing the appropriate DataTypeFactory based on the database that we use. In our case, we were using Oracle and hence the PROPERTY_DATATYPE_FACTORY has been mapped to OracleDataTypeFactory.

The following table shows the list of DataTypeFactories provided by DBUnit for different databases:

  • Oracle     –   org.dbunit.ext.oracle.Oracle10DataTypeFactory, org.dbunit.ext.oracle.OracleDataTypeFactory
  • MySql     –   org.dbunit.ext.mysql.MySqlDataTypeFactory
  • MsSql      –   org.dbunit.ext.mssql.MsSqlDataTypeFactory
  • Hsqldb   –    org.dbunit.ext.hsqldb.HsqldbDataTypeFactory
  • H2            –    org.dbunit.ext.h2.H2DataTypeFactory
  • Db2         –     org.dbunit.ext.db2.Db2DataTypeFactory
  • Others    –     org.dbunit.dataset.datatype.DefaultDataTypeFactory

What we learnt: Whenever you find some values from a dataset not being inserted into the database as expected, look for if you are using the proper DataTypeFactory that matches your database.

Hope you found this tip useful!


About this entry