Database connection pooling using Java

Hi Folks,

Today I am going to show you some old school stuff. The Database Connection Pooling. I know there are several frameworks in Java that provides this facility, but I thought of sharing this to you guys because it’s actually a good piece of code. And it is always fun to know what’s going on beneath. Else what’s the point of programming ;-).

So, first thing first. What is a Connection Pool?

A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database. (Courtesy: Wikipedia and for image – http://www.cubrid.org/)

So, let’s see the code that makes above definition practically happen:

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

public class ConnectionPool implements Runnable {

  private String driver, url, username, password;
  private int maxConnections;
  private boolean waitIfBusy;
  private Vector<Connection> availableConnections;
  private Vector<Connection> busyConnections;
  private boolean connectionPending = false;

  public ConnectionPool(String driver, String url,String username, String password, int initialConnections, int maxConnections, boolean waitIfBusy) throws SQLException {
    this.driver = driver;
    this.url = url;
    this.username = username;
    this.password = password;
    this.maxConnections = maxConnections;
    this.waitIfBusy = waitIfBusy;
    if (initialConnections > maxConnections) {
      initialConnections = maxConnections;
    }
    availableConnections = new Vector<Connection>(initialConnections);
    busyConnections = new Vector<Connection>();
    for(int i=0; i<initialConnections; i++) {
      availableConnections.addElement(makeNewConnection());
    }
  }

  public synchronized Connection getConnection() throws SQLException {

    if (!availableConnections.isEmpty()) {
      Connection existingConnection =	(Connection)availableConnections.lastElement();
      int lastIndex = availableConnections.size() - 1;
      availableConnections.removeElementAt(lastIndex);

      // If connection on available list is closed (e.g.,
      // it timed out), then remove it from available list
      // and repeat the process of obtaining a connection.
      // Also wake up threads that were waiting for a
      // connection because maxConnection limit was reached.
      if (existingConnection.isClosed()) {
        notifyAll(); // Freed up a spot for anybody waiting
        return(getConnection());
      } else {
        busyConnections.addElement(existingConnection);
        return(existingConnection);
      }
    } else {

      // Three possible cases:
      // 1) You haven’t reached maxConnections limit. So
      // establish one in the background if there isn’t
      // already one pending, then wait for
      // the next available connection (whether or not
      // it was the newly established one).
      // 2) You reached maxConnections limit and waitIfBusy
      // flag is false. Throw SQLException in such a case.
      // 3) You reached maxConnections limit and waitIfBusy
      // flag is true. Then do the same thing as in second
      // part of step 1: wait for next available connection.
      if ((totalConnections() < maxConnections) && !connectionPending) {
        makeBackgroundConnection();
      } else if (!waitIfBusy) {
        throw new SQLException("Connection limit reached");
      }

      // Wait for either a new connection to be established
      // (if you called makeBackgroundConnection) or for
      // an existing connection to be freed up.
      try {
        wait();
      } catch(InterruptedException ie) {}
      // Someone freed up a connection, so try again.
      return(getConnection());
    }
  }

  // You can’t just make a new connection in the foreground
  // when none are available, since this can take several
  // seconds with a slow network connection. Instead,
  // start a thread that establishes a new connection,
  // then wait. You get woken up either when the new connection
  // is established or if someone finishes with an existing
  // connection.
  private void makeBackgroundConnection() {
    connectionPending = true;
    try {
      Thread connectThread = new Thread(this);
      connectThread.start();
    } catch(OutOfMemoryError oome) {
      // Give up on new connection
    }
  }

  public void run() {
    try {
      Connection connection = makeNewConnection();
      synchronized(this) {
        availableConnections.addElement(connection);
        connectionPending = false;
        notifyAll();
      }
    } catch(Exception e) { 
      // SQLException or OutOfMemory
      // Give up on new connection and wait for existing one
      // to free up.
    }
  }

  // This explicitly makes a new connection. Called in
  // the foreground when initializing the ConnectionPool,
  // and called in the background when running.
  private Connection makeNewConnection() throws SQLException {
    try {
      // Load database driver if not already loaded
      Class.forName(driver);
      // Establish network connection to database
      Connection connection =
          DriverManager.getConnection(url, username, password);
      return(connection);
    } catch(ClassNotFoundException cnfe) {
      // Simplify try/catch blocks of people using this by
      // throwing only one exception type.
      throw new SQLException("Can’t find class for driver: " +
          driver);
    }
  }

  public synchronized void free(Connection connection) {

    busyConnections.removeElement(connection);
    availableConnections.addElement(connection);
    notifyAll();
  }

  public synchronized int totalConnections() {
    return(availableConnections.size() + busyConnections.size());
  }

  /** Close all the connections. Use with caution:
   * be sure no connections are in use before
   * calling. Note that you are not <I>required</I> to
   * call this when done with a ConnectionPool, since
   * connections are guaranteed to be closed when
   * garbage collected. But this method gives more control
   * regarding when the connections are closed.
   */
  public synchronized void closeAllConnections() {

    closeConnections(availableConnections);
    availableConnections = new Vector<Connection>();

    for(int i=0; i<availableConnections.size(); i++) {	

      availableConnections.elementAt(i);			
    }

    closeConnections(busyConnections);
    busyConnections = new Vector<Connection>();
  }

  private void closeConnections(Vector<Connection> connections) {
    try {
      for(int i=0; i<connections.size(); i++) {
        Connection connection =
            (Connection)connections.elementAt(i);
        if (!connection.isClosed()) {
          connection.close();
        }
      }
    } catch(SQLException sqle) {
      // Ignore errors; garbage collect anyhow
    }
  }

  public synchronized String toString() {
    String info ="ConnectionPool(" + url + "," + username + ")" + ", available=" + availableConnections.size() + ", busy=" + busyConnections.size() + ", max=" + maxConnections;
    return(info);
  }
}

I hope this will help the guys who are working in Core Java. You guys can tweak the class or may extend it as per your requirement.

There are lot of Connection Pooling code available on Internet. You can Google up and find plenty of them.

 

That’s it folks for today.

Critics/suggestion are very much welcome.

Have a nice day ahead.