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 class
The 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.
Constructor
The 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 pool
The 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 pool
The 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.
Shutdown
Most 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 class
The 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.
Initialization
The 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 connection
The 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);
}
}
Shutdown
Finally, 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 pool
The 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 considerations
Most 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.
Resources
Source code for the classes described in this article:
DBConnectionManager.java
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.
