CODEWITHSUNDEEP
javaoracle-databasespring-boot
DS.
DS.

Reputation: 612

Connection not closing

Im running a SpringBoot 2.0 webService, using Java 8. I had to use a PreparedStatement to handle clobs, so instead of letting Spring create the Oracle connection I had to create my own. Now however I appear to have a connection leak, even though I close the connection after i've finished and , after i've run my webService 3 times (im leaking 3 connections each run), i'm getting error:

 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)

So I added the following in application.properties to locate the leak :

spring.datasource.hikari.leakDetectionThreshold=2000

and then I got the following error on the first run of my webService:

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)

The method giving the error is below:

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");
}
}

How do I create a connection which is not leaky and why does Hikari think my conn.close() method is not closing the connection? Any ideas appreciated.

Upvotes: 0

Views: 2615

Answers (1)

darrendanvers
darrendanvers

Reputation: 96

I am a little confused about what the function is doing. It seems to be running a select constraining on the CLOB, but I may be mistaken. I was having trouble recreating the issue without knowing that.

That said, here are a few things to try. First, you don't need the NamedParameterJdbcTemplate, just a DataSource. Try autowiring that instead. Second, there doesn't seem to be a reason to get the raw Oracle connection itself and you can use the Connection returned by the DataSource directly. Third, the try with resources pattern makes the code easier to follow Java will handle closing the resources automatically. It is available in Java 8. Finally, perhaps freeing the CLOB will help.

Here is a sample with the things I mention:

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;
                }
            }
        }
    }
}

Upvotes: 1

Related Questions