Search code examples
javahibernatehsqldbdbunit

Timestamp with timezone in HSQLDB

My project is using postgres and there are also some in memory tests with hsqldb (2.4.1)

The Client entity has this field:

@CreationTimestamp
@Temporal(TemporalType.TIMESTAMP)
@Column(updatable = false)
private Date creationDate;

Where Date is the java.util.Date

The table is created using "timestamp with timezone"

CREATE TABLE client (
  id bigint NOT NULL,
  creationdate timestamp with time zone,
  ...)

When I am running the tests, dbunit is loading a file with data and i get this error:

2018-08-21 09:39:03,194 [warn] o.d.u.SQLHelper - CLIENT.CREATIONDATE data type (2014, 'TIMESTAMP WITH TIME ZONE') not recognized and will be ignored. See FAQ for more information.

So I figured that I should extend HSQLDialect to be able to support it. However I can't figure out how, is it registerColumnType() or registerHibernateType() what I need? Or maybe some casting instead?

Solution

  • It is a dbUnit class (perhaps obfuscated by the logging framework to "o.d.u.SQLHelper") emitting that warning message. It means that the specified data type is not supported by dbUnit.

    TL;DR

    Please create an enhancement request to support this SQL Standard data type: https://sourceforge.net/p/dbunit/feature-requests/

    Having dbUnit support this data type will happen much faster if you implement it with tests and create a merge request or attach a patch.

    Details

    The message says "See FAQ", and the FAQ page has info on the issue: http://dbunit.sourceforge.net/faq.html#typenotrecognized

    Click the "replace the default data type factory" link to this FAQ entry: http://dbunit.sourceforge.net/faq.html#typefactory

    It shows using a database-specific data type factory.
    Your database is HSQLDB, so, as it mentions, find its class in the "org.dbunit.ext.hsqldb" subpackage: http://dbunit.sourceforge.net/xref/org/dbunit/ext/hsqldb/HsqldbDataTypeFactory.html.

    Configure dbUnit to use it following the example. Or, depending on your setup (e.g. using Spring? Which TestCase?), this is a helpful example and another approach (this example is my typical setup): http://dbunit.sourceforge.net/testcases/PrepAndExpectedTestCase.html#Configuration_Example_Using_Spring

    However, reviewing HsqldbDataTypeFactory's source shows that data type not supported in it: http://dbunit.sourceforge.net/xref/org/dbunit/ext/hsqldb/HsqldbDataTypeFactory.html

    or its parent: http://dbunit.sourceforge.net/xref/org/dbunit/dataset/datatype/DefaultDataTypeFactory.html

    The parent, DefaultDataTypeFactory, on line 71 delegates to DataType: http://dbunit.sourceforge.net/xref/org/dbunit/dataset/datatype/DataType.html

    DataType defines the supported data types using the Java API Types class: https://docs.oracle.com/javase/8/docs/api/java/sql/Types.html

    Searching the Types page for "timestamp with timezone" finds: https://docs.oracle.com/javase/8/docs/api/java/sql/Types.html#TIMESTAMP_WITH_TIMEZONE

    We see that it was added in Java 8.

    Searching DataType for Types.TIMESTAMP_WITH_TIMEZONE does not find support for "timestamp with timezone". This is how we know it is missing.