shardingsphere+mybatis LocalDateTime转换报错java.time.LocalDateTime cannot be cast to java.sql.Timestamp

报错信息

java.time.LocalDateTime cannot be cast to java.sql.Timestamp

Caused by: java.lang.ClassCastException: java.time.LocalDateTime cannot be cast to java.sql.Timestamp
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.resultset.ShardingResultSet.getTimestamp(ShardingResultSet.java:251)
	at sun.reflect.GeneratedMethodAccessor75.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:69)
	at com.sun.proxy.$Proxy166.getTimestamp(Unknown Source)
	at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:38)
	at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:28)
	at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:81)
	... 166 common frames omitted

问题分析

java 实体类中的字段类型为LocalDateTime，mysql 表中字段类型为datetime
未使用shardingsphere之前一切正常，使用shardingsphere之后才出现的这个错误
从报错信息上看是调用了org.apache.shardingsphere.shardingjdbc.jdbc.core.resultset.ShardingResultSet.getTimestamp()方法导致的 类型转换异常

// 在ShardingResultSet中getTimestamp方法如下
@Override
public Timestamp getTimestamp(final String columnLabel) throws SQLException {
	int columnIndex = columnLabelAndIndexMap.get(columnLabel);
    return (Timestamp) ResultSetUtil.convertValue(mergeResultSet.getValue(columnIndex, Timestamp.class), Timestamp.class);
}

在网上查了很多
有说依赖版本不兼容的
有说日期格式不正确的
有说要增加mybatis-typehandlers-jsr310依赖的
还有的说要修改数据库字段类型为timestamp
试了很多方法，问题还是存在，那我就另辟蹊径，下面就是我的解决方法

解决方案

重写LocalDateTimeTypeHandler 避免调用getTimestamp()方法
代码如下

package com.zixiu.framework.handler;

import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;

/**
 * 重写LocalDateTimeTypeHandler ，解决shardingsphere + mybatis LocalDateTime转换问题
 *
 * @author ZiXiu
 */
@Component
public class LocalDateTimeTypeHandler extends org.apache.ibatis.type.LocalDateTimeTypeHandler {
    public LocalDateTimeTypeHandler() {
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, LocalDateTime parameter, JdbcType jdbcType) throws SQLException {
        ps.setTimestamp(i, new Timestamp(this.toTimeMillis(parameter)));
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
        Object object = rs.getObject(columnName);
        return object == null ? null : this.toLocalDateTime(object);
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        Object object = rs.getObject(columnIndex);
        return object == null ? null : this.toLocalDateTime(object);
    }

    @Override
    public LocalDateTime getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        Timestamp sqlTimestamp = cs.getTimestamp(columnIndex);
        return sqlTimestamp != null ? this.toLocalDateTime(sqlTimestamp.getTime()) : null;
    }

    private long toTimeMillis(LocalDateTime dateTime) {
        return dateTime.atZone(ZoneId.systemDefault()).toInstant().toEpochMilli();
    }

    private LocalDateTime toLocalDateTime(long timeMillis) {
        return new Date(timeMillis).toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
    }

    private LocalDateTime toLocalDateTime(Object object) {
        if (object instanceof LocalDateTime) {
            return (LocalDateTime) object;
        } else if (object instanceof java.sql.Date) {
            java.sql.Date date = (java.sql.Date) object;
            return this.toLocalDateTime(date.getTime());
        } else if (object instanceof java.sql.Timestamp) {
            Timestamp sqlTimestamp = (Timestamp) object;
            return this.toLocalDateTime(sqlTimestamp.getTime());
        } else {
            System.out.println("实际类型是：" + object.getClass().getName());
            return null;
        }
    }
}

这样就不会报错了，如果有问题请评论！