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 (
Implement a JDBC Connection Pool via the Object Pool Pattern
), 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.
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 sendRequest() method.
Listing 3. JDBCConnection2.java
The sendRequest() method, as seen in Listing 3, first checks to see if a connection is already in use. If so, it is closed.
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 getRs() method and do whatever processing is required.
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 closeRequest() method will free up the database resources and return the connection to the pool.
The inUse flag is used to determine whether the connection is in use or not. If the client makes another call to sendRequest() without first calling closeRequest(), the closeRequest() method will automatically be called, freeing up the resources of the previous request before making a new one.
When the sendRequest() method is called, it in turn requests a connection from the pool via the acquireImpl() method.
Listing 4. JDBCConnectionImpl2.java
Keeping Count
The acquireImpl() 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.
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, acquireImpl() 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.
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 releaseImpl() method.
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 close() methods implemented by the JDBCPool2 class.
The first JDBCPool2.close() 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 close() method in the JDBCConnectionImpl2 class is where the connection is actually released back to the database.
If all the pools need to be shutdown, there is a second JDBCPool2.close() method. This one iterates though the parmsDictionary, getting the parameters of each pool and closing all of its connections in turn.
Updates, Stored Procedures and Multiple Result Sets
The JDBCConnection2.sendRequest() 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.
A call to a stored procedure works the same but with the addition of parameters.
Listing 7. JDBCConnection2.sendStoredRequest()
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()
Here, instead of calling executeQuery(), a call to executeUpdate() is being made. The return value is the number of rows updated, and this value is returned to the client.
A stored procedure doing an update would be implemented in a similar fashion, replacing the executeQuery() call with executeUpdate().
Multiple result sets can be implemented by again changing the execute calls. In each send...() method, change the execute statement with the following two lines of code. A getNextRs() method will need to be added in order to work with the additional result sets.
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 getNextRs() method would be called by the client application as needed.
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.
