JDBC

JDBC is the standard Java API (java.sql, javax.sql) for talking to relational databases. Your code depends only on interfaces (Connection, Statement, ResultSet); a vendor-supplied driver implements them and speaks the database wire protocol.

• Layers: application -> JDBC API -> driver (or DriverManager/DataSource) -> network protocol -> database.
• JDBC is blocking and synchronous: a thread is parked on socket I/O for every call.
• ORMs, JdbcTemplate and jOOQ are all built on top of it.

Type 1 is the JDBC-ODBC bridge (removed in Java 8). Type 2 is native-API (uses vendor C libraries via JNI). Type 3 is network-protocol (middleware translates). Type 4 is pure Java and speaks the database protocol directly.

• Type 4 is what you use today: PostgreSQL, MySQL, Oracle thin, SQL Server.
• Type 2 (Oracle OCI) is still used for features like TAF or special auth, at the cost of native installs.

Since JDBC 4.0 (Java 6), drivers are discovered through ServiceLoader via META-INF/services/java.sql.Driver, so Class.forName("org.postgresql.Driver") is unnecessary. DriverManager.getConnection(url) asks each registered driver whether it accepts the URL.

• Class.forName is still needed for very old drivers or inside some custom classloader setups (app servers, plugin containers).
• If the driver jar is not on the classpath you get No suitable driver found for jdbc:....

Use try-with-resources for all three; they close in reverse order of declaration, even on exceptions. Closing a Connection closes its statements, and closing a Statement closes its ResultSets, but relying on that leaves resources open longer than needed.

String sql = "select id, name from users where status = ?";
try (Connection c = ds.getConnection();
     PreparedStatement ps = c.prepareStatement(sql)) {
    ps.setString(1, "ACTIVE");
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) { System.out.println(rs.getString("name")); }
    }
}

• With a pool, close() returns the connection to the pool instead of closing the socket.

Statement runs a complete SQL string; PreparedStatement runs parameterized SQL with ? placeholders; CallableStatement extends PreparedStatement to invoke stored procedures/functions and read OUT parameters.

• Prefer PreparedStatement for anything that has input values: safer and often faster.
• Statement is acceptable for static DDL or constant SQL.

Parameterized SQL gives the database a stable statement text, so it can parse and plan once and reuse the plan, and the driver can reuse the prepared handle. Parameters travel separately from SQL text, so no escaping or quoting bugs occur.

• PostgreSQL's driver switches to a server-side prepared statement after prepareThreshold executions (default 5).
• MySQL Connector/J does client-side preparation by default (useServerPrepStmts=false); set useServerPrepStmts=true plus cachePrepStmts=true for server-side caching.
• Concatenating literals creates a distinct SQL text per value, polluting the plan/statement cache.

Bind every value with ? placeholders. Placeholders only stand in for values, never for identifiers (table/column names), keywords or ORDER BY direction, so those must be validated against an allow-list.

Map<String, String> sortable = Map.of("name", "name", "created", "created_at");
String col = sortable.getOrDefault(req.sort(), "created_at");
String dir = "desc".equalsIgnoreCase(req.dir()) ? "desc" : "asc";
String sql = "select id, name from users where status = ? order by " + col + " " + dir;

• Escaping by hand is fragile (charset and quoting differences); do not rely on it.
• Least privilege DB accounts limit the damage if injection slips through.

Use the escape syntax {call proc(?, ?)} or {? = call func(?)}, bind IN values with setXxx, register OUT types with registerOutParameter, execute, then read with getXxx.

try (CallableStatement cs = c.prepareCall("{call get_balance(?, ?)}")) {
    cs.setLong(1, accountId);
    cs.registerOutParameter(2, Types.NUMERIC);
    cs.execute();
    BigDecimal balance = cs.getBigDecimal(2);
}

• Procedures returning result sets: call getResultSet() / getMoreResults(). PostgreSQL functions returning refcursor need autoCommit=false.
• Parameters can be accessed by name (getString("out")) where the driver supports it.

Type: TYPE_FORWARD_ONLY (default, cursor moves forward), TYPE_SCROLL_INSENSITIVE (scrollable, snapshot), TYPE_SCROLL_SENSITIVE (scrollable and reflects changes). Concurrency: CONCUR_READ_ONLY (default) or CONCUR_UPDATABLE.

• Scrollable result sets often make the driver buffer the whole result client-side, costing memory.
• Holdability: HOLD_CURSORS_OVER_COMMIT vs CLOSE_CURSORS_AT_COMMIT; the default is driver-specific.
• Many drivers silently downgrade a requested type they do not support; check rs.getType().

Primitive getters (getInt, getLong, getBoolean) return 0/false for SQL NULL, so call rs.wasNull() right after, or use getObject(col, Integer.class) which returns null.

Integer age = rs.getObject("age", Integer.class);   // null-safe
long id = rs.getLong("id");
if (rs.wasNull()) { /* id was NULL */ }

• getString returns null for NULL; getBigDecimal, getDate, getTimestamp also.
• Column indexes start at 1, not 0.

addBatch() queues parameter sets client-side; executeBatch() sends them together, cutting round trips. It returns an int[] of update counts per statement, where Statement.SUCCESS_NO_INFO (-2) means success with unknown count.

try (PreparedStatement ps = c.prepareStatement("insert into t(a, b) values (?, ?)")) {
    for (Row r : rows) {
        ps.setInt(1, r.a()); ps.setString(2, r.b());
        ps.addBatch();
    }
    int[] counts = ps.executeBatch();
}

• Flush in chunks (e.g. 500-5000) to bound memory.
• On failure a BatchUpdateException is thrown; getUpdateCounts() shows what completed. Behaviour (stop vs continue) is driver-dependent.
• Java 8+ offers executeLargeBatch() for counts above int.

Drivers may still send each statement as a separate round trip. MySQL Connector/J needs rewriteBatchedStatements=true to rewrite into multi-row INSERT ... VALUES (...),(...); PostgreSQL JDBC needs reWriteBatchedInserts=true to do the same.

• Oracle batches natively via the array interface; SQL Server uses useBulkCopyForBatchInsert for bulk copy.
• With MySQL rewriting, getGeneratedKeys behaviour and update counts (SUCCESS_NO_INFO) change.
• Hibernate adds hibernate.jdbc.batch_size; IDENTITY ids disable insert batching there.

By default a JDBC connection is in auto-commit mode: each statement is its own transaction, committed upon completion. To group statements atomically, call setAutoCommit(false) and then commit() or rollback().

• Turning auto-commit off starts a transaction implicitly on first statement.
• Calling commit() while auto-commit is on throws SQLException in most drivers.
• Pools reset auto-commit when the connection is returned (HikariCP does).

Disable auto-commit, run work, commit at the end, and roll back in a catch before rethrowing. Restore auto-commit if you own the connection.

try (Connection c = ds.getConnection()) {
    c.setAutoCommit(false);
    try {
        debit(c, from, amt);
        credit(c, to, amt);
        c.commit();
    } catch (Exception e) {
        c.rollback();
        throw e;
    }
}

• Rollback itself can throw (connection lost); add the original as primary and use addSuppressed.
• In Spring, @Transactional does this for you via DataSourceTransactionManager.

A Savepoint marks a point inside a transaction to which you can roll back partially without aborting the entire transaction. Create with c.setSavepoint("name"), roll back with c.rollback(sp), free with c.releaseSavepoint(sp).

Savepoint sp = c.setSavepoint();
try {
    insertOptionalAudit(c);
} catch (SQLException e) {
    c.rollback(sp);   // keep the rest of the transaction
}
c.commit();

• In PostgreSQL a failed statement aborts the whole transaction ("current transaction is aborted") unless you used a savepoint; the driver option autosave=conservative automates this.
• Spring's PROPAGATION_NESTED is built on savepoints.

Atomicity: all or nothing (commit/rollback). Consistency: constraints hold before and after. Isolation: concurrent transactions do not see each other's partial work, to a degree set by the isolation level. Durability: committed data survives crashes (WAL/redo log flushed).

• JDBC only exposes controls (commit, rollback, isolation); the guarantees are implemented by the database.
• Durability depends on DB settings like synchronous_commit or innodb_flush_log_at_trx_commit.

TRANSACTION_READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE (and NONE, rarely supported). Set via c.setTransactionIsolation(level) before the transaction starts.

• The table is the ANSI definition; real engines differ (snapshot isolation does not suffer phantoms in MySQL InnoDB for plain reads, and has write skew in PostgreSQL REPEATABLE READ).

PostgreSQL, Oracle and SQL Server default to READ COMMITTED; MySQL InnoDB defaults to REPEATABLE READ. H2 uses READ COMMITTED.

• MySQL InnoDB REPEATABLE READ uses MVCC snapshots for plain reads and gap/next-key locks for locking reads.
• Oracle only offers READ COMMITTED, SERIALIZABLE and read-only.
• Moving code between MySQL and PostgreSQL can change behaviour of the same read-then-write logic.

It is a hint to the driver/database that the transaction will not modify data. The effect varies: PostgreSQL issues SET SESSION CHARACTERISTICS ... READ ONLY so writes fail; MySQL Connector/J can route to replicas in replication mode; Oracle starts a read-only transaction.

• Must generally be called before the transaction begins.
• Spring's @Transactional(readOnly = true) calls it, and Hibernate also skips dirty checking.
• Pools reset it on return (HikariCP does).

Opening a physical connection costs TCP + TLS + authentication + session setup (often 5-100 ms) and the database can only hold a limited number of connections. A pool keeps a bounded set of connections open and lends them out.

• Bounds concurrency to the DB, giving back-pressure instead of overload.
• close() on a pooled connection is a proxy method that returns it to the pool and resets state.
• Common pools: HikariCP (Spring Boot default), Tomcat JDBC, DBCP2, c3p0, Agroal.

maximumPoolSize (10), minimumIdle (same as max, i.e. fixed size pool), connectionTimeout (30 s), idleTimeout (10 min, only applies when minimumIdle < max), maxLifetime (30 min), keepaliveTime (0 = disabled), validationTimeout (5 s), leakDetectionThreshold (0 = disabled).

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      connection-timeout: 3000
      max-lifetime: 1500000
      keepalive-time: 300000
      leak-detection-threshold: 20000

• HikariCP recommends a fixed-size pool (do not set minimumIdle lower unless needed).
• Setting connectionTimeout to a few seconds fails fast under overload.

Start small. A common formula from the PostgreSQL wiki and Hikari docs is connections = (core_count * 2) + effective_spindle_count, applied to the database server, not the app. Then measure and tune against throughput and wait time.

• A 4-core DB serving 20 pool connections can already be oversubscribed; extra connections queue inside the DB.
• Total across instances matters: pods * maximumPoolSize must stay below the DB max_connections minus admin/headroom.
• Little's Law: needed connections ~ requests/sec x average connection-hold time.
• Reduce hold time (short transactions, no remote calls while holding a connection) before increasing size.

maxLifetime retires a connection after a fixed age (with random variance), so it is replaced before server or firewall kills it. idleTimeout shrinks idle connections above the minimum. keepaliveTime pings idle connections periodically so network devices do not drop them.

• Set maxLifetime several seconds to minutes below the database/proxy limit (wait_timeout in MySQL, load balancer idle timeout, RDS Proxy limits).
• keepaliveTime must be less than maxLifetime (minimum 30 s).
• Retiring is done only for connections not in use; an in-use connection is evicted on return.

connectionTimeout is how long getConnection() waits for a connection from the pool before throwing SQLTransientConnectionException. validationTimeout is how long a liveness check of a borrowed connection may take (must be less than connectionTimeout).

• Hikari validates with Connection.isValid() (JDBC4) if the driver supports it, so connectionTestQuery is rarely needed.
• It skips validation if the connection was used in the last 500 ms.
• Other timeouts are separate: socket/read timeout, statement queryTimeout, and DB-side statement_timeout/lock_timeout.

With leakDetectionThreshold > 0 (minimum 2000 ms), Hikari starts a timer when a connection is borrowed and logs a warning with the borrowing stack trace if it has not been returned in time. It does not forcibly close or reclaim the connection.

• Set the threshold above your slowest legitimate transaction.
• A late return after warning logs "Previously reported leaked connection ... was returned to the pool".
• The stack trace points to where the connection was acquired, not where it is stuck.

Each pool connection is one DB session (a backend process in PostgreSQL, a thread in MySQL). The sum over all app instances must stay below max_connections. An external pooler (PgBouncer, ProxySQL, RDS Proxy) multiplexes many client connections onto fewer server connections.

• PgBouncer transaction mode breaks session features: session-level SET, advisory locks, LISTEN, and server-side prepared statements (use prepareThreshold=0 or a recent PgBouncer with prepared-statement support).
• Autoscaling apps can multiply connections unexpectedly; cap pool sizes per instance.
• Idle connections still consume memory on the server (PostgreSQL ~ several MB each).

setFetchSize(n) hints how many rows the driver should retrieve per network round trip. Too small means many round trips; too large means high memory.

• Oracle default is 10 (increase to 100-1000 for bulk reads).
• PostgreSQL fetches all rows at once by default; fetch size only applies when autoCommit=false and the ResultSet is forward-only.
• MySQL Connector/J loads the whole result unless useCursorFetch=true with fetch size set, or fetch size is Integer.MIN_VALUE (row-by-row streaming).
• SQL Server buffers adaptively by default.

Use a forward-only, read-only ResultSet, set a fetch size, and use the driver's cursor/streaming mode; process rows as you read and never collect them all.

• PostgreSQL: autoCommit=false + setFetchSize(1000).
• MySQL: setFetchSize(Integer.MIN_VALUE) or useCursorFetch=true + fetch size.
• The connection and often a transaction stay open for the entire read, so keep processing quick and do not run other queries on the same MySQL connection while streaming.
• For huge exports, consider keyset pagination or COPY (PostgreSQL CopyManager).

Pass Statement.RETURN_GENERATED_KEYS (or column names) when preparing, then call getGeneratedKeys() after executing.

try (PreparedStatement ps = c.prepareStatement(
        "insert into orders(customer_id) values (?)", Statement.RETURN_GENERATED_KEYS)) {
    ps.setLong(1, 42L);
    ps.executeUpdate();
    try (ResultSet keys = ps.getGeneratedKeys()) {
        if (keys.next()) { long id = keys.getLong(1); }
    }
}

• Oracle requires column names (new String[]{"ID"}) because ROWID is returned otherwise.
• PostgreSQL implements this by appending RETURNING * (or the listed columns).
• Works with batches on most drivers (one key row per statement).

javax.sql.DataSource is a factory for connections, configured externally (URL, credentials, pool settings) and can be pooled, wrapped or looked up through JNDI. DriverManager is a static, unpooled, global registry.

• Pooling, distributed transactions (XADataSource) and monitoring implementations all plug in via DataSource.
• Application code receives a DataSource (injection), so credentials and URLs stay in config.
• Spring Boot auto-configures a HikariDataSource from spring.datasource.*.

Connection.getMetaData() returns DatabaseMetaData: product/driver versions, supported features, and catalog info (getTables, getColumns, getPrimaryKeys, getImportedKeys, getIndexInfo). ResultSet.getMetaData() returns ResultSetMetaData: column count, names, labels, SQL types, nullability, precision.

DatabaseMetaData md = c.getMetaData();
try (ResultSet t = md.getTables(null, "public", "%", new String[]{"TABLE"})) {
    while (t.next()) { System.out.println(t.getString("TABLE_NAME")); }
}

• Used by schema migration tools, generic query UIs and ORMs for dialect detection.
• Metadata calls can be slow on big catalogs; cache results.

Use streaming setters/getters (setBinaryStream, getBinaryStream, setCharacterStream, getCharacterStream) or Blob/Clob locators (createBlob(), createClob()), instead of loading whole values as byte[]/String.

try (InputStream in = Files.newInputStream(path);
     PreparedStatement ps = c.prepareStatement("insert into docs(name, body) values (?, ?)")) {
    ps.setString(1, "report.pdf");
    ps.setBinaryStream(2, in, Files.size(path));
    ps.executeUpdate();
}

• Read streams while the ResultSet and connection are still open and the transaction is active (Oracle and PostgreSQL large objects require it).
• PostgreSQL has bytea (inline, up to 1 GB) and Large Objects (oid, needs a transaction).
• Often better: store files in object storage and keep a URL/key in the row.

Running one query to fetch N parents and then one query per parent for its children produces N+1 round trips. Fix with a join, a single IN/ANY query for all children, or a batch.

// 1 query for all children instead of N
String sql = "select order_id, sku, qty from order_items where order_id = any (?)";
Array ids = c.createArrayOf("bigint", orderIds.toArray());
try (PreparedStatement ps = c.prepareStatement(sql)) {
    ps.setArray(1, ids);
    // group rows by order_id into a Map<Long, List<Item>>
}

• Latency per round trip (0.5-2 ms same AZ) multiplies by N.
• A join duplicates parent columns per child row; a two-query approach avoids that and pagination of parents stays simple.
• Chunk large IN lists (e.g. 500-1000).

A deadlock is a cycle: transaction A waits for B while B waits for A; the database detects it and aborts one victim immediately. A lock wait is a plain wait for a lock held by a still-running transaction (no cycle), which ends when it commits or the wait times out.

• PostgreSQL: SQLState 40P01 for deadlock, 55P03 for lock_not_available, 57014 for statement timeout.
• MySQL: error 1213 (SQLState 40001) deadlock; error 1205 (HY000) lock wait timeout (innodb_lock_wait_timeout, default 50 s).
• Oracle: ORA-00060 deadlock; ORA-30006/ORA-00054 for waits with NOWAIT.
• Avoid deadlocks by acquiring locks in a consistent order and keeping transactions short.

JDBC provides Statement.setQueryTimeout(seconds), which the driver implements by cancelling the query (often via a timer thread and a second connection). Databases have their own settings: PostgreSQL lock_timeout, statement_timeout; MySQL innodb_lock_wait_timeout, max_execution_time; Oracle DDL_LOCK_TIMEOUT, resource limits.

• Network socketTimeout/oracle.net.READ_TIMEOUT is a last resort for hung connections; it should exceed the longest legitimate query.
• Set DB-side timeouts via connection init SQL or URL: jdbc:postgresql://h/db?options=-c%20statement_timeout=5000.
• A timeout produces SQLTimeoutException (or a vendor SQLState such as 57014).

SELECT ... FOR UPDATE locks the selected rows until the transaction ends. NOWAIT fails immediately if a row is locked; SKIP LOCKED ignores locked rows, which makes it a good fit for work queues. Available in PostgreSQL 9.5+, MySQL 8.0+, Oracle.

select id, payload from jobs
where status = 'NEW'
order by id
limit 10
for update skip locked;

• Needs autoCommit=false; the lock is released at commit/rollback, so keep the transaction short.
• Multiple workers can poll the same table without blocking each other.

Add a version column; read it with the row, then update with WHERE id = ? AND version = ? and SET version = version + 1. If executeUpdate() returns 0, someone else changed the row first: reload and retry, or fail.

String sql = "update account set balance = ?, version = version + 1 where id = ? and version = ?";
// ps.executeUpdate() == 0  ->  concurrent modification

• No row locks are held during user think time, so it scales well with low contention.
• Pessimistic locking (FOR UPDATE) is better when conflicts are frequent.

Use the JDBC 4 hierarchy: SQLTransientException subclasses (SQLTransientConnectionException, SQLTimeoutException, SQLTransactionRollbackException) may succeed on retry; SQLNonTransientException (syntax error, SQLIntegrityConstraintViolationException) will not; SQLRecoverableException needs a new connection. Use getSQLState() (portable, 5 chars) and getErrorCode() (vendor) for finer detail.

• SQLState class 40 = transaction rollback (deadlock 40001/40P01, serialization failure 40001), 08 = connection exception, 23 = integrity violation (23505 unique).
• Check getNextException()/getCause() and the chain; batch errors nest.
• Drivers do not all populate the subclass; SQLState is more reliable.

Retry the entire transaction on a fresh connection, only for transient errors, with bounded attempts and jittered backoff. Make the operation idempotent so a retry after an unknown outcome (timeout during commit) does not duplicate effects.

• Use an idempotency key with a unique constraint: insert ... on conflict do nothing (PostgreSQL) / insert ignore / merge.
• Prefer set-based updates (set status='PAID' where id=? and status='NEW') over increments computed in app code.
• If commit() throws a connection error, the outcome is unknown: check state before retrying.
• Do not retry inside an outer transaction; retry at the outermost boundary.

Since JDBC 4.2 (Java 8) use setObject/getObject with java.time types: LocalDate <-> DATE, LocalTime <-> TIME, LocalDateTime <-> TIMESTAMP, OffsetDateTime <-> TIMESTAMP WITH TIME ZONE. The legacy java.sql.Date/Time/Timestamp are mutable and timezone-sensitive.

ps.setObject(1, LocalDate.of(2026, 1, 31));
LocalDateTime t = rs.getObject("created_at", LocalDateTime.class);
OffsetDateTime o = rs.getObject("paid_at", OffsetDateTime.class);

• Instant is not required to be supported by JDBC; use OffsetDateTime at UTC (PostgreSQL rejects setObject(Instant)).
• Store instants as UTC timestamptz/TIMESTAMP and convert at the edges.
• java.sql.Timestamp.toLocalDateTime() uses the JVM default zone.

Legacy Timestamp/Calendar mapping interprets values in the JVM default zone, and the driver may also apply a session or server zone, so the same value can shift by hours when the JVM zone differs from the DB's, or at DST boundaries.

• Run JVMs in UTC (-Duser.timezone=UTC) and keep timestamptz/UTC columns.
• MySQL Connector/J 8: connectionTimeZone, forceConnectionTimeZoneToSession (older: serverTimezone, useLegacyDatetimeCode).
• PostgreSQL JDBC sets session TimeZone to the JVM zone.
• Use LocalDateTime only for wall-clock values without meaning of instant (e.g. store opening time).

Use setNull(index, Types.X) for explicit NULL, or setObject(index, value, Types.X). setString(i, null) also works for most drivers, but a typeless setObject(i, null) can fail (PostgreSQL, Oracle) because the driver cannot infer the type.

• Use setObject(i, x, Types.TIMESTAMP_WITH_TIMEZONE) to be explicit with java.time.
• Comparing to NULL with = ? never matches; use IS NULL or IS NOT DISTINCT FROM (PostgreSQL).
• Primitive wrappers require a null check before setInt(i, wrapper) (NPE on unboxing).

Generate one ? per element (IN (?,?,?)) and bind each, or use an array parameter (PostgreSQL = ANY(?) with createArrayOf). Cap the list size and chunk beyond it, because of driver/DB limits (Oracle 1000 expressions, PostgreSQL/SQL Server ~32k/2100 bind parameters).

String marks = String.join(",", Collections.nCopies(ids.size(), "?"));
String sql = "select id, name from users where id in (" + marks + ")";

• An empty list yields invalid SQL IN (): short-circuit and return empty.
• Varying SQL text per list size pollutes the statement cache; pad to power-of-two sizes if needed.

executeQuery returns a ResultSet and is for SELECT; executeUpdate returns the affected row count (int) for INSERT/UPDATE/DELETE/DDL; execute returns boolean (true if first result is a ResultSet) for unknown or multiple results.

• Calling executeQuery on DML throws SQLException (and vice versa on most drivers).
• With execute, iterate: getResultSet(), getUpdateCount(), getMoreResults().
• executeLargeUpdate returns long.

Layered timeouts: pool connectionTimeout (wait for a connection) -> queryTimeout (per statement cancel) -> lock/statement timeout on the server -> socketTimeout (no bytes from server) -> transaction-level timeouts (Spring @Transactional(timeout=)). Each protects against a different failure.

• Order them so inner ones fire first: queryTimeout < socketTimeout; request deadline > pool wait + query time.
• Without a socket timeout, a silently dropped TCP connection can hang a thread for the OS timeout (minutes).
• Spring's transaction timeout only applies to the next statement execution; it does not interrupt running SQL.

An XADataSource yields XAConnections whose XAResource participates in two-phase commit (prepare, then commit/rollback) coordinated by a transaction manager (Atomikos, Narayana, JTA in app servers) across several resources.

• Costs: extra log writes and locks held longer; in-doubt transactions need recovery.
• Many microservice designs prefer sagas, outbox and idempotent consumers instead.
• Not every pool supports XA; HikariCP does not.

JdbcTemplate handles acquisition/release of connections, statements and exceptions, translating SQLException into the unchecked DataAccessException hierarchy. DataSourceTransactionManager binds a connection to the current thread (TransactionSynchronizationManager) so all JdbcTemplate calls in the transaction reuse it.

• Without a transaction, each JdbcTemplate call borrows and returns its own connection (auto-commit).
• DataSourceUtils.getConnection(ds) participates in the thread-bound connection; ds.getConnection() directly does not.
• Wrapping with TransactionAwareDataSourceProxy makes legacy code transaction aware.

SMALLINT/INTEGER/BIGINT -> short/int/long; REAL/DOUBLE -> float/double; NUMERIC/DECIMAL -> BigDecimal. Money must use NUMERIC/BigDecimal since binary floating point cannot represent decimals like 0.1 exactly.

• Use rs.getBigDecimal("amount") and ps.setBigDecimal.
• Watch scale: NUMERIC(10,2) rounds on insert; Oracle NUMBER without scale may produce large or odd scales.
• Do not do new BigDecimal(0.1); use BigDecimal.valueOf(0.1) or a string.
• Unsigned BIGINT in MySQL can overflow long: it maps to BigInteger.

OFFSET n LIMIT m makes the database walk and discard n rows each page, so deep pages get slow and unstable under concurrent inserts. Keyset pagination remembers the last seen key and queries WHERE (created_at, id) > (?, ?) ORDER BY created_at, id LIMIT m, using an index seek.

• Needs a deterministic, unique sort key (add id as tie-breaker) and a matching composite index.
• Cannot jump to arbitrary page numbers; suits infinite scroll, exports and batch jobs.
• Row-value comparison works in PostgreSQL and MySQL 8; elsewhere expand to a > ? OR (a = ? AND id > ?).

Each Statement has at most one current ResultSet (per result of execute). Re-executing the statement or closing it closes the previous ResultSet; reading afterwards throws SQLException: ResultSet closed (or "Operation not allowed after ResultSet closed").

• Use separate PreparedStatements when interleaving nested loops over two result sets.
• MySQL streaming forbids another statement on the same connection until the result is fully read or closed.
• Closing the Statement before consuming rows is a common bug when returning a ResultSet from a method.

javax.sql.rowset.RowSet extends ResultSet with JavaBean properties and events. CachedRowSet is a disconnected, serializable, scrollable copy of data that can be modified offline and synced back with acceptChanges(), which uses optimistic conflict detection.

• Created with RowSetProvider.newFactory().createCachedRowSet().
• Holds all rows in memory, so unsuitable for large results.
• Rarely used in modern services; DTO mapping with JdbcTemplate/jOOQ/ORM is the norm.

Drivers (PostgreSQL preparedStatementCacheQueries, Connector/J cachePrepStmts, Oracle implicit cache) cache prepared statement handles per connection, so repeated SQL skips the parse step. Pools like Hikari deliberately do not cache statements, leaving that to the driver.

• Schema changes can invalidate cached plans: PostgreSQL raises cached plan must not change result type after ALTER TABLE changing columns (the driver retries in autocommit only).
• PostgreSQL generic-plan switching after 5 executions can pick a bad plan for skewed data (plan_cache_mode).
• Memory: each connection caches separately, so pool size multiplies cache size.

The pool had no free connection for connectionTimeout. Either connections are leaked, held too long, or demand exceeds pool capacity. Determine which before touching the pool size.

• Enable Hikari metrics (hikaricp_connections_active/pending/usage_seconds) and log leakDetectionThreshold.
• Take thread dumps: threads parked in HikariPool.getConnection are waiters; threads holding connections show where they are stuck (HTTP call, lock wait, slow query).
• On the DB (pg_stat_activity, SHOW PROCESSLIST) look for idle in transaction sessions or long queries from this app.
• Active == max with idle-in-transaction sessions means leaked or long-held connections; active near max with real queries means DB is slow or pool is too small.

Each failing call permanently removes one connection from the pool; after maximumPoolSize failures every caller times out waiting. The service works until the error rate accumulates, then hangs entirely.

// buggy
Connection c = ds.getConnection();
PreparedStatement ps = c.prepareStatement(sql);
ps.executeUpdate();     // throws -> connection never closed
c.close();

• Fix with try-with-resources so close runs on every path.
• Leak detection logs the borrowing stack trace, pointing at the method.

Connection hold time becomes database time plus network latency of the remote call. With 20 connections and a 500 ms remote call, throughput caps near 40 requests/second, and a slow dependency exhausts the pool, taking down unrelated endpoints.

• Do the remote call before opening the transaction or after commit.
• If atomicity with a message is required, use the outbox pattern: write the event in the same DB transaction and publish asynchronously.
• Spring Boot's spring.jpa.open-in-view has a similar effect by holding a connection through view rendering.

Under load every thread holds one connection and waits for a second, so when all connections are taken, no thread can progress: a pool-level deadlock until connectionTimeout expires. The second connection is also a different transaction: it cannot see uncommitted rows of the first and may block on its locks.

• Pass the same Connection (or use Spring-managed access) to participate in one transaction.
• If a separate transaction is intentional (audit log), keep that pool separate or use REQUIRES_NEW knowingly with enough pool headroom (pool >= threads x 2).

Capture the exact SQL and parameter values, run EXPLAIN (ANALYZE, BUFFERS) (PostgreSQL) or EXPLAIN ANALYZE (MySQL 8) on production-sized data, and look for sequential scans, wrong join order, misestimated rows and lock waits.

• Check data volume and statistics (ANALYZE), missing or unused indexes, implicit casts that defeat indexes (where varchar_col = ? bound as number, or setString on a numeric column).
• A function on an indexed column (where lower(email) = ?) needs a functional index.
• Compare plan with first parameters: PostgreSQL generic plans after 5 executions can differ from custom plans.
• Look at the DB's pg_stat_statements/slow query log to see whether it is CPU, I/O or waiting on locks.

The pool still holds sockets opened to the old server. Hikari validates on borrow only if the connection was idle longer than 500 ms and detects death via isValid, but in-flight and recently-used connections fail on first use.

• Set maxLifetime and keepaliveTime below infrastructure idle limits; Hikari evicts broken connections on fatal SQLExceptions.
• Set socketTimeout/TCP keepalive so half-open connections fail fast.
• Use DNS/cluster endpoints with short TTL (JVM networkaddress.cache.ttl), or a driver with failover support (AWS Advanced JDBC Wrapper, MariaDB failover).
• Retry idempotent transactions once on SQLTransientConnectionException/SQLState 08xxx.

MySQL closes idle connections after wait_timeout (default 8 hours), and firewalls/NAT/load balancers often drop idle TCP flows even sooner (e.g. 350 s on AWS NLB, 4 min on Azure). The pool then hands out dead connections.

• Set Hikari maxLifetime below the smallest of wait_timeout and network idle timeouts (e.g. 25 min vs DB 30 min).
• Use keepaliveTime (e.g. 5 min) to keep flows alive.
• Hikari warns when maxLifetime exceeds the server's timeout.

Not necessarily. If each request holds a connection for 10 ms, 10 connections serve about 1000 requests/second. Threads wait briefly in the pool queue; this acts as useful back-pressure for the DB.

• It becomes a problem when the hold time rises (slow queries, remote calls in transactions) or most requests need a connection at once.
• Check hikaricp_connections_pending and acquire time; if pending is regularly above zero for long periods, investigate hold time first.
• Tuning sequence: shorten transactions, add indexes, then raise the pool within DB capacity.

40 x 20 = 800 potential connections exceeds 500; surplus attempts fail with FATAL: sorry, too many clients already (PostgreSQL) or Too many connections (MySQL). During rolling deploys old and new pods overlap, so the peak is even higher.

• Cap by total_connections = pods_max * pool; shrink per-pod pool (e.g. 8), add a pooler (PgBouncer/RDS Proxy), or reduce replicas.
• Reserve connections for admin, migrations and monitoring (superuser_reserved_connections).
• Make Hikari minimumIdle = maximumPoolSize only if you can afford that always-open count; otherwise let it shrink.

It depends on the transaction. With auto-commit on, drivers may commit chunks or each statement; with autoCommit=false and a rollback on failure, none persist. BatchUpdateException.getUpdateCounts() shows which succeeded: PostgreSQL stops at the first failure, MySQL may continue or stop (continueBatchOnError), Oracle continues.

try {
    ps.executeBatch();
    c.commit();
} catch (BatchUpdateException e) {
    c.rollback();
    int[] done = e.getUpdateCounts();
}

• For partial success semantics, split into smaller chunks with per-chunk commits and record failed chunks.
• Inspect e.getNextException() for the real cause (PostgreSQL wraps it).

Risky: long transactions hold locks and bloat undo/WAL, fail all-or-nothing, and one error loses everything. Commit in chunks (e.g. 1000-10000 rows) and use the fastest bulk path available.

• Enable rewrite flags (rewriteBatchedStatements, reWriteBatchedInserts).
• Fastest options: PostgreSQL COPY via CopyManager, MySQL LOAD DATA LOCAL INFILE, Oracle direct-path/array insert, SQL Server bulk copy.
• Drop or defer secondary indexes and constraints for one-time loads when allowed.
• Make the job restartable using a checkpoint/idempotent upsert.

In PostgreSQL a failed statement marks the transaction aborted: every subsequent statement fails with current transaction is aborted, commands ignored until end of transaction block, and commit() effectively performs a rollback (no exception from the driver in some versions).

• Swallowing the exception hides data loss: the code believes it committed.
• Use a savepoint around statements whose failure you tolerate, or autosave=conservative in the URL.
• MySQL/Oracle keep the transaction usable after a statement-level error.

Session state set on a physical connection (isolation, read-only, catalog, schema, auto-commit, session variables) persists after close() if the pool does not reset it. HikariCP tracks and resets the standard JDBC properties (autoCommit, readOnly, transactionIsolation, catalog, schema) on return, but not arbitrary SET statements or SET ROLE.

• If you run SET search_path, SET TIME ZONE or temp-table creation manually, reset it in finally or use connectionInitSql for constants.
• Use DISCARD ALL or pooler reset queries for PostgreSQL in poolers.
• With transaction-mode poolers, session state is unreliable by design.

The driver buffered the entire ResultSet in the heap (default for PostgreSQL with auto-commit and for MySQL). Switch to streaming/cursor mode and process row by row, or page by key.

c.setAutoCommit(false);               // PostgreSQL requirement
try (PreparedStatement ps = c.prepareStatement("select id, data from big",
        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
    ps.setFetchSize(2000);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) { handle(rs.getLong(1), rs.getString(2)); }
    }
}

• Select only needed columns; avoid collecting rows to a list.
• Heap sizing is not the fix; memory use must be bounded.

Connector/J throws Streaming result set ... is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. The unread rows still occupy the protocol stream.

• Use a second connection for the nested query, or read the first result set fully (cache keys) before issuing nested queries.
• useCursorFetch=true with a fetch size uses server-side cursors and permits interleaving.
• Closing a streaming result set before the end makes the driver read and discard all the remaining rows, which may take long.

No. Identifiers and keywords cannot be bound, so order by + sort is injectable (e.g. sort=(case when (select ...) then id else name end) leaks data via side channels). Allow-list column names and directions.

Set<String> allowed = Set.of("id", "name", "created_at");
if (!allowed.contains(sort)) { throw new IllegalArgumentException("bad sort"); }
String dir = "desc".equalsIgnoreCase(direction) ? "DESC" : "ASC";

• Map public names to real column names, so internal schema is not exposed.
• Never quote-escape as a substitute.

No injection, since it is bound as a value, but % and _ in user input act as wildcards, so a term of % matches everything and a pattern like %_%_% can be slow (DoS). Escape wildcards and declare an escape character.

String escaped = term.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_");
PreparedStatement ps = c.prepareStatement("select id from items where name like ? escape '\\'");
ps.setString(1, "%" + escaped + "%");

• A leading % prevents B-tree index use; consider trigram (pg_trgm) or full-text indexes.
• The default escape character differs by database (MySQL \, PostgreSQL \, SQL Server none).

An empty IN () is invalid SQL in most databases. Check the list first and return an empty result without querying (or substitute a predicate such as 1 = 0).

if (ids.isEmpty()) { return List.of(); }

• For NOT IN with an empty list the semantics are "true for all rows": skip the predicate instead.
• NOT IN (subquery) returns nothing if the subquery contains a NULL; prefer NOT EXISTS.

A java.sql.Date/Timestamp represents midnight in the JVM's zone. When writer and reader (or driver and server) use different zones, converting to UTC shifts the instant across midnight and the calendar date moves by a day.

• Use LocalDate with setObject/getObject(col, LocalDate.class) for date-only fields; no zone is involved.
• Align all JVMs to UTC and check driver zone options (connectionTimeZone, serverTimezone).
• Check for DATETIME vs TIMESTAMP column choice and API layer serialization (Date in JSON).

Treat it as retryable: roll back, then re-run the entire transaction a bounded number of times with jittered backoff. Meanwhile find the cause and reduce the frequency.

• PostgreSQL logs both queries involved (log_lock_waits, deadlock details in the server log); MySQL SHOW ENGINE INNODB STATUS shows "LATEST DETECTED DEADLOCK".
• Typical causes: different row update order in two code paths, updating parent and child in opposite order, gap locks under REPEATABLE READ, missing index causing wider locks.
• Fixes: lock in consistent order (e.g. sort ids before updating), shorten transactions, add the index, lower isolation where acceptable.

Each transaction locks its own debit account first and then the credit one, forming a cycle. Always acquire locks in a global order, e.g. ascending account id, regardless of direction.

long first = Math.min(from, to), second = Math.max(from, to);
lock(c, first);    // select ... from account where id = ? for update
lock(c, second);

• Alternatively use a single atomic UPDATE per account with conditions, or a single statement updating both rows ordered by id.
• Retry on deadlock as a safety net.

Read-modify-write in application code races. Make the write atomic in the DB, or lock/validate.

// atomic and safe
String sql = "update account set balance = balance - ? where id = ? and balance >= ?";
int n = ps.executeUpdate();   // 0 -> insufficient funds

• Alternatives: SELECT ... FOR UPDATE before reading, optimistic version column, or SERIALIZABLE with retry.
• READ COMMITTED does not stop the lost update for the read-then-write pattern; PostgreSQL REPEATABLE READ aborts the second writer with a serialization error.

This is a non-repeatable read (or phantoms for newly inserted rows) at READ COMMITTED: each statement sees the latest committed data. Use REPEATABLE READ (snapshot per transaction) or run the report on a consistent snapshot.

c.setAutoCommit(false);
c.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
c.setReadOnly(true);

• Keep such snapshot transactions short; long ones hold old versions, delaying vacuum/purge (PostgreSQL bloat, InnoDB history list growth).
• Better: compute all numbers in one SQL statement, which is consistent at any level.

SQLState 40001: the SSI (serializable snapshot isolation) engine detected a dependency cycle that would break serializable behaviour and aborted one transaction. It is expected and the application must retry the whole transaction.

• Keep transactions small and read-only ones flagged (setReadOnly(true) with DEFERRABLE avoids aborts).
• Retry with backoff; keep the count of retries bounded.
• MySQL SERIALIZABLE instead turns plain reads into locking reads, increasing lock waits and deadlocks.

Both checks pass, then one insert fails with a unique violation (SQLState 23505 / MySQL 1062). The check-then-insert is racy; rely on the unique constraint as the single arbiter and handle the violation.

insert into users(email, name) values (?, ?)
on conflict (email) do nothing;          -- PostgreSQL
-- MySQL: insert ignore ... / on duplicate key update ...

• executeUpdate() returning 0 means the row already existed; then select it.
• Catch SQLIntegrityConstraintViolationException and translate it to a domain "already exists" error.
• In PostgreSQL a failed insert aborts the transaction unless using ON CONFLICT or a savepoint.

A timeout or connection loss during commit() leaves the outcome unknown: the transaction may have committed. Blind retry duplicates the effect. Make the operation idempotent with a client-supplied idempotency key stored under a unique constraint within the same transaction.

insert into payments(idempotency_key, order_id, amount)
values (?, ?, ?) on conflict (idempotency_key) do nothing;

• On retry, if the key exists, return the previously stored result.
• After an unknown commit outcome, query by key before repeating.

0 means no rows matched: wrong id, a stale version, or a precondition (status = 'NEW') no longer true. Zero is not an error for JDBC, so you must check it and decide: raise not-found, concurrent-modification, or an idempotent no-op.

• Treating 0 as success silently drops writes (e.g. optimistic lock failure ignored).
• For MySQL, useAffectedRows=true changes the semantic to rows actually changed, so a same-value update returns 0.
• Check array counts of a batch for SUCCESS_NO_INFO (-2) and EXECUTE_FAILED (-3).

The statement was prepared without requesting the key column, so Oracle returns the ROWID (or nothing). Prepare with the column names array so the driver adds RETURNING id INTO ?.

PreparedStatement ps = c.prepareStatement(
    "insert into orders(customer_id) values (?)", new String[] {"ID"});

• Identity columns (12c+) and sequences with triggers both work this way.
• PostgreSQL and MySQL work with RETURN_GENERATED_KEYS; with MySQL batch rewriting the keys may be partially available.
• Using select max(id) afterward is unsafe under concurrency.

Set a savepoint before the optional step, roll back to it on failure, and continue; commit at the end persists the main work and skips the optional work.

c.setAutoCommit(false);
insertOrder(c, order);
Savepoint sp = c.setSavepoint("loyalty");
try {
    awardPoints(c, order);
} catch (SQLException e) {
    c.rollback(sp);
}
c.commit();

• Release savepoints in loops (releaseSavepoint) to avoid resource buildup; PostgreSQL subtransactions beyond 64 per transaction degrade performance.
• Spring NESTED propagation does the same with DataSourceTransactionManager.

Load orders in one query, then load all items for those order ids in a second query and group in memory; or use a single LEFT JOIN and fold rows into aggregates while iterating.

Map<Long, Order> byId = new LinkedHashMap<>();
String sql = "select o.id, o.status, i.sku from orders o left join order_items i on i.order_id = o.id where o.id = any (?)";
// for each row: byId.computeIfAbsent(rs.getLong("id"), ...).addItem(rs.getString("sku"))

• 2 queries total instead of N+1; with the join approach LIMIT applies to joined rows, so page parents in a subquery first.
• Verify with a statement counter in tests (datasource-proxy assertSelectCount).

100k round trips at ~0.2 ms is 20 s of network alone; the rest is per-query overhead and server work. Replace per-row lookups with set-based queries: join, IN/ANY in chunks of 500-1000, or load a lookup map once.

• Reuse a single PreparedStatement at least, so parse cost is paid once.
• For per-row updates use addBatch/executeBatch rather than executeUpdate per row.
• Consider whether the logic can run entirely in SQL (UPDATE ... FROM, INSERT ... SELECT).

Exiting try-with-resources closes the Statement and Connection, which closes the ResultSet before the caller iterates. The DAO must map rows to objects (or take a callback/RowMapper) inside the scope.

public List<User> findAll() throws SQLException {
    List<User> out = new ArrayList<>();
    try (Connection c = ds.getConnection();
         PreparedStatement ps = c.prepareStatement("select id, name from users");
         ResultSet rs = ps.executeQuery()) {
        while (rs.next()) { out.add(new User(rs.getLong(1), rs.getString(2))); }
    }
    return out;
}

• This is what JdbcTemplate.query(sql, rowMapper) does for you.

Executing a second query on the same Statement closes its existing ResultSet (JDBC spec). The outer loop's next call to rs.next() fails with ResultSet closed.

• Use a separate statement per concurrently open result set.
• Better, avoid nested queries: use a join or a second set-based query.
• MySQL streaming and some drivers also restrict multiple open cursors per connection.

Without a value the driver cannot determine the parameter type, and PostgreSQL requires it for planning. Provide a type: setNull(1, Types.VARCHAR) or setObject(1, null, Types.VARCHAR).

• In dynamic filters (where (? is null or col = ?)) the type of the first ? is unknown; add casts: (?::text is null or col = ?).
• That pattern also kills index use, since the plan is generic; build the SQL conditionally instead.

Call Statement.cancel() from another thread; the driver sends a cancel request (PostgreSQL via a new connection and the backend PID, MySQL via KILL QUERY), and the executing thread receives SQLException (e.g. SQLState 57014). Combine with setQueryTimeout as a hard cap.

• cancel() is the only statement method safe to call across threads.
• Interrupting the Java thread does not stop the server query, because JDBC I/O is not interruptible.
• Servers also support statement_timeout/max_execution_time to bound runaway queries regardless of the client.

Server-side prepared statements live on a backend connection; in transaction pooling, consecutive transactions may use different backends, so a handle prepared earlier is missing. Disable server prepares in the driver with prepareThreshold=0, or use PgBouncer 1.21+ with max_prepared_statements set.

jdbc:postgresql://pgbouncer:6432/app?prepareThreshold=0

• Other session features also break: SET (without LOCAL), advisory locks, LISTEN/NOTIFY, temp tables.
• Alternatively use session pooling mode with a smaller app pool.

Workers read the same row before any marks it. Claim jobs atomically with FOR UPDATE SKIP LOCKED (or an UPDATE ... RETURNING claim) in a short transaction, mark them RUNNING, commit, then process.

update jobs set status = 'RUNNING', worker = ?
where id = (select id from jobs where status = 'NEW'
            order by id limit 1 for update skip locked)
returning id, payload;

• Add a lease timestamp and a reaper for crashed workers.
• Processing should be idempotent (at-least-once semantics).
• Do not hold the lock during processing; it would keep the connection busy.

Not necessarily. The warning means the connection was held longer than the threshold, which fits a legitimately long job. Verify whether the connection is later returned ("Previously reported leaked connection ... was returned to the pool").

• Run long jobs on a separate DataSource with leak detection disabled or a higher threshold.
• If it is never returned, the stack trace shows the borrowing site to fix.
• Check whether the long hold is justified; chunking the job into shorter transactions frees connections.

Filter by application_name/client address and look at state, state_change, xact_start, wait_event and query. Many idle in transaction rows with old xact_start mean the app opened transactions and stopped sending statements: leaks or remote calls inside transactions.

select pid, state, now() - xact_start as xact_age, wait_event_type, wait_event, left(query, 80)
from pg_stat_activity
where datname = 'app' order by xact_start nulls last;

• wait_event_type = Lock shows blocked sessions; pg_blocking_pids(pid) shows the blocker.
• Mitigate with idle_in_transaction_session_timeout so DB kills leaked transactions.
• Set ApplicationName in the JDBC URL to identify the service.

Locks last until the transaction ends. If code did setAutoCommit(false), wrote rows, and returned the connection without commit() or rollback() (exception path), the transaction may stay open, so row locks persist and other sessions block.

• HikariCP rolls back uncommitted work on return when auto-commit is false (isolateInternalQueries aside), but other pools or raw usage may not.
• Always commit or roll back explicitly, and prefer frameworks managing transaction boundaries.
• Detect with pg_locks/pg_stat_activity or information_schema.innodb_trx.

Compare hikaricp_connections_acquire_seconds (wait to get a connection) with hikaricp_connections_usage_seconds (hold time) and hikaricp_connections_pending. If acquire time is large and pending threads are non-zero, requests queue for connections.

• Then decide: reduce hold time (shorter transactions, indexes), raise pool size within DB limit, or add instances.
• If acquire time is near zero and queries are slow, the bottleneck is in the DB or network.
• Add percentile timers rather than averages; tail latency exposes queueing.

Clob/Blob are locators tied to the connection/transaction. After the connection is returned or the ResultSet closed, reading raises SQLException (invalid LOB locator, or "Large Objects may not be used in auto-commit mode" in PostgreSQL). Read the content inside the scope.

String text = rs.getString("body");          // small text
// or stream large content
try (Reader r = rs.getCharacterStream("body")) { r.transferTo(writer); }

• Use Clob.free()/Blob.free() to release resources early (JDBC 4).
• For big objects, copy to a stream or file inside the transaction.

Exact count(*) scans an index or the table (PostgreSQL has no cached count; MVCC), so it costs O(n). Options: avoid the total (show "next page" only), use an approximate count (pg_class.reltuples, EXPLAIN estimate), cap it (select count(*) from (select 1 from t where ... limit 10001)), or maintain a counter table.

• Use keyset pagination for deep pages.
• Cache totals for popular filters for a short TTL.
• Make sure filters are covered by an index so count can use an index-only scan.

Not by itself. It means Hikari's borrow-time check found a dead connection (idle too long, killed by firewall or DB), discarded it and opened another; callers did not see an error. Frequent warnings point to a maxLifetime/idle timeout mismatch.

• Lower maxLifetime under the network/server idle limit and add keepaliveTime.
• If errors still reach callers, those connections died in-flight (DB restart, network drop): add retries.
• Warnings that coincide with a DB restart are expected.

Virtual threads make it cheap to have tens of thousands of concurrent requests, but connections remain scarce; all of them still queue for the same bounded pool. JDBC calls block (cheaply, unpinning in newer drivers) but hold the connection throughout.

• Pool size should be driven by DB capacity, not thread count; keep connectionTimeout small and shed load.
• Older drivers using synchronized around I/O pin carrier threads (JDK 21); update drivers or use -Djdk.tracePinnedThreads.
• Limit concurrency to DB-bound work with a Semaphore so queues form before the pool.

The PostgreSQL driver cached a server-side prepared statement (e.g. select * from t) whose result shape changed after ALTER TABLE. The server rejects the stale plan. The driver auto-retries only outside a transaction; within one the error surfaces.

• Avoid select *; list columns explicitly so the shape is stable.
• Roll connections after DDL (restart or let maxLifetime recycle), or use backward-compatible deploy sequencing.
• Setting prepareThreshold=0 avoids server-side caching at some performance cost.

Values were read as double (rs.getDouble) or the column is FLOAT/DOUBLE, so binary floating-point error appears when summing. Store money as NUMERIC(p,s) and read with getBigDecimal.

BigDecimal total = BigDecimal.ZERO;
while (rs.next()) { total = total.add(rs.getBigDecimal("amount")); }

• Let the DB do sum(amount) over NUMERIC (exact).
• Choose rounding mode explicitly (setScale(2, RoundingMode.HALF_EVEN)).
• Alternatively store minor units as BIGINT cents.

Use a separate DataSource for the replica (explicit routing, or Spring AbstractRoutingDataSource keyed by @Transactional(readOnly = true)), or a driver feature such as MySQL jdbc:mysql:replication:// with setReadOnly(true), or PostgreSQL targetServerType=preferSecondary.

• Replication lag means reads may be stale: do not read-your-writes from a replica without safeguards.
• Pools must be sized separately per target.
• Route by transaction (not statement) so a transaction never mixes both.

Non-transient errors (syntax, constraint violation, permission, data truncation) will never succeed and retries only add load and delay. Retries must be selective and bounded, using exception class or SQLState: deadlock/serialization (40001, 40P01), connection (08xxx), and lock timeouts.

static boolean retryable(SQLException e) {
    String s = e.getSQLState();
    return e instanceof SQLTransientException || e instanceof SQLRecoverableException
        || (s != null && (s.startsWith("08") || s.equals("40001") || s.equals("40P01")));
}

• Add exponential backoff with jitter and a max elapsed time.
• Ensure idempotency; never retry inside a nested transaction.

PostgreSQL has a real boolean type and does not implicitly cast integers to boolean, giving operator does not exist: boolean = integer. Use where flag = true or bind with setBoolean. MySQL treats BOOLEAN as TINYINT(1).

• Other portability traps: identifier case (PostgreSQL folds unquoted names to lower case), LIMIT vs FETCH FIRST, AUTO_INCREMENT vs identity/sequences, string comparison case sensitivity, empty string vs NULL in Oracle.
• Use JDBC escape syntax or an abstraction only when needed; test against the real engine (Testcontainers).

Libraries like datasource-proxy, p6spy or Hibernate statistics wrap the DataSource and intercept every Statement, so you can log SQL with bound values and timings, count statements per test, and warn on slow queries.

• In tests assert query counts (assertSelectCount(1)) to stop N+1 regressions.
• In production sample slow queries; avoid logging parameters with PII.
• Combine with pg_stat_statements/performance schema for server-side timing.
• Cost is small but non-zero; keep verbose logging off by default.

Refcursors are only valid within a transaction, so auto-commit must be off; the cursor must be registered as Types.REF_CURSOR (or Types.OTHER) and read via getObject, cast to ResultSet.

c.setAutoCommit(false);
try (CallableStatement cs = c.prepareCall("{? = call list_orders(?)}")) {
    cs.registerOutParameter(1, Types.REF_CURSOR);
    cs.setLong(2, customerId);
    cs.execute();
    try (ResultSet rs = (ResultSet) cs.getObject(1)) { while (rs.next()) { /* map */ } }
}

• With auto-commit the cursor closes at statement end, so reading fails or gives nothing.
• PostgreSQL 11+ procedures use CALL and OUT parameters; set escapeSyntaxCallMode=callIfNoReturn as needed.

A single long transaction (or a streaming read with auto-commit off) pins an old snapshot; PostgreSQL vacuum cannot remove dead row versions newer than it, and InnoDB's undo history grows, so bloat and slowdowns appear. Updating every row in one transaction also creates massive WAL/undo.

• Process in chunks with keyset pagination and commit per chunk (e.g. 1000-5000 rows).
• Use a read-only cursor on one connection and a separate connection to write in batches.
• Run heavy jobs off-peak and monitor xact_start/history list length.