DBPool : Java Database Connection Pooling

Copyright © 2001-2017 Giles Winstanley
Updated: 15 October 2017
Version: 7.0.2

What is DBPool?

A Java-based database connection pooling utility, supporting time-based idle timeout, 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 © 2001-2017 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:

  1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
  2. 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.
  3. 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.
  4. 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?

Table of DBPool Java/JDBC version compatibility.
Platforms listed in bold are the default for that DBPool version, while other supported platforms may require recompilation, or a different version from the Maven Central repository.
DBPool versionCompatible Java/JDBC version
7.0Java 9+ / JDBC 4.3
Java 1.8 / JDBC 4.2
Java 1.7 / JDBC 4.1
6.0Java 1.7 / JDBC 4.1
5.1Java 1.6 / JDBC 4.0
5.0Java 1.6 / JDBC 4.0
Java 1.5 / JDBC 3.0
Java 1.4 / JDBC 3.0

Requirements: DBPool 7.0.2 requires Java Platform 8 (also known as Java 1.8) or above, with support for JDBC 4.2 features. If rebuilding from the source code, Apache Maven is recommended as a build tool.

Dependencies: DBPool 7.0.2 makes use of the SLF4J logging library, and the library's relevant JAR files should be included in the CLASSPATH. The package download includes the most recently available version of this library.

You can find out which Java version you have by typing java -version into a terminal session (aka "command prompt"), assuming your development platform is configured for terminal-based Java access. Perhaps confusingly, Java Platform 8 reports "1.8.x_xx" (showing internal build number, where x varies). Another quick way is to download this JAR file and launch it at the command-line by typing: java -jar JDBCInfo.jar after navigating to the download directory (it contains a single Java class just to show a little additional useful information).

The simplest way to use DBPool is via the Maven Central Repository (groupId:net.snaq, artifactId:dbpool). For example, if using Apache Maven you can add the following to your POM file:

<dependency>
  <groupId>net.snaq</groupId>
  <artifactId>dbpool</artifactId>
  <version>7.0.2</version>
</dependency>

If you need an version compiled for a different platform (as specified in the above table), you might find it availble in the Maven Central Repository using the modified artifactId dbpool-jdkN, where N refers to the respective Java Platform version (e.g. dbpool-jdk7 for a Java Platform 7 compatible version).

Alternatively you can use the links below to access the compiled JAR library, documentation, or source code:

 

If you need to directly access the binary/source, you can download the latest version of DBPool from: http://www.snaq.net/

What about support?

Please read this documentation thoroughly before requesting support. Most questions are best answered here, and support requests based on laziness are answered similarly. If you need to ask for help or raise an issue, please use the project issue tracker, and include as much information as possible to help diagnose the problem, including the version of DBPool in use, relevant stacktraces, log files (with debug option enabled), source code, and properties file where appropriate. I will endeavour to reply as soon as possible.


Table of Contents


How do I use it?

If using DBPool via a dependency management system (e.g. Apache Maven, Ivy) then simply add it as a dependency in your configuration as usual. For other scenarios, the DBPool JAR file must be in a location where it's accessible by the host application. For standalone applications this usually means in the CLASSPATH, and for web containers there is usually a specific directory recommended for JAR libraries (for example, when used with Apache Tomcat it can be placed in the <webapp>/WEB-INF/lib directory).

DBPool is usually used in one or more of these different ways:

  1. Using the DBPoolDataSource.
  2. Using the ConnectionPoolManager to manage one or more connection pools.
  3. Direct use of individual ConnectionPool objects.

Each of these methods is explained in more detail later, after this brief explanation of how pooling is controlled.

How pooling is controlled

Basic properties controlling pooling behaviour.
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).

Each connection within a pool connects to the same database source using the same authentication. The pool manages how those connections are handed out to be used, and recycles (or destroys) 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.

The number of connections available for use is determined by maxpool and maxsize. A maximum of maxpool items are ever held open for reuse in the pool, although up to maxsize can be used; surplus connections (above maxpool) will be destroyed when handed back. If maxsize is zero then an unlimited number of connections can be obtained. Pooled connections that are not used for idleTimeout seconds are destroyed (if idleTimeout > 0). 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.

Choosing values for the pooling properties is not always obvious. Various factors may affect your decisions, such as licence restrictions, system resources, etc. Many databases will close any unused connections once a certain time has elapsed (e.g. 8 hours for MySQL). It makes no sense to set idleTimeout to longer than this, so an obvious choice would be a non-zero smaller time period. You should also check pooling performance under both low and high load scenarios. 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. These should be carefully considered when configuring properties, and you should analyse the demand and performance of your application before finalising values for production systems.


Using a DataSource

DBPool comes with a javax.sql.DataSource implementation (snaq.db.DBPoolDataSource) which provides connections from a 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 (can also be specified via snaq.db.DBPoolDataSourceFactory).
PropertyDescription
nameName of the DataSource, which is also used to assign a ConnectionPool name.
descriptionDescription for the DataSource.
driverClassNameFully-qualified class name of JDBC Driver to use.
urlJDBC URL to connect to the database.
userUsername for database connections.
passwordPassword for database connections.
passwordDecoderClassNameFully-qualified class name of snaq.db.PasswordDecoder implementation to use.
(It must have a public no-argument constructor).
minPoolMinimum number of pooled connections to maintain.
maxPoolMaximum number of pooled connections to maintain.
maxSizeMaximum number of connection that can be created.
idleTimeoutIdle timeout of pooled connections (seconds).
loginTimeoutTimeout for database connection attempts (seconds).
validatorClassNameFully-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.setIdleTimeout(3600);  // Specified in seconds.
ds.setValidationQuery("SELECT COUNT(*) FROM Replicants");

The factory class, snaq.db.DBPoolDataSourceFactory, may also be used to create DBPoolDataSource instances, and is useful for using DBPool with web containers. For example, a Tomcat web container context.xml configuration file might have an entry as show below.

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

To facilitate the graceful shutdown of the connection pool, you should also configure a ServletContextListener which is notified when the context is destroyed as the web container is shut down. Such a listener implementation is provided, but needs to be configured, for example in the web.xml configuration file (note the same JNDI name reference as for the defined DataSource resource):

<listener>
	<listener-class>snaq.db.DBPoolServletContextListener</listener-class>
</listener>
<context-param>
	<param-name>name</param-name>
	<param-value>jdbc/pool-ds</param-value>
</context-param>

In your web application code you can now reference the DataSource using standard CDI techniques:

@WebServlet(urlPatterns = "/bladerunner")
public class TyrellCorpServlet extends HttpServlet {
    @Resource(name = "jdbc/pool-ds")     // JNDI reference to DataSource
    private DataSource ds;               // Field populated by JNDI reference
}

Using a ConnectionPoolManager

The pool manager provides support for defining connection pools in a properties file. Multiple pool managers are supported, allowing you to define groups of pools from multiple sources, but in most cases a single pool manager is enough. 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.

To obtain a pool manager instance, use one of the static getInstance(…) methods:

 
Method to obtain pool manager 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 takes a string specifying the text-encoding of the properties.
**Note 2: You cannot 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. Aside from this limitation, multiple ConnectionPoolManager instances may be used.

For example, to obtain the pool manager defined by the default properties file:

ConnectionPoolManager cpm = null;
try
{
    cpm = ConnectionPoolManager.getInstance();
}
catch (IOException ex)
{
    // handle exception
}

This step would normally be done at the application initialization stage. For example, 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 poolname.prop.property=<value> instead, where timeout is specified in milliseconds. If a connection becomes available within the timeout the method will return the connection, otherwise null is returned. Once you have finished using the connection, 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:

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

The close() method is called implicitly using the Java try-with-resources feature. Instead of being closed the connection is actually recycled by the pool ready to be used again.

When completely finished with all pools managed by a ConnectionPoolManager, you should "release" it to ensure all resources are released.

// Finished using connection pool manager.
cpm.release();

Using a ConnectionPool

Applications requiring just a single pool can directly use a ConnectionPool instance, providing a centralized location for access to connections to a single database. 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's assumed the relevant JDBC driver has already been appropriately registered with the java.sql.DriverManager:

Class c = Class.forName("…");  // Fill JDBC driver class name here.
Driver driver = (Driver)c.newInstance();
DriverManager.registerDriver(driver);

Now the appropriate JDBC driver has been registered, a 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 might do something similar to this:

Class c = Class.forName("oracle.jdbc.driver.OracleDriver");
Driver driver = (Driver)c.newInstance();
DriverManager.registerDriver(driver);
String url = "jdbc:oracle:thin:@myDB.myISP.com:1521:test";
// Note: idleTimeout is specified in seconds.
ConnectionPool pool = new ConnectionPool("local", 5, 10, 30, 180, url, "b_lightyear", "BeyondInfinity");

The pool is now 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:

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

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 being referenced unintentionally (and possibly causing a memory leak).

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

// Finished using connection pool.
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.


Defining the behaviour of the pool

When using a pool manager the behaviour of the pools is managed either by a properties file (by default called dbpool.properties) or by a Properties object. The format of the properties file is shown below. The same key/value pairs apply when specifying a pool manager using a Properties object.

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. Usually you would specify values for at least user/password and maxpool. When using a standalone ConnectionPool these properties are specified using instance methods instead of a properties file.

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.
(Deprecated; use SLF4J logging instead.)
string  
dateformat Date formatting string used for the custom log (java.text.SimpleDateFormat style).
(Deprecated; use SLF4J logging instead.)
string  
The following standard properties may be specified 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 additional/advanced properties may also be specified for each pool defined:
pool.logfile Specifies a custom log file for this pool
(in addition to regular logging).
(Deprecated; use SLF4J logging instead.)
string  
pool.dateformat Date formatting string used for the custom log (java.text.SimpleDateFormat style).
(Deprecated; use SLF4J logging instead.)
string  
pool.cache Option to enable/disable caching of statements. true/false true (caching enabled)
pool.selection 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.releaseTimeout When releasing the pool, specifies the timeout before individual connections are forcibly destroyed (milliseconds). integer, >=0 -1 (no timeout) when standalone, 0 (immediate) when managed
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)  
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, 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.

Validation of the 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 like not having a pool).

For example, the properties to connection to a MySQL database hosted by a computer on the local subnet might be:

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

local.url=jdbc:mysql://10.0.0.7:3306/homeDB
local.user=jason
local.password=Argonaut1
local.maxpool=3
local.maxsize=0
local.idleTimeout=3600

This pool will not create any connections until they are requested (minpool=0, default value), has no limit on the maximum number of simultaneous connections, and will retain up to 3 open connections to be reused quickly. Pooled connections that are not used for over an hour (3600 seconds) will be destroyed.

The next example 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:@datastore.groundcontrol.com:1521:test
pool.user=MajorTom
pool.password=ProteinPills
pool.minpool=1
pool.maxpool=10
pool.maxsize=30
pool.idleTimeout=600
pool.validator=snaq.db.Select1Validator

Note that comments are allowed, as it's a standard Java properties file. 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

Logging is supported via the SLF4J library, which provides automatic support for several logging libraries, of which these are the most likely to be used:

Log4j is a good choice, providing a lot of flexibility. Logback is also a good choice, with native SLF4J support. The Java Logging API (JUL) is also supported because of its inclusion with the Java Platform, but it's less versatile. If you don't already have a logging solution, use Log4j or Logback to make life easier. Configuration of each is done using a seperate configuration file, but all DBPool logging calls are routed through SLF4J.

Summary of logging mechanism: Configuration of logging depends which library used, and the respective documentation 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" ("handlers" in JUL), which specify the destination of the log entries for the logger. Each appender (or handler) has a "layout" ("formatter" in JUL) which specifies how the log entries are written to that appender. Every log entry has a "level" (either explicit, implicit, or inherited) which indicates its severity/importance: {fatal, error, warn, info, debug, trace} (slightly different for JUL; see below). The level of interest can be set for a logger/appender, and log entries which make it past the level-filtering are logged according to the appender specification. 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).

Apache Log4j

You are recommended to read the Log4j FAQ page to understand which JAR libraries you must include for correct logging configuration, and to understand how to write the configuration file for the version of Log4j that you choose. Note that configuration for Log4j 1.x and 2.x are different.

Here is a simple example configuration file (log4j.properties) for the Log4j 1.x library:

# 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 ("info" level, to file/console).
log4j.logger.snaq.db.ConnectionPoolManager=info, 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

Here is a sample example configuration file (log4j2.xml) for the Log4j 2.x library:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Example Log4j 2.x configuration file. -->
<Configuration>
  <Appenders>
    <Console name="CONSOLE" target="SYSTEM_OUT">
      <PatternLayout>%d{ABSOLUTE} [%-5p]: %m%n</PatternLayout>
    </Console>
    <File name="POOLMANAGER" fileName="DBPool-managers.log">
      <PatternLayout>
        <Pattern>%d{ABSOLUTE} [%-5p]: %m%n</Pattern>
      </PatternLayout>
    </File>
    <File name="POOLS" fileName="DBPool-pools.log">
      <PatternLayout>
        <Pattern>%d{ABSOLUTE} [%-5p]: %m%n</Pattern>
      </PatternLayout>
    </File>
    <File name="LOCAL" fileName="DBPool-local.log">
      <PatternLayout>
        <Pattern>%d{ABSOLUTE} [%-5p]: %m%n</Pattern>
      </PatternLayout>
    </File>
  </Appenders>
	<Loggers>
		<Root level="OFF"/>
		<Logger name="snaq.db.ConnectionPoolManager" level="INFO">
			<AppenderRef ref="POOLMANAGER"/>
		</Logger>
		<Logger name="snaq.db.ConnectionPool" level="INFO">
			<AppenderRef ref="POOLS"/>
			<AppenderRef ref="CONSOLE"/>
		</Logger>
		<Logger name="snaq.db.ConnectionPool.pool-local" level="DEBUG">
			<AppenderRef ref="LOCAL"/>
			<AppenderRef ref="CONSOLE"/>
		</Logger>
	</Loggers>
</Configuration>

Logback

Here is a sample example configuration file (logback.xml) for the Logback library:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Example Logback configuration file. -->
<configuration>

  <appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender">
    <target>System.out</target>
    <encoder>
      <pattern>%d{HH:mm:ss.SSS} [%-5p]: %m%n</pattern>
    </encoder>
  </appender>
  <appender name="POOLMANAGER" class="ch.qos.logback.core.FileAppender">
    <file>DBPool-managers.log</file>
    <encoder>
      <pattern>%d{HH:mm:ss.SSS} [%-5p]: %m%n</pattern>
    </encoder>
  </appender>
  <appender name="POOLS" class="ch.qos.logback.core.FileAppender">
    <file>DBPool-pools.log</file>
    <encoder>
      <pattern>%d{HH:mm:ss.SSS} [%-5p]: %m%n</pattern>
    </encoder>
  </appender>
  <appender name="LOCAL" class="ch.qos.logback.core.FileAppender">
    <file>DBPool-local.log</file>
    <encoder>
      <pattern>%d{HH:mm:ss.SSS} [%-5p]: %m%n</pattern>
    </encoder>
  </appender>

  <root level="OFF" />

  <logger name="snaq.db.ConnectionPoolManager" level="INFO">
    <appender-ref ref="POOLMANAGER" />
  </logger>
  <logger name="snaq.db.ConnectionPool" level="INFO">
    <appender-ref ref="POOLS" />
    <appender-ref ref="CONSOLE" />
  </logger>
  <logger name="snaq.db.ConnectionPool.pool-local" level="DEBUG">
    <appender-ref ref="LOCAL" />
    <appender-ref ref="CONSOLE" />
  </logger>

</configuration>

Java Logging API

Log level comparison.
Log4j/LogbackJava Logging
fatalSEVERE
errorSEVERE
warnWARNING
infoINFO
 CONFIG
debugFINE
 FINER
traceFINEST

The log levels in Java Logging API are different to other standard logging libraries, although can be compared using the adjacent table. Be careful to ensure you are using the expected log levels; in particular bear in mind that the standard trace level of SLF4J equates to FINEST in JUL, (not FINE).

A convenient single-line Formatter implementation has been packaged within the distribution to make log output more easily readable: snaq.util.logging.TerseFormatter. The default JUL java.util.logging.SimpleFormatter produces two-lines per entry, which can be overly verbose. 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).

Here is a sample example configuration file (logging.properties) for the Java Logging API:

# 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 ("INFO" level, to file only).
snaq.db.ConnectionPoolManager.level=INFO
snaq.db.ConnectionPoolManager.handlers=java.util.logging.FileHandler
# Enable parent connection pool logging ("INFO" level, to console/file).
snaq.db.ConnectionPool.handlers=java.util.logging.ConsoleHandler, java.util.logging.FileHandler
snaq.db.ConnectionPool.level=INFO
# Enable instance connection pool logging ("FINE" level, to console/file).
snaq.db.ConnectionPool.pool-local.handlers=java.util.logging.ConsoleHandler, java.util.logging.FileHandler
snaq.db.ConnectionPool.pool-local.level=FINE

Good JDBC programming practices

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

The following code shows the principal, albeit very simplistically:

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

Following this principal reduces the risk of resource retention problems ("memory leaks") and unexpected pooling behaviour. It seems obvious, yet many bugs/problems/headaches can be attributed to not following these two simple steps. This is made much simpler using the try-with-resources language feature, which allows you to write the above code as the following instead (for example):

try (Connection con = pool.getConnection(); Statement st = con.createStatement())
{
    // The following line could also be included above,
    // but more commonly would be in a separate try-block.
    try (ResultSet res = st.executeQuery("…"))
    {
        // whatever
    }
    catch (SQLException sqlx)
    {
        // whatever
    }
}
catch (SQLException ex)
{
    // whatever
}

Another recommended practice is to use a Type-4 JDBC driver for your database if possible. Type-4 drivers are ones written in pure Java (with no native code), which means all driver resources are managed by the Java Virtual Machine. The choice of driver obviously depends on what is available for your database, but the choice of driver type will also depend on performance considerations, cost, etc.

Choosing pooling parameters

Deciding pooling parameter values to use to get the best performance is a difficult task. Choices depend on many factors: the application (and its code efficiency), the database drivers, the database server, the server 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 to find useful parameter values. Remember that you can increase logging detail, which can help understand pooling behaviour, and is highly recommended during development. Be aware that increased logging detail is detrimental to overall performance, so remember to reduce it again for production releases.

General questions to think about:

If your application only occasionally requires access to a database then you may not need connection pooling. However, it might still be useful to provide a convenient mechanism for database access. Also, 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:

Issues that affect the likely value for maxsize:

Issues that affect the likely value for idleTimeout:

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 section on Advanced features.


Advanced features

Connection validation

Each time a connection is requested from a pool it is validated before being handed out. By default this check is done using the isClosed() method. Although this method is relatively quick to return, it's not reliable as it only returns whether the close() method has been called (the connection may have become invalid another way). You can override this default by using a custom validation class: 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:

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.

Security Note: Care should be taken when using snaq.db.SimpleQueryValidator to prevent unauthorised access (to DataSource creation or properties file). If not secured appropriately this potentially exposes a mechanism for a SQL-injection attack on the database.

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.

Note: if using a web container, you are recommended not to use the shutdown hook, but instead use the ServletContextListener as appropriate (see DataSource section), which provides a more reliable mechanism for ensuring resources are released.

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 either of the releaseImmediately() or releaseAfter(timeout) methods. 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 (unless the releaseTimeout property has been specified), 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 to 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 interface, and can register/deregister their interest by using the provided methods, for example:

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 pool listener 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

Statement caching & concurrency/holdability

When a Statement is created it is assigned options for ResultSet scrollability, concurrency, and holdability, such that all ResultSets created via that Statement inherit those settings. DBPool supports caching of statements, and has logic to ensure the options requested are discovered from the cached statements if possible. Bear in mind that this could affect statement caching performance, and if it provides no performance benefit it can be disabled via the respective property (pool.cache) or method (setCaching(…)).

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 version.

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 the one it was compiled for. The solution is to either upgrade your Java Platform, or use a compatible version of DBPool.

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

This exception is thrown 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 web containers the configuration of CLASSPATH is not obvious. Due to the need for both resource separation and security policies they often use separate classloaders which only permit loading of classes and resources from specific locations. Unsurprisingly this is vendor-specific, and the best way to solve the problem is to thoroughly read the documentation for the web container. 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 the filename string starts with a "/" character to ensure it is accessed directly. The default properties file is therefore accessed using the call:

Class.getResourceAsStream("/dbpool.properties")

Remember 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.


Release notes

In most cases upgrading from version 6.x should be a drop-in replacement without issues. Users of the few deprecated methods should make source changes when possible, and web container users should configure the new DBPoolServletContextListener for more graceful pool shutdown. Users who have made ConnectionPoolListener or ObjectPoolListener implementations may need to update their code, as the event typing mechanism has been updated to use enum types instead of integers.


Change log

2017-10-15
(v7.0.2)
  • Added automatic module name for improved Java 9+ support.
2016-09-21
(v7.0.1)
  • Bug fix for issue with DBPoolDataSource.setMinPool method (thanks to Wang Wei).
2015-02-07
(v7.0)
  • Updated for Java 8 / JDBC 4.2.
  • Improved pool shutdown when used in web containers.
  • Updated events to use enum types instead of integers.
  • Improved release logic, including support for forced release after timeout.
  • Fixed erroneous catching of exception in pool manager retrieval method.
  • Renamed PreparedSstatement.toString method to allow easy use of vendor implementations.
  • Fixed issue of SQLException not being thrown in some cases with bad password.