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.
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:
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).BeanPropertyRowMapper
to another class for fetching operations.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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With