Implementing Connection Pooling with JDBC

Course: Advanced Java Training

Topic: Implementing Connection Pooling with JDBC

Course Link: Advanced Java Training Course

Demo Link: Register for a Course Demo

JustAcademy Homepage: Visit JustAcademy

1. Introduction to JDBC Connection Pooling

JDBC (Java Database Connectivity) allows Java applications to communicate with relational databases such as MySQL, PostgreSQL, and Oracle. Whenever an application needs database access, it can obtain a connection using JDBC.

Creating a new database connection for every request can consume time and resources because the application must establish a network connection, authenticate with the database, and initialize the database session. In applications with frequent database requests, this repeated process can reduce performance.

Connection pooling solves this problem by maintaining a collection of reusable database connections. Instead of creating a new physical connection for every database operation, an application borrows an available connection from the pool, uses it, and returns it for reuse.

Connection pools are commonly used in web applications, REST APIs, enterprise applications, e-commerce platforms, and systems that process many database requests.

2. Learning Objectives

• Understand JDBC connection pooling and its purpose.
• Identify the limitations of creating connections repeatedly.
• Understand the role of the DataSource interface.
• Implement a connection pool using HikariCP.
• Configure minimum and maximum pool sizes and connection timeouts.
• Obtain and release pooled connections safely.
• Use connection pooling with SQL queries and transactions.
• Identify common configuration problems and performance issues.

3. What Is Connection Pooling?

Connection pooling is a technique in which a connection pool manager maintains reusable database connections. Applications borrow connections when needed and return them after completing their work.

How Connection Pooling Works

1. The application starts and initializes a connection pool.
2. The pool creates or gradually establishes physical database connections according to its configuration and demand.
3. A Java component requests a connection from the pool.
4. The pool supplies an available connection or waits for one until the configured timeout expires.
5. The application executes SQL statements using the connection.
6. The application closes the logical connection when finished.
7. The pool normally makes the underlying connection available for another request rather than destroying it immediately.

With a pooling implementation such as HikariCP, closing a borrowed connection normally returns it to the pool. Closing the pool itself is a separate operation that releases the pool's resources.

4. Why Is Connection Pooling Important?

Connection pooling does not automatically make every query faster. Its benefits depend on the workload, pool configuration, database capacity, and how efficiently the application uses its connections.

5. Understanding the DataSource Interface

The JDBC DataSource interface, available in javax.sql, provides a standard way to obtain database connections. Connection pools commonly implement or expose this interface so that application code can request connections without managing the physical connection lifecycle directly.

Basic Syntax

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

DataSource dataSource = /* configured data source */;

try (Connection connection = dataSource.getConnection()) {
    System.out.println("Database connection obtained.");
} catch (SQLException e) {
    e.printStackTrace();
}

The code above illustrates the general JDBC pattern. A real application must initialize dataSource using a suitable implementation before requesting a connection.

6. Introduction to HikariCP

HikariCP is a lightweight JDBC connection pooling library commonly used in Java applications. It manages a pool of database connections and exposes them through a DataSource.

HikariCP can be used in plain Java applications and integrated with frameworks such as Spring. The following example uses plain Java and MySQL to demonstrate the core concepts.

Key HikariCP Features

• Reusable database connections.
• Configurable maximum pool size.
• Connection acquisition timeouts.
• Idle connection management.
• Connection lifecycle and health management.
• Integration with JDBC-based applications and frameworks.

7. Adding HikariCP and MySQL Dependencies

If your project uses Maven, add the following dependencies to the pom.xml file. Select compatible versions according to your Java runtime and project requirements; these are example versions rather than a recommendation to use them in every environment.

<dependencies>
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>6.3.0</version>
    </dependency>

    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>9.3.0</version>
    </dependency>
</dependencies>

Maven downloads the required libraries and makes them available to the application. If you use Gradle, add the equivalent dependencies to your Gradle build file.

Important: Verify current versions and Java compatibility before using these dependencies in production.

8. Preparing the MySQL Database

Create a database and a sample table for testing the connection pool.

CREATE DATABASE company_db;

USE company_db;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(80) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

INSERT INTO employees
(employee_id, employee_name, department, salary)
VALUES
(101, 'Aarav Sharma', 'IT', 65000.00),
(102, 'Priya Verma', 'HR', 55000.00),
(103, 'Rahul Mehta', 'Finance', 60000.00);

Make sure MySQL is running and that the configured database user has the necessary permissions to access company_db.

9. Configuring the HikariCP Connection Pool

Create a Java class that initializes a HikariDataSource. This object manages the pool and supplies JDBC connections to application code.

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public final class DatabasePool {
    private static final HikariDataSource DATA_SOURCE =
            createDataSource();

    private DatabasePool() {
    }

    private static HikariDataSource createDataSource() {
        HikariConfig config = new HikariConfig();

        config.setJdbcUrl(
            "jdbc:mysql://localhost:3306/company_db"
        );
        config.setUsername(System.getenv("DB_USER"));
        config.setPassword(System.getenv("DB_PASSWORD"));

        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        config.setPoolName("CompanyPool");

        return new HikariDataSource(config);
    }

    public static HikariDataSource getDataSource() {
        return DATA_SOURCE;
    }

    public static void shutdown() {
        if (!DATA_SOURCE.isClosed()) {
            DATA_SOURCE.close();
        }
    }
}

Explanation of Configuration Properties

These values are illustrative. Pool size, idle timeout, maximum lifetime, and acquisition timeout should be tuned to the application's workload, database limits, network configuration, and deployment environment.

10. Obtaining and Releasing a Pooled Connection

After initializing the pool, obtain a connection with getConnection(). Use try-with-resources to ensure the borrowed connection is closed after the operation.

import java.sql.Connection;
import java.sql.SQLException;

public class ConnectionPoolTest {
    public static void main(String[] args) {
        try (Connection connection =
                 DatabasePool.getDataSource().getConnection()) {

            System.out.println("Connection obtained successfully.");
            System.out.println(
                "Connected to: " +
                connection.getMetaData().getDatabaseProductName()
            );

        } catch (SQLException e) {
            System.err.println(
                "Unable to obtain or use a database connection."
            );
            e.printStackTrace();
        } finally {
            DatabasePool.shutdown();
        }
    }
}

Explanation: The application requests a connection from the pool. The connection is closed automatically when the try-with-resources block ends. With HikariCP, closing this borrowed connection normally returns it to the pool. The pool itself is closed in the finally block when the example application finishes.

In a long-running web application, do not shut down the shared pool after each request. Initialize it for the application's lifecycle and close it when the application is stopping.

11. Executing SQL Queries Using a Pooled Connection

Connection pooling does not change the way SQL statements are executed. You can use standard JDBC interfaces such as PreparedStatement and ResultSet.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class EmployeeRepository {
    public void displayEmployees() {
        String sql =
            "SELECT employee_id, employee_name, department, salary " +
            "FROM employees";

        try (Connection connection =
                 DatabasePool.getDataSource().getConnection();
             PreparedStatement statement =
                 connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {

            while (resultSet.next()) {
                System.out.println(
                    resultSet.getInt("employee_id") + " | " +
                    resultSet.getString("employee_name") + " | " +
                    resultSet.getString("department") + " | " +
                    resultSet.getBigDecimal("salary")
                );
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Every call to displayEmployees() borrows a connection from the shared pool, executes the query, and returns the connection when the resource scope ends.

12. Managing Transactions with Pooled Connections

A pooled connection can also be used for transactions. Disable auto-commit, perform related operations, and commit or roll back before returning the connection to the pool.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class EmployeeService {
    public void updateSalary(int employeeId, double salary)
            throws SQLException {

        String sql =
            "UPDATE employees SET salary = ? WHERE employee_id = ?";

        try (Connection connection =
                 DatabasePool.getDataSource().getConnection()) {

            connection.setAutoCommit(false);

            try (PreparedStatement statement =
                     connection.prepareStatement(sql)) {

                statement.setBigDecimal(
                    1,
                    java.math.BigDecimal.valueOf(salary)
                );
                statement.setInt(2, employeeId);

                int rows = statement.executeUpdate();

                if (rows != 1) {
                    throw new SQLException(
                        "Expected one employee row to be updated."
                    );
                }

                connection.commit();

            } catch (SQLException | RuntimeException e) {
                try {
                    connection.rollback();
                } catch (SQLException rollbackError) {
                    e.addSuppressed(rollbackError);
                }
                throw e;
            }
        }
    }
}

Important: Ensure a connection is returned to the pool with transaction state handled correctly. HikariCP resets certain connection state when a connection is returned, but applications should not depend on pool cleanup as a replacement for explicit transaction handling. A failure during commit can leave the final transaction outcome uncertain and may require further investigation.

13. Connection Pooling vs. Traditional JDBC Connections

14. Common Connection Pooling Errors

15. Best Practices for JDBC Connection Pooling

• Use a maintained connection pooling library such as HikariCP rather than writing a custom pool without a compelling reason.
• Use a shared, application-managed pool instead of creating a new pool for every request.
• Close borrowed connections, statements, and result sets with try-with-resources.
• Keep transactions short and avoid holding connections while performing unrelated work.
• Set the maximum pool size according to database capacity and actual concurrency requirements.
• Remember that the maximum pool size applies per pool instance; account for every application replica and other clients of the database.
• Keep database credentials outside source code and use environment variables or a secrets-management solution.
• Use parameterized SQL to reduce SQL injection risk.
• Monitor connection acquisition latency, active connections, idle connections, and timeouts.
• Do not assume that increasing the pool size always improves performance. Excessive connections can overload the database.
• Configure connection validation and lifecycle settings according to the selected pool, JDBC driver, database, and infrastructure.
• Close the pool gracefully when the application shuts down.

16. Mini Project: Employee Management Using Connection Pooling

Project Objective: Develop a simple Java application that manages employee records using JDBC and HikariCP.

Functional Requirements

• Initialize a reusable database connection pool.
• Insert employee records using PreparedStatement.
• Retrieve and display employee information.
• Update employee salaries.
• Delete employee records by ID.
• Use transactions for operations that require multiple SQL statements.
• Handle database exceptions appropriately.
• Close the connection pool when the application shuts down.

Suggested Project Structure

employee-management/
├── pom.xml
└── src/
    └── main/
        └── java/
            ├── DatabasePool.java
            ├── EmployeeRepository.java
            ├── EmployeeService.java
            └── Main.java

Expected Learning Outcomes

By completing this project, learners will understand how to configure a connection pool, share it across application components, execute JDBC queries safely, manage transaction boundaries, and handle connection lifecycle issues.

17. Interview Questions and Answers

Q1. What is JDBC connection pooling?

JDBC connection pooling is a technique that reuses database connections instead of repeatedly creating new physical connections for application requests.

Q2. Why is connection pooling used?

It can reduce connection setup overhead, centralize connection management, and limit the number of connections opened by an application.

Q3. What is HikariCP?

HikariCP is a Java library that provides JDBC connection pooling through a configurable data source.

Q4. What is the purpose of DataSource?

DataSource is a JDBC interface used to obtain database connections. It provides a standard abstraction that connection pools and application frameworks can implement or expose.

Q5. Does calling close() destroy a pooled connection?

When closing a connection borrowed from HikariCP, the logical connection is normally returned to the pool for reuse. Closing the pool itself releases its managed resources.

Q6. What does maximumPoolSize mean?

It specifies the maximum number of connections that a particular pool manages concurrently.

Q7. What happens when all connections are busy?

A connection request waits for an available connection until the configured connection acquisition timeout expires. If no connection becomes available, the pool can throw an exception.

Q8. What is connection leakage?

Connection leakage occurs when application code borrows a connection but fails to return it to the pool, reducing the number of connections available for other requests.

Q9. Can connection pooling replace transaction management?

No. Connection pooling manages connection reuse and availability. Transactions still require correct commit, rollback, and transaction-boundary handling.

Q10. Should the pool be closed after every query?

No. A shared pool should usually remain open for the application's lifetime and be closed during application shutdown.

18. Practice Exercises

1. Add HikariCP and the MySQL JDBC driver to a Maven project.
2. Create a connection pool with a maximum of five connections.
3. Write a program that retrieves employee records using a pooled connection.
4. Insert a new employee using a parameterized SQL statement.
5. Update an employee's salary within a transaction.
6. Simulate slow queries and investigate connection acquisition timeouts.
7. Test what happens when borrowed connections are not closed.
8. Compare connection setup behavior with and without pooling.
9. Calculate a suitable total connection budget for several application instances.

19. Quick Revision Checklist

• Understand how a connection pool reuses physical connections.
• Know the role of JDBC DataSource.
• Understand HikariCP configuration and lifecycle.
• Use getConnection() to borrow a pooled connection.
• Use try-with-resources to return borrowed connections safely.
• Configure pool size and timeouts according to workload.
• Manage transactions explicitly.
• Monitor connection usage and acquisition failures.
• Close the shared pool during application shutdown.

20. Official Documentation and Further Learning

• HikariCP Official GitHub Repository — installation, configuration, and usage information.
• HikariCP Documentation Website — connection pool concepts and configuration guidance.
• Java DataSource API Documentation — JDBC connection factory interface.
• Spring Framework: Controlling Database Connections — data source configuration and connection management.

21. Conclusion

Connection pooling is an important technique for building efficient Java database applications. By reusing database connections, a pool such as HikariCP can reduce repeated connection setup overhead and control the number of connections managed by the application.

Successful implementation requires correct pool configuration, careful connection lifecycle management, secure credential handling, efficient SQL queries, and proper transaction management. Practice these concepts by building an employee management application and testing it under different workloads.

To explore more topics in JDBC, enterprise Java, and database application development, visit the Advanced Java Training Course, register for the JustAcademy Course Demo, or visit the JustAcademy Homepage.