javasqlitejdbchikaricpconnection-leaks

HikariPool reaching maximum connection


I am using HikariCP to run an SQLite Database. My configuration looks like this:

public class SQLiteDataSource {
    private static final HikariConfig config = new HikariConfig();
    private static final HikariDataSource ds;

    static {


        config.setJdbcUrl("jdbc:sqlite:database.db");
        config.setConnectionTestQuery("SELECT 1");
        config.addDataSourceProperty("cachePrepStmts", true);
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSwlLimit", "2048");
        config.setIdleTimeout(10000);
        config.setMaxLifetime(30000);
        config.setValidationTimeout(30000);
        config.setMaximumPoolSize(100);
        config.setMinimumIdle(10);
        config.setAllowPoolSuspension(false);

        ds = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

The method getConnection()is used to get the data from a table in the database. These functions look like this:

// This is a slightly abreviated function to get a boolean from a table in the database where the primary key "id" matches the one requested

 public static Boolean getBoolean(String Id, String column) {
        try (final PreparedStatement preparedStatement = SQLiteDataSource.getConnection()
                // language=SQLite
                .prepareStatement("SELECT " + column + " FROM table WHERE id = ?")) {

            preparedStatement.setString(1, Id);

            try (final ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    Boolean bool = resultSet.getBoolean(setting);
                    resultSet.close();
                    preparedStatement.getConnection().close();
                    return bool;
                }
            }

            preparedStatement.getConnection().close();

        } catch (SQLException e) {
        e.printStackTrace();
        }
        return false;
    }

Every time the function is called the connection of the PreparedStatement is closed at the end. The poolsize however keeps growing until it reaches maximum pool size and times out.

Is there a way i can prevent this or force shutdown connections?


Solution

  • Remove preparedStatement.getConnection().close() as @Nithin suggested and also get connection explicitly in try-with-resources block:

    try (Connection con = SQLiteDataSource.getConnection();final PreparedStatement preparedStatement = con
                    // language=SQLite
                    .prepareStatement("SELECT " + column + " FROM table WHERE id = ?")) {
    

    That way connection will be also closed at the end of block