Lars Bohl Lars Bohl - 5 months ago 70
Java Question

How to map sql DATE to LocalDate

Apologies if this is a duplicate. I know there have been some similar-sounding question already. Date handling in JDBC is traditionally sort of painful. There has been some recent development regarding Hibernate 5, but I couldn't find a good way to do the

DATE <-> LocalDate
mapping in plain jdbc, which should also be possible, given that Hibernate uses JDBC internally.

As a minimal example, I want to select from a
DATE
column, and convert to
LocalDate
, which seems to be an ideal representation in Java. Retrieving
LocalDate
directly or going through
java.sql.Date
are both o.k., but the result should always be correct (see below).

The example uses the maven artifact
com.h2database:h2:1.4.192
.

First, define methods
insert
and
retrieve
:

private static void insert(JdbcConnectionPool pool, LocalDate born) throws SQLException {
try (Connection conn = pool.getConnection();
Statement statement = conn.createStatement()) {
statement.execute("CREATE TABLE people (id BIGINT NOT NULL AUTO_INCREMENT"
+ ", born DATE NOT NULL, PRIMARY KEY (id) );");
statement.execute("INSERT INTO people (born) VALUES ('" + born + "')");
}
}

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


Notice that the
insert
method uses the
toString
value of the
LocalDate
, so there's no possibility of a timezone error here. Now add a
main
to call
insert
once and then several times
retrieve
, using different timzone settings, and print the results. Like this:

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


In this case, the following was printed:


-14 2015-05-20

+00 2015-05-19

+12 2015-05-18


So there are some configurations where the correct date
2015-05-20
is not printed. Is there a way to make the
retrieve
method "robust", so that it always returns the correct date, independent of the system timezone, current time etc? Also, is it possible to avoid the intermediate
java.sql.Date
?

Answer

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

-14 2015-05-20
+00 2015-05-20
+12 2015-05-20
Comments