HikariPool reaching maximum connection

Question

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?

Answer 1

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

Answer 2

You are leaking the connection because you don't retain a reference in the try-with-resources block. As a result, the connection you used to create the statement never gets closed. Instead you obtain a different connection and close that (at two different points!). Eventually you will exhaust the connection pool this way.

Also, given you're using try-with-resources, you don't need to explicitly close resultSet.

You need to change your code to:

try (Connection connection = SQLiteDataSource.getConnection();
     PreparedStatement preparedStatement = connection
            .prepareStatement("SELECT " + column + " FROM table WHERE id = ?")) {

    preparedStatement.setString(1, Id);

    try (ResultSet resultSet = preparedStatement.executeQuery()) {
        if (resultSet.next()) {
            Boolean bool = resultSet.getBoolean(setting);
            return bool;
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Also be aware that if the value of column is from an untrusted source, you are vulnerable to SQL injection.