DBPool : Java Database Connection Pooling

Copyright © 2001-2010 Giles Winstanley
Updated: 12 May 2010
Version: 5.0

What is DBPool?

A Java-based database connection pooling utility, supporting time-based expiry, statement caching, connection validation, and easy configuration using a pool manager.

Why would I use it?

Applications using databases often need to obtain connections to the database frequently. For example, a popular website serving information from a database may need a connection for each client requesting a page using their browser. To ensure good application response time for each client, the application needs to be profiled to find the time spent performing each of its tasks. One of the most expensive database-related tasks is the initial creation of the connection. Once the connection has been made the transaction often takes place very quickly. A connection pool maintains a pool of opened connections so the application can simply grab one when it needs to, use it, and then hand it back, eliminating much of the long wait for the creation of connections.

Licence Agreement

DBPool is available under a BSD-style licence as described below. This licence permits redistribution of the binary or source code (or both) for commercial or non-commercial use, provided the licence conditions are followed to acknowledge origination and authorship of the library.

DBPool : Java Database Connection Pooling <http://www.snaq.net/> Copyright (c) 2001-2010 Giles Winstanley. All Rights Reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. Redistributions of modified versions of the source code, must be accompanied by documentation detailing which parts of the code are not part of the original software. The name of the author may not be used to endorse or promote products derived from this software without specific prior written permission. THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDER "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Where can I get it?

What about support?

Please read this documentation before sending a support email. I frequently receive support emails from people who simply haven't taken the time to read the documentation and find the relevant information. Unsurprisingly I usually just point these people back here. If you need to ask for help, please email this address, and include as much information as possible to help diagnose the problem, including log files (with debug option enabled), stack traces, and source code, and properties file where appropriate. I will endeavour to reply as soon as possible.

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.

---

Table of Contents

• How do I use it?
• Using a ConnectionPool
• Using a DataSource
• Using the ConnectionPoolManager
• Defining the behaviour of the pool
• Logging configuration
• Good database programming practices
• Choosing pooling parameters
• Advanced features
  • Connection validation
  • Disabling statement caching
  • Password encryption
  • Shutdown hook
  • Asynchronous connection destruction
  • Asynchronous/forced pool release
  • Pool listeners
• Notes & troubleshooting
  • Exception/Error: java.lang.AbstractMethodError
  • Exception/Error: java.lang.NoSuchMethodError
  • Exception/Error: java.sql.SQLException: Unsupported feature
  • CLASSPATH problems? Unable to find properties file?
  • Connection creation
• Release notes for DBPool 5.0
• Change log

---

How do I use it?

To use DBPool you need to have the JAR file in a location where it's available for use by the host system you are using. For standalone applications this usually means in the CLASSPATH, and for application servers there is usually 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 one or more 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.

How pooling is controlled

A single pool maintains multiple open connections, where each connection connects to the same database source using the same authentication. The pool also manages how those connections are handed out to be used, and what happens to them when they are closed. Both the size of the pool and the number of connections available change based on user-specified properties, the most common of which are shown in the adjacent table.

Property	Explanation
minpool	Minimum number of connections that should be held in the pool.
maxpool	Maximum number of connections that may be held in the pool.
maxsize	Maximum number of connections that can be created for use.
idleTimeout	The idle timeout for connections (seconds).

When a pool is first created it contains no connections. If minpool > 0 then the pool automatically tries to create this number of new connections ready to be used. A total of maxsize connections can handed out for simultaneous use (unlimited if maxsize = 0). A maximum of maxpool connections are ever retained in the pool for reuse; returned connections above this limit are destroyed. Pooled connections that are not used for idleTimeout seconds are destroyed (if idleTimeout > 0).

A pool therefore has two general types of behaviour: expiring, and non-expiring. An expiring pool is one for which any connection that is idle/unused for a specified time (idle timeout) is "expired" (i.e. removed) from the pool. In both situations the pool can hand out up to maxsize connections, and pool up to maxpool connections. The difference is that a non-expiring pool will not expire unused connections, so will generally retain a larger number of connections for reuse as they only get removed from the pool if they become invalid. The two pool types also differ in how they initially become populated. Immediately after creation both types start out with no connections in the pool. Pooling of connections establishes automatically as items are checked in and out. Because of the additional checks that need to be done, an expiring pool can self-populate very quickly, but a non-expiring pool will populate gradually. If required it can be explicitly requested using the init() method, which requests that minpool connections are created.

Picking appropriate values for the pooling properties is not always easy. Various factors may affect your decisions, not least of which could be licence restrictions, system resources, etc. Many databases will close any unused connections once a certain time has elapsed (e.g. MySQL). It obviously makes no sense to set idleTimeout above this level (or to zero), and may well be better to set it lower. You should take time to analyse the demand and performance of your application setup before finalising values, which can help to maximize the effectiveness of the pooling.

---

Using a ConnectionPool

Direct use of ConnectionPool objects can provide substantial performance gains with minimum changes to the previous non-pooling code. A single ConnectionPool provides a centralized location for access to connections to a single database with specific authentication credentials and parameters. For more information on the details of each parameter see the section: Defining the behaviour of the pool.

When creating/using a ConnectionPool instance it is assumed that access to the relevant JDBC drivers has already been established. To ensure this is done your JDBC driver(s) should be appropriately registered with java.sql.DriverManager before creation/use of any pools; for example:

Class c = Class.forName(<class name>);
Driver driver = (Driver)c.newInstance();
DriverManager.registerDriver(driver);

Once the appropriate JDBC drivers have been registered, a connection pool may be created:

ConnectionPool pool = new ConnectionPool(<poolname>,
                                         <minpool>,
                                         <maxpool>,
                                         <maxsize>,
                                         <idleTimeout>,
                                         <url>,
                                         <username>,
                                         <password>);

or...

ConnectionPool pool = new ConnectionPool(<poolname>,
                                         <minpool>,
                                         <maxpool>,
                                         <maxsize>,
                                         <idleTimeout>,
                                         <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";
// Note, timeout is specified in milliseconds.
ConnectionPool pool = new ConnectionPool("local",
        5, 10, 30, 180000, url, "b_lightyear", "BeyondInfinity");

Once the pool is created it is ready to hand out connections. By default the pool doesn't open any connections until the first time one is requested, even if minpool > 0. If you would like to trigger the pool to start populating at startup, make a call to the pool's init() method.

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

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

The only difference between using a pooled connection and using a normal connection is how that connection is obtained. The connection is used and closed just like a normal connection. Be aware that as with unpooled connections, it's important to ensure you close the connection when finished with it, to avoid it becoming a point of unwanted resource retention (i.e. a possible memory leak).

Once the application no longer requires the pool you should release its resources:

pool.release();

Releasing the pool when finished is an important step and should not be omitted. Failure to release the pool can cause an application to hold on to resources, which often leads to unexpected results such as unexpected memory usage, failure of applications to terminate, etc. To help with this it's possible to automate the release with a shutdown-hook, which releases the pool when the Java Virtual Machine exits.

Using a DataSource

DBPool comes with a javax.sql.DataSource implementation which can be used similarly to other DataSource instances, with the benefit that it provides pooled connections using a wrapped ConnectionPool instance. The table below lists the properties that are supported, which are similar to those specified in the section: Defining the behaviour of the pool.

Bean properties supported by snaq.db.DBPoolDataSource, which can also be specified via snaq.db.DBPoolDataSourceFactory.

Property	Description
name	Name of the DataSource, which is also used to assign a ConnectionPool name.
description	Description for the DataSource.
driverClassName	Fully-qualified class name of JDBC Driver to use.
url	JDBC URL to connect to the database.
user	Username for database connections.
password	Password for database connections.
passwordDecoderClassName	Fully-qualified class name of snaq.db.PasswordDecoder implementation to use. (It must have a public no-argument constructor).
minPool	Minimum number of pooled connections to maintain.
maxPool	Maximum number of pooled connections to maintain.
maxSize	Maximum number of connection that can be created.
idleTimeout	Idle timeout of pooled connections (seconds).
loginTimeout	Timeout for database connection attempts (seconds).
validatorClassName	Fully-qualified class name of snaq.db.ConnectionValidator implementation to use. (It must have a public no-argument constructor).
validatorQuery	Query string to use for validation, if validatorClassName not specified. This is passed to a snaq.db.SimpleQueryValidator instance.

For example:

DBPoolDataSource ds = new DBPoolDataSource();
ds.setName("pool-ds");
ds.setDescription("Pooling DataSource");
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://192.168.1.101:3306/ReplicantDB");
ds.setUser("Deckard");
ds.setPassword("TyrellCorp1982");
ds.setMinPool(5);
ds.setMaxPool(10);
ds.setMaxSize(30);
ds.setExpiryTime(3600);  // Specified in seconds.
ds.setValidationQuery("SELECT COUNT(*) FROM Replicants");

There is also a factory class, snaq.db.DBPoolDataSourceFactory, which may be used to create DBPoolDataSource instances, and is useful for including DBPool pooling in application servers. The factory class enables an instance to be created and configured using the standard configuration mechanism of many application servers. For example, a web server "configuration.xml" file might have an entry as show below. For more information see the Javadoc API for snaq.db.DBPoolDataSource.

<Resource factory="snaq.db.DBPoolDataSourceFactory"
          type="javax.sql.DataSource"
          name="jdbc/pool-ds"
          auth="Application"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://192.168.1.101:3306/ReplicantDB"
          user="Deckard" password="TyrellCorp1982"
          minPool="5" maxPool="10" maxSize="30" idleTimeout="3600"
          validationQuery="SELECT COUNT(*) FROM Replicants" />

Using the ConnectionPoolManager

The pool manager provides comprehensive support for defining one or more connection pools in an external properties file. In fact multiple pool managers are support, allowing you to define groups of pools from multiple sources, but most applications generally only require a single pool manager. A ConnectionPoolManager instance 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 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.

To use it, you first need to obtain a ConnectionPoolManager using one of the static getInstance(...) methods:

Method of Access	Explanation
getInstance()*	Returns the pool manager instance defined by the default properties file ("dbpool.properties") within the CLASSPATH (or appropriate location for ClassLoader to find).
getInstance(String)*	Returns the pool manager instance defined by the properties file with the filename specified, located in the CLASSPATH (or appropriate location for ClassLoader to find).
getInstance(File)*	Returns the pool manager instance 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 has another version which also takes a string requesting the text-encoding in which the properties should be read.
**Note 2: It's not possible to use both a default properties file instance and a Properties object instance simultaneously. 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 ConnectionPoolManager instances may be used, each with its own parameters.

Using these 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:

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

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 you have a pool manager reference you can now obtain/return ("check-out"/"check-in") connections from/to its 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.

For example, the following code obtains a connection from the pool manager, performs some operations, then returns the connection:

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

Notice that when you have finished working with a connection you simply call its 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.

cpm.release();

---

Defining the behaviour of the pool

When using a pool manager the behaviour of the pools 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.

When using a standalone ConnectionPool many of these properties can also be set using a variety of instance methods.

name=<name>
drivers=<class name of driver>

<poolname>.url=<JDBC connection URL for database>
<poolname>.user=<user name>
<poolname>.password=<password>
<poolname>.minpool=<minimum pooled connections>
<poolname>.maxpool=<maximum pooled connections>
<poolname>.maxsize=<maximum possible connections>
<poolname>.idleTimeout=<idle timeout of connections (seconds)>
<poolname>.validator=<ConnectionValidator implementation>
<poolname>.decoder=<PasswordDecoder implementation>
<poolname>.prop.<property>=<value>

Properties drivers and url are mandatory, while the rest are optional and take on default values when not supplied as described in the table below. Inevitably you'll likely end up supplying values for user/password, and also for at least maxpool or you'll get no benefit from the pooling system.

Property	Purpose	Possible values	Default value
name	Specifies a name for the pool manager instance. Optional, but useful to obtain isolated logging output from this instance if required (see logging configuration).	string
drivers	Comma-separated list of fully-qualified JDBC Driver class names required by configured pools.	string, ...
logfile	Specifies a custom log file for this pool manager.	string
dateformat	Date formatting string used for the custom log (java.text.SimpleDateFormat style).	string
Any of the following properties may be repeated for each pool defined:
pool.url	Specifies the JDBC database connection URL.	string
pool.user	Specifies the JDBC database connection username.	string
pool.password	Specifies the JDBC database connection password.	string
pool.minpool	Specifies the minimum number of connections that should be held in the pool.	integer, >=0	0
pool.maxpool	Specifies the maximum number of connections that may be held in the pool.	integer, >=0 (>=minpool)	0
pool.maxsize	Specifies the maximum number of connections that can be created for use.	integer, >=0 (>=maxpool)	0 (unlimited)
pool.idleTimeout	Specifies the timeout for individual connections that are idle (seconds).	integer, >=0	0 (no timeout)
pool.validator	Determines how to ensure that connections are valid.	Class name (implements ConnectionValidator)	(none)
pool.decoder	Specifies an optional password decoder class.	Class name (implements PasswordDecoder)	(none)
pool.prop.property	Optional properties to be passed to the JDBC driver (e.g. prop.foo=bar).	string
These "non-standard" properties may also be repeated for each pool defined:
pool.logfile	Specifies a custom log file for this pool (in addition to regular logging).	string
pool.dateformat	Date formatting string used for the custom log (java.text.SimpleDateFormat style).	string
pool.cache	Option to enable/disable caching of statements.	true/false	true (caching enabled)
pool.access	Pool item selection strategy.	string : {LIFO, FIFO, RANDOM}	LIFO
pool.async	Option to enable/disable asynchronous destruction of invalid connections.	true/false	false (synchronous)
pool.recycleAfterDelegateUse	Option to enable/disable recycling of connections which have had the underyling delegate connection accessed.	true/false	false (don't recycle)
pool.listenerN	Option to specify a pool listener class name to attach to the pool. N denotes an integer, starting at zero, incrementing for each new listener. Valid classes must have no-argument or one-argument (Properties) constructor.	Class name (implements ConnectionPoolListener/ObjectPoolListener)
pool.listenerN.property	Optional properties to pass to constructor of listenerN (e.g. mypool.listener0.foo=bar). Item are collected as Properties instance (without prefix) and passed to class constructor.	string
pool.mbean	Option to register a basic MBean for JMX management of this pool.	true/false	true

You can define multiple pools in the properties file, provided each one has a different pool name. To specify multiple database driver classes simply separate them using a comma/space. 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.

Internal validation of the pooling properties is performed, so if you specify impossible values the pool will default to: no pooled items, unlimited total connections, no idle timeout. This will behave similarly to not having a pool, so it is worth thinking through the values you choose.

For example, the properties for a MySQL database hosted by a computer on the local subnet could be:

name=poolman1
drivers=com.mysql.jdbc.Driver

local.url=jdbc:mysql://10.0.0.7:3306/homeDB
local.user=Nemo
local.password=LuckyFin
local.minpool=0
local.maxpool=3
local.maxsize=10
local.idleTimeout=0

This pool will not create any connections until they are requested, will only ever hand out a maximum of 10 connections simultaneously, and will retain up to 3 to be reused once they have been created. Pooled connections that are not being used will stay in the pool indefinitely.

• minpool determines the minimum number of connections that should be held in the pool.
• maxpool determines the maximum number of connections that may be held in the pool.
• maxsize determines the absolute maximum number of connections that can be created for use.
• idleTimeout is the idle timeout for individual connections (seconds).

The number of connections available for use is dictated by maxpool and maxsize. A maximum of maxpool items are ever held for reuse in the pool, although up to maxsize can be created and handed out; surplus connections will be destroyed when handed back. If maxsize is zero then an unlimited number of connections can be obtained, but the surplus will be destroyed when closed.

Depending on your application's demands you'll see differing behaviour in the pool. With low demand only a few connections are likely to ever be created (sometimes fewer than maxpool), whereas with very high demand you may find the limits of the pooling parameters and have to think about changing them. 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 to deal with all likely scenarios. 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 maxsize at or just below this limit, and choose a maxpool value which deals well with the average load. If you have a consistently high-demand application with a limited number of connections you may want to have no idleTimeout and make the maxpool equal to maxsize 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. Whatever values you choose, make sure you choose them carefully based on a solid understanding of the behaviour of application and the demand for database access.

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 idle timeout 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".

# DBPool connection pool definitions.

name=poolman2
drivers=oracle.jdbc.driver.OracleDriver

# Pool for accessing data from space.
pool.url=jdbc:oracle:thin:@myHost.myDomain:1521:test
pool.user=b_lightyear
pool.password=BeyondInfinity
pool.minpool=1
pool.maxpool=10
pool.maxsize=30
pool.idleTimeout=600
pool.validator=snaq.db.Select1Validator

Firstly note that as it's a properties file, comments are allowed. Regarding pooling, there's 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 a maximum of 10 connections are pooled for re-use. When demand is high up to 30 connections may be handed out, and when demand is low, each connection will expire after ten minutes of idle time (600 seconds). When there is no demand there should also always be at least one open connection available. Note that this example also make use of custom connection validation (see the Advanced features section for more information).

---

Logging configuration

While individual pool and pool manager log files are still supported for backwards-compatibility, it's advisable to use the more powerful an configurable logging support that is now included. Logging is supported through the Apache Commons Logging library, which provides automatic support for several logging libraries, of which these are the two most likely to be used:

• Apache log4j
• Java Logging (in Java Platform 1.4 and later)

Log4j is the recommended choice, providing the most out-of-the-box flexibility. The Java Logging API is an easy and obvious choice for many because of its inclusion with the Java Platform, but it doesn't have the versatility of log4j. If you don't already have a logging solution, use log4j to avoid headaches.

Summary explanation: Configuration of logging is done according to the logging system used, so the documentation for the relevant library should be consulted. All libraries share the concept of a "logger", which is named and can be configured using an external file. Loggers have "appenders" (or "handlers" in Java Logging API), which dictate the destination of the log entries for the logger. Each appender (or handler) has a "layout" (or "formatter") which determines how the log entry is written to that destination. Every log entry has a "level" which indicates is severity/importance: {fatal, error, warn, info, debug, trace}. The level of interest can be set for a logger/appender, and log entries which make it past the level-filtering applied by the logger/handler get logged. For more details read the documentation with your chosen logging library which will explain it more thoroughly.

The names of the relevant loggers for DBPool follow the Java package/class format as follows:

Logger Name	Description
snaq.db.ConnectionPoolManager	Parent logger for all pool managers.
snaq.db.ConnectionPoolManager.<name>	Instance logger for pool manager with specified name. The name may be specified in the properties file, and if not defined the logger will not be created (output goes to parent logger).
snaq.db.ConnectionPool	Parent logger for all connection pools.
snaq.db.ConnectionPool.<name>	Instance logger for connection pool with specified name. Pool manager use: name is specified in the properties file in the pool definition (e.g. <name>.foo=bar). Standalone use: name is specified in the constructor, and if not specified (i.e. null or "") is not created (output goes to parent logger).

Commented examples of basic logging configuration files for the above two libraries (log4j.properties / logging.properties) are included with the complete download archive. The following simple example for the log4j library shows what might be used for a very basic application with minimal logging requirements:

# Appender to write to console.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%-5p %C{1} - %m%n

# Appender to write to shared/parent pool manager log.
log4j.appender.POOLMANAGER=org.apache.log4j.FileAppender
log4j.appender.POOLMANAGER.File=DBPool-managers.log
log4j.appender.POOLMANAGER.layout=org.apache.log4j.PatternLayout
log4j.appender.POOLMANAGER.layout.ConversionPattern=%d [%5p]: %m%n

# Appender to write to shared/parent pool log.
log4j.appender.POOLS=org.apache.log4j.FileAppender
log4j.appender.POOLS.File=DBPool-pools.log
log4j.appender.POOLS.layout=org.apache.log4j.PatternLayout
log4j.appender.POOLS.layout.ConversionPattern=%d [%5p]: %m%n

# Appender for pool instance (pool-local).
log4j.appender.LOCAL=org.apache.log4j.FileAppender
log4j.appender.LOCAL.File=DBPool-local.log
log4j.appender.LOCAL.layout=org.apache.log4j.PatternLayout
log4j.appender.LOCAL.layout.ConversionPattern=%d [%5p]: %m%n

# Turn off root logging.
log4j.rootLogger=none
# Enable parent pool manager logging ("trace" level, to file/console).
log4j.logger.snaq.db.ConnectionPoolManager=trace, POOLMANAGER, CONSOLE
# Enable parent connection pool logging ("info" level, to file/console).
log4j.logger.snaq.db.ConnectionPool=info, POOLS, CONSOLE
# Enable instance connection pool logging ("debug" level, to file only).
log4j.logger.snaq.db.ConnectionPool.pool-local=debug, LOCAL

Java Logging

Log level comparison.

Apache Commons & log4j	Java Logging
fatal	SEVERE
error	SEVERE
warn	WARNING
info	INFO
	CONFIG
debug	FINE
	FINER
trace	FINEST

For those who prefer to use the Java Logging API, you need to know how the various logging libraries compare in terms of their log levels, indicating the relative severity of each log entry. For example, it would be easy to think the "trace" level of Apache Commons Logging would equate to "FINE" in Java Logging, but it actually equates to "FINEST". The table of equivalents is shown to the right, and should help those trying to get the log output required.

Additionally, a convenient single-line Formatter implementation has been packaged within the distribution to make log output more easily readable: snaq.util.logging.TerseFormatter. By default TerseFormatter produces a simple output: "<date/time> <level>: <message>", but can be configured a to use a different log message format if required (see the API documentation).

Example logging.properties file for the Java Logging API, which writes single-line entries to the console and XML output to file:

# Handler to write to console (System.err in this case).
java.util.logging.ConsoleHandler.formatter=snaq.util.logging.TerseFormatter
java.util.logging.ConsoleHandler.encoding=UTF-8

# Handler to write to log files.
java.util.logging.FileHandler.formatter=java.util.logging.XMLFormatter
java.util.logging.FileHandler.encoding=UTF-8
java.util.logging.FileHandler.pattern=DBPool_%u_log.xml

# Turn off root logging (INFO level for when enabled).
handlers=
.level=INFO
# Enable parent pool manager logging ("FINEST"/trace level, to file/console).
snaq.db.ConnectionPoolManager.level=FINEST
snaq.db.ConnectionPoolManager.handlers=java.util.logging.ConsoleHandler, java.util.logging.FileHandler
# Enable parent connection pool logging ("INFO" level, to file/console).
snaq.db.ConnectionPool.handlers=java.util.logging.ConsoleHandler, java.util.logging.FileHandler
snaq.db.ConnectionPool.level=INFO
# Enable instance connection pool logging ("FINE"/debug level, to file only).
snaq.db.ConnectionPool.pool-local.handlers=java.util.logging.FileHandler
snaq.db.ConnectionPool.pool-local.level=FINE

Good database programming practices

The following two simple practices will help to ensure you get the best from DBPool:

• Always close your ResultSet/Statement/Connection instances once finished with them.
• Always close them in the reverse sequence to their creation:

Connection con = pool.getConnection();
Statement st = con.createStatement();
ResultSet res = st.executeQuery("...");
...
res.close();
st.close();
con.close();

Obviously not every application is this simple, but if the flow of code sticks to this principal, the likelihood of resource retention problems ("memory leaks") and unexpected pooling behaviour is markedly reduced. It seems obvious when stated succinctly, yet many bugs/problems/headaches can be ascribed to not following these two guidelines.

Choosing pooling parameters

Deciding what pooling parameter value to use to get the best performance from a connection pooling system is a difficult task. Your choices depend on many factors: the application (and its code efficiency), the database drivers, the database application, the database platform, etc. All these variables (and more) lead to the conclusion 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. To start, try to step back from the details of the code and view your application as a whole.

Some of the questions below may assist you finding useful parameter values. 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 output is detrimental to overall 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 minpool/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 maxsize:

• 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 idleTimeout:

• 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 its 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. For more useful information, see the following Advanced features section.

---

Advanced features

Connection validation

Each time a connection is requested from a pool it is validated 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 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 by using a custom validation class, which is simply a class which implements the snaq.db.ConnectionValidator interface. The simplest way of making the validation more rigorous is to use one of the supplied validator classes:

• snaq.db.AutoCommitValidator
• snaq.db.Select1Validator
• snaq.db.SimpleQueryValidator (not designed for use with pool manager)

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. Another fairly common way of validating connections is to issue the SQL query "SELECT 1", which can be done by using the snaq.db.Select1Validator class. However, you can write your own validation classes as required by implementing the ConnectionValidator interface, or by using/sub-classing the (Simple)QueryValidator convenience class.

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 validation rigorousness and application performance. To obtain (almost) error-proof validation you want a validator that performs full queries on the database to determine if it's operational. Sometimes this is necessary to provide the best service, but it can also impact performance greatly. It's 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 weak validation not identifying the closure. The chances of this is greater when using non-Type-4 JDBC drivers (i.e. one which uses native code instead of being pure Java). If in doubt use a validator that is more rigorous than required, then reduce the rigorousness later to enhance speed performance and assess if quality of validation becomes a problem. If necessary view debug logs 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). If caching is not required you can disable it 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 snaq.db.PasswordDecoder interface, which specifies single method:

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 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=<class name>

Note: this mechanism only allows for implementation of password-based security.

Shutdown Hook

When a pool or pool manager is no longer required, the standard procedure is to "release" it by calling the appropriate release() method, on either the ConnectionPool or ConnectionPoolManager instance. Another option is to register a "shutdown hook" to perform this step automatically when the Java VM is exiting. Bear in mind that because it occurs when the VM is exiting, you need to check if this is an appropriate time for the release for your application. To register a shutdown hook, simply call registerShutdownHook() on a ConnectionPool/ConnectionPoolManager/DBPoolDataSource instance, or use the static method ConnectionPoolManager.registerGlobalShutdownHook().

Note: if individual ConnectionPool hooks are registered, then a ConnectionPoolManager global hook registered and removed, the individual pools will have lost their individual shutdown hooks, so will need to be re-registered as required.

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 ask a pool to release its resources asynchronously by calling the releaseAsync() method instead of the release() method when finished with a pool. This method returns immediately and performs 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.

You can force a 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 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 may lead to unpredictable database contents and data loss.

Note: When using the pool manager calling release() performs the release synchronously and forcibly, so it's wise to ensure all connections from the underlying pools have been closed.

Pool listeners

It can be useful to have code able react to the current state of a pool. For this reason pools can issue events about their activity to objects which have registered interest. Listener objects must implement the snaq.db.ConnectionPoolListener or snaq.util.ObjectPoolListener interface, and can register/deregister their interest by using the provided methods, for example:

• addConnectionPoolListener(ConnectionPoolListener)
• removeConnectionPoolListener(ConnectionPoolListener)

Also provided for convenience is the snaq.db.ConnectionPoolEventAdapter class, which provides empty implementations of all the methods in the ConnectionPoolListener interface, so can be extended to override fewer methods. The events triggered are as follows:

Event Identifier	Description
INIT_COMPLETED	Fired when the init() method has completed creating new pool connections.
CHECKOUT	Fired just before a valid connection is handed back from 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 (maxsize) to be reached.
MAX_SIZE_LIMIT_ERROR	Fired when a check-out request is made but the pool's maximum size limit (maxsize) 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_FLUSHED	Fired when the pool is flushed of free/unused connections. (This generally only happens is flush() is explicitly called for a pool.)
POOL_RELEASED	Fired when a pool has been released. No more events are fired from the same pool following this event, as all listeners are removed.

PoolTracer

An example of the above pool listener feature is that of the included snaq.util.PoolTracer class. This class implements a simple PoolListener to write pool activity data to a log file. This class can either be instantiated via one of its constructors programatically, or if using the pool manager using the <poolname>.listenerN pool property. PoolTracer requires a filename for the trace log file, which can be specified as <poolname>.listenerN.file=<filename>. See the Javadoc API for the PoolTracer class for more information.

For example, to specify the pool tracer as the only listener on a pool "pool-local", writing to a trace log file called "trace.log" in the current folder, place the following lines in the properties file alongside the other properties:

pool-local.listener0=snaq.util.PoolTracer
pool-local.listener0.file=trace.log
# Why not specify the optional date format too?
pool-local.listener0.dateformat=HH:mm:ss,SSS

---

Notes & troubleshooting

Exception/Error: java.lang.AbstractMethodError

As the Java API documentation states, "this error can only occur at run time if the definition of some class has incompatibly changed since the currently executing method was last compiled". When using a pre-compiled DBPool JAR library, this error appears when using a database driver which does not support the same JDBC specification as the JAR library. The best solution is either to download the appropriate version of DBPool from the website, or recompile the library from source using the appropriate Java/JDBC versions.

Exception/Error: java.lang.NoSuchMethodError

This error is thrown if a class file cannot find a specified method, and is usually the result of running DBPool in an older virtual machine than that for which it was compiled. The solution is to either upgrade your Java Platform, or use a compatible version of DBPool, either downloaded or recompiled from source.

Exception/Error: java.sql.SQLException: Unsupported feature

This exception is raised when trying to use using a JDBC feature which the underlying database driver doesn't support, usually because your Java platform is using a more recent JDBC specification than the one the driver was compiled against. To resolve this issue, either update your database driver to the appropriate version, or use a different version of DBPool.

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 classloaders provided by other vendors may 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 use a different ConnectionPoolManager.getInstance(...) method.

Connection creation

The following pseudo-code shows how the connection pool tries to establish new database connections, and may help debug certain problems.

1. if Properties object used
2. ... use DriverManager.getConnection(url, properties)
3. else if username specified
4. ... use DriverManager.getConnection(url, username, password)
5. else
6. ... use DriverManager.getConnection(url)

Note: if any driver properties are specified in the properties file (e.g. <poolname>.prop.foo=bar), the pool manager injects the username/password into the properties for convenience so the first getConnection(...) method above works.

---

Change log

2010-05-12 (v5.0)	Overhaul of logging mechanism; now uses Apache Commons Logging, and recommend to use with Apache log4j. Changes to pooling logic, including: Addition of minpool property (specifies minimim target pool size) Changed from maxconn to maxsize (for codebase consistency). Significant changes to thread-safety, improving robustness of library. Changed event listener methods to fully asynchronous dispatch mechanism. Simplification of ConnectionPoolManager release logic (no longer tracks client count). Added shutdown-hook support to ensure pool/manager release on JVM exit (client must use registration method to enable). Option to read pool manager properties files in user-specified text encoding. Added properties file support for pool listener instantiation. Changed ConnectionPoolListener method name for consistency (poolInitCompleted instead of initCompleted). Changed ConnectionPool constructor to second-based expiry time to be consistent with ConnectionPoolManager and DataSource. Bug fix for duplicate events being triggered when pool created using a Properties object. Bug fix to ensure explictly closed CachedCallableStatements can't be used inadvertently.
2010-01-14 (v4.9.3)	Fixed logging regression bug (date/time prefix omission in some cases). Updated ObjectPool init() method to allow initialization of up to maxSize items when an expiry time is set. Fixed bug preventing unused initialized items in pools from being expired. Made pool parameter validation more rigorous (rejects negative values).
2010-01-11 (v4.9.2)	Integration of generics to support more robust source checking. Added basic javax.sql.DataSource and javax.naming.spi.ObjectFactory implementations for easier use of pooling library.
2009-10-06 (v4.9.1)	Bug fix for recycling of CallableStatements (thanks to Mathieu Durand).
2009-09-05 (v4.9)	Migrated software library to BSD-style license. Fix to ensure inactive pooled statements cannot be inadvertently used. Bug fix for synchronization problem with handling of open statements. Improved security for cached statements not in use. Bug fix for management of non-cachable statements (causing possible memory leak). Added option to not close log-stream on pool release (allows stream reuse). Added classes for easy/quick creation of connection validators (QueryValidator, SimpleQueryValidator). Added event/listener capability for pool initialization. Added event/listener capability for pool manager release. Removed non-authenticated fallback option when getting connection with authentication. Improved thread-safety for pool event listener management. Bug fix for object initialization with unlimited maximum connections (thanks to Domagoj Klepac). Changed logging system to work with PrintWriters instead of PrintStreams. Fixed logging initialisation bug when using pool manager (thanks to Stephen D'Souza). Added pool integrity safeguards for raw/delegate connection use.