c3p0 - JDBC3 Connection and Statement Pooling

c3p0 was designed to be butt-simple to use. Just put the jar file [lib/c3p0-0.8.x.jar] in your applications effective CLASSPATH, and make a DataSource like this:

[Optional] If you want to turn on PreparedStatement pooling, you must also set maxStatements (which defaults to 0):

Do whatever you want with your DataSource, which will be backed by a Connection pool set up with default parameters. You can bind the DataSource to a JNDI name service, or use it directly, as you prefer.

When you are done, you can clean up the DataSource you've created like this:

That's it! The rest is detail.

c3p0 is an easy-to-use library for making traditional JDBC drivers "enterprise-ready" by augmenting them with functionality defined by the jdbc3 spec and the optional extensions to jdbc2. In particular, c3p0 provides several useful services:

• Classes which adapt traditional DriverManager-based JDBC drivers to the new javax.sql.DataSource scheme for acquiring database Connections.
• Transparent pooling of Connection and PreparedStatements behind DataSources which can "wrap" around traditional drivers or arbitrary unpooled DataSources.

The library tries hard to get the details right:

• c3p0 DataSources are both Referenceable and Serializable, and are thus suitable for binding to a wide-variety of JNDI-based naming services.
• Statement and ResultSets are carefully cleaned up when pooled Connections and Statements are checked in, to prevent resource- exhaustion when clients use the lazy but common resource-management strategy of only cleaning up their Connections....
• The library adopts the approach and uses the internal interfaces defined by the JDBC 2 and 3 specification (even where these conflict with the library author's preferences). DataSources are written in the JavaBean style, offering all the required and most of the optional properties (as well as some non-standard ones), and no-arg constructors.

c3p0 hopes to provide DataSource implementations more than suitable for use by high-volume "J2EE enterprise applications". Please provide feedback, bug-fixes, etc.!

c3p0 requires a level 1.3.x or above Java Runtime Environment, and the JDBC 2.x or above javax.sql libraries. c3p0 works fine under Java 1.4.x.

Put the file lib/c3p0-0.8.x.jar required libraries somewhere in your CLASSPATH (or any other place where your application's classloader will find it). That's it!

From a users' perspective, c3p0 simply provides standard jdbc2 DataSource objects. When creating these DataSources, users can control pooling-related, naming-related, and other properties (See Appendix A). All pooling is entirely transparent to users once a DataSource has been created.

There are three ways of acquiring c3p0 pool-backed DataSources: 1) directly instantiate and configure a ComboPooledDataSource bean; 2) use the DataSources factory class; or 3) "build your own" pool-backed DataSource by directly instantiating PoolBackedDataSource and setting its ConectionPoolDataSource. Most users will probably find instantiating ComboPooledDataSource to be the most convenient approach. Once instantiated, c3p0 DataSources can be bound to nearly any JNDI-compliant name service.

Regardless of how you create your DataSource, c3p0 will use defaults for any configuration parameters that you do not specify programmatically. c3p0 has built-in, hard-coded defaults, but you can override these by creating a file called c3p0.properties and storing it as a top-level resource in the same CLASSPATH / classloader that loads c3p0's jar file. (See Configuration below.)

Perhaps the most straightforward way to create a c3p0 pooling DataSource is to instantiate an instance of com.mchange.v2.c3p0.ComboPooledDataSource. This is a JavaBean-style class with a public, no-arg constructor, but before you use the DataSource, you'll have to be sure to set at least the property jdbcUrl. You may also want to set user and password, and if you have not externally preloaded the old-style JDBC driver you'll use you should set the driverClass.

Alternatively, you can use the static factory class com.mchange.v2.c3p0.DataSources to build unpooled DataSources from traditional JDBC drivers, and to build pooled DataSources from unpooled DataSources:

If you use the DataSources factory class, and you want to programmatically override default configuration parameters, make use of the PoolConfig class:

c3p0 DataSources backed by a pool, which include implementations of ComboPooledDataSource and the objects returned by DataSources.pooledDataSource( ... ), all implement the interface com.mchange.v2.c3p0.PooledDataSource, which makes available a number of methods for querying the status of DataSource Connection pools. Below is sample code that queries a DataSource for its status:

The status querying methods all come in two overloaded forms, such as:

• public int getNumConnections()
• public int getNumConnections(String username, String password)

The version with arguments is necessary in case clients have retrieved Connections from the DataSource using an explicit username and password [i.e. by calling myDataSource.getConnection("myname", "mypassword")] rather than using the DataSource's default authentication, which is defined by the JavaBean properties user and password, and used in calls to myDataSource.getConnection() with no arguments. c3p0 maintains separate pools for Connections with distinct authentications, and you can query the status of those pools individually.

Most users acquire default-authenticated Connections from DataSources, and and can thus rely on the default, no-arg versions of the status-querying methods.

The easy way to clean up after c3p0-created DataSources is to use the static destroy method defined by the class DataSources. Only PooledDataSources need to be cleaned up, but DataSources.destroy( ... ) does no harm if it is called on an unpooled or non-c3p0 DataSource.

Alternatively, c3p0's PooledDataSource interface contains a close() method that you can call when you know you are finished with a DataSource. So, you can cast a c3p0 derived DataSource to a PooledDataSource and close it:

Regardless of which method you use, closing or destroying a PooledDataSource does not necessarily shut down the underlying pools. It is possible for several DataSource instances to share the same Connection and Statement pools. Calling close() or destroy() decrements a reference count, and the pools are actually shut down only when the reference count goes to zero. If you are careful to close() all your DataSource instances, you can reliably clean up all of c3p0's pooled Connections and helper threads.

Unreferenced instances of PooledDataSource that are not close()ed by clients close() themselves prior to garbage collection in their finalize() methods. As always, finalization should be considered a backstop and not a prompt or sure approach to resource cleanup.

There is little reason for most programmers to do this, but you can build a pooling DataSource in a step-by-step way by instantiating and configuring an unpooled DriverManagerDataSource, instantiating a WrapperConnectionPoolDataSource and setting the unpooled DataSource as its nestedDataSource property, and then using that to set the connectionPoolDataSource property of a new PoolBackedDataSource.

This sequence of events is primarily interesting if your driver offers an unpooled implementation of DataSource, and you'd like c3p0 to use that. Rather than using c3p0's DriverManagerDataSource implementation, you can substitute your vendor-supplied DataSource as the nestedDataSource for a WrapperConnectionPoolDataSource.

Configuration

While c3p0 does not require very much configuration, it is very tweakable. All the interesting knobs and dials are represented as JavaBean properties. Following JavaBean conventions, we note that if an Object has a property of type T called foo, it will have methods that look like...

public T getFoo();
public void setFoo(T foo);

The tweakable properties, along with their definitions and default values, are described in Appendix A below.

There are two ways to modify c3p0 properties: You can override the defaults for your entire application, or you can programmatically alter the values associated with a particular DataSource.

To override the library's built-in defaults, create a file called c3p0.properties and place it at the "root" of your classpath or classloader. For a typical standalone application, that means place the file in a directory named in your CLASSPATH environment variable. For a typical web-application, the file should be placed in WEB-INF/classes. In general, the file must be available as a classloader resource under the name /c3p0.properties, in the classloader that loaded c3p0's jar file. Review the API docs (especilly getResource... methods) of java.lang.Class, java.lang.ClassLoader, and java.util.ResourceBundle if this is unfamiliar.

The format of c3p0.properties should be a normal Java Properties file format, whose keys are c3p0 configurable properties. See Appendix A. for the specifics. An example c3p0.properties file is produced below:

DataSources are usually configured before they are used, either during or immediately following their construction. c3p0 does support property modifications midstream, however.

If you obtain a DataSource by instantiating a ComboPooledDataSource, configure it by simply calling appropriate setter methods offered by that class before attempting a call to getConnection(). See the example above.

If you obtain a DataSource by using factory methods of the utility class com.mchange.v2.c3p0.DataSources, and wish to use a non-default configuration, you should first create a PoolConfig Object, call the appropriate property setters on that PoolConfig, and pass your configuration as an argument to DataSources.pooledDataSource( ... ). See the example above.

Enhanced performance is the purpose of Connection and Statement pooling, and a major goal of the c3p0 library. For most applications, Connection pooling will provide a significant performance gain, especially if you are acquiring an unpooled Connection for each client Thread. If you are letting a single, shared Connection serve many clients to avoid Connection acquisition overhead, you may suffer performance issues and problems managing transactions when your Connection is under concurrent load; Connection pooling will enable you to switch to a one Connection-per-client-thread model with little or no cost. If you are writing Enterprise Java Beans, you may be tempted to acquire a Connection once and not return it until the bean is about to be destroyed or passivated. But this can be resource-costly, as dormant pooled beans needlessly hold the Connection's network and database resources. Connection pooling permits beans to only "own" a Connection while they are using it.

But, there are performance costs to c3p0 as well. In order to implement automatic cleanup of unclosed ResultSets and Statements when parent resources are returned to pools, all client-visible Connections, ResultSets, Statements are really wrappers around objects provided by an underlying unpooled DataSource or "traditional" JDBC driver. Thus, there is some extra overhead to all JDBC calls.

Some attention has been paid to minimizing the "wrapper" overhead of c3p0. In my environment, the wrapper overhead amounts from several hundreths to several thousandths of the cost of Connection acquisition, so unless you are making many, many JDBC calls in fast succession, there will be a net gain in performance and resource-utilization efficiency. Significantly, the overhead associated with ResultSet operations (where one might iterate through a table with thousands of records) appears to be negligibly small.

• Connections and Statements are pooled on a per-authentication basis. So, if one pool-backed DataSource is used to acquire Connections both for [user=alice, password=secret1] and [user=bob, password=secret2], there will be two distinct pools, and the DataSource might in the worst case manage twice the number of Connections specified by the maxPoolSize property.

• The overhead of Statement pooling is too high. For drivers that do not perform significant preprocessing of PreparedStatements, the pooling overhead outweighs any savings. Statement pooling is thus turned off by default. If your driver does preprocess PreparedStatements, especially if it does so via IPC with the RDBMS, you will probably see a significant performance gain by turning Statement pooling on. (Do this by defining a value for the configuration property maxStatements greater than zero.).

• The following advanced JDBC3 features are not yet supported when Statement Pooling is enabled:

  • ResultSet holdability
  • PreparedStatements returning autogenerated keys
  • PreparedStatements generatated with explicit rather than default column names
  • PreparedStatements generatated with explicit rather than default column indexes

  In other words, the following methods of Connection should not be used if Statement Pooling is enabled:

  • public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
  • public PreparedStatement prepareStatement(String sql, int[] columnIndexes)
  • public PreparedStatement prepareStatement(String sql, int rsType, int rsConcurrency, int rsHoldabaility)
  • public PreparedStatement prepareStatement(String sql, String[] columnNames)
  • public CallableStatement prepareCall(String sql, int rsType, int rsConcurrency, int rsHoldabaility)

  Most programmmers do not use these methods. They will be fully supported in the next version of c3p0. Note that variants that use resultSetType and resultSetConcurrency without resultSetHoldability are supported. All methods are supported when statement caching is not enabled (maxStatements is set to zero).

  To the author's knowledge, support of these methods for pooled statements is the last JDBC3 feature still unimplemented in c3p0. c3p0 now builds under JDK1.4.

Please provide any and all feedback to <swaldman@mchange.com>! Also, feel free to join and ask questions on the c3p0-users mailing list. Sign up at http://sourceforge.net/projects/c3p0/

Thank you for using c3p0!!!

initialPoolSize

Default: 3

minPoolSize

Default: 3

maxPoolSize

Default: 15

idleConnectionTestPeriod

Default: 0

If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.

maxIdleTime

Default: 0

Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.

maxStatements

Default: 0

The size of c3p0's PreparedStatement cache. Zero means statement caching is turned off.

propertyCycle

Default: 300

Maximum time in seconds before user configuration constraints are enforced. c3p0 enforces configuration constraints continually, and ignores this parameter. It is included for JDBC 3 completeness.

acquireIncrement

Default: 3

Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted.

testConnectionOnCheckout

Default: false

Don't use. Very expensive. If true, an operation will be performed at every connection checkout to verify that the connection is valid. Better choice: verify connections periodically using c3p0.idleConnectionTestPeriod

autoCommitOnClose

Default: false

The JDBC spec is unforgivably silent on what should happen to unresolved, pending transactions on Connection close. C3P0's default policy is to rollback any uncommitted, pending work. (I think this is absolutely, undeniably the right policy, but there is no consensus among JDBC driver vendors.) Setting autoCommitOnClose to true causes uncommitted pending work to be committed, rather than rolled back on Connection close. [Note: Since the spec is absurdly unclear on this question, application authors who wish to avoid bugs and inconsistent behavior should ensure that all transactions are explicitly either committed or rolled-back before close is called.]

forceIgnoreUnresolvedTransactions

Default: false

Strongly disrecommended. Setting this to true may lead to subtle and bizarre bugs. This is a terrible setting, leave it alone unless absolutely necessary. It is here to workaround broken databases / JDBC drivers that do not properly support transactions, but that allow Connections' autoCommit flags to go to false regardless. If you are using a database that supports transactions "partially" (this is oxymoronic, as the whole point of transactions is to perform operations reliably and completely, but nonetheless such databases are out there), if you feel comfortable ignoring the fact that Connections with autoCommit == false may be in the middle of transactions and may hold locks and other resources, you may turn off c3p0's wise default behavior, which is to protect itself, as well as the usability and consistency of the database, by either rolling back (default) or committing (see c3p0.autoCommitOnClose above) unresolved transactions. This should only be set to true when you are sure you are using a database that allows Connections' autoCommit flag to go to false, but offers no other meaningful support of transactions. Otherwise setting this to true is just a bad idea.

numHelperThreads

Default: false

c3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don't hold contended locks. Spreading these operations over multiple threads can significantly improve performance by allowing multiple operations to be performed simultaneously.

factoryClassLocation

Default: null

DataSources that will be bound by JNDI and use that API's Referenceable interface to store themselves may specify a URL from which the class capable of dereferencing a them may be loaded. If (as is usually the case) the c3p0 libraries will be locally available to the JNDI service, leave this set as null.

You can easily configure Apache's Tomcat web application server to use c3p0 pooled DataSources. Below is a sample config to get you started. It's a fragment of Tomcat's conf/server.xml file, which should be modified to suit and placed inside a <Context> element.

The rest is standard J2EE stuff: You'll need to declare your DataSource reference in your web.xml file:

And you can access your DataSource from code within your web application like this:

That's it!