Connection Pools

When writing an application that interacts with a database, the obvious strategy from a programmer's viewpoint is to create and destroy connections on an as-needed basis. Closer inspection, however, reveals this strategy to be a wellspring of performance problems. Even if the application and the data source are located on the same physical machine, the handshaking between the database and the application that occurs is time consuming and expensive in terms of server resources.

When, as is commonly the case, the database connection must be made across a network, the problem is compounded. Besides the fact that communication across a network is dramatically slower than communication among processes on a local machine, you must also take into account the overhead incurred in establishing a fresh network connection for each database transaction before you can even begin to address the database handshaking that I was whining about a couple of sentences ago.

Clearly something had to be done. That something turns out to be connection pools. (See Figure 7-2.) A connection pool is a named collection of JDBC connections.

Figure 7-2. Connection Pools

Connection pools are another aspect of the J2EE application framework. They are based on the clever observation that, regardless of what any particular application is doing at any given time, the set of applications deployed on a server will usually be interacting with the database at about the same rate.

Connection pools work by maintaining some number of open connections to a database at all times. The exact number is server specific and may in fact rise or fall depending on client demand.

When a client process needs to interact with the database, it does not attempt to establish a network connection or go through the normal handshaking process; rather, it simply checks out an existing open connection from the connection pool. Similarly, when the client process is finished with the connection, it does not close it but instead returns it to the pool for reuse by some other client. Thus, the majority of the network and database connection overhead is neatly circumvented.

For obvious reasons, all connections in a given connection pool must be to the same database. If you need to connect to more than one database, you can use MultiPools (see the discussion below).

WebLogic also provides facilities for optimizing the delivery of results.

Creating a Connection Pool

To create a new connection pool, click the Services folder, expand the JDBC folder, and then click Connection Pools. Click the "Create a new Connection Pool" link and fill out the fields as follows :

Name	This can be any (unique) value you like, though it's always a good idea to be as meaningful as possible when specifying resource names .
URL	A Protocol Servername Filepath URL that specifies the location of the data source to which you are connecting.
Driver Classname	The location of the JDBC driver that is to be used in communication with your database.
Properties	Some databases require more information than others to create a connection. The values in this box are passed on to the JDBC driver in key=value pairs (e.g., name=oracle, server=someserver). Distinct key=value pairs can be separated by carriage returns or semicolons.
ACLName	The name of the Access Control List that applies to this connection pool. Optional.
Password	The password required for database access.

When you have filled out the values under the General tab, click the Create button to create your connection pool. Next, you need to specify the values that will be used in maintaining your connection pool. Under the Connections tab, fill out the following fields as appropriate:

Initial Capacity	The number of connections to create when starting up the pool.
Maximum Capacity	An upper limit on the number of simultaneous connections to the database.
Capacity Increment	New connections to the database will be created in increments of this size.
Login Delay Seconds	Some databases bog down when bombarded with multiple simultaneous requests for connections. This value tells WebLogic to wait the specified number of seconds between connection requests. The default is 0 and should remain so for most databases.
Refresh Period	The number of minutes WebLogic waits between tests to see whether it needs to add or remove connection-pool members .
Allow Shrinking	Whether a pool, once expanded, can later be shrunk. Note that a pool will never be shrunk below the number specified in Initial Capacity.
Shrink Period	The number of minutes a connection must be idle before it will be shrunk out of the pool.
Prepared Statement Cache Size	The number of prepared statements that can be kept waiting for an available connection.