错过了在 Java 8 中修复 JDBC 日期处理的机会？

Missed opportunity to fix JDBC date handling in Java 8?

提问by JB Nizet

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?

任何 Java 8 + JDBC 专家都可以告诉我以下推理是否有问题？而且，如果在神的秘密中，为什么没有这样做？

A java.sql.Dateis 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.

Ajava.sql.Date目前是 JDBC 用来映射到 DATE SQL 类型的类型，它表示没有时间和时区的日期。但是这个类的设计非常糟糕，因为它实际上是 的一个子类java.util.Date，它存储了一个精确的瞬间，直到毫秒。

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

为了在数据库中表示日期 2015-09-13，我们因此被迫选择一个时区，将该时区中的字符串“2015-09-13T00:00:00.000”解析为 java.util.Date 以获得毫秒值，然后java.sql.Date根据这个毫秒值构造一个，最后调用setDate()准备好的语句，传递一个包含所选时区的日历，以便 JDBC 驱动程序能够从这个毫秒值正确地重新计算日期 2015-09-13。通过在任何地方使用默认时区，而不是传递日历，这个过程变得更简单了。

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.

Java 8 引入了一个 LocalDate 类，它更适合 DATE 数据库类型，因为它不是一个精确的时刻，因此不依赖于时区。Java 8 还引入了默认方法，允许对 PreparedStatement 和 ResultSet 接口进行向后兼容的更改。

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:

那么，我们是不是错过了在保持向后兼容性的同时清理 JDBC 中的烂摊子的大好机会？Java 8 可以简单地将这些默认方法添加到 PreparedStatement 和 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.

当然，同样的道理也适用于对 TIMESTAMP 类型的 Instant 的支持，以及对 TIME 类型的 LocalTime 的支持。

采纳答案by Lukas Eder

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

为了在数据库中表示日期 2015-09-13，我们因此被迫选择一个时区，将该时区中的字符串“2015-09-13T00:00:00.000”解析为 java.util.Date 以获得毫秒值，然后从这个毫秒值构造一个 java.sql.Date，最后在准备好的语句上调用 setDate()，传递一个 Calendar 保存选择的时区，以便 JDBC 驱动程序能够正确地重新计算日期 2015-09 -13 从这个毫秒值

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:

该行为在所有 JDBC 驱动程序中都是正确的：不带时区的本地日期。逆运算是：

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.Instantvia Date(long)or Date.getTime()

您永远不应该依赖于java.sql.Date的依赖java.util.Date，以及它因此继承了java.time.InstantviaDate(long)或Date.getTime()

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

那么，我们是不是错过了在保持向后兼容性的同时清理 JDBC 中的烂摊子的大好机会？[...]

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

这取决于。在JDBC 4.2规范指定，你是能够结合LocalDate通过类型setObject(int, localDate)，并获取一条LocalDate通过型getObject(int, LocalDate.class)驱动程序是否做好准备。default当然，正如您所建议的那样，不如更正式的方法优雅。

回答by Philippe Marschall

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 / JDBC 4.2 中是固定的，因为它支持Java 8 日期和时间类型。这无法使用默认方法进行模拟。

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

Java 8 可以简单地将这些默认方法添加到 PreparedStatement 和 ResultSet 中：

This is not possible for several reasons:

由于以下几个原因，这是不可能的：

• java.time.OffsetDateTimehas not equivalent in java.sql
• java.time.LocalDateTimebreaks on DST transitions when converting through java.sql.Timestampbecause the latter depends on the JVM time zone, see code below
• java.sql.Timehas millisecond resolution but java.time.LocalTimehas nanosecond resolution

• java.time.OffsetDateTime不等同于 java.sql
• java.time.LocalDateTime转换时中断 DST 转换，java.sql.Timestamp因为后者取决于 JVM 时区，请参阅下面的代码
• java.sql.Time具有毫秒分辨率但java.time.LocalTime具有纳秒分辨率

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

因此，您需要适当的驱动程序支持，默认方法必须通过java.sql引入数据丢失的类型进行转换。

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 LocalDateTimethat is right in the middle of the transition. This is perfectly valid because Java LocalDateTimeor SQL TIMESTAMPhave no time zone and therefore no time zone rules and therefore no daylight saving time. java.sql.Timestampon the other hand is bound to the JVM time zone and therefore subject to daylight saving time.

如果您在具有夏令时的 JVM 时区中运行此代码，它将中断。它搜索时钟被“向前设置”的下一个转换，并选择LocalDateTime正好在转换中间的一个。这是完全有效的，因为 JavaLocalDateTime或 SQLTIMESTAMP没有时区，因此没有时区规则，因此没有夏令时。java.sql.Timestamp另一方面，绑定到 JVM 时区，因此受夏令时的约束。

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