Datatype conversions between JDBC and JSR-310

[Lance Andersen - Oracle]

Here is an overview of the changes that will probably occur to the current java.sql.Date/Time/Timestamp classes for better integration with JSR 310.  We expect the work to be done as part of the integration of JSR 310.  

The overview below  is courtesy of Douglas Surber who is a JSR 310 EG member and also a member of the JDBC EG and the lead for the Oracle JDBC thin Driver team.  He has been helping with making sure we are in sync with JSR 310. He and I have had a few discussions/email exchanges on this over the past couple of days.


Best
Lance

-----------------------------

JDK 8 is getting down to the wire. JSR-310, the new Date/Time API, is not yet final, but it is getting close. We need to nail down the API for converting between the java.sql types and the new 310 types. We don't have absolute freedom in our choices to the extent the JDBC types inherit from java.util.Date. The sooner we make our preference known the more influence/freedom we may have.




FYI: Here's the JavaDoc for a reasonably current version of 310:

 http://threeten.github.com/threeten/dev/javadoc/index.html

I'll try to summarize the salient points so you don't have to digest the entire API.

* java.time.LocalTime is hh:mm:ss.nanos
* java.time.LocalDate is YYYY:MM:DD
* java.time.LocalDateTime is YYYY:MM:DD:hh:mm:ss.nanos
* java.time.OffsetDateTime is YYYY:MM:DD:hh:mm:ss.nanos +/-hh:mm:ss
* java.time.ZonedDateTime is YYYY:MM:DD:hh:mm:ss.nanos <zone id eg Americas/Los_Angeles>

All are immutable.

I propose that we make the following additions to JDBC to support conversion between the JDBC date/time types and the corresponding JSR-310 types:

 in java.sql.Time:
 public Time(java.time.LocalTime t) throws SQLException;
 public java.time.LocalTime toLocalTime() throws SQLException;

 in java.sql.Date
 public Date(java.time.LocalDate d) throws SQLException;
 public java.time.LocalDate toLocalDate() throws SQLException;

 in java.sql.Timestamp
 public Timestamp(java.time.LocalDateTime ldt) throws SQLException;
 public java.time.LocalDateTime toLocalDateTime() throws SQLException;


These few additions fully support interoperation between the java.sql types and the new java.time types. It will be straightforward to write code that interfaces the old and new types. On the other hand there are no convenience methods; one can't readily mix the types. I consider this a good thing.

The small api additions minimize the work Lance has to do in the very, very short time before JDK 8 freezes.

The API is very clear about the mapping between the SQL types and the new 310 types. This has a big education benefit. It clearly explains to JDBC users how to use the new 310 types for DATE, TIME, and TIMESTAMP WITHOUT TIME ZONE.

There is no real decision by the JDBC user on how to connect java.sql types to java.time types. That decision is forced by the API. That is a big ease of use benefit. 310 is quite large and dropping the whole thing on top of JDBC users would confuse many of them. This very strict API gives them a precise entry into 310. Once in 310 they can do whatever they want, but at least they have a good point of entry.

With this API, old code that uses java.sql and new code that uses java.time can interoperate. The JDBC user can move information back and forth between old code and new. What would be more difficult, though not impossible, is to intermix java.sql and java.time types in the same code. So long as the user stuck to the three java.time types, LocalDate, LocalTime, LocalDateTime, conversion back and forth is easy enough. But if the user begins to take advantage of the full java.time API there are no convenience methods for mixing the two type systems. The user would have to pass through the three supported java.time types. I don't consider this a bad thing as it makes clear any loss of information or additional info needed to do the desired conversion.

This is probably the smallest change that fully supports 310. I also think it is the right thing.

Note: Don't forget that setObject(int, Object, SQLType) and getObject(int, Class) will fully support these types: LocalDate/DATE, LocalTime/TIME, LocalDateTime/TIMESTAMP WITHOUT TIME ZONE. They will also support OffsetDateTime/TIMESTAMP WITH TIME ZONE and Period/INTERVAL. The above discussion is limited to the changes needed to support conversion between the existing java.sql date/time types and the new 310 types.

Lance Andersen| Principal Member of Technical Staff | +1.781.442.2037
Oracle Java Engineering 
1 Network Drive 
Burlington, MA 01803
Lance.Andersen at oracle.com