« Designing Remote Service APIs | Main | SsTemplates Spreadsheet Templates for Excel »

July 20, 2005

DBUnit, HSQL and the BOOLEAN data type

We have been using HSQL in-memory along with DBUnit for unit testing lately, and I found an issue using the most recent version of each. Basically, HSQL has added a new data type, BOOLEAN, which replaces BIT. But DBUnit is not updated to support this, and an error is throw when you attempt to insert some BOOLEAN data using DBUnit.

The error looks like this: 

WARNING - TABLE.COLUMN data type (16, 'BOOLEAN') not recognized 
and will be ignored. See FAQ for more information.

The solution outlined here is straightforward. You need to create a new data type factory that extends the DBUnit class DefaultDataTypeFactory. This new class just handles the SQL type Types.BOOLEAN as a special case. The code follows:

HsqlDataTypeFactory.java 

package company.project.test;

import org.apache.commons.logging.*;
import org.dbunit.dataset.datatype.*;

import java.sql.*;

public class HsqlDataTypeFactory
  extends DefaultDataTypeFactory
{
   private static final Log log = LogFactory.getLog(HsqlDataTypeFactory.class);
 
   public DataType createDataType(int sqlType, String sqlTypeName)
     throws DataTypeException
   {
      if (sqlType == Types.BOOLEAN)
      {
         return DataType.BOOLEAN;
       }
  
      return super.createDataType(sqlType, sqlTypeName);
    }
}
Then, in order to use this data type factory, just set a property on the IDatabaseConnection DBUnit object in your code (here is an example method): 

 protected IDatabaseConnection getConnection() throws Exception
  {
     IDatabaseConnection connection = 
       new DatabaseConnection( dataSource.getConnection() );
     DatabaseConfig config = connection.getConfig();
     config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, 
                        new HsqlDataTypeFactory());
 
     return connection;
   }

Posted by Mike Wynholds at July 20, 2005 10:40 AM

Comments

It looks like an HsqlDataTypeFactory has been added to the DBUnit project nine months ago:

http://cvs.sourceforge.net/viewcvs.py/dbunit/dbunit/src/java/org/dbunit/ext/hsqldb/

This however is not in the latest DBUnit release (version 2.1) from May 2004 so if you want it from DBUnit, you need to build DBUnit from source.

Alon

Posted by: Alon Salant at July 20, 2005 4:04 PM

This is great. Thanks.

But how do I do this if I'n calling dbunit as an ant task?

Posted by: Tom at August 10, 2005 5:20 PM

I believe you just set it as a property in the dbunit ant task like so:

<taskdef name="dbunit"
classname="org.dbunit.ant.DbUnitTask"
classpathref="build.class.path"
datatypeFactory="org.dbunit.ext.hsqldb.HsqlDataTypeFactory"/>

Posted by: Mike Wynholds at August 11, 2005 7:36 AM

Thanks for that! Since DBUnit still does not support BOOLEAN in HSQLDB, this was a great help for me and my computer science class project :)

Why they don't include it in the stable release is a uncertain, still...?!

Posted by: Fred at February 27, 2006 2:15 PM

I make these steps, but my dbUnit not recnizes a boolean dataType, this is my getConnection() method:

protected IDatabaseConnection getConnection() throws Exception {
// load the jdbc driver
Class.forName("org.hsqldb.jdbcDriver");

IDatabaseConnection connection =
new DatabaseConnection( DriverManager.getConnection(
"jdbc:hsqldb:hsql://localhost:1701", "sa", "" ) );
DatabaseConfig config = connection.getConfig();
config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
new HsqldbDataTypeFactory());

return connection;
}

Somebody can help me?
Thanksss

Posted by: Escobar at May 29, 2006 1:29 PM

Post a comment




Remember Me?