连接未关闭

Question

 org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30002ms.
DEBUG - HikariPool-1 - Pool stats (total=10, active=9, idle=1, waiting=0)

spring.datasource.hikari.leakDetectionThreshold=2000

java.lang.Exception: Apparent connection leak detected
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
at com.clarivate.singularity.chemworkbench.dcrws.database.ReadFromDb.readStructureSearch(ReadFromDb.java:187)

给出错误的方法如下：

public class myClass(){
@Autowired
 NamedParameterJdbcTemplate  jdbcTemplate;  

@Transactional
public List<DcrData> readStructureSearch(String sqlStr,  String fileData) throws SQLException  {
    List<DcrData> dcrDataList = null;
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
try {
    conn =  this.jdbcTemplate.getJdbcTemplate().getDataSource().getConnection().unwrap(OracleConnection.class); // <<<< This is line 187 mentioned above which is giving the error
    Clob myClob =  conn.createClob();

    int ret = myClob.setString( 1, fileData);

    ps = conn.prepareStatement(sqlStr);
    ps.setClob(1,myClob);       // This works, Types.CLOB doesn't work.

    rs = ps.executeQuery();

    ResultSetHandler<List<DcrData>> handler = 
            new BeanListHandler<DcrData>(DcrData.class, new BasicRowProcessor(new GenerousBeanProcessor()));

    dcrDataList = handler.handle(rs);
    return  dcrDataList;
}finally {
    if (rs != null) {
        rs.close();
    }
    if (ps != null) {
        ps.close();
    }
    if (conn != null) {
        conn.close();
    }
    logger.info("DSxxx4");
}
}

如何创建一个没有泄漏的连接？为什么Hikari认为我的conn.close（）方法没有关闭连接？任何想法都很欣赏。

Answer 1

我对函数在做什么有点困惑。它似乎在CLOB上运行select约束，但我可能错了。我在不知道的情况下很难重现这个问题。

也就是说，这里有几件事可以尝试。首先，您不需要NamedParameterJdbcTemplate，只需要一个数据源。试着自动取而代之。其次，似乎没有理由获取原始Oracle连接本身，您可以直接使用数据源返回的连接。第三，try with resources模式使代码更容易遵循Java将自动处理关闭资源。它在Java8中可用。最后，释放CLOB可能会有所帮助。

下面是我提到的一个示例：

public class MyClass {

    @Autowired
    DataSource dataSource;

    @Transactional
    public List<DcrData> readStructureSearch(String sqlStr, String fileData) throws SQLException {

        try (Connection conn = this.dataSource.getConnection()) {
            Clob myClob = conn.createClob();

            myClob.setString(1, fileData);

            try (PreparedStatement ps = conn.prepareStatement(sqlStr)) {
                ps.setClob(1, myClob);

                try (ResultSet rs = ps.executeQuery()) {
                    ResultSetHandler<List<DcrData>> handler =
                            new BeanListHandler<>(DcrData.class, new BasicRowProcessor(new GenerousBeanProcessor()));

                    List<DcrData> dcrDataList = handler.handle(rs);

                    myClob.free();

                    return dcrDataList;
                }
            }
        }
    }
}