jOOQ - support for JSR310
jOOQ Roadmap
Up until jOOQ 3.6, there was no official support for JSR-310 date time types, because both the jOOQ Open Source Edition and the commercial editions still supported Java 6.
With jOOQ 3.7, this changes as Java 6 is supported only in a specifically built commercial distribution, whereas standard distributions will require Java 8. The relevant issue for this change is #4338.
Another change involves a flag <javaTimeTypes/>
to tell the source code generator to generate JSR-310 types rather than java.sql.Date
and similar types. By default, this flag is set to false, so you will need to set it to true until issue #5714 is resolved.
Using JSR-310 types with jOOQ 3.6 or less
In the meantime, you can bind the desired types yourself and let the code generator use your custom Converter
or Binding
. See the relevant sections in the manual:
- http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings
- http://www.jooq.org/doc/latest/manual/code-generation/custom-data-types
- http://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings
The correct mapping
The JDBC 4.2 spec defines which JSR-310 data types should be mapped to which JDBC data types (which coincide with PostgreSQL's understanding of the same). In particular, the JDBC spec lists:
- Added support to map
java.time.LocalDate
to JDBCDATE
. - Added support to map
java.time.LocalTime
to JDBCTIME
- Added support to map
java.time.LocalDateTime
to JDBCTIMESTAMP
. - Added support to map
java.time.OffsetTime
to JDBCTIME_WITH_TIMEZONE
. - Added support to map
java.time.OffsetDateTime
to JDBCTIMESTAMP_WITH_TIMEZONE
.
JDBC's understanding does not seem entirely correct, though, as few databases really store the timezone information in a TIMESTAMP WITH TIME ZONE
data type (e.g. Oracle does), see also this DBA/StackExchange answer: https://dba.stackexchange.com/a/59021/2512. Since the time zone is not stored, your approach of mapping java.time.Instant
to TIMESTAMP WITH TIME ZONE
is better, because the SQL type is really just a UTC timestamp.
Having said so, OffsetDateTime
can still be mapped to TIMESTAMP WITH TIME ZONE
, but you will not know what time zone was used when the timestamp was stored in PostgreSQL.
JSR-310 support is now available in jOOQ 3.9.0 to configure in code:
new Configuration() .withGenerator(new Generator() .withGenerate(new Generate() .withJavaTimeTypes(true)));
The same structure would apply in the xml config (Configuration - Generator - Generate - javaTimeTypes)
Keep an eye on https://github.com/jOOQ/jOOQ/issues/5714 to see when this is enabled by default.