Jooq binding for "timestamp with time zone" type in postgres

Question

Jooq currently does not support JSR 310 types and support will not come until v3.8.

Using simple converters generally works, except for certain types, such as postgres' TIMESTAMP WITH TIME ZONE, which requires a custom binding. So I have tried to write one but the generated XxxRecord classes still use a Timestamp data type for the TIMESTAMP WITH TIME ZONE fields in my DB.

What do I need to change in my code below to see postgres' TIMESTAMP WITH TIME ZONE as an Instantin jooq's generated classes?

Converter

public class TimestampConverter implements Converter<Timestamp, Instant> {
  @Override public Instant from(Timestamp ts) {
    return ts == null ? null : ts.toInstant();
  }
  @Override public Timestamp to(Instant instant) {
    return instant == null ? null : Timestamp.from(instant);
  }
  @Override public Class<Timestamp> fromType() { return Timestamp.class; }
  @Override public Class<Instant> toType() { return Instant.class; }
}

Custom binding

public class TimestampBinding implements Binding<Timestamp, Instant> {

  private static final Converter<Timestamp, Instant> converter = new TimestampConverter();

  private final DefaultBinding<Timestamp, Instant> delegate = 
                                                       new DefaultBinding<> (converter());

  @Override public Converter<Timestamp, Instant> converter() { return converter; }

  @Override public void sql(BindingSQLContext<Instant> ctx) throws SQLException {
    delegate.sql(ctx);
  }

  //etc. same for all other overriden methods.
}

pom.xml (extracts)

<customType>
  <name>java.time.Instant</name>
  <type>java.time.Instant</type>
  <binding>xxx.TimestampBinding</binding>
</customType>

...

<forcedType>
  <name>java.time.Instant</name>
  <types>timestamp with time zone</types>
</forcedType>

Curler

Answer 1

One way would be to escape the spaces in <types> with backslashes, as follows:

<types>timestamp\ with\ time\ zone</types>

You can't just have regular spaces in <types> because by default, org.jooq.util.AbstractDatabase will parse regular expressions in COMMENTS mode which makes the created Pattern object ignore whitespace in your regex. You could also do something like <types>timestamp.*zone</types>, or specify your own <regexFlags>.

The following is the full Maven jooq-codegen-maven plugin tag that works for me. I also found the <binding> to be unnecessary.

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>3.7.0</version>

    <executions>
        <execution>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>

    <configuration>

        <jdbc>
            <driver>org.postgresql.Driver</driver>
            <url>jdbc:postgresql:postgres</url>
            <user>postgres</user>
            <password>mypass</password>
        </jdbc>

        <generator>
            <database>
                <customTypes>
                    <customType>
                        <name>Instant</name>
                        <type>java.time.Instant</type>
                        <converter>xxx.TimestampConverter</converter>
                    </customType>
                </customTypes>

                <forcedTypes>
                    <forcedType>
                        <name>Instant</name>
                        <types>timestamp\ with\ time\ zone</types>
                    </forcedType>
                </forcedTypes>

                <name>org.jooq.util.postgres.PostgresDatabase</name>
                <includes>author</includes>
                <excludes/>
                <inputSchema>public</inputSchema>
            </database>
            <target>
                <packageName>xxx.table</packageName>
                <directory>target/generated-sources/jooq</directory>
            </target>
        </generator>
    </configuration>
</plugin>

Barge

Actually now that I think about it, it is quite unintuitive: the manual only mentions "Java regex". Thanks anyway for the nice digging! – Curler

@Curler You're welcome. That aspect of the configuration is definitely unintuitive and inadequately documented. I got the answer by a little trial and error and then dug up the explanation as to why after the fact. – Barge

That's very unfortunate indeed, and shouldn't be necessary. Fix on the way (hopefully): github.com/jOOQ/jOOQ/issues/4691 – Janes

@LukasEder During my digging, I came across the GitHub issue for why the COMMENTS flag was introduced. I noted it in the new issue that you just created. – Barge

Answer 2

Jooq 3.11 seems to turn a TIMESTAMP WITH TIME ZONE into an OffsetDateTime when javaTimeTypes is enabled, and it also complains about customTypes being deprecated, so I wasn't able to get the other answers to work for me.

Here's how I was able to get this to work, using the gradle jooq plugin:

// inside the jooq...generator.database of build.gradle:
forcedTypes {
    forcedType {
        userType = 'java.time.Instant'
        converter = '''
        org.jooq.Converter.ofNullable(
            java.time.OffsetDateTime.class,
            java.time.Instant.class,
            o -> o.toInstant(),
            i -> i.atOffset(java.time.ZoneOffset.UTC))
        '''
        types = 'timestamp\\ with\\ time\\ zone'
    }
}

It should be pretty easy to turn this into XML for Maven or manual invocation of the code generator, as the gradle plugin's parameters exactly match the structure of the XML. Note that Groovy syntax requires the doubling of the backslashes in the types pattern, so that would need to be adjusted if converting to XML.

This uses an inline converter to turn the OffsetDateTime that Jooq currently uses into an Instant. No external converter class is necessary.

Ethelda