December 19, 2014
Hot Topics:

Implement a JDBC Connection Pool via the Object Pool Pattern

  • April 11, 2000
  • By Wiebe de Jong
  • Send Email »
  • More Articles »

Some things in life are expensive. Take vacation condos for example. Everybody would love to have one of his or her own, but most of us can't afford it. The solution to the problem is the time-share. You might have the condo this week; I'll get it next week; and someone else the week after that. One condo, but we all share it. In the computing world, one of the most expensive things around is the database connection, and many databases and application servers share connections via connection pools. In this article, I will show you how to implement a pool based on the Object Pool pattern and develop a JDBC connection pool as an example.

Basic Object Pool Pattern

An object pool is a control structure that manages a scarce or expensive resource by requiring the objects to be checked in and out of a finite pool. The number of objects available at any given time will vary. A client can check out an object, use it, and then return it so that someone else can make use of it. Occasionally, the pool will run dry and if there are no objects in the pool when a client makes a request, the client will have to wait until one is checked back in.

This sharing of objects has many benefits, especially when it comes to database connections. The number of connections allowed to a particular database may be limited due to server capacity or database licensing restrictions. As a result, the database or its administrator may limit the number of connections allowed to a particular application. A connection pool allows an application to work within these limits.

Also, connections are costly in terms of the amount of time needed for connecting and disconnecting to the database. Reusing the connections, rather than discarding and recreating them every time one is needed, means that each one will be connected to the database for its entire lifetime. This reuse results in a drastic increase in performance, as the connection is already live when the client acquires it.

Mark Grand describes the basic Object Pool pattern in his book Patterns in Java, Volume 1 and in the article Pattern Summaries: Object Pool . The UML class diagram is shown in Figure 1 (as illustrated by myself).

Figure 1
Figure 1. ReusablePool.

This pattern is made up of three classes: Reusable, ReusablePool, and Client.

Objects of the Reusable class are those things we want to share.

The ReusablePool class implements the pool and aggregates, or contains, a number of Reusable objects. The maximum number of these objects is a set value. The objects can be acquired and released by Client objects making requests of the ReusablePool.

An important aspect of the ReusablePool is that it is implemented as a Singleton. This can be deduced from the class diagram in Figure 1 in a couple of ways. The ReusablePool always has a cardinality of 1 in any association of which it is a part. Also, the ReusablePool constructor is private, and there is a getInstance() method. These characteristics are all aspects of the Singleton pattern, which was covered in detail in a previous article entitled Global Variables in Java with the Singleton Pattern .

Objects of the Client class will make requests of the ReusablePool to get access to Reusable objects. There can be any number of Clients and each Client can be accessing any number of Reusable objects, up to the maximum size of the pool.

Connection Pool Pattern

In his book, Grand further refines the Object Pool pattern into a generic Connection Pool Management example, which I've illustrated.

Figure 2
Figure 2. ConnectionPool.

Programs that require database connections can make use of a Connection object to encapsulate the database logic. The databaseName string, which is a unique identifier for a particular database, is used as a parameter when acquiring ConnectionImpl reusable objects. The significant difference between the UML diagrams in Figure 1 and Figure 2 is that each Connection object can access a maximum of one ConnectionImpl object. This reflects our design constraint that database connections are scarce and expensive and need to be rationed.

When a query is made, the Connection requests a ConnectionImpl from the ConnectionPool. The Connection uses it and returns it immediately to the pool. If the pool is empty when the request is being made, the pool will create a new ConnectionImpl object. If the maxPoolSize has been reached, the pool won't be able to create a new ConnectionImpl object and several things can happen.

The first option is to have the client sit and wait until a ConnectionImpl is returned to the pool. This will affect the performance of the application but doesn't require any extra effort on the developer's part.

Otherwise, the acquireImpl() method could generate an exception. The client would not waste any time waiting, but now the exception will have to be handled, generating more control and more work for the developer.

Due to the many ways the maxPoolSize can be implemented, it will be omitted from our JDBC example and left as an exercise for the reader.

The JDBC Connection Pool

Many commercial implementations of connection pools require the client to request a database connection, use it, and then release when it is no longer needed. All of these actions have to be coded by the application developer and the potential for misuse is high. What if the client never releases the connection or delays the release for an inordinately long period of time? The JDBCConnection class, as seen in Figure 3, will encapsulate all this behavior, making coding easier for the developer and safer for the pool.

Figure 3
Figure 3. JDBCPool.

The JDBCConnection is the application's gateway to the database. See Listing 1 for the Java source of this class. When an instance of JDBCConnection is first created, it grabs a reference to a connection pool described by dbName, a parameter that uniquely identifies a database. This parameter will correspond to those needed by the JDBCConnectionImpl's constructor. If multiple databases are going to be utilized, the dbName, along with a corresponding statement in the JDBCConnectionImpl constructor, will be needed. This will be described in more detail later.

Listing 1. JDBCConnection.

Passing a string containing a SQL statement to the sendRequest() method, with a ResultSet being returned, performs the query of the database. An implementation detail to notice here is that it is left to the application to manage the ResultSet object. This enables the application to keep the ResultSet around as long as needed.

The sendRequest() method requests a JDBCConnectionImpl object from the connection pool. It then sends the sqlString and resultSet to the matching sendRequest() method in the JDBCConnectionImpl object, as seen in Listing 2, to perform the query. Once the resultSet is returned, the JDBCConnectionImpl is released back to the pool, and the resultSet is passed back to the application.

Listing 2. JDBCConnectionImpl and JDBCPool.

To perform additional database functions, such as doing updates or calling stored procedures, you will need to create more methods in JDBCConnection, such as sendUpdate() and callProcedure(). A matching method will be needed in the JDBCConnectionImpl. These additional methods are left as an exercise to the reader.

The JDBCConnectionImpl constructor is made private so that no one outside the class can create a connection on his or her own. As a result, JDBCPool must be a member of JDBCConnectionImpl in order to have access to this constructor.

When a request is made to get a database connection via the acquireImpl() method, the JDBCPool object will lookup the supplied dbName in a private dictionary to retrieve the corresponding vector of available JDBCConnectionImpl objects. The first time this request is made, the vector will be empty. Since there are no existing connection objects, a new JDBCConnectionImpl will be created and returned. If the vector is not empty, a JDBCConnectionImpl object will be removed from it and returned to the calling JDBCConnection object.

When JDBCConnection is finished with the JDBCConnectionImpl object, it is returned to the pool via the releaseImpl() method. The JDBCConnectionImpl object remembers the dbName of the database it belongs to. This dbName is used to retrieve the appropriate vector of available connection objects. If this is the first object to be returned, the vector will be null. A new vector will then be created containing the returned object and added to the dictionary. If the vector already has available objects within it, the returned object is simply added, ready to be reused.

Conclusion

In the physical world, not everybody can have his or her own vacation condo; it needs to be shared so that everyone can have a bit of it. The time-share is the accepted method to solve the problem. In the software world, database connections suffer the same problem. The Connection Pool is the method to solve the problem. In the Java world, database connections are implemented via JDBC and a JDBC Connection Pool is an ideal solution.

In part two of this article ( Implement a JDBC Connection Pool via the Object Pool Pattern ), I will enhance the pool in several ways. The maxSize constraint will be implemented, methods to do updates and execute stored procedures will be added, a way of performing a database shutdown will be developed, and everything will be made generally more robust. In the meantime, I look forward to your e-mail.

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.






Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.


Rocket Fuel