Connection Pooling with HikariCP

Example

A pool is like a counter with N tokens. Here 2 "connections" serve 5 requests; extra requests wait for a free connection. A Semaphore models it:

import java.util.*;
import java.util.concurrent.*;

public class Main {
    public static void main(String[] args) throws Exception {
        Semaphore pool = new Semaphore(2);                 // maximum-pool-size = 2
        ExecutorService requests = Executors.newFixedThreadPool(5);
        List<Future<String>> results = new ArrayList<>();

        for (int i = 1; i <= 5; i++) {
            int id = i;
            results.add(requests.submit(() -> {
                boolean waited = !pool.tryAcquire();
                if (waited && !pool.tryAcquire(2, TimeUnit.SECONDS)) return "request " + id + ": timeout";
                try {
                    Thread.sleep(100);                     // run the SQL query
                    return "request " + id + (waited ? ": waited for a connection" : ": got a connection immediately");
                } finally {
                    pool.release();
                }
            }));
            Thread.sleep(10);                              // requests arrive one after another
        }
        for (Future<String> f : results) System.out.println(f.get());
        requests.shutdown();
    }
}

Output

request 1: got a connection immediately
request 2: got a connection immediately
request 3: waited for a connection
request 4: waited for a connection
request 5: waited for a connection

Example

application.yml

spring:
  datasource:
    url: jdbc:mysql://${DB_HOST:localhost}:3306/shopease
    username: ${DB_USER}
    password: ${DB_PASSWORD}
    hikari:
      maximum-pool-size: 20        # total connections per instance
      minimum-idle: 5
      connection-timeout: 3000     # ms to wait for a free connection, then fail fast
      idle-timeout: 600000
      max-lifetime: 1800000        # below the DB's wait_timeout
      leak-detection-threshold: 20000
      pool-name: shopease-hikari