Improved Performance with a Connection Pool by Hans Bergsten
If you have heard anything about Servlets you know that one of the advantages over CGI is that a Servlet can keep information between requests and share common resources. This article describes one common use of this feature, namely a database connection pool.
September 01, 1999
A dynamic web site generates HTML pages from information stored in a database. Each request for a page results in a database access. But connecting to a database is a time consuming activity since the database must allocate communication and memory resources as well as authenticate the user and set up the corresponding security context. The exact time varies, of course, but it is not unusual to see connection times of one or two seconds. Establishing the connection once and then use the same connection for subsequent requests can therefore dramatically improve the performance of a database driven web application. Since Servlets can keep information between requests, a database connection pool is a straight forward solution.
A Servlet is a Java class. A Servlet Engine (native to the web server or a separate add-on module) loads the class in the Java Virtual Machine (JVM) process and creates one instance of the Servlet class at start-up or the first time the Servlet is requested. Each request for the Servlet is then executed in a separate thread using the same Servlet instance. The Servlet remains resident between requests and can keep persistent data in instance variables or in separate helper objects, such as connection pool objects.
The Java Database Connect API (JDBC) is supported by all major database vendors as well as many small databases. To access a database through JDBC you first open a connection to the database, resulting in a Connection object. A Connection object represents a native database connection and provides methods for executing SQL statements. The database connection pool described in this article consists of manager class that provides an interface to multiple connection pool objects. Each pool manages a set of JDBC Connection objects that can be shared by any number of Servlets.
The database connection pool class, DBConnectionPool, provides methods to
get an open connection from the pool,
return a connection to the pool,
release all resources and close all connections at shutdown. It also handles connection failures, such as time-outs, communication failures, etc. and can limit the number of connections in the pool to a predefined max value.
The manager class, DBConnectionManager, is a wrapper around the DBConnectionPool class that manages multiple connection pools. It
loads and registers all JDBC drivers,
creates DBConnectionPool objects based on properties defined in a properties file,
maps connection pool names to DBConnectionPool instances,
keeps track of connection pool clients to shut down all pools gracefully when the last client is done. The rest of this article describes each class in detail, starting with the DBConnectionPool class. You will also see an example of how a Servlet is using the connection pool. The complete source code for the DBConnectionManager and the DBConnectionPool is also available here.
The DBConnectionPool classThe DBConnectionPool class represents a pool of connections to one database. The database is identified with a JDBC URL. A JDBC URL consists of three parts: the protocol identifier (always jdbc), the driver identifier (e.g. odbc, idb, oracle, etc.) and the database identifier (the format is driver specific). As an example, jdbc:odbc:demo, is the JDBC URL for a database named demo accessed through the JDBC-ODBC bridge driver. The pool also has a name used by the clients and optionally a user name and password and a max connection limit. If you develop a web application where all users can execute some database operations but others are restricted to authorized users, you can define one pool for the general user and another pool for the restricted group using the same JDBC URL but different user names and passwords.
ConstructorThe DBConnectionPool constructor takes all values described above as its parameters: public DBConnectionPool(String name, String URL, String user,
String password, int maxConn) {
this.name = name;
this.URL = URL;
this.user = user;
this.password = password;
this.maxConn = maxConn;
}
It saves all parameter values in its instance variables.
Get an open connection from the poolThe DBConnectionPool class provides two methods for checking out a connection. They both return an existing Connection if one is available, otherwise they create a new Connection. If no Connection is available and the max number of connections have been reached, the first method returns null but the other waits until an existing Connection is checked in. public synchronized Connection getConnection() {
Connection con = null;
if (freeConnections.size() > 0) {
// Pick the first Connection in the Vector
// to get round-robin usage
con = (Connection) freeConnections.firstElement();
freeConnections.removeElementAt(0);
try {
if (con.isClosed()) {
log("Removed bad connection from " + name);
// Try again recursively
con = getConnection();
}
}
catch (SQLException e) {
log("Removed bad connection from " + name);
// Try again recursively
con = getConnection();
}
}
else if (maxConn == 0 || checkedOut < maxConn) {
con = newConnection();
}
if (con != null) {
checkedOut++;
}
return con;
}
All Connection objects in the pool are kept in a Vector, named freeConnections, when they are checked in. If there is at least one Connection in the Vector getConnection() picks the first one. As you will see later, Connections are added to the end of the Vector when they are checked in so picking the first ensures an even utilization to minimize the risk that the database disconnects a connection due to inactivity.
Before returning the Connection to the client, the isClosed() method is used to verify that the connection is okay. If the connection is closed, or an exception is thrown, the method calls itself again to get another connection.
If no Connection is available in the freeConnections Vector the method checks if the max connection limit is specified, and if so, if it's been reached. A maxConn value of 0 means "no limit". If no limit has been specified or the limit has not been reached, the method tries to create a new Connection. If it's successful, it increments the counter for the number of checked out connections and returns the Connection to the client. Otherwise it returns null.
The newConnection() method is used to create a new Connection. This is a private method that creates a Connection based on if a user name and a password have been specified or not. private Connection newConnection() {
Connection con = null;
try {
if (user == null) {
con = DriverManager.getConnection(URL);
}
else {
con = DriverManager.getConnection(URL, user, password);
}
log("Created a new connection in pool " + name);
}
catch (SQLException e) {
log(e, "Can't create a new connection for " + URL);
return null;
}
return con;
}
The JDBC DriverManager provides a set of getConnection() methods that takes a JDBC URL plus other parameters, for instance a user name and a password. The DriverManager uses the URL to locate a JDBC driver matching the database and opens a connection.
The second getConnection() method takes a time-out parameter, with a value in milliseconds for how long the client is willing to wait for a connection. It's implemented as a wrapper around the first getConnection() method: public synchronized Connection getConnection(long timeout) {
long startTime = new Date().getTime();
Connection con;
while ((con = getConnection()) == null) {
try {
wait(timeout);
}
catch (InterruptedException e) {}
if ((new Date().getTime() - startTime) >= timeout) {
// Timeout has expired
return null;
}
}
return con;
}
The local startTime variable is initialized with the current time. A while loop first try to get a connection. If it fails, wait() is called with the the number of milliseconds we are prepared to wait. wait() returns either when another thread calls notify() or notifyAll(), as you will see later, or when the time has elapsed. To figure out if wait() returned due to a time-out or a notification, the start time is subtracted from current time and if the result is greater than the time-out value the method returns null. Otherwise getConnection() is called again.
Return a connection to the poolThe DBConnectionPool class also provides a method for returning a connection to the pool. The freeConnection() method takes the returned Connection object as its parameter. public synchronized void freeConnection(Connection con) {
// Put the connection at the end of the Vector
freeConnections.addElement(con);
checkedOut--;
notifyAll();
}
The Connection is added to the end of the freeConnections Vector and the counter for number of checked out connections is decremented. The notifyAll() is called to notify other clients waiting for a connection.
ShutdownMost Servlet Engines provide some method for graceful shutdown. The database connection pools need to be notified about this event so that all connections can be closed correctly. The DBConnectionManager class is responsible for coordinating the shutdown but it's the DBConnectionPool class that closes all connections in the pool. The release() method is called by the DBConnectionManager. public synchronized void release() {
Enumeration allConnections = freeConnections.elements();
while (allConnections.hasMoreElements()) {
Connection con = (Connection) allConnections.nextElement();
try {
con.close();
log("Closed connection for pool " + name);
}
catch (SQLException e) {
log(e, "Can't close connection for pool " + name);
}
}
freeConnections.removeAllElements();
}
This method loops through the freeConnections Vector and closes all Connections. When all Connections have been closed they are removed from the Vector.
The DBConnectionManager classThe DBConnectionManager class is implemented according to the Singleton pattern described in many design books. A Singleton is a class with just one instance. Other objects can get a reference to the single instance through a static method (class method).
Constructor and getInstance()The DBConnectionManager constructor is private to prevent other objects to create instances of the class. private DBConnectionManager() {
init();
}
Clients of the DBConnectionManager calls the getInstance() method to get a reference to the single instance. static synchronized public DBConnectionManager getInstance() {
if (instance == null) {
instance = new DBConnectionManager();
}
clients++;
return instance;
}
The single instance is created the first time this method is called and a reference is then kept in the static variable named instance. A counter for the number of DBConnectionManager clients is incremented before the reference is returned. This counter is later used to coordinate the shutdown of the pools.
InitializationThe constructor calls a private method called init() to initialize the object. private void init() {
InputStream is = getClass().getResourceAsStream("/db.properties");
Properties dbProps = new Properties();
try {
dbProps.load(is);
}
catch (Exception e) {
System.err.println("Can't read the properties file. " +
"Make sure db.properties is in the CLASSPATH");
return;
}
String logFile = dbProps.getProperty("logfile",
"DBConnectionManager.log");
try {
log = new PrintWriter(new FileWriter(logFile, true), true);
}
catch (IOException e) {
System.err.println("Can't open the log file: " + logFile);
log = new PrintWriter(System.err);
}
loadDrivers(dbProps);
createPools(dbProps);
}
The getResourceAsStream() method is a standard method for locating an external file and open it for input. How the file is located depends on the class loader but the standard class loader for local classes searches for the file in the CLASSPATH, starting in the directory where the class file is located. The db.properties file is a file in the Properties format containing key-value pairs that define the connection pools. The following common properties can be defined:
drivers
A space separated list of JDBC driver class names
logfile
The absolute path for a log file
Another set of properties are used for each pool. The property name starts with the name of the connection pool:
<poolname>.url
The JDBC URL for the database
<poolname>.maxconn
The max number of connections in the pool. 0 means no limit.
<poolname>.user
The user name for the pool
<poolname>.password
The corresponding password
The url property is mandatory but all the others are optional. The user name and the matching password must be valid for the database defined by the URL.
Below is an example of a db.properties file for a Windows platform, with one pool for an InstantDB database and one pool for an MS Access database accessed through an ODBC Data Source Name (DSN) demo. drivers=sun.jdbc.odbc.JdbcOdbcDriver jdbc.idbDriver
logfile=D:\\user\\src\\java\\DBConnectionManager\\log.txt
idb.url=jdbc:idb:c:\\local\\javawebserver1.1\\db\\db.prp
idb.maxconn=2
access.url=jdbc:odbc:demo
access.user=demo
access.password=demopw
Note that the backslashes (\) in a Windows path must be duplicated since a backslash in a properties file is also used as an escape character.
The init() method creates a Properties object and loads the db.properties file. It then reads the logfile property. If a log file hasn't been specified a file named DBConnectionManager.log in the current directory is used instead. As a last resort, System.err is used for log messages.
The loadDrivers() method loads and registers all JDBC drivers specified by the drivers property. private void loadDrivers(Properties props) {
String driverClasses = props.getProperty("drivers");
StringTokenizer st = new StringTokenizer(driverClasses);
while (st.hasMoreElements()) {
String driverClassName = st.nextToken().trim();
try {
Driver driver = (Driver)
Class.forName(driverClassName).newInstance();
DriverManager.registerDriver(driver);
drivers.addElement(driver);
log("Registered JDBC driver " + driverClassName);
}
catch (Exception e) {
log("Can't register JDBC driver: " +
driverClassName + ", Exception: " + e);
}
}
}
loadDrivers() uses a StringTokenizer to split the drivers property value into a string for each driver class name and and then loops through all class names. Each class is loaded into the JVM and an instance is created. The instance is then registered with the JDBC DriverManager and added to a private Vector. The drivers Vector is used at shutdown to deregister all drivers from the DriverManager.
Next the DBConnectionPool objects are created by the private createPools() method. private void createPools(Properties props) {
Enumeration propNames = props.propertyNames();
while (propNames.hasMoreElements()) {
String name = (String) propNames.nextElement();
if (name.endsWith(".url")) {
String poolName = name.substring(0, name.lastIndexOf("."));
String url = props.getProperty(poolName + ".url");
if (url == null) {
log("No URL specified for " + poolName);
continue;
}
String user = props.getProperty(poolName + ".user");
String password = props.getProperty(poolName + ".password");
String maxconn = props.getProperty(poolName + ".maxconn", "0");
int max;
try {
max = Integer.valueOf(maxconn).intValue();
}
catch (NumberFormatException e) {
log("Invalid maxconn value " + maxconn + " for " +
poolName);
max = 0;
}
DBConnectionPool pool =
new DBConnectionPool(poolName, url, user, password, max);
pools.put(poolName, pool);
log("Initialized pool " + poolName);
}
}
}
An Enumeration of all property names is created and scanned for property names ending with .url. When such a property is found, the pool name is extracted, all properties for the corresponding connection pool are read and a DBConnectionPool object is created and saved in an instance variable named pools. pools is a Hashtable, using the pool name as the key and the DBConnectionPool object as the value.
Get and return a connectionThe DBConnectionManager provides the getConnection() and freeConnection() methods used by the clients. All of them take a pool name parameter and relay the call to the corresponding DBConnectionPool object. public Connection getConnection(String name) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection();
}
return null;
}
public Connection getConnection(String name, long time) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
return pool.getConnection(time);
}
return null;
}
public void freeConnection(String name, Connection con) {
DBConnectionPool pool = (DBConnectionPool) pools.get(name);
if (pool != null) {
pool.freeConnection(con);
}
}
ShutdownFinally, the DBConnectionManager has a method called release(). This method is used for graceful shutdown of the connection pools. Each DBConnectionManager client must call the static getInstance() method to get a reference to the manager. As you could see above, a client counter variable is used in this method to keep track of the number of clients. The release() method is called by each client during shutdown and the client counter is decremented. When the last client calls release(), the DBConnectionManager calls release() on all DBConnectionPool objects to close all connections. public synchronized void release() {
// Wait until called by the last client
if (--clients != 0) {
return;
}
Enumeration allPools = pools.elements();
while (allPools.hasMoreElements()) {
DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
pool.release();
}
Enumeration allDrivers = drivers.elements();
while (allDrivers.hasMoreElements()) {
Driver driver = (Driver) allDrivers.nextElement();
try {
DriverManager.deregisterDriver(driver);
log("Deregistered JDBC driver " + driver.getClass().getName());
}
catch (SQLException e) {
log(e, "Can't deregister JDBC driver: " +
driver.getClass().getName());
}
}
}
When all DBConnectionPool objects have been released, all JDBC drivers are deregistered.
Example of a Servlet using the connection poolThe Servlet API defines a Servlet's life cycle like this:
Servlet is created then initialized (the init() method).
Zero or more service calls from clients are handled (the service() method).
Servlet is destroyed then garbage collected and finalized (the destroy() method). A Servlet using the connection pool described in this article typically performs the following actions in these methods:
in init(), calls DBConnectionManager.getInstance() and saves the reference in an instance variable.
in service(), calls getConnection(), performs all database operations, and returns the Connection to the pool with freeConnection().
in destroy(), calls release() to release all resources and close all connections. The following is an example of a simple Servlet using the connection pool classes this way. import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class TestServlet extends HttpServlet {
private DBConnectionManager connMgr;
public void init(ServletConfig conf) throws ServletException {
super.init(conf);
connMgr = DBConnectionManager.getInstance();
}
public void service(HttpServletRequest req, HttpServletResponse res)
throws IOException {
res.setContentType("text/html");
PrintWriter out = res.getWriter();
Connection con = connMgr.getConnection("idb");
if (con == null) {
out.println("Can't get connection");
return;
}
ResultSet rs = null;
ResultSetMetaData md = null;
Statement stmt = null;
try {
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT * FROM EMPLOYEE");
md = rs.getMetaData();
out.println("<H1>Employee data</H1>");
while (rs.next()) {
out.println("<BR>");
for (int i = 1; i < md.getColumnCount(); i++) {
out.print(rs.getString(i) + ", ");
}
}
stmt.close();
rs.close();
}
catch (SQLException e) {
e.printStackTrace(out);
}
connMgr.freeConnection("idb", con);
}
public void destroy() {
connMgr.release();
super.destroy();
}
}
Class loader considerationsMost Servlet Engines use a special class loader for Servlets to be able to automatically reload a Servlet when it is modified. This class loader handles all class files located in a special directory, typically named servlets. A common mistake among new Servlet developers is to also put helper classes, such as the connection pool classes described here, in the servlets directory. This doesn't work at all when static variables are involved since the special class loader creates a separate class instance for each Servlet. To avoid this problem you should place all helper classes in the regular CLASSPATH used by the Servlet Engine. How you set the CLASSPATH varies between Servlet Engines so read the documentation for the product you use.
ResourcesSource code for the classes described in this article:
Servlet-enabled Products:
http://jserv.java.sun.com/products/java-server/servlets/environments.html
Sun's JDBC Pages:
http://java.sun.com/products/jdbc/jdbc.drivers.html
Sun's Servlet Pages:
http://java.sun.com/products/java-server/servlets/
Sun, Sun Microsystems, Java, and JDBC are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries.
