How to map sql DATE to LocalDate

Question

I want to store a LocalDate in a DATE column and retrieve it unchanged. Both DATE and LocalDate are "local" types by definition. Therefore, the concept of timezone should not interfere in any way.

The code below is a minimal example that creates a table with a DATE column in a in-memory database. The maven artifact com.h2database:h2:1.4.192 must be in the classpath.

First, define methods insert and retrieve:

static void insert(DataSource ds, String date) throws SQLException {
  try (Connection conn = ds.getConnection();
       Statement stmt = conn.createStatement()) {
    stmt.execute("CREATE TABLE people (id BIGINT NOT NULL AUTO_INCREMENT"
      + ", born DATE NOT NULL, PRIMARY KEY (id) );");
    stmt.execute("INSERT INTO people (born) VALUES ('" + date + "')");
  }
}

static LocalDate retrieve(DataSource ds) throws SQLException {
  try (Connection conn = ds.getConnection();
       Statement stmt = conn.createStatement();
       ResultSet rs = stmt.executeQuery("SELECT * FROM people limit 1")) {
    if (rs.next()) {
      java.sql.Date retrieved = java.sql.Date.valueOf(rs.getString("born"));
      return retrieved.toLocalDate();
    }
    throw new IllegalStateException("No data");
  }
}

Notice that the insert method uses the toString value of the LocalDate in single quotes, so there's no opportunity for Java™ to create timezone ambiguity. Now call insert once and then several times retrieve, with different timzone settings each time:

public static void main(String[] args) throws Exception {
  DataSource ds = JdbcConnectionPool.create("jdbc:h2:mem:test", "sa", "sa");
  LocalDate born = LocalDate.parse("2015-05-20");
  insert(ds, born.toString());
  System.out.println("Inserted:  " + born);
  for (int i : new int[]{-14, 0, 12}) {
    TimeZone z = TimeZone.getTimeZone(String.format("Etc/GMT%+02d", i));
    TimeZone.setDefault(z);
    System.out.println("Retrieved: " + retrieve(ds));
  }
}

Then the following is printed:

Inserted:  2015-05-20
Retrieved: 2015-05-20
Retrieved: 2015-05-19
Retrieved: 2015-05-18

How to write the retrieve method so that it returns the same value that was inserted unconditionally, assuming that the database table doesn't change?

Answer 1

The following solution also works. I prefer the conversion via String in the accepted answer, because it avoids the timezone tinkering shown below. It may however not work the same way on all databases because some, e.g. Oracle, have a different definition of DATE.

static LocalDate retrieve(DataSource ds) throws SQLException {
  try (Connection conn = ds.getConnection();
       Statement stmt = conn.createStatement();
       ResultSet rs = stmt.executeQuery("SELECT * FROM people limit 1")) {
    if (rs.next()) {
      ZoneId utc = ZoneId.of("UTC");
      TimeZone z = TimeZone.getTimeZone(utc);
      Calendar cal = Calendar.getInstance(z);
      java.sql.Date retrieved = rs.getDate("born", cal);
      long time = retrieved.getTime();
      java.util.Date utilDate = new java.util.Date(time);
      Instant instant = utilDate.toInstant();
      ZonedDateTime zdt = instant.atZone(utc);
      return zdt.toLocalDate();
    }
  }
  throw new IllegalStateException("No data");
}

The conversion via java.util.Date is outlined in this question, as suggested by user Tunaki: Missed opportunity to fix JDBC date handling in Java 8?

Answer 2

I just tried the following modification to your retrieve method and it worked for me:

The H2 documentation for the DATE Type says that it is

The date data type. The format is yyyy-MM-dd.

So, instead of your ...

java.sql.Date retrieved = (java.sql.Date) rs.getObject("born");
return retrieved.toLocalDate();

... I just used ...

return LocalDate.parse(rs.getString("born"));

... and my code produced

Inserted:  2015-05-20
Retrieved: 2015-05-20
Retrieved: 2015-05-20
Retrieved: 2015-05-20