Missed opportunity to fix JDBC date handling in Java 8?

Question

Can any Java 8 + JDBC expert tell me if something's wrong in the following reasoning? And, if in the secrets of Gods, why this hasn't been done?

A java.sql.Date is currently the type used by JDBC to map to the DATE SQL type, which represents a date without time, and without timezone. But this class is awfully designed, since it's in fact a subclass of java.util.Date, which stores a precise instant in time, up to the millisecond.

To represent the date 2015-09-13 in database, we're thus forced to choose a timezone, parse the string "2015-09-13T00:00:00.000" in that timezone as a java.util.Date to get a millisecond value, then construct a java.sql.Date from this millisecond value, and finally call setDate() on the prepared statement, passing a Calendar holding the timezone chosen in order for the JDBC driver to be able to correctly recompute the date 2015-09-13 from this millisecond value. This process is made a bit simpler by using the default timezone everywhere, and not passing a Calendar.

Java 8 introduces a LocalDate class, which is a much better fit for the DATE database type, since it's not a precise moment in time, and is thus not dependent on the timezone. And Java 8 also introduces default methods, which would allow to make backward-compatible changes to the PreparedStatement and ResultSet interfaces.

So, haven't we missed a huge opportunity to clean up the mess in JDBC while still maintaining backward compatibility? Java 8 could simply have added those default methods to PreparedStatement and ResultSet:

default public void setLocalDate(int parameterIndex, LocalDate localDate) {
    if (localDate == null) {
        setDate(parameterIndex, null);
    }
    else {
        ZoneId utc = ZoneId.of("UTC");
        java.util.Date utilDate = java.util.Date.from(localDate.atStartOfDay(utc).toInstant());
        Date sqlDate = new Date(utilDate.getTime());
        setDate(parameterIndex, sqlDate, Calendar.getInstance(TimeZone.getTimeZone(utc)));
    }
}

default LocalDate getLocalDate(int parameterIndex) {
    ZoneId utc = ZoneId.of("UTC");
    Date sqlDate = getDate(parameterIndex, Calendar.getInstance(TimeZone.getTimeZone(utc)));
    if (sqlDate == null) {
        return null;
    }

    java.util.Date utilDate = new java.util.Date(sqlDate.getTime());
    return utilDate.toInstant().atZone(utc).toLocalDate();
}

Of course, the same reasoning applies to the support of Instant for the TIMESTAMP type, and the support of LocalTime for the TIME type.

Answer 1

To represent the date 2015-09-13 in database, we're thus forced to choose a timezone, parse the string "2015-09-13T00:00:00.000" in that timezone as a java.util.Date to get a millisecond value, then construct a java.sql.Date from this millisecond value, and finally call setDate() on the prepared statement, passing a Calendar holding the timezone chosen in order for the JDBC driver to be able to correctly recompute the date 2015-09-13 from this millisecond value

Why? Just call

Date.valueOf("2015-09-13"); // From String
Date.valueOf(localDate);    // From java.time.LocalDate

The behaviour will be the correct one in all JDBC drivers: The local date without timezone. The inverse operation is:

date.toString();    // To String
date.toLocalDate(); // To java.time.LocalDate

You should never rely on java.sql.Date's dependency on java.util.Date, and the fact that it thus inherits the semantics of a java.time.Instant via Date(long) or Date.getTime()

So, haven't we missed a huge opportunity to clean up the mess in JDBC while still maintaining backward compatibility? [...]

It depends. The JDBC 4.2 spec specifies that you are able to bind a LocalDate type via setObject(int, localDate), and to fetch a LocalDate type via getObject(int, LocalDate.class), if the driver is ready for that. Not as elegant as more formal default methods, as you suggested, of course.

Answer 2

Short answer: no, date time handling is fixed in Java 8 / JDBC 4.2 because it has support for Java 8 Date and Time types. This can not be emulated using default methods.

Java 8 could simply have added those default methods to PreparedStatement and ResultSet:

This is not possible for several reasons:

• java.time.OffsetDateTime has not equivalent in java.sql
• java.time.LocalDateTime breaks on DST transitions when converting through java.sql.Timestamp because the latter depends on the JVM time zone, see code below
• java.sql.Time has millisecond resolution but java.time.LocalTime has nanosecond resolution

Therefore you need proper driver support, default methods would have to convert through java.sql types which introduces data loss.

If you run this code in a JVM time zone with daylight savings time it will break. It searches the next transition in which the clocks are "set forward" and picks a LocalDateTime that is right in the middle of the transition. This is perfectly valid because Java LocalDateTime or SQL TIMESTAMP have no time zone and therefore no time zone rules and therefore no daylight saving time. java.sql.Timestamp on the other hand is bound to the JVM time zone and therefore subject to daylight saving time.

ZoneId systemTimezone = ZoneId.systemDefault();
Instant now = Instant.now();

ZoneRules rules = systemTimezone.getRules();
ZoneOffsetTransition transition = rules.nextTransition(now);
assertNotNull(transition);
if (!transition.getDateTimeBefore().isBefore(transition.getDateTimeAfter())) {
  transition = rules.nextTransition(transition.getInstant().plusSeconds(1L));
  assertNotNull(transition);
}

Duration gap = Duration.between(transition.getDateTimeBefore(), transition.getDateTimeAfter());
LocalDateTime betweenTransitions = transition.getDateTimeBefore().plus(gap.dividedBy(2L));

Timestamp timestamp = java.sql.Timestamp.valueOf(betweenTransitions);
assertEquals(betweenTransitions, timestamp.toLocalDateTime());