JavaPerspective.com - 2.6. Connection pooling

JavaPerspective.com > Advanced Tutorials > 2. JDBC - database access > 2.6. Connection pooling

2.6. Connection pooling

Last updated: 1 January 2013.

This tutorial discusses connection pooling with JDBC.

As you might already know, a typical database access involves 3 steps:

Establish a connection with the database by calling one of the methods getConnection provided by the class java.sql.DriverManager.
Use the created connection to execute one or more SQL statements.
Close the connection by calling the method close provided by the class java.sql.DriverManager.

The problem with this approach is that establishing a connection with a database is costly. Consequently, if an application establishes a new connection with a database every time it needs to issue an SQL statement, performance is degraded.

A connection pool uses JDBC to create a limited number of connections with the database. Whenever your application needs a connection, it borrows it from the connection pool. Later on, instead of closing the connection, the application gives it back to the connection pool. That connection can then be reused later since it has not been closed.

Connection pools are supplied as simple JAR files that you include in your classpath. There is a number of open source connection pools you can download for free. Some of the most commonly used are Commons DBCP, BoneCP, C3P0 and Proxool.

As an example, the following section will show you how to use Proxool. I have tried several connection pools but what I found interesting about Proxool is that you can easily keep track of the events that occur on each connection. In particular, Proxool gives you the execution time of each SQL statement in milliseconds, allowing you to pinpoint the time-consuming statements in your application.

How to use Proxool

Download Proxool, unzip the downloaded file and include in your classpath the JAR files contained in the /lib directory. The example shown in this section provides two classes: ConnectionPool and ConnectionListener:

ConnectionPool creates a connection pool and provides a getConnection method which returns a connection borrowed from the pool.
ConnectionListener is the class that listens to the events that occur on each connection.

The class ConnectionPool assumes that you are using a locally installed Oracle database. If you are using another database, all you have to do is modify driverClass and driverUrl accordingly. Here is the code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.logicalcobwebs.proxool.ProxoolFacade;

public final class ConnectionPool {

    public ConnectionPool() throws Exception {
          // Set up the URL
          String alias = "demo"; // The alias uniquely identifies the pool
          String driverClass = "oracle.jdbc.driver.OracleDriver";
          String driverUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
          String url = "proxool." + alias + ":" + driverClass + ":" + driverUrl;

          // Set up the Properties object
          Properties info = new Properties();
          info.setProperty("proxool.maximum-connection-count", "30");
          info.setProperty("user", "yourName");
          info.setProperty("password", "yourPassword");

          // Initialize the pool, borrow a connection and return it to the pool by calling "close"
          // If something goes wrong, an exception is thrown
          Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
          Connection connection = DriverManager.getConnection(url, info);
          connection.close();

          // Add a connection listener to the pool
          ProxoolFacade.addConnectionListener(alias, new ConnectionListener());
    }

    public Connection getConnection() throws SQLException {
          // Use the alias "demo" defined in the constructor to borrow a connection
          return DriverManager.getConnection("proxool.demo");
    }

}

The class ConnectionListener shown below implements the interface ConnectionListenerIF. Its purpose is to listen to the events that occur on each connection. Since I am only interested in the execution time of each SQL statement, I have implemented a single method: onExecute.

import java.sql.Connection;
import java.sql.SQLException;
import org.logicalcobwebs.proxool.ConnectionListenerIF;

public final class ConnectionListener implements ConnectionListenerIF {

    public void onBirth(Connection arg0) throws SQLException {

    }


    public void onDeath(Connection arg0, int arg1) throws SQLException {

    }


    public void onExecute(String command, long elapsedTime) {
          System.out.println("command = " + command);
          System.out.println("elapsedTime = " + elapsedTime);
    }


    public void onFail(String arg0, Exception arg1) {

    }

}

The following sample creates an instance of the class ConnectionPool and borrows a connection from the pool to issue an SQL statement:

Connection connection = null;
PreparedStatement statement = null;

try {
    // Create an instance of the class ConnectionPool and use it to issue an SQL statement
    ConnectionPool connectionPool = new ConnectionPool();

    connection = connectionPool.getConnection();

    statement = connection.prepareStatement("SELECT * FROM RIVERS WHERE CONTINENT = ? AND LENGTH > ?");

    statement.setString(1, "Africa");
    statement.setInt(2, 3000);

    ResultSet resultSet = statement.executeQuery();

    while(resultSet.next()){
          // do something with the selected rows
    }
}
finally{
    if(statement != null)
          statement.close();
    if(connection != null)
          connection.close();
}

If you run the above sample, you will get an output like this:

command = SELECT * FROM RIVERS WHERE CONTINENT = 'Africa' AND LENGTH > 3000
elapsedTime = 33

You are here : JavaPerspective.com > Advanced Tutorials > 2. JDBC - database access > 2.6. Connection pooling
Next tutorial : JavaPerspective.com > Advanced Tutorials > 2. JDBC - database access > 2.7. Transactions

Java is a trademark of Oracle Corporation