Connection PoolsA web application that has been deployed on a server may have many clients accessing it. If each time the database connection is needed it has to be reopened, performance will degenerate quickly. The standard solution is to create a pool of connections that can be used when needed and then returned to the pool for use by other servlets.The pool is stored in the ServletContext object so that all servlets in the application can use it to get connections. A special servlet called ConnectionServlet is needed to create the pool. It can also be used to display some information about the usage. The web deployment file, web.xml, contains along with the servlet name and class, <load-on-startup>1</load-on-startup>. This assures that the connection pool will be created before anything else is done.The pool is implemented as a vector of ConnectionObjects. These objects not only contain the connection, but also some useful information about it. They record the number of times the connection was used, the last time it was accessed and whether or not it is currently in use.The vector is stored in a class called ConnectionPool. This class has methods to create the connection pool, destroy it when no longer needed, get a connection from the pool and return a connection to the poolafter it is no longer needed by the servlet that was using it. Some connection information is stored in a configuration file called ConnectionPool.cfg. This way the ConnectionPool class is not tied to any one database. Changing a few lines in the configuration file changes the target database.Servlets that use the connection pool only require a little code in order to get the pool from the ServletContext and then obtain a connection. The main work of getting connections is done by the ConnectionPool class.The code for this connection pool was adapted from that found in Java Servlets, Developers Guide by Karl Moss. Parts of it can be found on pages 226 to 249. The complete code is available on Karl Moss’ website, http://www.servletguru.com.The ConnectionObjectThe ConnectionObject class stores a connection and some data related to it. It uses a Date object to store information about the last access time. The code follows:package produce;import java.sql.*;import java.io.*;// There is a Date object in both java.sql and java.util. The compiler must be told which one to use.import java.util.Date;public class ConnectionObject{private Connection con;private int useCount;private Date lastAccessTime;private boolean inUse;public Connection getConnection () {return con;}public int getUseCount () {return useCount;}public Date getLastAccessTime () {return lastAccessTime;}public boolean getInUse () {return inUse;}public void setConnection (Connection c) {con = c;}public void setUseCount (int u) {useCount = u;}public void setLastAccessTime (Date d) {lastAccessTime = d;}public void setInUse (boolean i) {inUse = i;}public boolean isAvailable () {return !inUse;}} // ConnectionObjectThe ConnectionServlet ClassThe ConnectionServlet class stores the connection pool in the ServletContext. It can then be accessed by any servlet in the web application. Like any servlet, it has either a doGet or a doPost method. But in this case, its main use is not to respond to a request. Instead it instantiates the connection pool and stores it in the ServletContext. Code for the init and destroy methods follow. The init method is automatically run when the servlet is loaded and the destroy method when the application ends.ConnectionPool pool;public static String PoolKey = "produce.ConnectionServlet";// The init method gets a new pool and initializes it.public void init (ServletConfig cfg){try{super.init (cfg);pool = new ConnectionPool ();pool.initialize ();} catch (ServletException e) {System.out.println ("Could not initialize");}ServletContext context = getServletContext ();context.setAttribute (PoolKey, pool); // Store the pool in the ServletContext.} // init/* The destroy method closes all connections in the pool and removes the attribute from the ServletContext. */public void destroy (){getServletContext ().removeAttribute (PoolKey);if (pool != null) pool.destroy ();super.destroy ();} // destroy A Servlet that Uses the PoolBefore going into details about how the ConnectionPool class works, we can see just how a servlet uses the ServletContext to get a connection. The only code needed gets the pool from the ServletContext and then gets a connection.ServletContext context = getServletContext ();String key = ConnectionServlet.PoolKey;Object object = context.getAttribute (key);if (object == null) out.println ("No connection pool.");else{ConnectionPool pool = (ConnectionPool) object;Connection con = pool.getConnection ();// Use the connection to access the database.pool.close (con);}The rest of the servlet is the same as before, except that it no longer throws a ClassNotFoundException. That is handled by the ConnectionPool class.The Configuration FileConnectionPool.cfg contains some connection information needed to create the connection. The file can be easily changed to refer to a different database. This makes the ConnectionPool class more flexible.# ConnectionPool.cfg# Defines connection pool parametersJDBCDriver=sun.jdbc.odbc.JdbcOdbcDriverJDBCConnectionURL=jdbc:odbc:produceConnectionPoolSize=5ConnectionPoolMax=50ConnectionUseCount=5The configuration file can be loaded into a Properties class and from there into the ConnectionPool class. Changing the JDBCDriver or the JDBCConnectionURL will change the target database.The ConnectionPool ClassThe ConnectionPool class is the most complicated. It not only creates the vector of ConnectionObjects, but it also has methods to manage these objects. package produce;import java.sql.*;import java.io.*;// There is a Date object in java.sql, so the specific Data object required is specifically imported here.import java.util.Date;import java.util.Vector;import java.util.Properties;/* The ConnectionPool class manages a pool of database connections. It reads configuration information from ConnectionPool.cfg and uses this to create a connection to the database. It also has methods that geta connection, get the entire pool, close a connection by returning it to the pool, and destroy the pool.public class ConnectionPool{private String JDBCDriver;private String JDBCConnectionURL;private int ConnectionPoolSize;private int ConnectionPoolMax;private int ConnectionUseCount;private