How can I map Java Instants to H2 TIMESTAMP WITH TIME ZONE using jOOQ?

Question

Using Postgres I am able to force TIMESTAMPTZ columns to map to Instant with a forced type in the jOOQ mapping

create table if not exists public.items
(
    id           varchar(100) not null,
    modified     timestamptz    not null,
    name         varchar(100) not null,
    "sellByDate" date,
    quality      integer      not null
    );

forcedTypes {
  forcedType {
    name = 'instant'
    includeExpression = '.*'
    includeTypes = 'TIMESTAMPTZ'
  }
}

When I try to do a similar thing with H2

create table if not exists public.items
(
    id           varchar(100) not null,
    modified     timestamp with time zone   not null,
    name         varchar(100) not null,
    "sellByDate" date,
    quality      integer      not null
    );

forcedTypes {
    forcedType {
        name = 'instant'
        includeExpression = '.*'
        includeTypes = 'TIMESTAMP WITH TIME ZONE'
    }
}

The mapping is not applied

WARNING Unused forced types      : There are unused forced types, which have not been used by this generation run.
This can be because of misconfigurations, such as, for example:
- case sensitive regular expressions
- regular expressions depending on whitespace (Pattern.COMMENTS is turned on!)
- missing or inadequate object qualification
- the object to which the configuration was applied in the past has been dropped
Try turning on DEBUG logging (-X in Maven, --debug in Gradle, and <logging/> in jOOQ) to get additional info about the schema
16:42:53 WARNING Unused forced type       : <priority>0</priority><name>instant</name><autoConverter>true</autoConverter><includeExpression>.*</includeExpression><includeTypes>TIMESTAMP WITH TIME ZONE</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>

Can anyone help diagnose the problem please?

Answer 1

The hint is in the warning: "(Pattern.COMMENTS is turned on!)". This means that the regular expression's white spaces are being ignored by Java's Pattern class. This is generally useful for regular expressions that are formatted like this:

(

# Comment
  SOME_VALUE

# Other comment
| SOME_OTHER_VALUE
)

In this case, you don't want whitespace (or comments) to be part of the regular expression. You don't want this to apply to your regex, of course, so escape the whitespace:

includeTypes = 'TIMESTAMP\\ WITH\\ TIME\\ ZONE'

Or:

includeTypes = 'TIMESTAMP\\sWITH\\sTIME\\sZONE'