Spring JDBC + Postgres SQL + Java 8 - conversion from/to LocalDate

Question

I am using Postgres SQL 9.2, Spring JDBC with version 4.0.5, and Java 8.
Java 8 introduced new date/time API and I would like to use it, but I encountered some difficulties. I have created table TABLE_A:

CREATE TABLE "TABLE_A"
(
  new_date date,
  old_date date
)

I am using Spring JDBC to communicate with database. I have created Java class, which corresponds to this table:

public class TableA
{
    private LocalDate newDate;
    private Date oldDate;
    //getters and setters

}

this is my code which is reponsible for inserting new row:

public void create(TableA tableA)
{
    BeanPropertySqlParameterSource parameterSource = new BeanPropertySqlParameterSource(tableA);
    final String sql = "INSERT INTO public.TABLE_A (new_date,old_date) values(:newDate,:oldDate)";
    namedJdbcTemplate.update(sql,parameterSource);

}

When I executed this method I got exception:

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.time.LocalDate. Use setObject() with an explicit Types value to specify the type to use.

so I updated cretion of BeanPropertySqlParameterSource:

BeanPropertySqlParameterSource parameterSource = new BeanPropertySqlParameterSource(tableA);
parameterSource.registerSqlType("newDate", Types.DATE); 

after that change I was able to insert row. But next, I would like to fetch rows from database. Here is my method:

public List<TableA> getAll()
{
    final String sql = "select * from public.TABLE_A";
    final BeanPropertyRowMapper<TableA> rowMapper = new BeanPropertyRowMapper<TableA>(TableA.class);
    return namedJdbcTemplate.query(sql,rowMapper);
}

and of course I got exception:

...
at org.springframework.beans.BeanWrapperImpl.convertIfNecessary(BeanWrapperImpl.java:474)
at org.springframework.beans.BeanWrapperImpl.convertForProperty(BeanWrapperImpl.java:511)
at org.springframework.beans.BeanWrapperImpl.setPropertyValue(BeanWrapperImpl.java:1119)
at org.springframework.beans.BeanWrapperImpl.setPropertyValue(BeanWrapperImpl.java:902)
at org.springframework.jdbc.core.BeanPropertyRowMapper.mapRow(BeanPropertyRowMapper.java:255)
...
Caused by: java.lang.IllegalStateException: Cannot convert value of type [java.sql.Date] to required type [java.time.LocalDate] for property 'newDate': no matching editors or conversion strategy found.

So I updated my code, this time BeanPropertyRowMapper, I have added conversion service to bean wrapper, which is able to perform conversion from java.sql.Date to java.time.LocalDate

public List<TableA> getAll()
{
    final String sql = "select * from public.TABLE_A";
    final BeanPropertyRowMapper<TableA> rowMapper = new BeanPropertyRowMapper<TableA>(TableA.class)
    {
        @Override
        protected void initBeanWrapper(BeanWrapper bw) {
            super.initBeanWrapper(bw);
           bw.setConversionService(new ConversionService() {
               @Override
               public boolean canConvert(Class<?> aClass, Class<?> aClass2) {
                   return aClass == java.sql.Date.class && aClass2 == LocalDate.class;
               }

               @Override
               public boolean canConvert(TypeDescriptor typeDescriptor, TypeDescriptor typeDescriptor2) {
                   return canConvert(typeDescriptor.getType(), typeDescriptor2.getType());
               }

               @Override
               public <T> T convert(Object o, Class<T> tClass) {
                   if(o instanceof Date && tClass == LocalDate.class)
                   {
                       return (T)((Date)o).toLocalDate();
                   }

                   return null;


       }

           @Override
           public Object convert(Object o, TypeDescriptor typeDescriptor, TypeDescriptor typeDescriptor2) {
               return convert(o,typeDescriptor2.getType());
           }
       });
    }
}   ;

return namedJdbcTemplate.query(sql,rowMapper);

and now everything works, but it is quite complicated.
Is it easier way to achieve that? Generally speaking, I would like to operate on LocalDate in my Java code, because it is much more convenient, and be able to persist it to database. I would expect that it should be enabled by default.

Solution 1

New Date & Date API support with JDBC is defined by JEP 170: JDBC 4.2. Postgres download page compatibility with JDBC 4.2 new features only starts as of the Postgres version 9.4, so some compatibility challenges will pop up using the new API with older drivers.

Even setObject(1, new java.util.Date()); is rejected by the same constraint in Postgres (which is happily accepted by MySQL), not only the the new API like LocalDate. Some behaviors will be implementation dependent, so only java.sql.* is guaranteed pretty much (roughly speaking).

---

As for the Spring JDBC framework, I think overriding its behavior works to get around it without regretting it later. I suggest a slightly different approach for what you already did:

1. Extend BeanPropertySqlParameterSource behavior to work with the new date & time API, and other classes associated with parameters input if needed (I am not familiar with that Spring API).
2. Extract the already overrided behavior of BeanPropertyRowMapper to another class for fetching operations.
3. Wrap it all up with a factory pattern or utility class so you don't have to look at it again.

This way you enhance future refactoring capabilities if API gets supported and reduce amount of code needed during development.

You could also look at some DAO approaches.

Solution 2

Please note Java 8 Date and Time API (JSR-310) supported but implementation is not complete: https://jdbc.postgresql.org/documentation/head/8-date-time.html quote:

Note that ZonedDateTime, Instant and OffsetTime / TIME [ WITHOUT TIMEZONE ] are not supported.

Comments

[Broken_Window]

I fixed my org.postgresql.util.PSQLException: Cant infer the SQL type to use for an instance of java.time.LocalDate. Use setObject() with an explicit Types value to specify the type to use exception updating my postgres JDBC from 9.4 Build 1206 to 9.4.1212 version

[Hubbitus]

By that link description does not changed. What you mean say it is not true anymore?