Comparison of Database Connection Pool Parameters and Recommended Settings

The author revisits a previous article on Tomcat‑JDBC connection pool parameters and expands the discussion to include several other popular pools, focusing on how they implement connection validation and the impact on reliability and performance.

Tomcat‑JDBC : testOnBorrow is disabled by default and governed by validationInterval (default 30 seconds). For mission‑critical services, setting validationInterval to 0 forces validation on every borrow, ensuring immediate detection of network or database failures at the cost of extra overhead.

Druid : testOnBorrow always checks the connection on each borrow, with no interval configuration. While Druid also validates SQL failures, the author argues that even for critical paths the check should remain enabled to avoid first‑request failures after a network recovery.

The article then surveys other pools:

MySQL .NET Connector : Not a pool per se, but its driver includes a pool that pings the database on each borrow.

c3p0 : Uses parameters such as testConnectionOnCheckout , testConnectionOnCheckin , idleConnectionTestPeriod , and preferredTestQuery . Enabling testConnectionOnCheckout validates each borrow, with customizable testers.

HikariCP : Lacks explicit test flags; if a connectionTestQuery is provided it runs on each borrow, otherwise it relies on java.sql.Connection.isValid . Validation is always performed, and the pool employs a lock‑free ConcurrentBag with ThreadLocal caching for high performance.

BoneCP : No borrow‑time validation switch. It offers idleConnectionTestPeriodInSeconds and connectionTestStatement for idle checks, and marks connections as possibly broken based on SQL exceptions, testing them on return.

Summary

Tomcat‑JDBC: Enable testOnBorrow and set validationInterval to 0 for critical paths.

Druid: Enable testOnBorrow to guarantee per‑borrow validation.

c3p0: Enable testConnectionOnCheckout for per‑borrow checks, using either JDBC validation or a custom query.

HikariCP: Validation is mandatory; provide a connectionTestQuery if needed.

BoneCP: No per‑borrow validation; relies on exception‑based detection and idle checks.

Recommended usage : Avoid BoneCP for critical applications. For Tomcat‑JDBC, turn on testOnBorrow and set validationInterval to 0. Enable testOnBorrow in Druid, testConnectionOnCheckout in c3p0, and rely on HikariCP’s built‑in validation.