Implement a JDBC Connection Pool via the Object Pool Pattern
Requirements vs. Constraints
A design pattern describes the requirements that a portion of code must fulfill in an ideal world. When implementing that pattern in the real world, however, constraints appear that may affect the basic design.
In part 1 of this article, I described a JDBC connection pool that was developed from a design pattern. One of the requirements for the JDBCConnection class was that it would encapsulate the database connection. The client would never have to work the connection and would simply get a result set back.
In the real world, as several readers mentioned, this will not work. Before the connection could be returned to the pool, the JDBC ResultSet would have to be copied to an object that could be passed to the client. This would result in performance problems, especially with large result sets.
In this article, I will enhance the connection pool with all the real-world constraints in mind. The result set will not be copied, resulting in increased performance; the connection itself will be passed to the client, resulting in increased versatility; the hard coding of the JDBC driver and connection information will be moved into a parameter file, increasing ease of use; and a way of shutting down the system will be devised.
To start, lets begin with a sample client application, which uses the JDBC Pool developed in this article.
Listing 1. testApp2.java
import java.sql.*;
public class testApp2 {
public static void main() {
try {
// establish database connection
JDBCConnection2 mydb = new JDBCConnection2("mySqlAnywhereDB");
// send SQL request to database
mydb.sendRequest("select * from contact");
// get result set
ResultSet rs = mydb.getRs();
// process result set
while (rs.next())
System.out.println(rs.getString("first_name"));
// release resources
mydb.closeRequest();
} catch (Exception e) {
e.printStackTrace();
}
}
}
|
Storing Parameters
The testApp2 application, shown in Listing 1, begins with the establishment of a database connection. The JDBCConnection2 constructor takes a connection pool identifier as its only parameter. This identifier is stored in the dbPool.properties file, as seen in Listing 2.
Listing 2. dbPool.properties
myOracleDB.driver=oracle.jdbc.driver.OracleDriver myOracleDB.url=jdbc:oracle:oci8:@ myOracleDB.user=scott myOracleDB.password=tiger myOracleDB.maximum=20 mySqlAnywhereDB.driver=com.sybase.jdbc.SybDriver mySqlAnywhereDB.url=jdbc:sybase:Tds:localhost:2638 mySqlAnywhereDB.user=dba mySqlAnywhereDB.password=sql mySqlAnywhereDB.maximum=5 |
The properties file can hold the parameters for any number of connection pools. The property names start with the connection pool identifier, which must be unique for a particular pool. For each pool, there is a property that describes the name of the JDBC driver class to be used, the URL of the database, the user name and password required to make a connection to that database, and the maximum number of connections allowed in this particular pool.
Performing Queries and Getting Results
Getting back to the example, the client applicationtestApp2 gets a shared connection to the database by creating a JDBCConnection2 object. Due to Lazy Instantiation, the actual database connection won't be created until it is about to be used.
The application then sends a request, in the form of a SQL string, to the database via the
method.sendRequest()
// JDBCConnection2.java
import java.sql.*;
public class JDBCConnection2 {
// database instance name, such as mySqlAnywhereDB
private String dbName;
// the connection pool for that database instance
private JDBCConnectionImpl2.JDBCPool2 connectionPool;
// the connection
private JDBCConnectionImpl2 impl;
// JDBC statement and resultset
private Statement stmt;
private ResultSet rs;
// flag: is there an open ResultSet in use?
private boolean inUse;
// get connection to database pool
public JDBCConnection2 (String dbName) {
this.dbName = dbName;
connectionPool = JDBCConnectionImpl2.JDBCPool2.getInstance();
inUse = false;
}
// send a request to the database
void sendRequest (String sqlString)
throws SQLException, ClassNotFoundException, PoolException {
if (inUse)
closeRequest();
impl = connectionPool.acquireImpl(dbName);
stmt = impl.getConnection().createStatement();
rs = stmt.executeQuery(sqlString);
inUse = true;
}
// return the result set of the request
ResultSet getRs() {
return rs;
}
// close request and return resources
void closeRequest() throws SQLException {
rs.close();
stmt.close();
connectionPool.releaseImpl(impl);
inUse = false;
}
// free resources when object is destroyed
protected void finalize() throws SQLException {
if (inUse)
closeRequest();
}
}
|
The
method, as seen in Listing 3, first checks to see if a connection is already in use. If so, it is closed. sendRequest()
Next, the actual database connection is acquired from the pool, the SQL statement is executed, and the resultSet is made available to the calling application. The application can then get a reference to the result set via the
method and do whatever processing is required.getRs()
Since there is no copying of the result set to the client, the connection must stay open until the client is finished with it. When the result set processing is complete, a call to the
method will free up the database resources and return the connection to the pool.closeRequest()
The inUse flag is used to determine whether the connection is in use or not. If the client makes another call to
without first calling sendRequest()
, the closeRequest()
method will automatically be called, freeing up the resources of the previous request before making a new one.closeRequest()
When the
method is called, it in turn requests a connection from the pool via the sendRequest()
method.acquireImpl()
Listing 4. JDBCConnectionImpl2.java
// JDBCConnectionImpl2.java
import java.util.*;
import java.sql.*;
import java.io.*;
public class JDBCConnectionImpl2 {
// database instance name, such as myOracleDB, mySqlAnywhereDB, myAccessDB
private String dbName;
private Connection conn; // the precious connection
private JDBCConnectionImpl2 (String dbName, String dbUrl, String dbUser, String dbPwd) // private constructor
throws SQLException {
this.dbName = dbName;
conn = DriverManager.getConnection (dbUrl, dbUser, dbPwd);
}
String getDatabaseName() {
return dbName;
}
Connection getConnection() {
return conn;
}
void close() throws SQLException {
conn.close();
}
// free resources when object is destroyed
protected void finalize() throws SQLException {
close();
}
static class JDBCPool2 {
// dictionary of database names with corresponding vector of connections
private Hashtable poolDictionary = new Hashtable();
// dictionary of database names with corresponding connection parameters
private Hashtable parmsDictionary = new Hashtable();
// methods and attributes for Singleton pattern
private JDBCPool2() {} // private constructor
private static JDBCPool2 _instance; // get class instance
// Singleton getter utilizing Double Checked Locking pattern
public static JDBCPool2 getInstance() {
if (_instance == null) {
synchronized(JDBCPool2.class) {
if (_instance == null)
_instance = new JDBCPool2();
}
}
return _instance;
}
// get connection from pool
public synchronized JDBCConnectionImpl2 acquireImpl (String dbName)
throws SQLException, ClassNotFoundException, PoolException {
// get connection parameters matching database name
JDBCParms p = (JDBCParms)parmsDictionary.get(dbName);
// first call to database
if (p == null) {
Properties props = new Properties();
// read properties file
try {
props.load(new FileInputStream("dbPool.properties"));
} catch (Exception e) {
System.err.println("Can't read dbPool.properties");
return (JDBCConnectionImpl2) null;
}
// process properties
p = new JDBCParms();
p.name = dbName;
p.driver = props.getProperty(dbName + ".driver");
if (p.driver == null)
throw new PoolException("parameters not found for " + dbName);
p.url = props.getProperty(dbName + ".url");
p.user = props.getProperty(dbName + ".user");
p.password = props.getProperty(dbName + ".password");
String pMax = props.getProperty(dbName + ".maximum");
p.maxConn = Integer.valueOf(pMax).intValue();
p.curConn = 0;
// load database driver
Class.forName (p.driver);
// save parms
parmsDictionary.put(dbName, p);
}
// get connection pool matching database name
Vector pool = (Vector)poolDictionary.get(dbName);
if (pool != null) {
int size = pool.size();
if (size > 0) {
JDBCConnectionImpl2 impl = null;
// retrieve existing unused connection
impl = (JDBCConnectionImpl2)pool.elementAt(size-1);
// remove connection from pool
pool.removeElementAt(size-1);
// return connection
return impl;
}
}
// pool is empty so create new connection
if (p.curConn < p.maxConn) {
p.curConn++;
return new JDBCConnectionImpl2(dbName, p.url, p.user, p.password);
}
// pool is empty and max connections reached
throw new PoolException("max connections reached for " + dbName);
// return;
}
// return connection to pool
public synchronized void releaseImpl (JDBCConnectionImpl2 impl) {
String dbName = impl.getDatabaseName();
Vector pool = (Vector)poolDictionary.get(dbName);
if (pool == null) {
pool = new Vector();
poolDictionary.put(dbName, pool);
}
pool.addElement(impl);
}
public JDBCParms getParms(String dbName) {
return (JDBCParms)parmsDictionary.get(dbName);
}
public void close(String dbName) throws SQLException {
// get connection pool matching database name
Vector pool = (Vector)poolDictionary.get(dbName);
if (pool != null) {
int size = pool.size();
if (size > 0) {
JDBCConnectionImpl2 impl = null;
// retrieve existing unused connection
impl = (JDBCConnectionImpl2)pool.elementAt(size-1);
// remove connection from pool
pool.removeElementAt(size-1);
// close connection
impl.close();
}
JDBCParms p = (JDBCParms)parmsDictionary.get(dbName);
p.curConn = 0;
}
}
public void close() throws SQLException {
for (Enumeration e = parmsDictionary.elements(); e.hasMoreElements();) {
JDBCParms p = (JDBCParms)e.nextElement();
close(p.name);
}
}
}
}
|
Keeping Count
The
method begins by determining the current status of the particular pool. The parmsDictionary contains not only the parameters required to make database connections but the connection counter as well. All of this information is stored in the parmsDictionary in a JDBCParms object, as seen in Listing 5.acquireImpl()
Listing 5. JDBCParms.java
public class JDBCParms {
String name; // pool identifier
String driver; // JDBC driver class name
String url; // JDBC database URL
String user; // user name for the pool
String password; // user name's password
int maxConn; // maximum number of connections
int curConn; // current number of connections in use
}
|
Pool Exceptions
If this is the first call to the particular pool, there will be no corresponding JDBCParms object, so a new one will have to be created. At this point,
will go out, load the parameters file, create the new parameter object and initialize the connection counter to 0. If the pool identifier supplied by the client is misspelled or not in the parameter file, a PoolException will be raised. An exception will also occur here if the JDBC driver class file cannot be found and loaded.acquireImpl()
Checking the poolDictionary then creates a vector of the free connections. If there is a connection available, it is returned to the client. If there are no available connections, a new connection needs to be created.
Listing 6. PoolException.java
import java.lang.*;
public class PoolException extends Exception {
// constructors
public PoolException(){
super();
}
public PoolException(String s){
super(s);
}
}
|
If the maximum number of connections has been reached, a PoolException is raised and returned to the client. If the maximum has not been reached, a new one is created using the parameters from the JDBCParms object.
When the client is finished with the connection, it is returned to the pool via the
method.releaseImpl()
Performing a Shutdown
Another real-world constraint that wasn't part of the original design pattern is the need to be able to do a shutdown. There are two
methods implemented by the JDBCPool2 class.close()
The first
method takes the pool identifier as a parameter. The corresponding vector of free connections will be obtained and as each JDBCConnectionImpl2 object is removed, it is closed. The JDBCPool2.close()
method in the JDBCConnectionImpl2 class is where the connection is actually released back to the database.close()
If all the pools need to be shutdown, there is a second
method. This one iterates though the parmsDictionary, getting the parameters of each pool and closing all of its connections in turn.JDBCPool2.close()
Updates, Stored Procedures and Multiple Result Sets
The
method, in Listing 3, is designed for processing SQL Select statements which return result sets. If other types of database calls are going to be made, additional methods will need to be added to the JDBCConnection2 class.JDBCConnection2.sendRequest()
A call to a stored procedure works the same but with the addition of parameters.
Listing 7. JDBCConnection2.sendStoredRequest()
CallableStatement callStmt;
void sendStoredRequest (String sqlString, String parm1)
throws SQLException, ClassNotFoundException, PoolException {
if (inUse)
closeRequest();
impl = connectionPool.acquireImpl(dbName);
callStmt = impl.getConnection().prepareCall(sqlString);
callStmt.setString(1, parm1);
callStmt.registerOutParameter(2, java.sql.Types.INTEGER);
rs = stmt.executeQuery();
inUse = true;
}
|
In this example, sqlString will contain the reference to the stored procedure and parm1 will be a parameter to that procedure.
If the query or procedure is performing an update, then the return value will be the number of rows processed.
Listing 8. JDBCConnection2.sendUpdate()
int sendUpdate (String sqlString)
throws SQLException, ClassNotFoundException, PoolException {
if (inUse)
closeRequest();
impl = connectionPool.acquireImpl(dbName);
stmt = impl.getConnection().createStatement();
int rows = stmt.executeUpdate(sqlString);
inUse = true;
return rows;
}
|
Here, instead of calling
, a call to executeQuery()
is being made. The return value is the number of rows updated, and this value is returned to the client.executeUpdate()
A stored procedure doing an update would be implemented in a similar fashion, replacing the
call with executeQuery()
.executeUpdate()
Multiple result sets can be implemented by again changing the execute calls. In each
method, change the execute statement with the following two lines of code. A send...()
method will need to be added in order to work with the additional result sets. getNextRs()
Listing 9. JDBCConnection2.getNextRs()
stmt.execute(); // return multiple result sets
rs = stmt.getResultSet(); // get first result set
ResultSet getNextRs() {
rs = stmt.getMoreResults(); // get next result set
return rs;
}
|
The
method would be called by the client application as needed.getNextRs()
Conclusion
The JDBC connection pool shown here is now ready for use. The original pool was developed in the first half of this article to requirements put forth in a design pattern. Real-world shortcomings of the pool were addressed in this follow-up article by considering constraints. This is a classic example of how requirements may change when implementation begins.
About the Author
With over thirteen years' experience in application development, Wiebe de Jong is a Web developer for IMRglobal Ltd. in Vancouver, BC, Canada. He develops Internet and intranet applications for clients using Java, UML, and XML. He also teaches.
This article was originally published on May 9, 2000