Missed opportunity to fix JDBC date handling in Java 8?

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());

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.