How to Check Hikaricp Connection Pooling Is Working or Not in Java

Using HikariCP's connection pool the correct way

When opening a Connection you also need to close it. However you are storing the Connection in a instance variable. Which, for certain paths in your code, might result in multiple Connection instances being used. Due the the storage in the instance variable only the last one used will get closed, all the others are leaked.

Instead you want to make it local or hide parts of the complexity. You could rewrite your Database class to something like this.

Note: Assuming Java 8 here!

public class Database {
    private static Latte instance = Latte.getInstance();
    private static Config config = new Config();
    private static HikariConfig dbConfig;

    static {

        dbConfig = new HikariConfig();
        dbConfig.setJdbcUrl("jdbc:mysql://localhost:3306/" + config.get("database.database"));
        dbConfig.setUsername(config.get("database.username"));
        dbConfig.setPassword(config.get("database.password"));
        dbConfig.setDriverClassName("com.mysql.jdbc.Driver");
        dbConfig.addDataSourceProperty("cachePrepStmts", "true");
        dbConfig.addDataSourceProperty("prepStmtCacheSize", "250");
        dbConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
    }

    private static HikariDataSource ds = new HikariDataSource(dbConfig);

    public static <T> T execute(ConnectionCallback<T> callback) {
      try (Connection conn = ds.getConnection()) {
        return callback.doInConnection(conn);
      } catch (SQLException e) {
           throw new IllegalStateException("Error during execution.", e);
      }
    }

    public static interface ConnectionCallback<T> {
        public T doInConnection(Connection conn) throws SQLException;
    }
}

Notice no more getConnection and due to the try-with-resources the connection will get closed automatically.

You can now call this method with instances of ConnectionCallback instead of getting the Connection and manage it yourself.

Now the code that uses the Connection can be refactored, to something like this. (Notice no more catches, closes etc. all that is handled in the Database.execute method.

private void updateData(String sql, String[] sqlValues) {
    BukkitRunnable r = new BukkitRunnable() {
        @Override
        public void run() {
            Database.execute( (conn) -> {
                PreparedStatement q = conn.prepareStatement(sql);
                q.setString(1, sqlValues[0]);
                q.setString(2, sqlValues[1]);
                System.out.println(q);
                q.executeUpdate();
                return null;
            }} );
    };
    r.runTaskAsynchronously(instance);
}

This code will close the Connection after each use (and you cannot forget to close it).

Hikari connection is not available and unexpected multiple pool issue

If you upgrade Hibernate from 4.3.5 to 4.3.6, there's a official ConnectionProvider for Hibernate:

UPDATE : Hibernate 4.3.6+
As of Hibernate 4.3.6 there is an official ConnectionProvider class from Hibernate, which should be used instead of the HikariCP implementation. The class is called org.hibernate.hikaricp.internal.HikariCPConnectionProvider.

So you should replace com.zaxxer.hikari.hibernate.HikariConnectionProvider with org.hibernate.hikaricp.internal.HikariCPConnectionProvider

See also MySQL Hikari Configuration

Related Topics