可能发生的报错信息:
Cause: java.lang.ClassCastException: java.sql.Timestamp cannot be cast to java.time.LocalDateTime;
或
Cause: java.lang.ClassCastException: java.sql.Timestamp cannot be cast to java.time.LocalDate;
解决方案:
1.组件版本升级
mybatis-spring-boot-starter 升级至->2.1.1
com.alibaba.druid 升级至->1.2.1
mysql-connector-java 升级至->8.0.15;
2.编写 LocalDateTimeTypeHandler类
升级之后如果如果还有问题 查询可能会有以下报错: org.springframework.dao.InvalidDataAccessApiUsageException: Error attempting to get column 'orderTime' from result set. Cause: java.sql.SQLFeatureNotSupportedException: getObject with type ; SQL []; getObject with type; nested exception is java.sql.SQLFeatureNotSupportedException: getObject with type Caused by: java.sql.SQLFeatureNotSupportedException: getObject with type at io.shardingjdbc.core.jdbc.unsupported.AbstractUnsupportedOperationResultSet.getObject(AbstractUnsupportedOperationResultSet.java:223) at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:38) at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:28)
在自己项目下创建mybatis相同包路径org.apache.ibatis.type的LocalDateTimeTypeHandler类:
package org.apache.ibatis.type; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.TypeHandler; import java.sql.*; import java.time.Instant; import java.time.LocalDateTime; import java.time.ZoneOffset; public class MyLocalDateTimeTypeHandler implements TypeHandler<LocalDateTime> { @Override public void setParameter(PreparedStatement preparedStatement, int i, LocalDateTime localDateTime, JdbcType jdbcType) throws SQLException { if(localDateTime == null) { preparedStatement.setTimestamp(i,null); }else { long epochMilli = localDateTime.toInstant(ZoneOffset.of("+8")).toEpochMilli(); Timestamp date = new Timestamp(epochMilli); preparedStatement.setTimestamp(i,date); } } @Override public LocalDateTime getResult(ResultSet resultSet, String columnName) throws SQLException { Timestamp timestamp = resultSet.getTimestamp(columnName); if(timestamp == null) { return null; } else { final Instant instant = timestamp.toInstant(); return instant.atZone(ZoneOffset.of("+8")).toLocalDateTime(); } } @Override public LocalDateTime getResult(ResultSet resultSet, int columnIndex) throws SQLException { Date date = resultSet.getDate(columnIndex); if(date == null) { return null; } else{ final Instant instant = date.toInstant(); return instant.atZone(ZoneOffset.of("+8")).toLocalDateTime(); } } @Override public LocalDateTime getResult(CallableStatement callableStatement, int columnIndex) throws SQLException { Date date = callableStatement.getDate(columnIndex); if(date == null) { return null; } else { final Instant instant = date.toInstant(); return instant.atZone(ZoneOffset.of("+8")).toLocalDateTime(); } } }
如果使用sharedingJdbc分表组件时
用了LocalDate类型的字段作为分表逻辑,则可能还会出现java.sql.Date can not be cast to java.time.localDate, 此时localDate类型的分表字段应使用 java.sql.Date,而不应该使用LocalDate, 原因也是显而易见的,因为分表逻辑在PreparedStatment.setParameter()之后,代码清单如下所示:
public final class DayShardingTableAlgorithm implements PreciseShardingAlgorithm<Date> { @Override public String doSharding(Collection<String> tableNames, PreciseShardingValue<Date> shardingValue) { java.util.Date date = new java.util.Date(shardingValue.getValue().getTime()); final LocalDate localDate = date.toInstant().atZone(ZoneOffset.of("+8")).toLocalDate(); return tableNames.stream().filter(e ->e.endsWith(String.valueOf(localDate.getDayOfMonth()))).findFirst().orElseThrow(() -> new UnsupportedOperationException()); } }
参考: https://www.jianshu.com/p/d5ad2dc7ef5b
- LocalDateTime 分表 LocalDate sharedingJdbc 组件localdatetime分表localdate sharedingjdbc localdatetime chronounit localdate localtime localdatetime zonedatetime localdate localtime localdatetime localdate localtime jdk1 localdatetime construct localdate instance localdatetime localdate calendar日期 localdatetime localdate string date localdatetime localtime localdate大全 localdatetime localdate之间 localdatetime localdate localtime时间