解决使用sharding-jdbc-spring-boot-starter 造成SQLFeatureNotSupportedException: isValid的问题

一.理解造成的原因

1.sharding没有实现isValid方法

参考类 AbstractUnsupportedOperationConnection

@Override
public final boolean isValid(final int timeout) throws SQLException {
    throw new SQLFeatureNotSupportedException("isValid");
}

2. 调用isValid的原因

参考类DataSourceHealthIndicator

创建DataSourceHealthIndicator时没有传递query进来导致query为空

private DataSource dataSource;

private String query;

private JdbcTemplate jdbcTemplate;

public DataSourceHealthIndicator() {
   this(null, null);
}

/**
 * Create a new {@link DataSourceHealthIndicator} using the specified
 * {@link DataSource}.
 * @param dataSource the data source
 */
public DataSourceHealthIndicator(DataSource dataSource) {
   this(dataSource, null);
}

/**
 * Create a new {@link DataSourceHealthIndicator} using the specified
 * {@link DataSource} and validation query.
 * @param dataSource the data source
 * @param query the validation query to use (can be {@code null})
 */
public DataSourceHealthIndicator(DataSource dataSource, String query) {
   super("DataSource health check failed");
   this.dataSource = dataSource;
   this.query = query;
   this.jdbcTemplate = (dataSource != null) ? new JdbcTemplate(dataSource) : null;
}

开始进行检查,发现query字段为null,开始调用jdbc自带的isValid()方法

@Override
protected void doHealthCheck(Health.Builder builder) throws Exception {
   if (this.dataSource == null) {
      builder.up().withDetail("database", "unknown");
   }
   else {
      doDataSourceHealthCheck(builder);
   }
}

private void doDataSourceHealthCheck(Health.Builder builder) throws Exception {
   builder.up().withDetail("database", getProduct());
   String validationQuery = this.query;
   if (StringUtils.hasText(validationQuery)) {
      builder.withDetail("validationQuery", validationQuery);
      // Avoid calling getObject as it breaks MySQL on Java 7 and later
      List<Object> results = this.jdbcTemplate.query(validationQuery, new SingleColumnRowMapper());
      Object result = DataAccessUtils.requiredSingleResult(results);
      builder.withDetail("result", result);
   }
   else {
      builder.withDetail("validationQuery", "isValid()");
      boolean valid = isConnectionValid();
      builder.status((valid) ? Status.UP : Status.DOWN);
   }
}

private Boolean isConnectionValid() {
   return this.jdbcTemplate.execute((ConnectionCallback<Boolean>) this::isConnectionValid);
}

private Boolean isConnectionValid(Connection connection) throws SQLException {
   return connection.isValid(0);
}

 最后调用AbstractUnsupportedOperationConnection的isValid()方法导致抛出异常

二.解决方法

1.方法一 对shardingsphere进行增强

重新对类AbstractUnsupportedOperationConnection编写

对类org.apache.shardingsphere.driver.jdbc.adapter.AbstractConnectionAdapter进行修改

复写isValid()方法

@Override
    public final boolean isValid(final int timeout) throws SQLException {
        for (Connection connection : cachedConnections.values()) {
            if (!connection.isValid(timeout)) {
                return false;
            }
        }
        return true;
    }

 对类org.apache.shardingsphere.shardingjdbc.jdbc.unsupported.AbstractUnsupportedOperationConnection进行修改

删除isValid()方法

//@Override
//public final boolean isValid(final int timeout) throws SQLException {
//    throw new SQLFeatureNotSupportedException("isValid");
//}

完成后替换2个jar包 sharding-jdbc-orchestration-*.jar 及 sharding-jdbc-core-*.jar

2.方法二暂时关闭actuator对db的监控

配置参数

//application.properties
management.health.db.enabled=false