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.
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.
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 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, the driver identifier and the database identifier (the format is driver specific). 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,It saves all parameter values in its instance variables.
String password, int maxConn) {
this.name = name;
this.URL = URL;
this.user = user;
this.password = password;
this.maxConn = maxConn;
}
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 < con =" newConnection();">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:
driversA space separated list of JDBC driver class names logfileThe 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:
.url The JDBC URL for the database .maxconn The max number of connections in the pool. 0 means no limit. .user The user name for the pool .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 a pool for an MySQL database.
drivers=com.mysql.jdbc.DriverNote that the backslashes (\) in a Windows path must be duplicated since a backslash in a properties file is also used as an escape character.
logfile=D:\\user\\src\\java\\DBConnectionManager\\log.txt
mysql.maxconn=100
mysql.url=jdbc:mysql://localhost:3306/test
mysql.user=root
mysql.password=root
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).
- 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.
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("mysql");
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("Employee data");
while (rs.next()) {
out.println("");
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("mysql", con);
}
public void destroy() {
connMgr.release();
super.destroy();
}
}
No comments:
Post a Comment