postgresql Spring JDBC + Postgres SQL + Java 8 - 从/到 LocalDate 的转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25536969/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spring JDBC + Postgres SQL + Java 8 - conversion from/to LocalDate
提问by Adam Szecowka
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:
我使用的是 Postgres SQL 9.2、4.0.5 版的 Spring JDBC 和 Java 8。Java
8 引入了新的日期/时间 API，我想使用它，但我遇到了一些困难。我创建了表 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:
我正在使用 Spring JDBC 与数据库进行通信。我已经创建了 Java 类，它对应于这个表：
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 的创建：
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
所以我更新了我的代码，这次 BeanPropertyRowMapper，我添加了转换服务到 bean 包装器，它能够执行从 java.sql.Date 到 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.
现在一切正常，但它非常复杂。
是否有更简单的方法来实现这一目标？一般来说，我想在我的Java代码中对LocalDate进行操作，因为它更方便，并且能够将其持久化到数据库中。我希望它应该默认启用。
采纳答案by Evandro Pomatti
New Date & Date API support with JDBC is defined by JEP 170: JDBC 4.2. Postgres download pagecompatibility 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.
JEP 170: JDBC 4.2定义了对 JDBC 的新日期和日期 API 支持。Postgres下载页面与 JDBC 4.2 新特性的兼容性仅从 Postgres 9.4 版开始，因此使用带有旧驱动程序的新 API 会出现一些兼容性挑战。
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).
EvensetObject(1, new java.util.Date());被 Postgres 中的相同约束拒绝（它被 MySQL 愉快地接受），不仅是像LocalDate. 某些行为将取决于实现，因此只能java.sql.*保证（粗略地说）。
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:
至于 Spring JDBC 框架，我认为覆盖它的行为可以绕过它而不会在以后后悔。对于您已经做过的事情，我建议采用稍微不同的方法：
- Extend
BeanPropertySqlParameterSourcebehavior 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). - Extract the already overrided behavior of
BeanPropertyRowMapperto another class for fetching operations. - Wrap it all up with a factory pattern or utility class so you don't have to look at it again.
BeanPropertySqlParameterSource如果需要，扩展行为以使用新的日期和时间 API，以及与参数输入相关的其他类（我不熟悉那个 Spring API）。- 将已覆盖的行为提取
BeanPropertyRowMapper到另一个类以进行获取操作。 - 用工厂模式或实用程序类将其全部包裹起来，这样您就不必再看一遍了。
This way you enhance future refactoring capabilities if API gets supported and reduce amount of code needed during development.
如果 API 得到支持，您可以通过这种方式增强未来的重构能力，并减少开发过程中所需的代码量。
You could also look at some DAOapproaches.
您还可以查看一些DAO方法。
回答by Hubbitus
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.htmlquote:
请注意 Java 8 Date and Time API (JSR-310) 支持但实现不完整：https: //jdbc.postgresql.org/documentation/head/8-date-time.html引用：
Note that ZonedDateTime, Instant and OffsetTime / TIME [ WITHOUT TIMEZONE ] are not supported.
