记一次Shard JDBC的坑（SQLFeatureNotSupportedException: getObject with type）

环境

Mybatis: 3.5.2+
JDBC: shardingsphere-jdbc 4.1.1

错误日志

Error attempting to get column 'create_time' from result set.  Cause: java.sql.SQLFeatureNotSupportedException: getObject with type
; getObject with type; nested exception is java.sql.SQLFeatureNotSupportedException: getObject with type
org.springframework.dao.InvalidDataAccessApiUsageException: Error attempting to get column 'update_time' from result set.  Cause: java.sql.SQLFeatureNotSupportedException: getObject with type
; getObject with type; nested exception is java.sql.SQLFeatureNotSupportedException: getObject with type
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:96)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
	at com.sun.proxy.$Proxy193.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)
	...
	Caused by: java.sql.SQLFeatureNotSupportedException: getObject with type
	at org.apache.shardingsphere.shardingjdbc.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)
	at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:81)

让我们来找一下原因，定位到错误提示的最后一句，发现问题出在Mybatis的LocalDateTimeTypeHandler这个类型转化器上，代码如下：

public class LocalDateTimeTypeHandler extends BaseTypeHandler<LocalDateTime> {
    public LocalDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return (LocalDateTime)rs.getObject(columnName, LocalDateTime.class);
    }}

Mybatis的TypeHandelr试图调用ResultSet.getObject()获取结果，但是JDBC却说我们不支持这玩意儿？

我们去Mybatis那边看一下，发现了一些端倪。下方是Mybatis的更新报告

解决方案

改写LocalDateTimeTypeHandler（既然JDBC不支持(LocalDateTime)rs.getObject(),那我们就不用好了，用自定义的转换器去替换Mybatis的转换器）。如下：

@Component
//定义转换器支持的JAVA类型
@MappedTypes(LocalDateTime.class)
//定义转换器支持的数据库类型
@MappedJdbcTypes(value = JdbcType.TIMESTAMP, includeNullJdbcType = true)
public class CustomLocalDateTimeTypeHandler extends BaseTypeHandler<LocalDateTime> {
   private final DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

   @Override
   public void setNonNullParameter(PreparedStatement ps, int i, LocalDateTime parameter, JdbcType jdbcType)
           throws SQLException {
       if (parameter != null) {
           ps.setString(i, dateTimeFormatter.format(parameter));
       }
   }

   @Override
   public LocalDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
       String target = rs.getString(columnName);
       if (StringUtil.isEmpty(target)) {
           return null;
       }
       return LocalDateTime.parse(target, dateTimeFormatter);
   }
       @Override
   public LocalDateTime getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
       String target = rs.getString(columnIndex);
       if (StringUtil.isEmpty(target)) {
           return null;
       }
       return LocalDateTime.parse(target, dateTimeFormatter);
   }

   @Override
   public LocalDateTime getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
       String target = cs.getString(columnIndex);
       if (StringUtil.isEmpty(target)) {
           return null;
       }
       return LocalDateTime.parse(target, dateTimeFormatter);
   }