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

Question

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

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

I am using Spring JDBC to communicate with the database. I created a Java class that corresponds to this table:

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

this is my code which is not acceptable for inserting a new line:

 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 an 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 the creation of BeanPropertySqlParameterSource:

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

after this change i managed to insert a row. But then I would like to get the rows from the 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 an 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 added a conversion service to the bean shell, which can 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's pretty complicated.
Is it easier to achieve? Generally speaking, I would like to work with LocalDate in my Java code, because it is much more convenient and will be able to store it in the database . I would suggest that it should be enabled by default.

Answer 1

Support for new API dates and dates using JDBC is defined by JEP 170: JDBC 4.2 . Postgres download JDBC 4.2 compatibility page new features only begin with Postgres 9.4, so some compatibility issues will pop up using the new API with older drivers.

Even setObject(1, new java.util.Date()); rejected by the same restriction in Postgres (which MySQL happily accepted), and not just a new API, such as LocalDate . Some behavior will be implementation dependent, so only java.sql.* guaranteed to a large extent (roughly).

---

Regarding the Spring JDBC framework, I think redefining its behavior helps get around it without regretting it later. I suggest a slightly different approach to what you have already done:

• Extend the BeanPropertySqlParameterSource behavior to work with the new date and time API and other classes related to its parameters (I am not familiar with this Spring API).
• Retrieve the already overridden BeanPropertyRowMapper behavior in another class for select operations.
• Wrap everything with a template or factory utility class so you don't have to look at it again.

This way you will improve refactoring capabilities in the future if the API gets support and reduces the amount of code needed during development.

You can also look at the DAO .

Answer 2

Note: Java 8 Date and Time API support (JSR-310) is supported, but implementation is not complete: https://jdbc.postgresql.org/documentation/head/8-date-time.html Note that ZonedDateTime, Instant and OffsetTime / TIME [WITHOUT TIMEZONE] are not supported.