JDBC - Connection Pooling

Quiz

For web sites with high traffic, resources to acquire a database connection can be expensive. Also, imagine a situation where there are 500 concurrent users of a web site and each is trying to get hold of a connection to select or update data. In this scenario, to prevent multiple users to corrupt the data, the database has locks. When a user does a select on a table, the table is locked. No other user can get hold of the table unless the lock is freed. For 500 users to wait for a connection is not acceptable.

Connection pooling in Java is a technique used to improve the performance of database-driven applications. Instead of creating a new connection every time the application needs to interact with the database, a pool of connections is created and managed by a connection pool manager. This eliminates the overhead of establishing a new connection each time, resulting in faster response times and better resource utilization.

Advantages of connection pooling

Following are the advantages of the connection pooling.

Improved performance − Connection pooling reduces the overhead of creating and closing connections, resulting in faster response times for your application.
Resource optimization − By reusing connections, connection pooling helps to conserve system resources, such as memory and network connections.
Scalability − Connection pools can be configured to grow or shrink dynamically based on demand, allowing your application to handle varying workloads.

Popular Connection Pooling Libraries

Following are popular connection pooling libraries.

HikariCP − Known for its performance and efficiency.
Apache Commons DBCP − Widely used and well-established.
c3p0 − Another popular option with a focus on stability.

Example: Connection Pooling Using HikariCP

We will use HikariCP connection pool library. In Eclipse, go to File -> New -> Java Project.

Set the Name of project as HikariCPExample. Click Finish. Now in Project explorer, right click on the project name, select Build Path -> Add External Archives. Add following 3 jars:

HikariCP-5.0.1.jar − Download from HikariCP-5.0.1.jar
mysql-connector-j-8.4.0.jar − Download from mysql-connector-j-8.4.0.jar
slf4j-api-2.1.0-alpha1.jar − Download from slf4j-api-2.1.0-alpha1.jar

In Eclipse, click on menu File -> New class to create a new class. Name the file as HikariCPManager. Don't write anything for package name. Click Finish.

In Eclipse, click on File -> New Class. Name the file TestPooledConnection. Don't give any package name. Click <Finish>.

HikariCPManager.java

We've created a HikariConfig() instance and set JDBC url, username and password. Maximum pool size is set as 10. getPooledConnection() method is used to get a database connection.

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

public class HikariCPManager {
   public static HikariDataSource dataSource;
   public  static HikariConfig config = null;
   // Set properties in constructor
   HikariCPManager(){
      config = new HikariConfig();
      config.setJdbcUrl("jdbc:mysql://localhost/TUTORIALSPOINT");
      config.setUsername("root");
      config.setPassword("guest123");
      // Set maximum connection pool size
      config.setMaximumPoolSize(10); 
      dataSource = new HikariDataSource(config);
   }

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

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

TestPooledConnection.java

In this example, we've created HikariCPManager instance and using getPooledConnection(), we've prepared a pooled connection. Once connection object is ready, we've prepared a PreparedStatement instance with a SELECT query. Using executeQuery(), we've executed the query and printed all the employees by iterating the resultSet received.

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

public class TestPooledConnection {
   public static void main(String[] args) {
      Connection conn = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      try {
         HikariCPManager hcpm = new HikariCPManager();
         conn = hcpm.getPooledConnection();

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

            // Execute query
            rs = pstmt.executeQuery();

            // Process and print results
            while (rs.next()) {
               int id = rs.getInt("id");
               String fname = rs.getString("first");
               String lname = rs.getString("last");
               System.out.println("ID: " + id + ", First Name: " + fname + ",  Last Name:  " + lname);
            }
         } else {
            System.out.println("Error getting connection.");
         }
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         // Close all resources
         try {
            if (rs != null) rs.close();
               if (pstmt != null) pstmt.close();
                  if (conn != null) conn.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
      // Close connection pool
      HikariCPManager.close();
   }
}

Output

In Eclipse, on Package explorer, right-click on TestPooledConnection.java, go to Run as -> Java application. On the console, you will see −

ID: 1, First Name: Shahbaz,  Last Name:  Ali
ID: 2, First Name: Mahnaz,  Last Name:  Fatma
ID: 4, First Name: Sumit,  Last Name:  Mittal
ID: 21, First Name: Jeevan,  Last Name:  Rao
ID: 22, First Name: Dinesh,  Last Name:  Kumar
ID: 25, First Name: Jeevan,  Last Name:  Rao
ID: 26, First Name: Aditya,  Last Name:  Chaube
ID: 34, First Name: Ahmed,  Last Name:  Ali
ID: 35, First Name: Raksha,  Last Name:  Agarwal

Print Page