JavaData & JavaUnderstanding JDBC Connection Pooling

Understanding JDBC Connection Pooling

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Connection pooling is a mechanism to create and maintain a collection of JDBC connection objects. The primary objective of maintaining the pool of connection object is to leverage re-usability. A new connection object is created only when there are no connection objects available to reuse. This technique can improve overall performance of the application. This article will try to show how this pooling mechanism can be applied to a Java application.

Why Do We Need Connection Pooling?

Establishing a database connection is a very resource-intensive process and involves a lot of overhead. Moreover, in a multi-threaded environment, opening and closing a connection can worsen the situation greatly. To get a glimpse of what actually may happen with each request for creating new database connection, consider the following points. Database connections are established using either DriverManager of DataSource objects.

  • An application invokes the getConnection() method.
  • The JDBC driver requests a JVM socket.
  • JVM has to ensure that the call does not violate security aspects (as the case may be with applets).
  • The invocation may have to percolate through a firewall before getting into the network cloud.
  • On reaching the host, the server processes the connection request.
  • The database server initializes the connection object and returns back to the JDBC client (again, going through the same process).
  • And, finally, we get a connection object.

This is just an overview of what actually goes on behind the scenes. Rest assured, the actual process is more complicated and elaborate than this. In a single-threaded controlled environment, database transactions are mostly linear, like opening a connection, doing database transaction, and closing the connection when done. Real-life applications are more complex; the mechanism of connection pooling can add to the performance although there are many other properties that are critical to overall performance of the application.

The complexity of the concept of connection pooling gets nastier as we dive deep into it. But, thanks go to the people who work to produce libraries specifically for the cause of connection pooling. These libraries adhere to the norms of JDBC and provide simpler APIs to actually implement them in a Java application.

What Actually Happens with Connection Pooling?

Connection pooling works behind the scenes and does not affect how an application is coded. That means once the properties are set, the developer almost can forget about it and focus on the code, just like any other JDBC application. But, the application must use a DataSource object to obtain connection rather than using the DriverManager class. This, however, does not mean that if we are using a DataSource object, we are using connection pooling. What the DataSource object does is register with the JNDI naming service in a standard way. For example:

Context context=new InitialContext();
DataSource dataSource=(DataSource)
   context.lookup("jdbc/library_jndi");

But, when DataSource uses connection pooling, the lookup return a connection from the pool of available connection objects. If there is no available connection, the lookup creates a new connection. The application establishes a connection to the database and accesses data in the usual way.

Connection connection=dataSource.getConnection
   ("root","password");
// ...
connection.close();

Once the application is done with database activity, it explicitly closes the connection. This makes the released connection available again for reuse. The closing event of the pooled connection signals the pooling module to restore back to the connection pool. Because the reuse of the connection object does not require any code changes, it is faster that creating a new connection.

The JDBC 3.0 API provides the general framework for connection pooling. Several third-party vendors built upon this framework, implementing their own caching or pooling algorithms. The general JDBC API framework provides three hooks in the form of interfaces, upon which several third-party vendors implemented their connection pooling library. They are:

  • PooledConnection
  • ConnectionPooledDataSource
  • ConnectionEventListener

Refer to the Java API documentation to get an elaborate description on these interfaces. Let’s not get into the details here; instead, let’s focus on how to implement a connection pooling mechanism in a Java application. As mentioned earlier, there are many third-party libraries available, such as C3P0, UCP, BoneCP, H2, DBCP, and so forth, Here, we focus on only one of them.

What We Need for the Following Examples

The following example will be a minimal stand-alone Java application. The database we will be using is MySQL. Therefore, we need

  • Eclipse IDE (optional)
  • MySQL JDBC Driver

The DDL statement for the database table that we will work on is as follows:

create table books
(
   isbn varchar(20) primary key,
   title varchar(50),
   edition varchar(20),
   price float(10,2)
);

Connection Pooling with the c3p0 Library

c3p0 is a third-party connection pooling library, made available under the terms of LGPL or EPL. The official c3p0 site states:

c3p0 is an easy-to-use library for making traditional JDBC drivers “enterprise-ready” by augmenting them with functionality defined by the jdbc3 spec and the optional extensions to jdbc2. As of version 0.9.5, c3p0 fully supports the jdbc4 spec.

In particular, c3p0 provides several useful services:

  • A class that adapts traditional DriverManager-based JDBC drivers to the newer javax.sql.DataSource scheme for acquiring database Connections.
  • Transparent pooling of Connections and PreparedStatements behind DataSources that can “wrap” around traditional drivers or arbitrary unpooled DataSources.

It is designed to be simple and only needs two jar files:

  • c3p0.x.x.x.x.jar
  • mchange-commons-java-x.x.xjar

These jars need to be in the CLASSPATH, apart from MySQL JDBC driver.

package org.mano.example;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0DataSource {
   private static C3P0DataSource dataSource;
   private ComboPooledDataSource comboPooledDataSource;

   private C3P0DataSource() {
      try {
         comboPooledDataSource = new ComboPooledDataSource();
         comboPooledDataSource
            .setDriverClass("com.mysql.jdbc.Driver");
         comboPooledDataSource
            .setJdbcUrl("jdbc:mysql://localhost:3306/testdb");
         comboPooledDataSource.setUser("root");
         comboPooledDataSource.setPassword("secret");
      catch (PropertyVetoException ex1) {
         ex1.printStackTrace();
      }
   }

   public static C3P0DataSource getInstance() {
      if (dataSource == null)
         dataSource = new C3P0DataSource();
      return dataSource;
   }

   public Connection getConnection() {
      Connection con = null;
      try {
         con = comboPooledDataSource.getConnection();
      } catch (SQLException e) {
         e.printStackTrace();
      }
      return con;
   }
}

package org.mano.example;

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

public class MainApp {

   public static void insertOrUpdate(String isbn, String title,
         String edition, float price) {
      try (Connection con = C3P0DataSource.getInstance()
            .getConnection()) {
         PreparedStatement pstmt=null;
         if(isIsbnExists(isbn)){
            pstmt = con.prepareStatement("UPDATE books "
                  + "SET title=?,edition=?,"
                  + "price=? WHERE isbn LIKE ?");
            pstmt.setString(1, title);
            pstmt.setString(2, edition);
            pstmt.setFloat(3, price);
            pstmt.setString(4, isbn);
         }else{
            pstmt = con.prepareStatement("INSERT INTO "
                  + "books(isbn,title,"
                  + "edition,price) VALUES (?,?,?,?)");
            pstmt.setString(1, isbn);
            pstmt.setString(2, title);
            pstmt.setString(3, edition);
            pstmt.setFloat(4, price);
         }
         pstmt.executeUpdate();
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }

  public static void isIsbnExists(String isbn) {
      Boolean flag = false;
      try (Connection con = C3P0DataSource.getInstance()
            .getConnection()) {
         Statement stmt=con.createStatement();
         ResultSet rs=stmt.executeQuery("SELECT isbn "
                     +"FROM books WHERE "
                     +"isbn LIKE '"+isbn+"'");
         flag=rs.next();
      } catch (SQLException e) {
         e.printStackTrace();
      }
      return flag;
   }

   public static void delete(String isbn) {
      try (Connection con = C3P0DataSource.getInstance()
            .getConnection()) {
         PreparedStatement pstmt=null;
         if(isIsbnExists(isbn)){
            pstmt = con.prepareStatement("DELETE FROM "
                  + "books "
                  + "WHERE isbn LIKE ?");
            pstmt.setString(1, isbn);
            pstmt.executeUpdate();
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }

   public static void showAll() {
      try (Connection con = C3P0DataSource.getInstance()
            .getConnection()) {
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM books");
         ResultSetMetaData metadata = rs.getMetaData();
         int cols = metadata.getColumnCount();
         System.out.println("n-----------------------------"
         + "--------------------------------");
         for (int i = 0; i < cols; i++) {
            System.out.printf("%-20st",
            metadata.getColumnName(i + 1).toUpperCase());
         }
         System.out.println("n-----------------------------"
         + "--------------------------------");
         while (rs.next()) {
            for (int i = 0; i < cols; i++)
            System.out.printf("%-20st", rs.getObject(i + 1));
            System.out.println();
         }
         System.out.println("-------------------------------"
         + "--------------------------------");

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

   public static void main(String[] args) {
      showAll();
      insertOrUpdate("111111", "Complex Numbers",
         "Second", 56.78f);
      showAll();
      insertOrUpdate("111111", "Complex Numbers",
         "Fourth", 87.50f);
      showAll();
      delete("111111");
      showAll();
   }
}

Output

----------------------------------------------------------------
ISBN             TITLE                   EDITION           PRICE
----------------------------------------------------------------
456789           Graph Theory            Second            33.8
567890           Set Theory              Fourth            34.89
----------------------------------------------------------------

----------------------------------------------------------------
ISBN             TITLE                   EDITION           PRICE
----------------------------------------------------------------
111111           Complex Numbers         Second            56.78
456789           Graph Theory            Second            33.8
567890           Set Theory              Fourth            34.89
----------------------------------------------------------------

----------------------------------------------------------------
ISBN             TITLE                   EDITION           PRICE
----------------------------------------------------------------
111111           Complex Numbers         Fourth            87.5
456789           Graph Theory            Second            33.8
567890           Set Theory              Fourth            34.89
----------------------------------------------------------------

----------------------------------------------------------------
ISBN             TITLE                  EDITION            PRICE
----------------------------------------------------------------
456789           Graph Theory           Second             33.8
567890           Set Theory             Fourth             34.89
----------------------------------------------------------------

Conclusion

Connection pooling is particularly suitable for optimizing performance in a high-load environment where a request for connection is dropped or delayed. This type of situation has adverse impact in the overall performance of the application. In fact, when creating a JDBC Web or enterprise application, it is always a practice to use connection pools for all practical reasons. The third-party libraries are pretty much stable and able to provide what they claim. Understanding the concept of connection pooling and being able to implement them is a must for all JDBC programmers.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories