Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 119 ยท Databases with JDBC

Connection Pooling and DataSource

Learn why Java apps pool database connections, how the DataSource interface works, how to configure HikariCP, size a pool, and find connection leaks.

Every example so far has called DriverManager.getConnection(url) and closed the connection a moment later. With an in-memory H2 database that costs almost nothing. With a real database server, opening a connection means a TCP handshake, often a TLS handshake, authentication, and the server setting up a session (PostgreSQL even starts a new operating system process for each one). That can take tens of milliseconds, which is longer than most queries.

A connection pool opens a few connections once and lends them out over and over. Your code still calls getConnection() and close(), but close() hands the connection back to the pool instead of hanging up. Every serious Java backend works this way, usually with HikariCP, the default pool in Spring Boot. In this lesson you will build a tiny pool by hand to see the trick, use the standard DataSource interface, run H2's built-in pool under load, cause a connection leak on purpose, and learn how to configure and size HikariCP.

How a pool works

A pool is a container of open connections plus a rule: if one is free, lend it; if all are busy, wait (up to a timeout); when it comes back, make it available again. The clever part is close(). The pool doesn't give you the real connection, it gives you a wrapper whose close() returns the real one to the pool.

The next example builds that with an ArrayBlockingQueue and a dynamic proxy (java.lang.reflect.Proxy, see Reflection). It counts how many physical connections were really opened.

Java A tiny connection pool, built by hand

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

All 20 requests ran on the same database session, and only one physical connection was ever opened, because each request returned it before the next one asked. SESSION_ID() is an H2 function that identifies the server-side session, which makes the reuse visible.

Real pools add much more: validating connections before lending them, replacing broken ones, closing idle ones, retiring old ones, fair waiting, metrics and leak detection. Writing your own for production is a bad idea; understanding this sketch is a good one.

The DataSource interface

javax.sql.DataSource is the standard way to say "something that gives out connections". Its key method is just:

Java
Connection getConnection() throws SQLException;

The difference from DriverManager is where the configuration lives. With DriverManager, every caller needs the URL, user name and password. With a DataSource, you configure it once at startup and hand the object to the code that needs it. That code can't tell whether it got a plain driver-provided DataSource, a pool, or a test double, so you can switch between them without touching it. Frameworks (Spring, Jakarta EE servers) expose their configured pool as a DataSource for this reason.

Drivers ship simple, non-pooling implementations: H2 has org.h2.jdbcx.JdbcDataSource, PostgreSQL has PGSimpleDataSource, MySQL has MysqlDataSource. H2 also includes a small real pool, org.h2.jdbcx.JdbcConnectionPool. Both are on the classpath here.

Java Code that only knows DataSource

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The exact times change from run to run, but the pooled version is usually faster even here, where a new connection is only a few hundred microseconds of in-process work. Against a server over a network, the gap becomes dramatic. More importantly, NoteStore didn't change at all: the choice of pool is configuration, not business code.

A pool under load

A pool also acts as a limiter. If the pool holds three connections and eight requests arrive at once, three run and five wait in line. That protects the database from being flooded, which is often more valuable than the speed gain.

In the next example, eight tasks each "work" for 100 ms while holding a connection from an H2 pool limited to three connections. The program tracks how many connections were in use at the same time.

Java Eight tasks share three connections

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The tasks start in three waves of at most three (roughly 0, 100 and 200 ms; the order within a wave varies), the peak never goes above 3, and the whole job takes about 300 ms instead of 100. The times are rounded down to whole 100 ms steps so the output stays readable.

This is also why virtual threads (see Virtual Threads and Structured Concurrency) don't mean you need thousands of connections. A server can run 10,000 virtual threads, but the pool still decides how many talk to the database at once, and that number should stay small.

Connection leaks

A leak is a borrowed connection that is never closed, usually because an exception skipped the close() call or someone stored the connection in a field. Each leak permanently removes one connection from the pool. Eventually every request waits for a connection that never comes back, and the application freezes even though the database is idle.

Java Leaking connections until the pool runs dry

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The third call waits one second and fails with H2's "Login timeout" error, SQLState 08001 (the standard class 08 means connection problems). In production with HikariCP the message reads like "Connection is not available, request timed out after 30000ms", and the cure is always the same: acquire every connection in try-with-resources (see try-with-resources). HikariCP can also log a stack trace of the code that borrowed a connection and held it too long; see leakDetectionThreshold below.

HikariCP in practice

HikariCP (com.zaxxer:HikariCP) is small, fast and well-tested, and it is what you will meet in most codebases. It can't run on our server, so the following is read-only. In plain Java you configure a HikariConfig and create a HikariDataSource, which is a DataSource:

Java
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://db.internal:5432/shop");
config.setUsername("shop_app");
config.setPassword(System.getenv("DB_PASSWORD"));   // never hard-code secrets
config.setMaximumPoolSize(10);
config.setMinimumIdle(10);
config.setConnectionTimeout(5_000);       // ms to wait for a free connection
config.setMaxLifetime(1_800_000);         // retire connections after 30 minutes
config.setLeakDetectionThreshold(20_000); // log a stack trace if held > 20 s
config.setPoolName("shop-db");

HikariDataSource dataSource = new HikariDataSource(config);
// ... pass dataSource to your DAOs; call dataSource.close() at shutdown

In Spring Boot you don't write that code: Boot creates a HikariCP pool from properties.

PROPERTIES
spring.datasource.url=jdbc:postgresql://db.internal:5432/shop
spring.datasource.username=shop_app
spring.datasource.password=${DB_PASSWORD}
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.connection-timeout=5000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.leak-detection-threshold=20000

The settings that matter most:

Setting Default What it controls
maximumPoolSize 10 Most connections the pool will hold, busy or idle.
minimumIdle same as max Idle connections to keep ready. Leaving it equal to the max (a fixed-size pool) is the recommended setup.
connectionTimeout 30,000 ms How long getConnection() waits before throwing. Lower it so overload fails fast instead of piling up requests.
idleTimeout 600,000 ms When idle connections above minimumIdle are closed.
maxLifetime 1,800,000 ms Connections are replaced after this age. Keep it a little shorter than any timeout in the database, firewall or load balancer that kills old connections.
keepaliveTime 120,000 ms How often idle connections are pinged to keep them alive.
leakDetectionThreshold 0 (off) Logs a warning with the borrower's stack trace when a connection is held longer than this.

Hikari also resets connection state (autocommit, isolation level, read-only flag, catalog) when a connection comes back, so one request's setAutoCommit(false) doesn't leak into the next. Some other pools don't, which is one more reason to restore state in finally, as shown in Transactions with JDBC.

How big should the pool be?

The intuition "more connections, more throughput" is wrong past a small number. A database server has a fixed number of CPU cores and disks. Once they are busy, extra concurrent queries only add context switching, lock contention and memory use, so every query gets slower.

A widely used starting point, from the PostgreSQL community, is:

TEXT
connections = (CPU cores of the database server ร— 2) + number of disks

For an 8-core database server with an SSD, that suggests around 17 connections in total. Then:

  • Divide by the number of application instances. Five instances with a pool of 10 each already open 50 connections. With autoscaling, the total can surprise you and hit the database's max_connections.
  • Measure, don't guess. Watch the pool's metrics (active, idle, waiting threads, acquire time). If threads often wait while the database CPU is low, the pool may be too small; if the database is saturated, a bigger pool makes things worse.
  • Fix slow queries and long transactions first. A connection held for 2 seconds serves 20 times fewer requests than one held for 100 ms.

A pool can deadlock itself. If a request borrows one connection and then, while still holding it, asks for a second one (for example, two DAO methods that each open their own connection inside one operation), then under load every connection can be held by a request waiting for another. Use one connection per unit of work and pass it along.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does calling close() on a connection borrowed from a pool usually do?

    The pool hands out a wrapper whose close() returns the real connection to the pool. You must still call it, ideally with try-with-resources.
  2. 2. Why do applications depend on DataSource rather than calling DriverManager directly?

    The calling code only sees getConnection(). Whether a pool sits behind it is a deployment decision.
  3. 3. A pool has maximumPoolSize=4. Ten requests each hold a connection for 50 ms, all arriving together. Roughly how long until the last one finishes?

    Four run at a time, so ten requests need three waves (4 + 4 + 2) of about 50 ms each.
  4. 4. An app freezes under load and logs "Connection is not available, request timed out", but the database is nearly idle. What is the most likely cause?

    An idle database with an exhausted pool means connections are checked out but unused. Enable leak detection and make sure every connection is closed in try-with-resources.

Summary

  • Opening a database connection is slow; a pool keeps a few open and lends them out. close() on a pooled connection returns it to the pool.
  • DataSource is the standard interface for getting connections. Configure it once and pass it to your code; switching between a plain and a pooled implementation needs no code changes.
  • A pool also limits concurrency against the database: extra requests wait, up to a timeout.
  • Leaks (connections never closed) slowly exhaust a pool. Always use try-with-resources, and turn on leak detection.
  • HikariCP is the standard pool. Key settings: maximumPoolSize, connectionTimeout, maxLifetime, leakDetectionThreshold. Spring Boot configures it from spring.datasource.hikari.*.
  • Keep pools small: start near (cores ร— 2) + disks for the whole database, divide across app instances, then measure.

Next: The DAO and Repository Patterns organizes all this JDBC code so the rest of your application never has to see SQL.