jOOQ - support for JSR310

Question

Does jOOQ provide support for JSR310 in combination with PostgreSQL? In particular, I am trying to use the following classes:

• java.time.Instant
• java.time.LocalDate
• java.time.LocalTime
• java.time.LocalDateTime

I am storing in the following data types (as per http://www.postgresql.org/docs/9.1/static/datatype-datetime.html):

• java.time.Instant: timestamp with timezone
• java.time.LocalDate: date
• java.time.LocalTime: time without timezone
• java.time.LocalDateTime: timestamp without timezone

Are these data types correct?

Does jOOQ support translation between java.sql.Timestamp, java.sql.Date and java.sql.Time and the four classes above (in both directions)?

Answer 1

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.

Answer 2

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 JDBC DATE.
• Added support to map java.time.LocalTime to JDBC TIME
• Added support to map java.time.LocalDateTime to JDBC TIMESTAMP.
• Added support to map java.time.OffsetTime to JDBC TIME_WITH_TIMEZONE.
• Added support to map java.time.OffsetDateTime to JDBC TIMESTAMP_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.