JDBC First: Connections, Pools & Transactions
2:47 AM, the Saturday after the Diwali sale went live. The checkout service's error rate jumped from 0.01% to 38% in four minutes. The logs were a wall of the same line, repeated thousands of times: Connection is not available, request timed out after 30000ms. HikariCP was telling us the pool was empty — every checkout thread was queueing for a database connection that would never come back.
Nothing had been deployed. Traffic was high but not record-breaking. The database was healthy, CPU at 20%. The connections hadn't gone anywhere. They were being held: a refund-reconciliation job, added the week before, opened a connection per refund batch and — on one code path — never closed it. Each run leaked a handful of connections. At sale traffic, "a handful per run" emptied a 10-connection pool in under an hour. The fix was twelve lines. Finding it took four engineers and most of the night, because nobody on the call had ever thought about what a connection pool is. They'd only ever used JPA, which hides this entire layer.
That's the argument for this post. JPA and Hibernate sit on top of JDBC the way a car sits on top of an engine: you can drive for years without opening the hood, but the day it breaks down at 2:47 AM, you debug at the JDBC layer — the SQL log, the pool metrics, the transaction boundaries. This post opens the hood: DataSource basics, HikariCP pool tuning, a worked JdbcTemplate CRUD example against a real database, connection leaks, and transactions done right.
Why JDBC before JPA
Every JPA operation eventually becomes JDBC calls: open connection (or borrow one from the pool), prepare statement, bind parameters, execute, map the result set, close. When Hibernate is fast, that layer is invisible. When it's slow, every clue lives there:
- Slow page? You read the SQL Hibernate generated — that's JDBC-level thinking.
- Pool exhausted? You read pool metrics — connections are a JDBC concept.
- Half-written data? You check transaction boundaries —
@Transactionalis a JDBC transaction manager wearing a nice coat.
Learn the layer once, and every ORM mystery becomes a JDBC question you already know how to answer. Skip it, and every ORM mystery stays magic. The checkout service team learned this at 2:47 AM. You'll learn it over coffee.
DataSource: the one interface to learn
The naive way to talk to a database opens a brand-new physical connection on every call — TCP handshake, authentication, session setup — and, in the version below, never closes it. This method compiles, runs, returns the right answer, and slowly kills your database:
public BigDecimal leakyTotalForOrder(long orderId) throws SQLException {
Connection con = DriverManager.getConnection("jdbc:h2:mem:checkout", "sa", "");
PreparedStatement ps = con.prepareStatement("SELECT total FROM orders WHERE id = ?");
ps.setLong(1, orderId);
ResultSet rs = ps.executeQuery();
rs.next();
return rs.getBigDecimal(1);
// Connection, PreparedStatement, ResultSet: nothing closed. Three leaks per call.
}The fix has two parts. First, stop asking DriverManager for connections and ask a DataSource instead — javax.sql.DataSource is the single interface all of Spring's data access is built on. A DataSource is a factory for connections; what kind of factory is an implementation detail. Spring's DriverManagerDataSource opens a new connection per call (fine for a test, never for production). A pooling DataSource hands out already-open connections and takes them back when you close them. Your code talks to the interface; the pool is a configuration choice, not a code change.
Second, close what you borrow. The try-with-resources version closes in reverse order — ResultSet, then PreparedStatement, then Connection — even when the body throws. And when the DataSource is a pool, closing the Connection doesn't tear down the TCP connection; it returns it to the pool:
public BigDecimal safeTotalForOrder(DataSource ds, long orderId) throws SQLException {
try (Connection con = ds.getConnection();
PreparedStatement ps = con.prepareStatement("SELECT total FROM orders WHERE id = ?")) {
ps.setLong(1, orderId);
try (ResultSet rs = ps.executeQuery()) {
rs.next();
return rs.getBigDecimal(1);
}
}
}Decision rule: every Connection must be closed in the same method that borrowed it — try-with-resources, no exceptions, no "the caller will close it." With a pool, close() means "return to the pool," so this rule costs nothing and prevents the 2:47 AM outage.
The pool: HikariCP and its five knobs
Spring Boot's default pool is HikariCP — the fastest widely-used JDBC pool, and the one you'll meet in most production Spring apps. Here's a complete pool configuration in plain Spring (no Boot magic yet, so you can see every moving part). Every class below is real and this file compiles:
@Configuration
@EnableTransactionManagement
public class JdbcConfig {
@Bean
public DataSource dataSource() {
HikariConfig cfg = new HikariConfig();
cfg.setJdbcUrl("jdbc:h2:mem:checkout;DB_CLOSE_DELAY=-1");
cfg.setUsername("sa");
cfg.setPassword("");
cfg.setMaximumPoolSize(10);
cfg.setMinimumIdle(2);
cfg.setConnectionTimeout(30_000);
cfg.setIdleTimeout(600_000);
cfg.setMaxLifetime(1_800_000);
return new HikariDataSource(cfg);
}
@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean
public PlatformTransactionManager transactionManager(DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}What each knob means — and these are worth learning precisely, because mis-set pool knobs are a top-three cause of "the app is slow but the DB is fine":
maximumPoolSize(default 10) — the hard cap on connections. This is the number in the 2:47 AM story. Size it from the workload: how many threads genuinely need the database at the same instant, bounded by what the database can survive (a Postgres withmax_connections = 100shared across five app instances cannot give each instance a pool of 50).minimumIdle(default = maximumPoolSize) — how many idle connections the pool tries to keep warm. Set it below the max to let the pool shrink during quiet hours; keep it equal to the max when you want zero connection-setup latency on the first request after idle.connectionTimeout(default 30s) — how long a thread waits for a free connection before HikariCP throws. Thirty seconds of threads piling up is how a pool problem becomes a thread-pool problem becomes a full outage. Keep it shorter than your upstream request timeout so a pool problem surfaces as a fast, clear error instead of a cascade.idleTimeout(default 10 min) — how long an idle connection may sit before being retired. Only applies when the pool holds more thanminimumIdle.maxLifetime(default 30 min) — the maximum age of any connection, idle or not. Retire connections before the database or a firewall kills them — set it a few minutes below the database's idle-connection timeout (MySQL'swait_timeout, for example). A connection killed server-side while sitting in your pool becomes a nasty intermittent failure.
Decision rules: put @Transactional on the service method that expresses the business unit of work — never on repositories (too fine-grained) and never on private methods (the proxy can't see them, so the annotation is silently ignored; self-invocation within the same class bypasses the proxy too). Rollback is the default for unchecked exceptions only — if your method throws a checked exception, declare rollbackFor or translate it. And mark read-only flows @Transactional(readOnly = true): it's a hint that lets the provider skip dirty-checking and, on some databases, route to a replica.
The Spring Boot way: properties over beans
Everything above was plain Spring so the machinery stays visible. In a Spring Boot app you rarely write that JdbcConfig by hand — Boot's auto-configuration builds the same DataSource, JdbcTemplate, and transaction manager from properties (if you define your own DataSource bean, Boot politely backs off). The project setup:
<project>
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
</parent>
<groupId>com.javamakeuse</groupId>
<artifactId>checkout-service</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<java.version>25</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.5.252</version>
<scope>runtime</scope>
</dependency>
</dependencies>
</project>(Check for newer versions than the ones pinned above — the coordinates are the stable part. Spring Boot 4 requires Java 17 or newer; this track targets JDK 25. spring-boot-starter-jdbc pulls in HikariCP, spring-jdbc, and spring-tx.)
spring.datasource.url=jdbc:h2:mem:checkout;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.connection-timeout=5000
spring.datasource.hikari.max-lifetime=1500000H2 here is a deliberate choice for learning: in-memory, zero-install, and fast enough that your tests stay fast. It is not a production database. When you're ready to test against the real thing, this track's Testcontainers post shows how to spin up real Postgres in your tests — same JdbcTemplate code, honest database underneath. And when you want to prove the pool survives real traffic rather than a unit test, that's what the Gatling post is for: pool exhaustion is exactly the kind of failure that only appears under concurrent load.
Field check before you move on: take the JdbcConfig from this post, set maximumPoolSize to 2 and leakDetectionThreshold to 5 seconds, then run the leaky method in a loop from two threads. Watch the pool drain and the leak detector print the stack trace pointing at the exact line that borrowed and never returned. Then switch to the try-with-resources version and watch the pool stay healthy. That ten-minute experiment teaches more about pools than any documentation page.
Continue: Java Learning Roadmap 2026
Comments
Post a Comment