Connection Pooling and DataSource in JDBC

Naveen AnamaneniUpdated Nov 13, 2026 · 7 min read

mediumJava 25java jdbc performance datasource interview

Connection pooling is the practice of keeping a set of already-open database connections and reusing them, so the program doesn't open a new connection for every piece of work. Opening a connection is expensive: the driver must create a network connection, the database must check the user name and password, and both sides allocate memory. A connection pool opens a number of connections once, lends one to each caller that asks, and takes it back when the caller is done. In JDBC, a pool is reached through the javax.sql.DataSource interface. In this lesson, you will learn how pooling works, how to use a DataSource, which settings matter, and which pool libraries are used in practice.

How a Pool Works

Step	What happens
1. Startup	The pool opens some connections and keeps them idle.
2. Borrow	`dataSource.getConnection()` hands out an idle connection at once.
3. Use	The caller runs its SQL as usual.
4. Return	The caller calls `close()`. The pool does not close the real connection; it marks it idle again.
5. Full	If every connection is in use, the next caller waits, up to a timeout.

The crucial point is step 4: with a pool, close() means "give it back". Your code doesn't change at all, which is why try-with-resources works perfectly with pooled connections.

DriverManager vs DataSource

Point	`DriverManager.getConnection`	`DataSource.getConnection`
Each call	Opens a new connection (slow)	Borrows an open connection (fast)
`close()`	Really closes it	Returns it to the pool
Limit on connections	None; a busy application can overwhelm the database	A fixed maximum
Configuration	In the calling code	In one place, when the `DataSource` is created
Used in	Small programs	Every server application

Example: A Pool in Action

H2 includes a simple pool, JdbcConnectionPool, which is enough to see the behavior. It implements DataSource.

Pool.java▶ Try it

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import org.h2.jdbcx.JdbcConnectionPool;
 
public class Pool {
    // Application code only knows the DataSource interface.
    static int countStudents(DataSource ds) throws SQLException {
        try (Connection con = ds.getConnection();
             Statement st = con.createStatement();
             ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM student")) {
            rs.next();
            return rs.getInt(1);
        }
    }
 
    public static void main(String[] args) throws Exception {
        JdbcConnectionPool pool = JdbcConnectionPool.create(
            "jdbc:h2:mem:school;DB_CLOSE_DELAY=-1", "sa", "");
        pool.setMaxConnections(3);
        try {
            try (Connection con = pool.getConnection();
                 Statement st = con.createStatement()) {
                st.execute("CREATE TABLE student (id INT, name VARCHAR(50))");
                st.executeUpdate("INSERT INTO student VALUES "
                    + "(1, 'Navika'), (2, 'Srihitha'), (3, 'Advitha')");
            }
            System.out.println("Students: " + countStudents(pool));
            System.out.println("In use after the query: "
                + pool.getActiveConnections());
 
            Connection a = pool.getConnection();
            Connection b = pool.getConnection();
            System.out.println("Borrowed two: " + pool.getActiveConnections()
                + " in use, limit " + pool.getMaxConnections());
            a.close();
            b.close();
            System.out.println("Returned them: "
                + pool.getActiveConnections() + " in use");
 
            // Borrow more than the pool has and never give them back.
            pool.setLoginTimeout(1);
            Connection[] held = new Connection[3];
            for (int i = 0; i < 3; i++) {
                held[i] = pool.getConnection();
            }
            try {
                pool.getConnection();
            } catch (SQLException e) {
                System.out.println("Pool empty: " + e.getMessage());
            }
            for (Connection c : held) {
                c.close();
            }
        } finally {
            pool.dispose();
        }
    }
}

Output

Students: 3
In use after the query: 0
Borrowed two: 2 in use, limit 3
Returned them: 0 in use
Pool empty: Login timeout

Explanation:

countStudents takes a DataSource, not a pool class. It borrows a connection, uses it, and returns it through try-with-resources. This method would work unchanged with any pool library.
"In use after the query: 0" shows that close() returned the connection to the pool.
Borrowing two raises the in-use count to 2; closing them brings it back to 0. The real connections stay open inside the pool, ready for the next caller.
The limit: the pool allows 3 connections. With all 3 borrowed and not returned, a fourth request waits for the 1-second timeout and then fails. In a real application, that error almost always means some code forgot to close a connection.

Pool Libraries

Library	Notes
HikariCP	The most widely used pool; fast and small. The default in Spring Boot.
Apache Commons DBCP 2	A long-standing pool from Apache.
c3p0	An older pool, still found in existing projects.
Tomcat JDBC Pool	Comes with the Tomcat server.

Setting up HikariCP looks like this:

HikariSetup.java

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/school");
config.setUsername(System.getenv("DB_USER"));
config.setPassword(System.getenv("DB_PASSWORD"));
config.setMaximumPoolSize(10);
config.setConnectionTimeout(3000);   // ms to wait for a free connection
 
DataSource dataSource = new HikariDataSource(config);

After that, the rest of the application only sees DataSource.

Settings That Matter

Setting	Meaning	Guidance
Maximum pool size	The most connections the pool will open	Start small (around 10); more is often slower, because the database has limited CPU and disks
Minimum idle	Connections kept ready when quiet	Often the same as the maximum
Connection timeout	How long a caller waits for a free connection	A few seconds; fail fast
Idle timeout, maximum lifetime	When unused or old connections are retired	Keep the lifetime below any timeout the database or network imposes
Validation	Testing a connection before lending it	Pools do this with `isValid`

Common Mistakes

Not closing borrowed connections. The pool empties and every caller times out. This is the classic connection leak.

A pool that is too large. Hundreds of connections make the database slower, not faster.

Creating a new pool for each request. A pool is created once, at startup, and shared.

Leaving a borrowed connection in a changed state. Restore auto-commit and end transactions before returning it; the next borrower gets the same connection.

Interview Corner

What is connection pooling and why is it used?

Connection pooling maintains a cache of open database connections that are reused instead of being created and closed for each request. Creating a connection is costly (network setup, authentication, session allocation), so reuse greatly improves response time and throughput, and the pool's maximum size protects the database from being overwhelmed. Applications obtain connections from a javax.sql.DataSource; calling close() on a pooled connection returns it to the pool. Common implementations are HikariCP, Apache DBCP and c3p0.

What is a DataSource?

javax.sql.DataSource is the standard interface for a factory of connections, the preferred alternative to DriverManager. It is configured once with the URL, credentials and pool settings, and then passed to the code that needs connections, which calls getConnection(). Implementations may be basic, pooled, or support distributed transactions.

Practice

Easy: Change the pool's maximum to 5 and adjust the last part of the example so it still demonstrates an empty pool.
Harder: Rewrite the StudentDao from "CRUD Operations with JDBC" to take a DataSource and borrow a connection inside each method.

Quick check: Connection pooling

4 questions

1What does close() do on a pooled connection?

Closes the real database connectionReturns the connection to the poolDeletes the poolRolls back and reconnects

2Why is opening a connection expensive?

Java is slowNetwork setup, authentication and session memory are needed each timeThe driver is recompiledIt writes to disk

3Every connection is in use and another is requested. What happens?

A new connection is always openedThe caller waits, then fails after the timeoutThe oldest user is disconnectedThe program exits

4What usually causes a pool to run out?

Too little memoryCode that borrows connections and never closes themToo many tablesSlow disks

Show answers

Returns the connection to the pool: It becomes available to the next caller.
Network setup, authentication and session memory are needed each time: A pool pays that cost once.
The caller waits, then fails after the timeout: The maximum size is a hard limit.
Code that borrows connections and never closes them: A connection leak.

Key Takeaways

Was this tutorial helpful?

Lesson 500 of 578

How a Pool Works

DriverManager vs DataSource

Example: A Pool in Action

Pool Libraries

Settings That Matter

Common Mistakes

Interview Corner

Practice

Key Takeaways

Related tutorials

Batch Processing in JDBC

Buffered Streams in Java

CallableStatement in Java