如何将sql DATE映射到LocalDate

我想在 DATE 列中存储 LocalDate 并保持不变。根据定义， DATE 和 LocalDate 都是本地类型。因此， timezone 的概念不应以任何方式干扰。

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.

下面的代码是创建一个 DATE 内存数据库中的列。 maven工件 com.h2database：h2：1.4.192 必须在类路径中。

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.

首先，定义方法 insert 和 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");
  }
}

请注意插入方法在单引号中使用 LocalDate 的 toString 值，因此Java没有机会™创造时区歧义。现在调用插入一次，然后多次检索，每次使用不同的timzone设置：

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

然后打印以下内容：

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?