DBPool - Java Database Connection Pooling

What is DBPool?

A Java-based database connection pooling utility, which supports time-based expiry and statement caching, connection validation, and easy configuration using a pool manager. Also included is a generic object pool which can be extended to create your own pools of custom types.

Licence Agreement
Currently you may freely use DBPool in all applications, both commercial and non-commercial, provided it is used for the purpose intended as stated in this document. The source code is provided for reference, but you are not permitted in any way to use the code for purposes other than that which is intended. You are permitted to redistribute the original code on the condition that it is also distributed with the original documentation. If the code is changed in any way and then redistributed an acknowledgment to the source of the material must be made along with a link to the website (www.snaq.net), and it must be made plainly clear that the code has been modified from the original. It is not permitted to distribute a modified version of DBPool for commercial purposes without explicit permission from the author.

Disclaimer
Whilst the product has been developed with the utmost care, the author takes no responsibility for the failure of DBPool in either commercial or non-commercial operation.

Why would I use it?

Applications that make use of databases often need to frequently obtain connections to the database. For example, a popular website that is serving out information from a back-end database may need to obtain a database connection for each client who is requesting a page with their browser. To ensure the application is capable of responding to each client fast enough we need to profile the time spent performing each of it's tasks. One of the most expensive tasks involving accessing databases is the initial creation of the connection. Once the connection has been made the transaction often takes place very quickly. This is where the connection pool comes in, by retaining a pool of already-opened connections so the application can simply grab one when it needs to, use it, and then hand it back, without the long wait for the initial creation of the connection.

Where can I get it?

Select one of the links below to download the version you require.

• Download DBPool as ZIP - contains JAR file & all documentation
• Download DBPool as JAR and DBPool_javadoc.zip
• Download DBPool source

What do I need to use it?

The latest version requires Java 1.4 or greater, supporting the JDBC 3.0 specification. Versions 4.x have been updated to support the JDBC 3.0 specification and are not JDBC 2.0 compatible. Version 5.0 is in progress which aims to provide support for both specifications, and will hopefully be released soon once final changes are finalised and the testing is completed.

What about support?

Please make sure you have read this documentation before sending a support email. I frequently receive support emails from people who seem to have simply been too lazy to read the documentation and find which bits are relevant to them. Unsurprisingly I am not overly helpful to those people, and point them back to the documentation. Please remember to check here first, as you may find an updated version of the library. If you still need to ask for help, please send all support emails concerning DBPool to this address, and include as much information as possible to help diagnose the problem, including log file, stack traces, and source code, and properties file where appropriate. I will endeavour to reply as soon as possible, but due to the nature of my work (a lot of travel) it is possible that I won't be able to reply quickly.

If you would like to be added to the DBPool mailing list list to receive notification of new versions when they are released, send an email to the support address with your email contact details, asking to be added to the list.

How do I use it?

To use DBPool you need to have the JAR file (inside the ZIP file if you downloaded that) in a location where it's available for use by the host system you are using. For standalone applications this is usually simply within the CLASSPATH, but with many application servers a specific directory is recommended for JAR libraries. (For example, when used with Apache Tomcat it can be placed in the <webapp>/WEB-INF/lib directory.)

Usually DBPool is used in two different ways:

1. Direct use of individual connection pools.
2. Using the ConnectionPoolManager to manage multiple connection pools.

If you have never used DBPool before it's recommended that you start by simply integrating a single connection pool into your application/applet to see how it works and performs. This provides the simplest direct support for pooled database connections and will get you up and running quickly. Once you learn exactly how it behaves and the benefits it can give you can try the pool manager approach to manage multiple pools if necessary.

If you require connection pooling for an application server hosted project such as a web application it is recommended that you use the pool manager. This allows you to define the pooling parameters in an external file which allows you to change the parameters without recompilation of a possibly large project.

Using a ConnectionPool

Direct use of ConnectionPool objects can provide substantial performance gains for applications with minimum changes to the previous non-pooling code. A single ConnectionPool object provides a centralized location for access to connections to a single database with specific authentication credentials and parameters.

To create a single connection pool use a line like the following:

	ConnectionPool pool = new ConnectionPool(<poolname>,
 	                                         <maxpool>,
	                                         <maxconn>,
	                                         <expiry>,
	                                         <url>,
	                                         <username>,
	                                         <password>);

or...

	ConnectionPool pool = new ConnectionPool(<poolname>,
	                                         <maxpool>,
	                                         <maxconn>,
	                                         <expiry>,
	                                         <url>,
	                                         <properties>);

For example, to create a connection pool to access a database using the Oracle "thin" driver you could do something similar to this:

	String url = "jdbc:oracle:thin:@myDB.myISP.com:1521:test";
	ConnectionPool pool = new ConnectionPool("local",
	                                         10,
	                                         30,
	                                         180000,  // milliseconds
	                                         url,
	                                         "b_lightyear",
	                                         "BeyondInfinity");

To obtain a Connection object from the pool and use it you can now do this:

	Connection con = null;
	long timeout = 2000;  // 2 second timeout
	try
	{
	  con = pool.getConnection(timeout);
	  if (con != null)
	    ...use the connection...
	  else
	    ...do something else (timeout occurred)...
	}
	catch (SQLException sqle)
	{
	  ...deal with exception...
	}
	finally
	{
	  try { con.close(); }
	  catch (SQLException e) { ... }
	}

Once you are finished with the entire connection pool you should release the resources held by the pool:

	pool.release();

In addition to this basic use, you can initialize a number of connections within the pool. This is useful on applications startup, for instance, when you would like to create the connections so the first users to access the database don't have to wait for a connection to be created. It is advisable, and it makes the most sense, to do this just after creation of the ConnectionPool object:

	String url = "jdbc:mysql://localhost:3306/homeDB";
	ConnectionPool pool = new ConnectionPool("local", 10, 20, 0, url, "Nemo", "LuckyFin");
	pool.init(10);

The example above will initialize all the available connections in the pool. The connections are created in a seperate thread, so that the application can get on with other things while the initialization is performed in the background.

Using the ConnectionPoolManager

The ConnectionPoolManager provides comprehensive support for external definition of the behaviour of each connection pool, and additionally can manage multiple pools easily. (Better still, it even has integral support for multiple pool managers, allowing you to define pools from multiple sources, but this is seldom required.)

Conceptually a single pool manager provides access to a number of ConnectionPool objects, each of which provides access to a user-specified database source. For each pool manager the user specifies the JDBC drivers required, the log file for output, and the parameters for each connection pool. With this information the pool manager registers the necessary JDBC drivers and creates the pools ready for use. The log file shows a trace of the manager's activity, along with the activity of each of it's pools.

To use a pool manager you first need to obtain an instance of ConnectionPoolManager. This can be done in several different ways depending on your exact requirements, but the two easiest options are:

	ConnectionPoolManager.getInstance();
	ConnectionPoolManager.getInstance(File);

The getInstance() method returns an instance defined by the default properties file, which needs to be within the CLASSPATH (or equivalent location for specific host system). The getInstance(File) method returns an instance defined by the file specified. When either of these methods is called it either returns the requested instance immediately (if it already exists), or first creates the pools required and then returns the instance.

It is also possible to define properties using a user-specified file within the CLASSPATH, or even using a Properties object. Each option is explained in the following table:

Method of Access	Explanation
getInstance()*	Returns the pool manager instance as defined by the default properties file (dbpool.properties) which is located in the CLASSPATH (or equivalent location for specific host system)+.
getInstance(String)*	Returns the pool manager instance as defined by the properties file with the filename specified, located in the CLASSPATH (or equivalent location for specific host system)+.
getInstance(File)*	Returns the pool manager instance as defined by the properties file specified.
createInstance(Properties) followed by getInstance()*	Creates a pool manager instance from the specified Properties object and makes it available via the getInstance() method.
*Note 1: Each of these methods can throw an IOException if there is a problem loading the properties from the file.
+Note 2: Application servers often specify user-accessible areas differently from the CLASSPATH variable. For example, when used with Apache Tomcat it can be placed in the <webapp>/WEB-INF/classes directory.

Important: It is not possible to use both a default properties file instance and a Properties object instance simulteneously. If the default properties file instance is obtained and not released, a call to createInstance(Properties) will fail with a RuntimeException. Aside from this limitation multiple instances of ConnectionPoolManagers can be obtained and used, each with it's own parameters.

Using these various instance accessors you have access to a theoretically unlimited number of different pool managers, although in reality using more than just one is rare.

So, to obtain the pool manager defined by the default properties file us:

	ConnectionPoolManager cpm = null;
	try
	{
		cpm = ConnectionPoolManager.getInstance();
	}
	catch (IOException ioe)
	{
		...
	}

This step would normally be done at the initialization stage of an application. For instance, in a web application the pool manager could be created and assigned to an application scope variable, where it could be accessed by other classes which require database access.

Once a reference to a pool manager has been obtained you can now checkout/checkin (obtain/return) connections from/to it's pools. To obtain a connection use the getConnection(<poolname>) method. This method will obtain a database connection if one is immediately available, or return null if not. If you would rather wait a certain amount of time in case a connection becomes available use the getConnection(<poolname>, timeout) instead, where timeout is specified in milliseconds. If a connection becomes available within the timeout the method will return with the connection, otherwise null is returned. Once you have finished with this connection you simply close it as you would a normal connection. On closing the connection it is cleaned up and then returned to the pool for re-use.

For example, the following code obtains a connection from the ConnectionPoolManager, performs some operations, then returns the connection.

	Connection con = null;
	long timeout = 2000;  // 2 second timeout
	try
	{
	  con = cpm.getConnection(<poolname>, timeout);
	  if (con != null)
	    ...use the connection...
	  else
	    ...do something else (timeout occurred)...
	}
	catch (SQLException sqle)
	{
	  ...whatever...
	}
	finally
	{
	  try { con.close(); }
	  catch (SQLException e) { ... }
	}

Notice that when you have finished working with a connection you simply call it's close() method as you would normally. Instead of being closed the connection is actually recycled within the pool ready to be used again.

When you have completely finished with all the pools managed by a ConnectionPoolManager object you should release it to ensure all of the resources it is using are released. This is done using the call:

	cpm.release();

which if necessary shuts down the pools and releases the resources held by the pool manager.

Important Notes about ConnectionPoolManager

Every successful call to one of the getInstance() methods increases an internal counter of the number of clients which hold a reference to that pool manager. A call to release() decrements this client counter. If the counter hits zero then the pool manager shuts down all it's connection pools and cleans up all it's resources. Any database connections referred to by the underlying pools that are still open may be forcibly closed by this operation, which could have undesirable effects. Therefore is it recommended (and good programming practice) to ensure that all your connections are closed when no longer needed.

What this means is that you need to keep a careful track of the references to each pool manager created. Each call to getInstance() should be matched by a call to release() for that pool manager, but it is a good idea to keep a global reference within an application for as long as the database access is required, to avoid unnecessary destruction and new creation of the pool manager.

Defining the behaviour of the pool

The behaviour of the pools used by the ConnectionPoolManager is governed by either a properties file (by default called dbpool.properties) or by a Properties object supplied by the user.

The format of the properties file is shown below. The same key/value pairs apply when specifying a pool manager using a Properties object.

	 drivers=<fully-qualified class name of driver>
	 logfile=<filename>

	 <poolname>.url=<JDBC connection URL for database>
	 <poolname>.user=<user name>
	 <poolname>.password=<password>
	[<poolname>.maxpool=<maximum pooled connections>]
	[<poolname>.maxconn=<maximum possible connections>]
	[<poolname>.expiry=<expiry time of connections (seconds)>]
	[<poolname>.init=<initial number of connections>]
	[<poolname>.validator=<fully-qualified classname of ConnectionValidator>]
	[<poolname>.decoder=<fully-qualified classname of PasswordDecoder>]
	[<poolname>.cache=true/false]
	[<poolname>.debug=true/false]
	[<poolname>.prop.property=value]

Those properties in [brackets] are optional and take on default values when not supplied as described in the table below. Inevitably is is worthwhile supplying values for at least maxpool and maxconn, or you will get no benefit from the pooling system.

Property	Purpose	Possible values	Default value
maxpool	Determines the maximum number of connections that are held in the pool	integer, >=0 (>=minpool)	0
maxconn	Determines the absolute maximum number of connections that can be created for use	integer, >=0 (>=maxpool)	0 - unlimited
expiry	The expiry time for individual connections that are unused (seconds)	integer, >=0	0 - no expiry
init	Initial a number of connections on startup (if more than minpool required)	integer, >=0 (<=maxpool)	0 - none
validator	Determines how to ensure that connections are valid	Java class name (must implement ConnectionValidator)	none - validates with Connection.isClosed()
decoder	Allows use of a custom password decoder class	Java class name (must implement PasswordDecoder)	none - no password encoding supported
cache	Option to turn off caching of statements	true/false	true
debug	Option to turn on debug information in the log file	true/false	false
prop.property	Optional properties to be passed to the JDBC driver	string
In addition there are a few properties which control advanced features for each pool:
async	Option to turn on asynchronous destruction of invalid/dead connections	true/false	false
logfile	Overrides generic logfile for this pool	string
dateformat	Formatting string used for log entries (SimpleDateFormat object used)	string	EEE MMM dd hh:mm:ss.SSS ZZZ yyyy

You can define more than one pool provided each one has a different pool name. To specify multiple database driver classes simply comma or whitespace separate them. Each driver entry needs to be the fully-qualified class name of a valid JDBC Driver which implements the java.sql.Driver interface.

You can optionally supply additional properties to the JDBC driver by adding <poolname>.prop.property=value within the properties file. This allows you to pass values to the driver which otherwise might be passed by adding them to the JDBC URL.

Internal validation of the properties is performed, so if you specify impossible values the pool will default to: no pooled items, unlimited total connections, no expiry. This will behave similarly to not having a pool, so it is worth thinking through the numbers you use. Each of the defaults shown are used to avoid making any assumptions about the needs of any individual circumstance. Each time a pool is setup you should take care to analyse and determine the entries that are likely to perform well in that specific situation.

So, for example, the properties for a locally-hosted MySQL database with a pool name of local could be:

	drivers=com.mysql.jdbc.Driver
	logfile=dbpool.log

	local.url=jdbc:mysql://localhost:3306/homeDB
	local.user=Nemo
	local.password=LuckyFin
	local.maxpool=10
	local.maxconn=20
	local.expiry=0

maxpool determines the maximum number of connections that can be held in the pool.
maxconn determines the absolute maximum number of connections that can be created for use.
expiry is the expiry time for individual connections (seconds).

These properties determine the behaviour of the pool when in use. The number of connections available for use is dictated by maxpool and maxconn. A maximum of maxpool items are ever held for re-use in the pool, although up to maxconn can be created and handed out, but the surplus connections will be destroyed when handed back. If maxconn is set to zero then an unlimited number of additional connections can be checked out, but the surplus will be destroyed when checked back in.

Depending on the connection demands of your application you will see differing behaviour in the pool. With a low demand only a few connections are likely to ever be created (sometimes fewer than maxpool), whereas with a very high demand you may well stretch the pooling system to it's limits. The likely usage should be carefully taken into consideration when configuring the properties file.

What this means...

You have great flexibility to create connection pools dealing with most situations you are likely to require. For instance, some database drivers only give you a limited number of connections due to either limited resources or licence limitations. In this case you set maxconn at or just below this limit, and establish a convenient maxpool size which deals well with the average load. If you have a high-demand application with a limited number of connections you may simply want to remove the expiry and make the maxpool equal to maxconn which will simply pool all available connections and keep them open; this gives you maximum database connection performance, but may tie up resources on the machine which could be used elsewhere.

Example...

The pool shown above will create new connections as required, up to a maximum of 20 simultaneous connections. When each connection is returned it will only be kept in the pool if there are fewer than 10 already in the pool. Otherwise it will close the returned connection. This occurs because there is no expiry for the pool.

The example below shows the properties file for an Oracle database using the Oracle Thin JDBC driver to access the database called "test".

	drivers=oracle.jdbc.driver.OracleDriver
	logfile=dbpool.log

	local.url=jdbc:oracle:thin:@myDB.myISP.com:1521:test
	local.user=b_lightyear
	local.password=BeyondInfinity
	local.maxpool=10
	local.maxconn=30
	local.expiry=180
	local.validator=snaq.db.AutoCommitValidator

In this case there is a maximum limit on the number of connections as might be the case when using an ISP-hosted database server. To save resources and to avoid compromising performance the pool is only 10 connections in size. When demand is high the pool size is able to expand up to 30, and when demand is low, connections will expire after three minutes of idleness (180 seconds). This example also makes use of more rigorous connection validation (for more information see the Advanced section).

Choosing Pooling Parameters

One difficult aspect of using a connection pooling system is figuring out what parameters to use to get the best out of it. This is not surprising, as it dependent on many factors: the application (and it's code efficiency), the database drivers, the database application, the database platform, etc. All these variables (and more besides) lead to the inevitability that choosing good parameters is a matter of trial and error; there is no absolute way of knowing what is best without trying out a few ideas. Try to step back from the details of the code that has been written and view the application as a whole.

By answering some of the questions below they may assist you in coming to more useful values for the parameters in question. Bear in mind that you can turn on the debug option for each pool to see more detailed usage information in the log. This helps determine whether the behaviour is appropriate to the circumstances, and is highly recommended during development. Be aware that enabling debug can be detrimental to performance, so remember to disable it again for production releases.

General questions to think about:

• Are database accesses generally quick, or do they take a long time?
• Are database accesses generally frequent or infrequent?
• Are there any database accesses which obviously take a long time to complete?

If your application only occasionally requires access to a database then it's possible you don't even need connection pooling. However, it might still be able to provide a convenient mechanism for providing access to the database. Additionally, if the system is likely to be up-scaled you will need to think of the future implications in terms of database access.

Issues that may affect the likely value for maxpool:

• How much connection demand is there under normal use? ...and under heavy use?
• Are connections usually used for a long time, or only quickly?

Issues that affect the likely value for maxconn:

• Is there a limit on the number of connections imposed by the JDBC driver (either code or licence)?
• Does the database have resource limitations so only a certain number of connections can be used simultaneously?
• Are there other system limitations (memory, processor, etc.) that restrict connection creation (or creation efficiency)?

Issues that affect the likely value for expiry:

• Does the database automatically close connections after a certain period of inactivity?
• Is this behaviour an option which can be switched off?
• Are the connections reliable over long periods of inactivity (hidden resource use, memory leaks, etc.)?
• How much connection demand is there under normal use? ...and under heavy use?

Issues that may affect whether to use statement caching:

• Does the application frequently issue the same queries to the database (Prepared/CallableStatements)?
• Does the application make use of user-specified Statements (ie. ResultSet type, concurrency settings, etc.)?

Issues that may affect whether/how to use custom connection validation:

• Do you frequently find connections in an invalid state after a while?
• Do you need to validate connections, but the database doesn't support the auto-commit feature?

By looking at the system as a whole and trying to determine it's database access behaviour you can try to understand the most likely useful values for these parameters. Try them out to see if they give a performance boost. Try adjusting them a bit and seeing what effect this has on the performance. You may find adding timing and/or pool analysis code to the application helps achieve better performance. To achieve this the pooling system has methods to discover connection "hit rate", the pool parameters, and information about the number of connections held, both available and in-use.

Advanced Features

Connection Validation

Each time a connection is requested from a pool it is checked for validity prior to being handed out. By default this check is done using the isClosed() method of the connection object. Although this method is relatively quick to return, it is not 100% reliable as it only returns whether the close() method has explicitly been called (the connection may have become invalid another way). You can override this default connection validation by using a custom validation class. The simplest way of making the validation more rigorous is to use the supplied snaq.db.AutoCommitValidator class.

The AutoCommitValidator class has been provided as a convenience to perform connection validation using the standard SQL call setAutoCommit(true) which should determine whether the connection can be used without problems, provided your JDBC database driver supports this feature. However, you can write your own validation classes as required by implementing the ConnectionValidator interface and specifying the class name in the properties file.

If using a standalone ConnectionPool call the setValidator(ConnectionValidator) method. If using the pool manager include this line (key/value pair) in your properties:

	<poolname>.validator=snaq.db.AutoCommitValidator

Bear in mind that you will need to find a practical balance between the rigorousness of the validation and the performance of the application. To obtain an (almost) error-proof validation you may wish to provide a class which performs full queries on the database to determine if it's operational. Sometimes this is necessary to provide the best quality service, but it might also impact performance if the connection needs to be tested in this way each time it is requested from the pool. However, it is also worth noting that some database/driver combinations can operate in a strange way if the validation is not rigorous enough, due to connections being closed unexpectedly and the validation routine not identifying the closure. The likelihood of this is liable to increase when using non-Type-4 JDBC drivers which use native code instead of being pure Java. If in doubt write connection validators that are more rigorous than required, then reduce the strength later to enhance speed performance and assess the situation. If necessary enable debugging information to obtain more information about the functioning of the pool.

Disabling statement caching

Caching of Statement objects is provided for by the pooling system by using a connection wrapper class (Statements created with a user-specified ResultSet type and concurrency are not cached; only the default statements are cached). In the event of using DBPool in a system where caching is not required you can disable the caching by adding the following to the properties file:

	<poolname>.cache=false

or by directly disabling caching on a standalone pool by calling one of the setCaching(...) methods.

Password Encryption

Connection pools are usually setup using the text-based properties file, which contains a plaintext password. Should the security of the machine hosting this properties file be compromised this allows the plaintext password to become available, which in turn compromises the security of the database server. To deal with this users can create a class which implements the PasswordDecoder interface, which has a one method signature:

	char[] decode(String encoded);

A very simple example PasswordDecoder (snaq.db.RotDecoder) has been provided which performs simple Rot13-encoding. This algorithm is very insecure and is only provided as an example implementation for demonstration purposes.

Once you have implemented your PasswordDecoder class, if using a standalone ConnectionPool call the setPasswordDecoder(PasswordDecoder) method, or if using the pool manager include this line (key/value pair) in your properties:

	<poolname>.decoder=<full-qualified classname>

Note: this mechanism only allows for implementation of password-based key systems, and while it is not as potentially secure as key-pair encryption, it does dramatically enhance security.

Bear in mind that you will need to find a practical balance between the rigorousness of the validation and the performance of the application. To obtain an (almost) error-proof validation you may wish to provide a class which performs full queries on the database to determine if it's operational. Sometimes this is necessary to provide the best quality service, but it might also impact performance if the connection needs to be tested in this way each time it is requested from the pool. However, it is also worth noting that some database/driver combinations can operate in a strange way if the validation is not rigorous enough, due to connections being closed unexpectedly and the validation routine not identifying the closure. The likelihood of this is liable to increase when using non-Type-4 JDBC drivers which use native code instead of being pure Java. If in doubt write connection validators that are more rigorous than required, then reduce the strength later to enhance speed performance and assess the situation. If necessary enable debugging information to obtain more information about the functioning of the pool.

Asynchronous connection destruction

Sometimes you will come across situations where the destruction of a database connection takes a disproportionate length of time. This could occur because the connections regularly get into an unstable state for some reason, or simply that the nature of the system being used means that many other resources need to be cleaned up as well. This situation can be indicative of a more serious instability (worth considering more rigorous connection validation), but there are some occasions when this is simply unavoidable. To ensure that pooling performance is not affected when this occurs you can turn on this option to perform the connection destruction asynchronously, thereby returning control immediately and not tying up the pool unnecessarily. It is recommended (and the default setting) that you leave this option disabled unless you are sure you need it. To enable this option add this line to the properties file:

	<poolname>.async=true

or call setAsyncDestroy(true) on a ConnectionPool instance.

Asynchronous/forced pool release

You can make a connection pool release it's resources asynchronously by calling the releaseAsync() method instead of the release() method when finished with a pool. This will return immediately and perform the pool cleanup in a background thread. This can be useful if the application continues after it has finished using the pool, but you don't want the application to have to wait for all the resources of the pool to be released, which in some circumstances can take a while.

You can force a connection pool to be released by using the releaseForcibly() method. Any open connections which have not been returned to the pool will be forcibly closed using this method. This method of release can sometimes be useful when the pool has been used to hand out connections to third party code where it is unreliable whether or not all connections will be returned, and an application termination is required. Bear in mind that forcibly closing connections can sometimes lead to unpredictable results in terms of database contents.

Note: When using the ConnectionPoolManager calling release() performs the release synchronously and forcibly, so it is wise to ensure all connections from the underlying pools have been finished with and closed.

Pool Listeners

Sometimes it's useful to have code be able react to the current state of a connection pool. For this reason ConnectionPool objects can issue events about their activity to objects which have registered interest. Listener objects must implement the ConnectionPoolEventListener interface, and can register/deregister their interest by using the provided methods:

	addConnectionPoolListener(ConnectionPoolListener)
	removeConnectionPoolListener(ConnectionPoolListener)

Also provided for convenience is the ConnectionPoolEventAdapter class, which provides null implementations of all the methods in the ConnectionPoolEventListener interface.

The events triggered are as follows:

Event Identifier	Description
CHECKOUT	Fired just before a valid connection is handed back to a checkOut(...) request.
CHECKIN	Fired when a connection is handed back with a checkIn(...) call.
MAX_POOL_LIMIT_REACHED	Fired when a check-out request causes the pooling limit (maxpool) to be reached.
MAX_POOL_LIMIT_EXCEEDED	Fired when a check-out request causes the pooling limit (maxpool) to be exceeded.
MAX_SIZE_LIMIT_REACHED	Fired when a check-out request causes the pool's maximum size limit (maxconn) to be reached.
MAX_SIZE_LIMIT_ERROR	Fired when a check-out request is made but the pool's maximum size limit (maxconn) has been reached.
VALIDATION_ERROR	Fired when a connection cannot be validated (when the isValid(...) method call fails).
PARAMETERS_CHANGED	Fired when the pool's parameters have been changed.
POOL_RELEASED	Fired when a pool has been released. No more events are fired following this one, as all listeners are deregistered automatically.

The event notification is synchronous, therefore registered listeners should make sure that code executed in their event-handling methods is executed quickly. Code that requires more time should spawn a separate thread to avoid disrupting the pooling mechanism.

Debugging

There is an extra property that can be set to allow more detailed debug information about the pooling mechanism to be output to the log file. To enable this add the following property:

	<poolname>.debug=true

or invoke the setDebug(true) method on a ConnectionPool instance.
Note, this is for debugging purposes only, and in a deployment environment it is likely to impact performance.

Notes & Troubleshooting

CLASSPATH problems? Unable to find properties file?

With some application servers the CLASSPATH variable is less than obvious. Due to the need for segregation of both resources and security policies they very often use separate classloaders which only permit loading of classes and resources from specific locations. Not surprisingly this is vendor-specific, and the only way to really get to grips with the problem is to thoroughly read the documentation provided with the application server. If it helps to diagnose the problem the pool manager uses the following syntax to obtain a locate the properties file:

	ConnectionPoolManager.class.getResourceAsStream(filename)

where filename is prepended with a "/" character to ensure it is accessed directly. The default properties file is therefore accessed using the call: Class.getResourceAsStream("/dbpool.properties"). Bear in mind that different classloaders provided by vendors may well not conform to the standard resource-finding mechanism as used by the system classloader, but this should help in tracking down problems.

Alternatively you can simply use one of the other methods of specifying properties to the ConnectionPoolManager, either referencing a specific file, or passing a Properties object. See the ConnectionPoolManager section for more details.

Connection Creation

This is how the connection pool tries to establish new database connections:

if Properties object used
    use DriverManager.getConnection(url, properties)
else if username specified
    use DriverManager.getConnection(url, username, password)
    if this fails
        use DriverManager.getConnection(url)
else
    use DriverManager.getConnection(url)

This pseudo-code may help debug certain problems when using the pooling system.

Bad statement caching performance

If you experience bad statement caching performance, as indicated when debugging is enabled, it is likely that statements are not being explicitly closed. To be recycled, statements must be closed so that the pool knows they can be reused, otherwise it assumes they are still in use.

Latest Changes