Jooq Converter: from java.sql.Date to java.time.LocalDate

Question

I tried to write Converter<java.sql.Date, java.time.LocalDate> , but I can not get it to work with all the time zone settings.

Idea:

• If the client code has LocalDate , say, on August 20, 2014 and stores it in the database, it should appear in the database as on August 20, 2014, regardless of the client’s time zone.
• If the database contains the date of August 20, 2014, the client must receive the LocalDate of August 20, 2014, regardless of the client’s time zone.

My test:

 @Test public void dateConverter() { for (int offset = -12; offset <= 12; offset++) { TimeZone localTz = TimeZone.getTimeZone(ZoneOffset.ofHours(offset)); TimeZone.setDefault(localTz); LocalDate ld = LocalDate.now(); sql.insertInto(DATE_TEST).set(new DateTestRecord(ld)).execute(); LocalDate savedLd = sql.selectFrom(DATE_TEST).fetchOne(DATE_TEST.DATE_); assertEquals(savedLd, ld, "offset=" + offset); sql.delete(DATE_TEST).execute(); } } 

My converter:

 public class DateConverter implements Converter<Date, LocalDate>{ @Override public LocalDate from(Date date) { return date.toLocalDate(); } @Override public Date to(LocalDate ld) { return Date.valueOf(ld); } @Override public Class<Date> fromType() { return Date.class; } @Override public Class<LocalDate> toType() { return LocalDate.class; } } 

I tried different options but no one worked ...

Answer 1

The problem is actually in the test! The JDBC driver caches the time zone when it is created, and time zone updates were not taken into account in the test cycle. Accepting a new connection every time a time change in a zone changes, the test passes.

So, the code in the question works for the Date to LocalDate converter (except that it must be null). Final version:

 public class DateConverter implements Converter<Date, LocalDate> { @Override public LocalDate from(Date date) { return date == null ? null : date.toLocalDate(); } @Override public Date to(LocalDate ld) { return ld == null ? null : Date.valueOf(ld); } @Override public Class<Date> fromType() { return Date.class; } @Override public Class<LocalDate> toType() { return LocalDate.class; } } 

Time with the time zone to the OffsetTime converter can be performed in a similar way:

 public class TimeConverter implements Converter<Time, OffsetTime> { @Override public OffsetTime from(Time time) { return time == null ? null : OffsetTime.ofInstant(Instant.ofEpochMilli(time.getTime()), ZoneOffset.systemDefault()); } @Override public Time to(OffsetTime offsetTime) { return offsetTime == null ? null : new Time(offsetTime.atDate(LocalDate.ofEpochDay(0)).toInstant().toEpochMilli()); } @Override public Class<Time> fromType() { return Time.class; } @Override public Class<OffsetTime> toType() { return OffsetTime.class; } }