Getting the date from a ResultSet for use with java.time classes

Question

Is there anyway to get a java.time (new in Java 8) compatible time class out of a ResultSet?

I am aware you can use ResultSet's getDate or getTimestamp but these method return java.sql.Date / java.sql.Timestamp objects which are now deprecated so it seems like bad practice to use them in order to create a ZonedDateTime or similar.

Answer 1

Most database vendors don't support JDBC 4.2 yet. This specification says that the new java.time-types like LocalDate will/should be supported using the existing methods setObject(...) and getObject(). No explicit conversion is required and offered (no API-change).

A workaround for the missing support can be manual conversion as described on the Derby-mailing list.

Something like:

LocalDate birthDate = resultSet.getDate("birth_date").toLocalDate();

As you can see, these conversions use the non-deprecated types java.sql.Date etc., see also the javadoc.

Answer 2

Today most of us are using JDBC 4.2 compliant drivers, which improves the situation quite a bit compared to the answers from 2015.

To get a LocalDate from your result set:

LocalDate dateFromDatabase = yourResultSet.getObject(yourColumnIndex, LocalDate.class);

or

LocalDate dateFromDatabase = yourResultSet.getObject("yourColumnLabel", LocalDate.class);

No new method has been added to ResultSet for this to work. The getObject method was there all the time. The new thing is that since JDBC 4.2 it accepts LocalDate.class as the second argument and returns a LocalDate. The above works when the query returns a column with SQL datatype date (really the JDBC type counts, but they tend to agree).

You can pass classes of other java.time types too. And get the corresponding type back. For example:

OffsetDateTime dateTimeFromDatabase
        = yourResultSet.getObject(yourTimestampWithTimeZoneColumnIndex, OffsetDateTime.class);

The java.time types to use are:

SQL datatype            | java.time type
------------------------+-----------------------------------------------------------
date                    | LocalDate
time                    | LocalTime
timestamp               | LocalDateTime
timestamp with timezone | Officially OffsetDateTime; many drivers accept Instant too
time with timezone      | OffsetTime

For passing the other way, from Java to your database (for use as query parameters or for storing) PreparedStatement.setObject now accepts objects of the above java.time types too. Since you are passing an object of a type, there is no need for a separate type parameter when going this way.

Answer 3

New Methods On Timestamp

Java 8 includes new methods on the java.sql.Timestamp class to convert to and from java.time objects. These convenience methods are a stop-gap measure until JDBC drivers can be updated for the new data types.

• toInstant
• toLocalDateTime
• valueOf
• from

Ditto For Date & Time

The java.sql.Date and java.sql.Time classes have similar java.time conversion methods added in Java 8 as well.