2.x mybatis 使用sharedingJdbc分表组件时间类型LocalDateTime或LocalDate，把Timestamp转成LocalDateTime或LocalDate

 

可能发生的报错信息：

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