BackendBench
Coffee

Unravel › Java concepts › JDBC and SQL mappers

Persistence

JDBC connections, DataSource and pools

A Connection is an expensive, stateful channel to the database.

🔎 Unravel this examplePrerequisites (1)

Learn these first

JDBC and prepared statements

At a glance

Mental model

A taxi rank: calling a new taxi from the depot every time is slow, so a small fleet waits at the rank.

What happens, in order
  1. 1try (Connection c = ds.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) { ..}. For a pooled connection close() returns it to the pool; it does not close the socket.
  2. 2DriverManager.getConnection(url, user, pass) opens a new physical connection each time: fine for a script, wrong for a server
  3. 3HikariCP settings: maximumPoolSize (often 10 to 20), connectionTimeout, maxLifetime (shorter than the database or firewall idle timeout), leakDetectionThreshold (log connections held too long)
  4. 4JDBC URL examples: jdbc:postgresql://host:5432/db, jdbc:mysql://host/db, jdbc:oracle:thin:@host:1521/serviceCredentials belong in environment variables or a secrets store.
Remember
Watch out
Not closing connections or statementsOpening a connection per query with DriverManagerHolding a connection during slow non-database workA pool far larger than the database can serve
Example (highlighted lines explain it)
import com.zaxxer.hikari.*;import java.sql.*;import javax.sql.DataSource;public class Db {  static DataSource pool(String url, String user, String pass) {    HikariConfig cfg = new HikariConfig();    cfg.setJdbcUrl(url); cfg.setUsername(user); cfg.setPassword(pass);    cfg.setMaximumPoolSize(10);    return new HikariDataSource(cfg);  }  static boolean alive(DataSource ds) throws SQLException {    try (Connection c = ds.getConnection()) { return c.isValid(2); }  }}
  1. line 3 uses DataSource, the core of JDBC connections, DataSource and pools
  2. line 5 uses DataSource, the core of JDBC connections, DataSource and pools
  3. line 6 uses HikariConfig, the core of JDBC connections, DataSource and pools
  4. line 9 uses HikariDataSource, the core of JDBC connections, DataSource and pools
  5. line 11 uses DataSource, the core of JDBC connections, DataSource and pools
  6. line 12 uses Connection, the core of JDBC connections, DataSource and pools
Read the full explanation (about 2 min)

A Connection is an expensive, stateful channel to the database. A DataSource is a factory for connections, usually a pool (HikariCP) that keeps a few open and hands them out, so each request borrows one and returns it by calling close().

A mental model

A taxi rank: calling a new taxi from the depot every time is slow, so a small fleet waits at the rank. You take one for a trip and hand it back; if all are out, you wait.

How it works

try (Connection c = ds.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) { ... }. For a pooled connection close() returns it to the pool; it does not close the socket.

DriverManager.getConnection(url, user, pass) opens a new physical connection each time: fine for a script, wrong for a server.

HikariCP settings: maximumPoolSize (often 10 to 20), connectionTimeout, maxLifetime (shorter than the database or firewall idle timeout), leakDetectionThreshold (log connections held too long).

JDBC URL examples: jdbc:postgresql://host:5432/db, jdbc:mysql://host/db, jdbc:oracle:thin:@host:1521/service. Credentials belong in environment variables or a secrets store.

Going deeper

Not closing connections is the number one cause of "pool exhausted" errors: threads wait for connectionTimeout then fail. Use try-with-resources everywhere, and look for code that holds a connection while doing slow work (HTTP calls, sleeping).

A bigger pool is not faster: the database has limited cores and a pool of a few dozen connections often beats hundreds. Size it from the database capacity, not from the number of web threads.

SQLException is checked and carries getSQLState() and getErrorCode() which tell deadlocks, constraint violations and timeouts apart.

Under the hood

The pool wraps the real connection in a proxy: close() on the proxy resets state (auto-commit, transaction, read-only) and returns it to a concurrent bag. A housekeeping thread retires connections that exceed maxLifetime and validates idle ones.

Common mistakes

  • Not closing connections or statements.
  • Opening a connection per query with DriverManager.
  • Holding a connection during slow non-database work.
  • A pool far larger than the database can serve.

How Unravel recognises it

When you paste code, Unravel looks for these signs of jdbc connections, datasource and pools. Spotting them yourself is a good habit when you read unfamiliar code:

Part of a learning path

Related concepts

JDBC and prepared statementsPersistenceJDBC transactions and batchingPersistence@TransactionalSpringMemory leaks in JavaJava language

Frequently asked questions

What is JDBC connections, DataSource and pools?

A Connection is an expensive, stateful channel to the database. A DataSource is a factory for connections, usually a pool (HikariCP) that keeps a few open and hands them out, so each request borrows one and returns it by calling close().

What are the most common mistakes with JDBC connections, DataSource and pools?

Not closing connections or statements. Opening a connection per query with DriverManager. Holding a connection during slow non-database work. A pool far larger than the database can serve.

How can I see JDBC connections, DataSource and pools in real code?

Paste your own Java into Unravel and it will point out where jdbc connections, datasource and pools appears, explain how it works at the detail level you choose, and draw the flow of the code around it.