Spring Boot多数据源查询时HikariDataSource自动关闭问题排查

问题现象 #

配置主从数据库后，当从库可用时，请求路由到从库查询会抛出以下异常：

o.h.engine.jdbc.spi.SqlExceptionHelper  (137)    |> SQL Error: 0, SQLState: null
[ http-nio-2100-exec-6 ] |ERROR|    o.h.engine.jdbc.spi.SqlExceptionHelper  (142)    |> HikariDataSource HikariDataSource (REPORT_POOL) has been closed.
 [ http-nio-2100-exec-6 ] |ERROR|    c.n.c.exception.GlobalExceptionHandler  (485)   |> Critical Error: 
org.springframework.orm.jpa.JpaSystemException: Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection

当应用启动时从库不可用，自动切换到主库则无问题。

核心原因 #

问题出在readDataSource()方法的实现中，使用了try-with-resources语法创建HikariDataSource。该语法会在代码块执行完成后自动调用资源的close()方法，导致从库数据源被提前关闭，后续路由到从库时无法获取有效连接。

问题代码片段：

try (HikariDataSource readDataSource = new HikariDataSource(report)) {
    readDataSource.getConnection();
    return readDataSource;
} catch (Exception e) {
    log.warn("\n\n******REPORT DB NOT FOUND, CONNECTED TO MASTER DB *******\n\n");
    return masterDataSource;
}

修复方案 #

1. 修改从库数据源创建逻辑 #

移除try-with-resources语法，单独测试连接并处理异常，确保数据源实例不会被提前关闭：

private DataSource readDataSource() {
    HikariConfig report = new HikariConfig();
    report.setPoolName("REPORT_POOL");
    report.setJdbcUrl(env.getProperty(String.format("%s.url", REPORT)));
    report.setUsername((env.getProperty(String.format("%s.username", REPORT))));
    report.setPassword((env.getProperty(String.format("%s.password", REPORT))));
    report.setMinimumIdle(Integer.valueOf(env.getProperty(String.format("%s.minimum-idle", REPORT))));
    report.setMaximumPoolSize(Integer.valueOf(env.getProperty(String.format("%s.maximum-pool-size", REPORT))));
    report.setConnectionTimeout(Long.valueOf(env.getProperty(String.format("%s.connection-timeout", REPORT))));
    report.setIdleTimeout(Long.valueOf(env.getProperty(String.format("%s.idle-timeout", REPORT))));
    report.setMaxLifetime(Long.valueOf(env.getProperty(String.format("%s.max-lifetime", REPORT))));
    report.setLeakDetectionThreshold(Long.valueOf(env.getProperty(String.format("%s.leakDetection", REPORT))));
    report.setAutoCommit(Boolean.valueOf(env.getProperty(String.format("%s.auto-commit", REPORT))));
    // 注意：配置文件中无read-only项，需注释或添加对应配置
    // report.setReadOnly(Boolean.valueOf(env.getProperty(String.format("%s.read-only", REPORT))));

    HikariDataSource readDataSource = new HikariDataSource(report);
    try (Connection conn = readDataSource.getConnection()) {
        // 测试连接有效性
    } catch (Exception e) {
        log.warn("\n\n******REPORT DB NOT FOUND, CONNECTED TO MASTER DB *******\n\n");
        readDataSource.close(); // 关闭无效数据源，避免资源泄漏
        return masterDataSource;
    }
    return readDataSource;
}

2. 清理ThreadLocal路由标记 #

DataSourceInterceptor中设置的ThreadLocal变量未在请求结束后清理，会导致线程复用（如Tomcat线程池）时路由逻辑混乱。添加清理逻辑：

@Slf4j
@Component
public class DataSourceInterceptor extends HandlerInterceptorAdapter {

    protected static final String[] PREFIX_REPORT_DS = new String[] { "/admin/report/**", "/report/**" };

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
            throws Exception {

        String uri = request.getRequestURI();

        if (StringUtil.isStartWith(uri, StringUtil.trimStricks(PREFIX_REPORT_DS))) {
            log.info("Redirect to Report Database for URL: {}", uri);
            DataSourceRouting.setReportRoute();
        }

        return true;
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {
        // 清理ThreadLocal，避免线程复用导致的路由错误
        DataSourceRouting.routeContext.remove();
    }
}

3. 修复配置缺失问题 #

代码中尝试读取report.datasource.hikari.read-only配置，但你的配置文件中没有该项，会触发NullPointerException。需二选一：

• 在配置文件中添加report.datasource.hikari.read-only=true
• 注释掉代码中report.setReadOnly(...)这一行

额外建议 #

• 确保主从数据源由Spring容器管理，避免手动创建的实例被意外回收
• 可以为从库数据源单独声明@Bean，提高代码的可维护性和Spring的生命周期管理能力

内容的提问来源于stack exchange，提问作者Sudan Shrestha