Using java.time with ResultSet and PreparedStatement

Taking another look at ResultSet in Java 8, it looks like the JDBC 4.2 Changes (sections 21 and 22) show the java.time classes such as LocalDateTime are directly supported. Let’s give it a try, and also see the differences between a 4.0 driver and a 4.2 driver. To make things easier we’ll use in memory databases. The current H2 database uses 4.0 and the current Derby database uses 4.2. We will also try accessing java.time with a PreparedStatement. And processing a ResultSet as a Java 8 Stream.

First let’s make sure Derby supports 4.2.

@Test
public void testVersion() throws Exception {
  try (Connection connection = getConnection()) {
    DatabaseMetaData metaData = connection.getMetaData();
    assertEquals(4, metaData.getJDBCMajorVersion());
    assertEquals(2, metaData.getJDBCMinorVersion());
  }
}

This test passes, so let’s update a ResultSet.

try (Connection connection = getConnection()) {
  connection.createStatement().execute(
      "create table test1 (id int primary key,
      test1 timestamp, test2 date, test3 time)");
  ResultSet resultSet = connection
      .createStatement(
          ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_UPDATABLE)
      .executeQuery("select * from test1");
  resultSet.moveToInsertRow();
  resultSet.updateObject(
      2, LocalDateTime.now(), JDBCType.TIMESTAMP);

Maybe it is the new java 8 method, let’s try the old method

resultSet.updateObject(2, LocalDateTime.now());

Nope, same thing. I guess I’m misreading the JDBC 4.2 specification, and java.time is not well supported in ResultSet.

So instead let’s add some helper methods to ResultSet and PreparedStatement for java.time.

public final class ResultSetWrapper {
  ...
  public LocalDate getLocalDate(int columnIndex)
      throws SQLException {
    // At Java One 2015, it was mentioned, that
    // Optional.ofNullable is not a valid
    // replacement for ?:, but I think
    // in this case, it is better than calling
    // getDate twice.
    return Optional
        .ofNullable(resultSet.getDate(columnIndex))
        .map(Date::toLocalDate)
        .orElse(null);
  }
  ...
}

And for the PreparedStatement

public class PreparedStatementWrapper {
  ...
  public void setLocalTime(int oneBasedIndex,
      LocalTime localTime) throws SQLException {
    preparedStatement.setTimestamp(
        oneBasedIndex, convert(localTime));
  }
  /**
   * Convert a LocalDateTime to a Timestamp
   * using the system timezone.
   */
  private Timestamp convert(LocalDateTime localDateTime) {
    return localDateTime == null ? null :
        Timestamp.valueOf(localDateTime);
  }
  private Date convert(LocalDate localDate) {
    return localDate == null ? null : Date.valueOf(localDate);
  }
  /**
   * Convert a LocalTime to a Timestamp.
   * The LocalTime can't be converted
   * to a java.sql.Time because the nanoseconds will be lost.
   */
  private Timestamp convert(LocalTime localTime) {
    return localTime == null ? null : Timestamp.valueOf(
        localTime.atDate(LocalDate.ofEpochDay(0)));
  }
}

The other piece that is needed is a Stream. To make things easier, the apache commons ResultSetIterator is used. This makes everything objects, so unfortunately the previous helper methods can’t be used.

public final class ResultSetWrapper {
  ...
  public Stream stream() {
     return StreamSupport
         .stream(
             Spliterators
                 .spliteratorUnknownSize(
                     new ResultSetIterator(resultSet), 0),
                         false);
   }
  ... }

This can then be accessed

List sortedIds = resultSetWrapper
    .stream()
    .filter(row -> getTime(row) != null)
    .sorted(Comparator.comparing(this::getTime))
    .map(row -> getId(row))
    .collect(Collectors.toList());

Then running the unit tests with JDBC 4.0 and JDBC 4.2.

Overall this seems to work, but still feels a bit clunky. Checking out ResultSet for Java 9 (build 91), it does not look like setters and getters for java.time are being added. Maybe in Java 10, ResultSet can be deprecated and replaced with a nice stream compatible version.

The full source is available on github.