JavaData & JavaJava Tip: Inside the Connection Pool Logic of Java App Servers

Java Tip: Inside the Connection Pool Logic of Java App Servers

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

Connection pools play a major role in Java Web development because of their ability to provide — as Manikesh Verma explains it — “maximum services with minimum resources.” In this beginner Java tip, Verma explains how connection pools work and offers his hand-coded logic for creating them.

To better understand connection pools, suppose a taxi cab service drives 100 employees to and from an office building every day. The service does not have 100 cabs, so they cannot dedicate one for each employee; they have a pool of 30-40 cars. How do they still serve 100 customers?

First, not all 100 employees will request cabs at the same time. The taxi cab service can start with some 20 cabs available in a pool. After each cab serves an employee, it will come back to the pool and get ready to serve the next employee. During times of high demand, the service can add a few more cabs to the pool.

In Java, developers do the same thing: we start with limited database connections and we use them only when needed, closing them immediately after transactions complete. If necessary, we add a few more connections to the pool. Many application servers provide this function automatically; you just have to define the database URL and credentials to use the built-in connection pools.

Here is Manikesh Verma’s attempt to write the same connection pool logic that application servers provide.

package com.testing;

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

/*
 * Created on Dec 22, 2010
 */

/**
 * @author manikesh
 */
public class ConnectionPool implements Runnable{
   private Vector m_availableConnections = new Vector();
   private Vector m_usedConnections = new Vector();
   private String m_user = null;
   private String m_password = null;
   private String m_url = null;
   private int m_initialConnectionCount = 5;
   static {
      try{
         Class.forName("com.mysql.jdbc.Driver");
      }
      catch(Exception e){
         System.out.println("Problem while loading JDBC Driver..");
      }
   }
   
      
   public ConnectionPool(String url, String user, String password) throws SQLException{
      m_url = url;
      m_user = user;
      m_password = password;
      for(int index=0; index<m_initialConnectionCount; index++){
         m_availableConnections.add(createConnection());
      }
      new Thread(this).start();
   }
   
   private Connection createConnection() throws SQLException{
      return DriverManager.getConnection(m_url,m_user,m_password);
   }
   
   public synchronized Connection getConnection() throws SQLException{
      Connection con = null;
      // check if no connections are there to return in pool
      if(m_availableConnections.size() == 0){
         //if no connection, create a new connection.
         con = createConnection();
         //add this connection to the used pool 
         m_usedConnections.add(con);
      }
      else{
         //if connections are there in pool
         // get one connection
         con = (Connection) m_availableConnections.lastElement();
         //remove it from available pool
         m_availableConnections.removeElement(con);
         //add to used pool 
         m_usedConnections.add(con);
      }
      return con;
   }
   
   public synchronized void returnConnection(Connection con) throws SQLException{
      if(con != null){
         // remove this from the used pool
         m_usedConnections.removeElement(con);
         //add to the available pool
         m_availableConnections.addElement(con);
      }
   }
   
   public int availableCount(){
      return m_availableConnections.size();
   }
   
   public void run(){
      System.out.println("thread started...");
      try{
         while(true){
            synchronized(this){
               while(m_availableConnections.size()>m_initialConnectionCount){
                  //Clean Extra connection.
                  Connection con = (Connection)m_availableConnections.lastElement();
                  m_availableConnections.removeElement(con);
                  //close the connection
                  con.close();
               }
               System.out.println("Clean Up : Available Connections : "+availableCount());
               //Now Sleep for 1 minute 
               Thread.sleep(1 * 60 * 1000);
            }
         }
      }
      catch (Exception e) {
         System.out.println("Problem while Connection Clean Up......");
      }
   }
}

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories