Spring JDBC + Postgres SQL + Java 8 - 从/到LocalDate的转换 [英] Spring JDBC + Postgres SQL + Java 8 - conversion from/to LocalDate

问题描述

我正在使用Postgres SQL 9.2，Spring JDBC版本4.0.5和Java 8.

Java 8引入了新的日期/时间API，我想使用它，但是我遇到了一些困难。
我创建了表TABLE_A：

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
)

我使用Spring JDBC与数据库通信。我创建了Java类，它对应于这个表：

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.

所以我更新了BeanPropertySqlParameterSource的创建：

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);
}

当然我得到例外：

...
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.

所以我更新了我的代码，这次是BeanPropertyRowMapper，我已经为bean包装器添加了转换服务，这是能够执行从java.sql.Date到java.time.LocalDate的转换

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);

现在一切正常，但它很复杂。

更容易实现这一目标的方法？
一般来说，我想在我的Java代码中运行LocalDate，因为它更方便，并且能够将它持久化到数据库。我希望默认情况下应该启用它。

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.

推荐答案

新日期&使用JDBC的Date API支持由 JEP 170：JDBC 4.2 定义。 Postgres 下载页面与JDBC 4.2新功能的兼容性仅从Postgres版本9.4开始，因此使用旧版驱动程序的新API会弹出一些兼容性挑战。

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.

甚至 setObject（1，new java.util.Date（））; 被Postgres中的相同约束（MySQL很乐意接受）拒绝，而不仅仅是新的API，如 LocalDate 。一些行为将依赖于实现，因此只有 java.sql。* 得到保证（粗略地说）。

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).

对于Spring JDBC框架，我认为重写它的行为可以解决它，而不会后悔。我建议你采用一种略有不同的方法：

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. 扩展 BeanPropertySqlParameterSource 行为使用新的日期& time API，以及与参数输入相关的其他类（如果需要）（我不熟悉Spring API）。


2. 提取已经被覆盖的 BeanPropertyRowMapper行为到另一个类来获取操作。


3. 用工厂模式或实用程序类包装它，这样你就不用再看它了。

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.

这样，如果API得到支持，您可以增强未来的重构功能，并减少开发过程中所需的代码量。

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

您还可以查看一些 DAO 方法。

You could also look at some DAO approaches.

这篇关于Spring JDBC + Postgres SQL + Java 8 - 从/到LocalDate的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！