使用PreparedStatement生成SQL致Spring中HikariCP连接耗尽问题求助

问题根源 #

你遇到的连接池耗尽问题，核心原因是代码只关闭了PreparedStatement，但创建Statement时占用的数据库连接没有被正确释放回连接池。自定义的getPreparedStatement方法应该是从连接池获取了连接来创建Statement，但你的try-with-resources块仅管理了Statement的生命周期，却没有释放底层连接——连接被持续占用，几次请求后就会把池里的连接耗尽。

另外还有个隐藏问题：依赖PreparedStatement.toString()获取带参数的SQL是不可靠的，不同JDBC驱动的toString实现差异很大，很多驱动只会输出带占位符的原始SQL，不会替换参数值，最终导致crosstab查询出错。

解决方案 #

方案1：避免创建实际PreparedStatement，直接安全生成动态SQL #

不需要通过创建PreparedStatement来拼接带参数的SQL，而是直接基于命名参数模板和参数值生成安全的SQL字符串：

1. 定义带命名参数的SQL模板：

String baseSql1 = "select * from table where 1=1 :filter";
String baseSql2 = "select distinct name from table where 1=1 :filter";

2. 使用Spring的NamedParameterUtils解析SQL并替换参数：

SqlParameterSource paramSource = new MapSqlParameterSource("filter", filterString);
ParsedSql parsedSql1 = NamedParameterUtils.parseSqlStatement(baseSql1);
String sql1 = NamedParameterUtils.substituteNamedParameters(parsedSql1, paramSource);
ParsedSql parsedSql2 = NamedParameterUtils.parseSqlStatement(baseSql2);
String sql2 = NamedParameterUtils.substituteNamedParameters(parsedSql2, paramSource);

3. 用生成的SQL构造crosstab查询：

return String.format("select * from crosstab($$%s$$, $$%s$$) as (%s)", sql1, sql2, columndefinition);

这种方式不会占用任何数据库连接，从根源上避免连接池耗尽问题，同时Spring的工具类会帮你处理参数的转义，降低SQL注入风险。

方案2：正确管理连接和Statement的生命周期 #

如果必须保留PreparedStatement的方式，一定要把连接也纳入try-with-resources的管理范围，确保连接能被释放回池：

try(Connection conn = jdbcTemplate.getDataSource().getConnection();
    PreparedStatement stmt1 = conn.prepareStatement(String.format("select * from table where 1=1 %s", filterString));
    PreparedStatement stmt2 = conn.prepareStatement(String.format("select distinct name from table where 1=1 %s", filterString))) {
    // 手动设置参数到PreparedStatement
    // 例如：stmt1.setString(1, paramMap.get("someParam"));
    // ... 根据参数类型逐个设置
    
    // 注意：不同驱动的toString()可能不返回带参数值的SQL，需测试验证
    String sql1 = stmt1.toString();
    String sql2 = stmt2.toString();
    return String.format("select * from crosstab($$%s$$, $$%s$$) as (%s)", sql1, sql2, columndefinition);
} catch (SQLException e) {
    throw new RuntimeException("生成Crosstab SQL失败", e);
}

这里的关键是把Connection放在try-with-resources中，关闭连接时会自动释放回连接池，避免资源泄漏。

方案3：使用PostgreSQL参数化Crosstab（推荐） #

PostgreSQL的crosstab支持参数化查询，不需要把过滤条件硬编码到内部SQL里，而是通过外部参数传递，既安全又高效：

1. 构造带参数占位符的crosstab SQL：

select * from crosstab(
    'select id, name, value from table where 1=1 ' || $1,
    'select distinct name from table where 1=1 ' || $1
) as (id int, col1 text, col2 text, ...) -- 替换为你的列定义

2. 在Java代码中用NamedParameterJdbcTemplate执行查询，直接传递过滤条件参数：

String crosstabSql = "select * from crosstab('select id, name, value from table where 1=1 ' || :filter, 'select distinct name from table where 1=1 ' || :filter) as (%s)".formatted(columndefinition);
MapSqlParameterSource params = new MapSqlParameterSource("filter", filterString);
return namedJdbcTemplate.queryForList(crosstabSql, params);

这种方式完全不需要手动拼接SQL，参数由JDBC驱动安全处理，同时不会占用额外连接，彻底解决连接池耗尽问题。

总结 #

优先选择方案3，它既符合PostgreSQL的最佳实践，又能避免连接泄漏和SQL注入风险；如果需要动态生成SQL，方案1是更安全的选择；方案2仅作为临时兼容方案，需注意驱动的toString()实现差异。

内容的提问来源于stack exchange，提问作者Marko Taht