Extending SQLUnit: Adding support for your own datatypes

Differences in implementation among different JDBC drivers and databases will require you to add support for your database if your datatype is not listed above, or if it uses a different native type from that listed. SQLUnit allows you to define these outside the package in a properties file usertypes.properties which must be in your CLASSPATH. Some common scenarios are covered below:

Using an existing implementation for an unlisted datatype

For example, assume you wished to use the Sybase ASE DATETIME datatype which behaves in much the same way as a TIMESTAMP. You can add the information for SQLUnit to support the DATETIME in the usertypes.properties files as sshown below:

# usertypes.properties
adaptive_server_enterprise.DATETIME.class = net.sourceforge.sqlunit.types.TimestampType
adaptive_server_enterprise.DATETIME.type = 93

The adaptive_server_enterprise is the name we get from Connection.getMetaData().getDatabaseProductName() when we open a connection to a Sybase ASA server. Actually it is "Adaptive Server Enterprise", but SQLUnit lowercases it and replaces embedded whitespace with underscores. Both entries are required. You may need to verify these values. When these values are added to the usertypes.properties file, SQLUnit will read them in after it reads its own types.properties file, and DATETIME will now work like TIMESTAMP for Sybase ASA.

Overriding the implementation for a datatype

PostgreSQL treats NUMERIC types as BigDecimal (and so does Oracle), while the standard mapping for NUMERIC is Double. To override this for PostgreSQL, we need to add the following entry in your usertypes.properties. (This is already added in to types.properties, see the table above).

# usertypes.properties
postgresql.NUMERIC.class = net.sourceforge.sqlunit.types.BigDecimalType
postgresql.NUMERIC.type = 2

This will override the mapping for NUMERIC when the current server is PostgreSQL.

Adding a new implementation for a new datatype

Suppose that in the DATETIME example, we wanted to input and output the DATETIME in a different format than the default. The default is "yyyy-MM-dd HH:mm:ss.SSS" while we want "MMM dd, yyyy HH:mm:ss Z". We will need to write a new type class for this and map the new type class to the DATETIME type in our usertypes.properties file. Looking at the TimestampType class, we notice that all we need to do is to override the PATTERN variable to make this work, like so:

// DateTimeType.java
package com.myapplication.mypackage;

import net.sourceforge.sqlunit.types.TimestampType;

 * Models a Sybase DATETIME type.
public class DateTimeType extends TimestampType {
    // just use this new pattern to parse and format
    protected static String PATTERN = "MMM dd, yyyy HH:mm:ss Z";

And the user.properties file will now look like this:

# user.properties
adaptive_server_enterprise.DATETIME.class = com.myapplication.mypackage.DateTimeType
adaptive_server_enterprise.DATETIME.type = 93