How to Handle Connection Pooling in JDBC in Java?

In Java applications, interacting with the databases using JDBC (Java Database Connectivity), and managing the database connections efficiently is crucial for optimal performance and resource utilization. Connection pooling is the technique employed to achieve the goal by reusing the established database connections instead of creating new ones for each request.

JDBC usage is establishing the new database connection involving the costly network communication process, authentication, and resource allocation. This can significantly impact application performance, especially in the interactions of frequent databases. When an application needs to perform database operations, it will be requested to the connection from the pool, utilized it and it will be returned it to the pool once it is done. This approach can minimize the overhead of the connection establishment and teardown, leading to be improved performance and scalability.

Prerequisites:

The Following are the Prerequisites to Handle Connection Pooling in JDBC in Java

1. Database Setup
2. JDBC Driver (Java Database Connectivity Driver)
3. Connection Pooling Library
4. Java Development Environment
5. Project Configuration

Steps to Handle Connection Pooling in JDBC

Step 1: Create a Table in Your Database

1. Create a table in your database (Example: Employee).

Here is the basic example of an employee table:

Step 2: Set Up Project in Eclipse IDE

1. Open Eclipse IDE. Create one Java project and name it as JDBCWithHikariCP.
2. Add HikariCP Dependency to the Java project.
3. Create two Class files in a Java project and name it as ConnectionManager and Main respectively.

Here is the path for Class files in Java project:

Step 3: Implement the Code

Open ConnnectionManager.java file and replace with the below code.

import java.sql.Connection;
import java.sql.SQLException;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class ConnectionManager {
    private static final HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3307/work");
        config.setUsername("root");
        config.setPassword("tiger");
      
          // Set maximum pool size
        config.setMaximumPoolSize(10); 
        // Other configuration options

        dataSource = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static void close() {
        if (dataSource != null) {
            dataSource.close();
        }
    }
}

Explanation of the above Program:

1. Import the classes/interfaces for the JDBC and HikariCP configuration.
2. Manage the database connection using the HikariCP connection pooling.
3. Once the class is loaded into the memory, it will be execute.
4. Configures HikariCP DataSource with the connection parameters such as JDBC URL, username, passwords and maximum pool size.
5. Retrieves a database connection from connection pool with the help of getConnection() Method.
6. If a connection can't be obtained, it will throws the SQLException.
7. Closes the connection with close() method.
8. To avoid the NullPointerException, checks if the dataSource is not null before closing.

Note: Make sure that you can replace JDBC URL, username and password.

Step 4: Use ConnectionManager in Main Class

Open Main.java file and replace with the below code:

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

public class Main {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            // Get connection from ConnectionManager
            connection = ConnectionManager.getConnection();

            if (connection != null) {
                // Prepare statement
                String sql = "SELECT * FROM employees";
                preparedStatement = connection.prepareStatement(sql);

                // Execute query
                resultSet = preparedStatement.executeQuery();

                // Process and print results
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    System.out.println("ID: " + id + ", Name: " + name);
                }
            } else {
                System.out.println("Failed to get connection.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Close resources
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close(); // Return connection to pool
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        // Close the connection pool when application exits
        ConnectionManager.close();
    }
}

Explanation of the above Program:

1. In main class, program starts the execution.
2. We can declare the three variables i.e. connection, preparedstatement, resultSet .Here, connection is used to establish the connection to the database. preparedstatement s used to execute the SQL queries. resultSet is used to stores the results returned by database query.
3. We are use the try-catch block to handle the potential errors that will be occur during the database operations.
4. We are get the database connection from ConnectionManager class. Make sure it is properly configured.
5. If you are getting the connection successfully, we can prepare an SQL statement for retrieve data from database table employees.
6. We are execute SQL query and store the results in resultSet.
7. We loop through resultSet to retrieve the each row data such as ID and name and it prints in the console window.
8. If any errors are occurred in the above steps SQLException will be catch the exception and prints the message in the console window.
9. Make sure that database resources are properly closed in the finally block to avoid the risks.
10. After finish the program, we can close the connection pool which is managed by the ConnectionManager.

Step 5: Run the Code

1. After complete the implementation of code, you need to run the code.
2. For run the code, right click on the project the Run As > Java Application.

Here is the Output shown below in your console window:

Note:

1. If you get "Failed to get connection", it means application is failed to establish a connection to the database.
2. You need to check the following troubleshoot the issues:
   • Verify Database URL
   • Check Database Credentials
   • Database Server Running
   • Firewall and Network Issues
   • Database Driver
   • Database Configuration
   • Error Logging

jagan716

Follow

Improve

Article Tags :

• Java
• Java Programs
• Java Examples

Practice Tags :

• Java