Why Connection Pooling?

Introduction

Opening a JDBC connection is expensive: TCP handshake, authentication, and server-side session setup add up on every request. A connection pool keeps ready-to-use connections so your code borrows and returns them instead of creating new physical connections repeatedly. This chapter explains why pools exist and which parameters matter—before you wire up HikariCP in the next chapter.

Prerequisites

  • MySQL running with the jdbc_demo database
  • The users table from Your first JDBC program
  • A Maven project with mysql-connector-j in pom.xml

The Cost of DriverManager.getConnection()

When you call DriverManager.getConnection(), the driver performs roughly this sequence:

  1. Resolve the host and open a TCP socket.
  2. Perform the MySQL protocol handshake.
  3. Authenticate the user.
  4. Allocate server-side memory for the session.
  5. Negotiate session settings such as time zone and character set.

Reversing that process on close() tears down the session. In a web application handling hundreds of requests per second, creating and destroying connections per query wastes CPU and memory on both the client and the database server.

How a Pool Works

A connection pool maintains a set of idle physical connections:

plaintext
Request thread  →  getConnection()  →  borrow idle connection
                →  run SQL
                →  close()           →  return to pool (not destroy)

When your code calls close() on a pooled connection, the TCP socket usually stays open—the connection goes back to the pool for another thread.

Warning

Never Create Connections in a Loop

for (each row) { DriverManager.getConnection(); ... } is a common performance bug. Use one connection per transaction—or a pool shared by the whole application.

Core Pool Parameters

ParameterTypical defaultMeaning
Maximum pool size10Max physical connections the pool will open
Minimum idleSame as maxConnections kept warm while idle
Connection timeout30 sHow long a thread waits for a free connection
Idle timeout10 minHow long an unused connection may sit before retirement
Max lifetime30 minAbsolute age limit; connection replaced after this

Tip

Max Lifetime Matters

Databases and load balancers often kill stale connections. Set maxLifetime slightly below MySQL wait_timeout to avoid random "connection reset" errors overnight.

DataSource as the Abstraction

Production code should depend on javax.sql.DataSource, not raw URLs:

  • DriverManager — fine for one-off scripts and chapter demos
  • DataSource — implemented by HikariCP, Tomcat JDBC Pool, etc.; same DAO code for all

Your DAO constructor takes a DataSource; it does not know whether the connection came from HikariCP, a test double, or JNDI.

When You Still Skip a Pool

  • Local learning scripts with a single main() method
  • One-shot migration tools
  • Command-line utilities that run one query and exit

Anything long-lived (Spring Boot, servlet containers, background workers) should use a pool.

FAQ

Does pooling remove the need to close connections?

No. You must still call close() (or use try-with-resources). With a pool, close() means return to pool, not "disconnect forever."

One pool per application or per request?

One shared pool per application (or per JVM). Creating a new pool per HTTP request defeats the purpose.

What is Too many connections?

Often leaked connections (not closed) or maximumPoolSize too high for MySQL max_connections. Fix leaks first, then tune pool size.

What comes next?

Using HikariCP — Maven dependency, HikariConfig, DAO refactor, and benchmark vs DriverManager.