Lars Bohl Lars Bohl - 1 year ago 250
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
column, and convert to
, which seems to be an ideal representation in Java. Retrieving
directly or going through
are both o.k., but the result should always be correct (see below).

The example uses the maven artifact

First, define methods

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 ( {
java.sql.Date retrieved = (java.sql.Date) rs.getObject("born");
return retrieved.toLocalDate();
throw new IllegalStateException("No data");

Notice that the
method uses the
value of the
, so there's no possibility of a timezone error here. Now add a
to call
once and then several times
, 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));
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
is not printed. Is there a way to make the
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

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download