DBunit - Unable to typecast <1997/02/14> to TimeStamp

Question

I'm doing an integration testing with DBUnit (2.49) + Hibernate (4.1.3) following this tutorial.

• Production database : Oracle 10
• Test database : Hsqldb 2.3.3

---

Context

My data contains the current format of date : yyyy/MM/dd. However,according to DBUnit faq, DBUnit only supports this format yyyy-mm-dd hh:mm:ss.fffffffff, so I had to create a new format for TimeStamp.

How I tried to fix it

• I created a CustomTimeStampDataType based on this tutorial. I changed this part:

  String formats[] = {"yyyy-MM-dd HH:mm", "yyyy-MM-dd HH:mm a", "yyyy-MM-dd HH:mm:ss.fffffffff"};
  

into this one:

    String formats[] = {"yyyy/MM/dd"};

• I created a CustomeDataTypeFactory following the same tutorial. I only make it extend Oracle10DataTypeFactory rather than DefaultDatatTypeFactory.

• In HibernateDBUnitTestCase, I override setDatabaseConfig() with the following:

  @Override
  protected void setUpDatabaseConfig(DatabaseConfig config){
      config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new CustomDataTypeFactory());
    }
  

But I got new errors

I ran a unit test and got this error.

org.dbunit.dataset.datatype.TypeCastException: Unable to typecast value <1997/02/14> of type <java.lang.String> to TIMESTAMP
    at org.dbunit.dataset.datatype.TimestampDataType.typeCast(TimestampDataType.java:120)
    at org.dbunit.dataset.datatype.TimestampDataType.setSqlValue(TimestampDataType.java:176)
    at org.dbunit.database.statement.SimplePreparedStatement.addValue(SimplePreparedStatement.java:73)
    at org.dbunit.operation.RefreshOperation$RowOperation.execute(RefreshOperation.java:189)
    at org.dbunit.operation.RefreshOperation.execute(RefreshOperation.java:113)
    at org.dbunit.AbstractDatabaseTester.executeOperation(AbstractDatabaseTester.java:190)
    at org.dbunit.AbstractDatabaseTester.onSetup(AbstractDatabaseTester.java:103)
    at org.dbunit.DatabaseTestCase.setUp(DatabaseTestCase.java:156)
    at test.HibernateDbUnitTestCase.setUp(HibernateDbUnitTestCase.java:85)
    at test.PlayerTest.setUp(PlayerTest.java:117)

Caused by: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
    at java.sql.Timestamp.valueOf(Unknown Source)
    at org.dbunit.dataset.datatype.TimestampDataType.typeCast(TimestampDataType.java:116)
    ... 20 more

That was weird, it seemed like my CustomTimeStamp was not called, so I changed the date in the dataset using the default format : 1997-02-14 00:00:00.0, and ran the unit test again. Then I got:

org.dbunit.dataset.datatype.TypeCastException: Unable to typecast value <1997-02-14 00:00:00.0> of type <java.lang.String> to TIMESTAMP
    at test.CustomTimestampDataType.typeCast(CustomTimestampDataType.java:69)
    at test.CustomTimestampDataType.setSqlValue(CustomTimestampDataType.java:84)
    at org.dbunit.database.statement.SimplePreparedStatement.addValue(SimplePreparedStatement.java:73)
    at org.dbunit.operation.RefreshOperation$RowOperation.execute(RefreshOperation.java:189)
    at org.dbunit.operation.RefreshOperation.execute(RefreshOperation.java:113)
    at org.dbunit.AbstractDatabaseTester.executeOperation(AbstractDatabaseTester.java:190)
    at org.dbunit.AbstractDatabaseTester.onSetup(AbstractDatabaseTester.java:103)
    at org.dbunit.DatabaseTestCase.setUp(DatabaseTestCase.java:156)
    at test.HibernateDbUnitTestCase.setUp(HibernateDbUnitTestCase.java:85)
    at test.PlayerTest.setUp(PlayerTest.java:117)

That means CustomTimeStamp was actually called. Seems like, the problem stemed from DatabaseTestCase.setUp which somehow called the wrong TimeStampDataType.

How could I fix this issue?

• My first option was to replace every yyyy/MM/dd into yyyy-mm-dd in the dataset using regular expressions. This worked fine, until I had to test a method that selected a date based on a request (so the format is yyyy-mm-dd) and compared it to the current date. ( so the format is yyyy / mm / dd). Hsqldb can't compare two dates with different format.

• My second option was to decompile dbunit.jar, rewrite TimeStampDataType based on the tutorial. I'm unfamiliar with bytecode writing so before entering uncharted waters, I wanted to know if you had another solution.

Thank you in advance

Answer 1

Fixed it!

So I ended up using my second option. This is the detailed path for those who need it.

• Download dbUnit.2.2.source.jar
• Unzip the jar
• Go to Eclipse, File > New > Java Project
• Uncheck "Use default location"
• In Location : specify the path to the new folder created from the jar
• Click on Finish

• Modify the TimestampDataType.java (if needed)

  • Instead of ts = java.sql.Timestamp.valueOf(stringValue); use the code below

        String formats[] =
         {"dd/MM/yyyy HH:mm:ss.SS"}; //and more depending on your need
         Timestamp ts = null;
         for (int i = 0; i < formats.length; i++) 
         {
         SimpleDateFormat sdf = new SimpleDateFormat(formats[i]);
         try {
             java.util.Date date = sdf.parse(stringValue);
             ts = new Timestamp(date.getTime());
             return ts;
         }
         catch( ParseException e) {
        }
    

• Modify the DateDataType.java (if needed)

  • Instead of return java.sql.Date.valueOf(stringValue); , use the code below

    String formats[] =
         {"dd/MM/yyyy"};  //and more depending on your need
         for (int i = 0; i < formats.length; i++) 
         {
         SimpleDateFormat sdf = new SimpleDateFormat(formats[i]);
    
         try {
    
         java.util.Date date = sdf.parse(stringValue);
         java.sql.Date datesql = new java.sql.Date(date.getTime());
         return datesql;
         }
         catch( ParseException e) {
         }
         }
    

• Right-click on your project, then Export
• Select JAR file, then Next
• Fill the export destination then Finish.
• You just have to add this new jar to the library to make it work.