Jooq binding for type timestamp with time zone in postgres

Question

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

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

What do I need to change in my code below to see postgres' TIMESTAMP WITH TIME ZONE as Instant in jooq 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 (excerpts)

 <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> 

Answer 1

One way would be to avoid spaces in <types> with a backslash, as shown below:

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

You cannot just have regular spaces in <types> because, by default, org.jooq.util.AbstractDatabase will parse regular expressions in org.jooq.util.AbstractDatabase mode , which causes the created Pattern ignore spaces in the regular expression. You can also do something like <types>timestamp.*zone</types> or specify your own <regexFlags> .

Below is the full Maven plugin tag jooq-codegen-maven which is me jooq-codegen-maven . I also found that <binding> not needed.

 <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> 

Answer 2

Jooq 3.11 seems to turn TIMESTAMP WITH TIME ZONE into OffsetDateTime when javaTimeTypes on, and also complains that customTypes are out of date, so I couldn't get the other answers to work for me.

Here is 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 into a manual call to the code generator, since the gradle plug-in parameters exactly match the XML structure. Note that Groovy syntax requires a double backslash in the types template, so you will need to adjust it when converting to XML.

It uses the built-in converter to turn the OffsetDateTime that Jooq currently uses into Instant . No external converter class required.