ERROR DruidDataSource java.sql.SQLException: validateConnection false问题解决

问题：

Spring Boot 集成 Shardingsphere和Shardings-jdbc后，使用properties配置完成后，启动项目遇如下错误:

ERROR DruidDataSource create connection error, errorCode 0, state null java.sql.SQLException: validateConnection false

完整错误如下：

org.springframework.dao.InvalidDataAccessApiUsageException: ConnectionCallback; isValid; nested exception is java.sql.SQLFeatureNotSupportedException: isValid
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:96) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:336) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
	at org.springframework.boot.actuate.jdbc.DataSourceHealthIndicator.isConnectionValid(DataSourceHealthIndicator.java:130) ~[spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.jdbc.DataSourceHealthIndicator.doDataSourceHealthCheck(DataSourceHealthIndicator.java:116) ~[spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.jdbc.DataSourceHealthIndicator.doHealthCheck(DataSourceHealthIndicator.java:100) ~[spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.health.AbstractHealthIndicator.health(AbstractHealthIndicator.java:82) ~[spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.health.HealthIndicator.getHealth(HealthIndicator.java:37) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.health.HealthEndpoint.getHealth(HealthEndpoint.java:71) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.health.HealthEndpoint.getHealth(HealthEndpoint.java:39) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.health.HealthEndpointSupport.getContribution(HealthEndpointSupport.java:99) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.health.HealthEndpointSupport.getAggregateHealth(HealthEndpointSupport.java:110) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.health.HealthEndpointSupport.getContribution(HealthEndpointSupport.java:96) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.health.HealthEndpointSupport.getHealth(HealthEndpointSupport.java:74) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.health.HealthEndpointSupport.getHealth(HealthEndpointSupport.java:61) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.health.HealthEndpoint.health(HealthEndpoint.java:65) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.health.HealthEndpoint.health(HealthEndpoint.java:55) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_221]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_221]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_221]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_221]
	at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:282) [spring-core-5.2.10.RELEASE.jar:5.2.10.RELEASE]
	at org.springframework.boot.actuate.endpoint.invoke.reflect.ReflectiveOperationInvoker.invoke(ReflectiveOperationInvoker.java:77) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.endpoint.annotation.AbstractDiscoveredOperation.invoke(AbstractDiscoveredOperation.java:60) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.endpoint.jmx.EndpointMBean.invoke(EndpointMBean.java:121) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.boot.actuate.endpoint.jmx.EndpointMBean.invoke(EndpointMBean.java:96) [spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:819) [na:1.8.0_221]
	at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:801) [na:1.8.0_221]
	at javax.management.remote.rmi.RMIConnectionImpl.doOperation(RMIConnectionImpl.java:1468) [na:1.8.0_221]
	at javax.management.remote.rmi.RMIConnectionImpl.access$300(RMIConnectionImpl.java:76) [na:1.8.0_221]
	at javax.management.remote.rmi.RMIConnectionImpl$PrivilegedOperation.run(RMIConnectionImpl.java:1309) [na:1.8.0_221]
	at javax.management.remote.rmi.RMIConnectionImpl.doPrivilegedOperation(RMIConnectionImpl.java:1401) [na:1.8.0_221]
	at javax.management.remote.rmi.RMIConnectionImpl.invoke(RMIConnectionImpl.java:829) [na:1.8.0_221]
	at sun.reflect.GeneratedMethodAccessor84.invoke(Unknown Source) ~[na:na]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_221]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_221]
	at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:357) [na:1.8.0_221]
	at sun.rmi.transport.Transport$1.run(Transport.java:200) [na:1.8.0_221]
	at sun.rmi.transport.Transport$1.run(Transport.java:197) [na:1.8.0_221]
	at java.security.AccessController.doPrivileged(Native Method) [na:1.8.0_221]
	at sun.rmi.transport.Transport.serviceCall(Transport.java:196) [na:1.8.0_221]
	at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:573) [na:1.8.0_221]
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:834) [na:1.8.0_221]
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:688) [na:1.8.0_221]
	at java.security.AccessController.doPrivileged(Native Method) [na:1.8.0_221]
	at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:687) [na:1.8.0_221]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_221]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_221]
	at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_221]
Caused by: java.sql.SQLFeatureNotSupportedException: isValid
	at org.apache.shardingsphere.shardingjdbc.jdbc.unsupported.AbstractUnsupportedOperationConnection.isValid(AbstractUnsupportedOperationConnection.java:157) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_221]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_221]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_221]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_221]
	at org.springframework.jdbc.core.JdbcTemplate$CloseSuppressingInvocationHandler.invoke(JdbcTemplate.java:1525) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
	at com.sun.proxy.$Proxy146.isValid(Unknown Source) ~[na:na]
	at org.springframework.boot.actuate.jdbc.DataSourceHealthIndicator.isConnectionValid(DataSourceHealthIndicator.java:134) ~[spring-boot-actuator-2.3.5.RELEASE.jar:2.3.5.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:328) ~[spring-jdbc-5.2.10.RELEASE.jar:5.2.10.RELEASE]
	... 46 common frames omitted

原因：

数据库健康检查出错了

解决方法：

方法1：最直接的，加配置

management:
  health:
    db:
      enabled: false

完整如下图:

来自于这个配置：

方法2：自定义配置类，继承DataSourceHealthContributorAutoConfiguration，重写方法。

注意： 只适用于Spring Boot 2.3，后面版本有修改，所以推荐用方法1。

@Configuration
public class DataSourceHealthConfig extends DataSourceHealthContributorAutoConfiguration {
 
    @Value("${spring.datasource.dbcp2.validation-query:select 1}")
    private String defaultQuery;
 
 
    public DataSourceHealthConfig(Map<String, DataSource> dataSources, ObjectProvider<DataSourcePoolMetadataProvider> metadataProviders) {
        super(dataSources, metadataProviders);
    }
 
    @Override
    protected AbstractHealthIndicator createIndicator(DataSource source) {
        DataSourceHealthIndicator indicator = (DataSourceHealthIndicator) super.createIndicator(source);
        if (!StringUtils.hasText(indicator.getQuery())) {
            indicator.setQuery(defaultQuery);
        }
        return indicator;
    }
}

改完之后，重启项目就好了。