c3p0 - JDBC3 Connection and Statement Pooling

version @c3p0.version@

c3p0 was designed to be butt-simple to use. Just put the jar file [lib/c3p0-@c3p0.version@.jar] in your application's effective CLASSPATH, and make a DataSource like this:

[Optional] If you want to turn on PreparedStatement pooling, you must also set maxStatements and/or maxStatementsPerConnection(both default 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-@c3p0.version@.jar 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.)

Instantiating and Configuring a ComboPooledDataSource

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.

Using the DataSources factory class

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:

Querying a PooledDataSource's current status

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 three overloaded forms, such as:

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

c3p0 maintains separate pools for Connections with distinct authentications. The various methods let you query the status of pools individually, or aggregate statistics for all authentifications for which your DataSource is maintaining pools. Note that pool configuration parmeters such as maxPoolSize are enforced on a per-authentification basis! For example, if you have set maxPoolSize to 20, and if the DataSource is managing connections under two username-password pairs [the default, and one other pair established via a call to getConnection(user, password), you should expect to see as many as 40 Connections from getNumConnectionsAllUsers().

Most applications only acquire default-authenticated Connections from DataSources, and can typically just use the getXXXDefaultUser() to gather Connection statistics.

Cleaning up after c3p0 PooledDataSources

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.

Advanced: Building your own PoolBackedDataSource

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.

Advanced: Raw Connection and Statement Operations

JDBC drivers sometimes define vendor-specific, non-standard API on Connection and Statement implementations. C3P0 wraps these Objects behind a proxies, so you cannot cast C3P0-returned Connections or Statements to the vendor-specific implementation classes. C3P0 does not provide any means of accessing the raw Connections and Statements directly, because C3P0 needs to keep track of Statements and ResultSets created in order to prevent resource leaks and pool corruption.

C3P0 does provide an API that allows you to invoke non-standard methods reflectively on an underlying Connection. To use it, first cast the returned Connection to a C3P0ProxyConnection. Then call the method rawConnectionOperation, supplying the java.lang.reflect.Method object for the non-standard method you wish to call as an argument. The Method you supply will be invoked on the target you provide on the second argument (null for static methods), and using the arguments you supply in the third argument to that function. For the target, and for any of the method arguments, you can supply the special token C3P0ProxyConnection.RAW_CONNECTION, which will be replaced with the underlying vendor-specific Connection object before the Method is invoked.

C3P0ProxyStatement offers an exactly analogous API.

Any Statements (including Prepared and CallableStatements) and ResultSets returned by raw operations will be c3p0-managed, and will be properly cleaned-up on close() of the parent proxy Connection. Users must take care to clean up any non-standard resources returned by a vendor-specific method.

Here's an example of using Oracle-specific API to call a static method on a raw Connection:

Note: C3P0 now includes special support for some Oracle-specific methods. See Appendix C.

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.

Overriding c3p0 defaults via c3p0.properties

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:

Programmatic configuration of DataSource instances

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.

Basic Pool Configuration

c3p0 Connection pools are very easy to configure via the following basic parameters:

• acquireIncrement
• initialPoolSize
• maxPoolSize
• maxIdleTime
• minPoolSize

initialPoolSize, minPoolSize, maxPoolSize define the number of Connections that will be pooled. Please ensure that minPoolSize <= maxPoolSize. Unreasonable values of initialPoolSize will be ignored, and minPoolSize will be used instead.

Within the range between minPoolSize and maxPoolSize, the number of Connections in a pool varies according to usage patterns. The number of Connections increases whenever a Connection is requested by a user, no Connections are available, and the pool has not yet reached maxPoolSize in the number of Connections managed. Since Connection acquisition is very slow, it is almost always useful to increase the number of Connections eagerly, in batches, rather than forcing each client to wait for a new Connection to provoke a single acquisition when the load is increasing. acquireIncrement determines how many Connections a c3p0 pool will attempt to acquire when the pool has run out of Connections. (Regardless of acquireIncrement, the pool will never allow maxPoolSize to be exceeded.)

The number of Connections in a pool decreases whenever a pool tests a Connection and finds it to be broken (see Configuring Connection Testing below, or when a Connection is expired by the pool after sitting idle for a period of time. By default, pools will never expire idle Connections. If you wish idle Connections to be expired over time, set maxIdleTime.

Configuring Connection Testing

c3p0 can be configured to test the Connections that it pools in a variety of ways, to minimize the likelihood that your application will see a broken or "stale" Connection. Pooled Connections can go bad for a variety of reasons -- some JDBC drivers intentionally "time-out" long-lasting database Connections; back-end databases or networks sometimes go down "stranding" pooled Connections; and Connections can simply become corrupted over time and use due to resource leaks, driver bugs, or other causes.

c3p0 provides users a great deal of flexibility in testing Connections, via the following configuration parameters:

• automaticTestTable
• connectionTesterClassName
• idleConnectionTestPeriod
• preferredTestQuery
• testConnectionOnCheckin
• testConnectionOnCheckout

idleConnectionTestPeriod, testConnectionOnCheckout, and testConnectionOnCheckin control when Connections will be tested. automaticTestTable, connectionTesterClassName, and preferredTestQuery control how.

When configuring Connection testing, first try to minimize the cost of each test. By default, Connections are tested by calling the getTables() method on a Connection's associated DatabaseMetaData object. This has the advantage of working with any database, and is independent of database schema. However, empirically a DatabaseMetaData.getTables() call is often much slower than a simple database query.

The most convenient way to speed up Connection testing is to define the parameter automaticTestTable. Using the name you provide, c3p0 will create an empty table, and make a simple query against it to test the database. Alternatively, if your database schema is fixed prior to your application's use of the database, you can simply define a test query with the preferredTestQuery parameter. Be careful, however. Setting preferredTestQuery will lead to errors as Connection tests fail if the query target table does not exist in your database table prior to initialization of your DataSource. (Advanced users may define any kind of Connection testing they wish, by implementing the interface ConnectionTester or QueryConnectionTester, and supplying the fully qualified name of the class as connectionTesterClassName.)

The most reliable time to test Connections is on check-out. But this is also the most costly choice from a performance perspective. Most applications should work quite reliably using a combination of idleConnectionTestPeriod and testConnectionsOnCheckIn. Both the idle test and the check-in test are performed asynchronously, which leads to better performance, both perceived and actual.

Note that for many applications, high performance is more important than the risk of an occasional database exception. In its default configuration, c3p0 does no Connection testing at all. Setting a fairly long idleConnectionTestPeriod, and not testing on checkout and check-in at all is an excellent, high-performance approach.

Configuring Statement Pooling

c3p0 implements transparent PreparedStatement pooling as defined by the JDBC spec. Under some circumstances, statement pooling can dramatically improve application performance. Under other circumstances, the overhead of statement pooling can slightly harm performance. Whether and how much statement pooling will help depends on how much parsing, planning, and optimizing of queries your databases does when the statements are prepared. Databases (and JDBC drivers) vary widely in this respect. It's a good idea to benchmark your application with and without statement pooling to see if and how much it helps.

You configure statement pooling in c3p0 via the following configuration parameters:

• maxStatements
• maxStatementsPerConnection

maxStatements is JDBC's standard parameter for controlling statement pooling. maxStatements defines the total number PreparedStatements a DataSource will cache. The pool will destroy the least-recently-used PreparedStatement when it hits this limit. This sounds simple, but it's actually a strange approach, because cached statements conceptually belong to individual Connections; they are not global resources. To figure out a size for maxStatements that does not "churn" cached statements, you need to consider the number of frequently used PreparedStatements in your application, and multiply that by the number of Connections you expect in the pool (maxPoolSize in a busy application).

maxStatementsPerConnection is a non-standard configuration parameter that makes a bit more sense conceptually. It defines how many statements each pooled Connection is allowed to own. You can set this to a bit more than the number of PreparedStatements your application frequently uses, to avoid churning.

If either of these parameters are greater than zero, statement pooling will be enabled. If both parameters are greater than zero, both limits will be enforced. If only one is greater than zero, statement pooling will be enabled, but only one limit will be enforced.

Configuring Recovery From Database Outages

c3p0 DataSources are designed (and configured by default) to recover from temporary database outages, such as those which occur during a database restart or brief loss of network connectivity. You can affect how c3p0 handles errors in acquiring Connections via the following configurable properties:

• acquireRetryAttempts
• acquireRetryDelay
• breakAfterAcquireFailure

When a c3p0 DataSource attempts and fails to acquire a Connection, it will retry up to acquireRetryAttempts times, with a delay of acquireRetryDelay between each attempt. If all attempts fail, any clients waiting for Connections from the DataSource will see an Exception, indicating that a Connection could not be acquired. Note that clients do not see any Exception until a full round of attempts fail, which may be some time after the initial Connection attempt. If acquireRetryAttempts is set to 0, c3p0 will attempt to acquire new Connections indefinitely, and calls to getConnection() may block indefinitely waiting for a successful acquisition.

Once a full round of acquisition attempts fails, there are two possible policies. By default, the c3p0 DataSource will remain active, and will try again to acquire Connections in response to future requests for Connections. If you set breakAfterAcquireFailure to true, the DataSource will consider itself broken after a failed round of Connection attempts, and future client requests will fail immediately.

Note that if a database restart occurs, a pool may contain previously acquired but now stale Connections. By default, these stale Connections will only be detected and purged lazily, when an application attempts to use them, and sees an Exception. If you wish to avoid application Exceptions, you must adopt a connection testing strategy that is likely to detect stale Connections prior to their delivery to clients. (See "Configuring Connection Testing".) Even with active Connection testing (testConnectionsOnCheckout set to true, or testConnectionsOnCheckin and a short idleConnectionTestPeriod), your application may see occasional Exceptions on database restart, for example if the restart occurs after a Connection to the database has already been checked out.

Configuring Unresolved Transaction Handling

Connections checked into a pool cannot have any unresolved transactional work associated with them. If users have set autoCommit to false on a Connection, and c3p0 cannot guarantee that there is no pending transactional work, c3p0 must either rollback() or commit() on check-in (when a user calls close()). The JDBC spec is (unforgivably) silent on the question of whether unresolved work should be committed or rolled back on Connection close. By default, c3p0 rolls back unresolved transactional work when a user calls close().

You can adjust this behavior via the following configuration properties:

• autoCommitOnClose
• forceIgnoreUnresolvedTransactions

If you wish c3p0 to allow unresolved transactional work to commit on checkin, set autoCommitOnClose to true. If you wish c3p0 to leave transaction management to you, and neither commit nor rollback (nor modify the state of Connection autoCommit), you may set forceIgnoreUnresolvedTransactions to true. Setting forceIgnoreUnresolvedTransactions is strongly discouraged, because if clients are not careful to commit or rollback themselves prior to close(), or do not set Connection autoCommit consistently, bizarre unreproduceable behavior and database lockups can occur.

Other DataSource Configuration

See Appendix A for information about the following configuration properties:

• checkoutTimeout
• factoryClassLocation
• numHelperThreads
• usesTraditionalReflectiveProxies

Configuring Logging

c3p0 uses a custom logging library similar to jakarta commons-logging. Log messages can be directed to the popular log4j logging library, to the standard logging facility introduced with jdk1.4, or to System.err. Nearly all configuration should be done at the level of your preferred logging library. There are a very few configuration options specific to c3p0's logging, and usually the defaults will be fine. Logging-related parameters may be placed in your c3p0.properties file, in a file called mchange-log.properties at the top-level of your classpath, or they may be defined as System properties. See the box below.

c3p0's logging behavior is affected by certain build-time options. If build-option c3p0.debug is set to false, all messages at a logging level below INFO will be suppressed. Build-option c3p0.trace controls how fine-grained c3p0's below INFO level reporting will be. For the moment, distributed c3p0 binaries are compiled with debug set to true and trace set to its maximum level of 10. But binaries may eventually be distributed with debug set to false. (For the moment, the performance impact of the logging level-checks seems very small, and it's most flexible to compile in all the messages, and let your logging library control which are emitted.) When c3p0 starts up, it emits the build-time values of debug and trace, along with the version and build time.

com.mchange.v2.log.MLog

Determines which library c3p0 will output log messages to. By default, if log4j is available, it will use that library, otherwise if jdk1.4 logging apis are available it will use those, and if neither are available, it will use a simple fallback that logs to System.err. If you want to directly control which library is used, you may set this property to one of:

• com.mchange.v2.log.log4j.Log4jMLog
• com.mchange.v2.log.jdk14logging.Jdk14MLog
• com.mchange.v2.log.FallbackMLog

You may also set this property to a comma separated list of the above alternatives, to define an order of preference among logging libraries.

com.mchange.v2.log.NameTransformer

By default, c3p0 uses very fine-grained logging, in general with one logger for each c3p0 class. For a variety of reasons, some users may prefer fewer, more global loggers. You may opt for one-logger-per-package by setting com.mchange.v2.log.NameTransformer to the value com.mchange.v2.log.PackageNames. Advanced users can also define other strategies for organizing the number and names of loggers by setting this variable to the fully-qualified class name of a custom implementation of the com.mchange.v2.log.NameTransformer interface.

com.mchange.v2.log.FallbackMLog.DEFAULT_CUTOFF_LEVEL

If, whether by choice or by necessity, you are using c3p0's System.err fallback logger, you can use this parameter to control how detailed c3p0's logging should be. Any of the following values (taken from the jdk1.4 logging library) are acceptable:

• OFF
• SEVERE
• WARNING
• INFO
• CONFIG
• FINE
• FINER
• FINEST
• ALL

This property defaults to INFO.

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 access. 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 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 setting the configuration property maxStatements or maxStatementsPerConnection to a value greater than zero.).

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!!!

acquireIncrement

Default: 3

Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted. [See "Basic Pool Configuration"]

acquireRetryAttempts

Default: 30

Defines how many times c3p0 will try to acquire a new Connection from the database before giving up. If this value is less than or equal to zero, c3p0 will keep trying to fetch a Connection indefinitely. [See "Configuring Recovery From Database Outages"]

acquireRetryDelay

Default: 1000

Milliseconds, time c3p0 will wait between acquire attempts. [See "Configuring Recovery From Database Outages"]

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.] [See "Configuring Unresolved Transaction Handling"]

automaticTestTable

Default: null

If provided, c3p0 will create an empty table of the specified name, and use queries against that table to test the Connection. If automaticTestTable is provided, c3p0 will generate its own test query, therefore any preferredTestQuery set will be ignored. You should not work with the named table after c3p0 creates it; it should be strictly for c3p0's use in testing your Connection. (If you define your own ConnectionTester, it must implement the QueryConnectionTester interface for this parameter to be useful.) [See "Configuring Connection Testing"]

breakAfterAcquireFailure

Default: false

If true, a pooled DataSource will declare itself broken and be permanently closed if a Connection cannot be obtained from the database after making acquireRetryAttempts to acquire one. If false, failure to obtain a Connection will cause all Threads waiting for the pool to acquire a Connection to throw an Exception, but the DataSource will remain valid, and will attempt to acquire again following a call to getConnection(). [See "Configuring Recovery From Database Outages"]

checkoutTimeout

Default: 0

The number of milliseconds a client calling getConnection() will wait for a Connection to be checked-in or acquired when the pool is exhausted. Zero means wait indefinitely. Setting any positive value will cause the getConnection() call to time-out and break with an SQLException after the specified number of milliseconds.

connectionTesterClassName

Default: com.mchange.v2.c3p0.impl.DefaultConnectionTester

The fully qualified class-name of an implememtation of the ConnectionTester interface, or QueryConnectionTester if you would like instances to have access to a user-configured preferredTestQuery. This can be used to customize how c3p0 DataSources test Connections, but with the introduction of automaticTestTable and preferredTestQuery configuration parameters, "rolling your own" should be overkill for most users. [See "Configuring Connection Testing"

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.

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. [See "Configuring Unresolved Transaction Handling"]

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. [See "Configuring Connection Testing"]

initialPoolSize

Default: 3

Number of Connections a pool will try to acquire upon startup. Should be between minPoolSize and maxPoolSize. [See "Basic Pool Configuration"]

maxIdleTime

Default: 0

Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire. [See "Basic Pool Configuration"]

maxPoolSize

Default: 15

Maximum number of Connections a pool will maintain at any given time. [See "Basic Pool Configuration"]

maxStatements

Default: 0

The size of c3p0's global PreparedStatement cache. If both maxStatements and maxStatementsPerConnection are zero, statement caching will not be enabled. If maxStatements is zero but maxStatementsPerConnection is a non-zero value, statement caching will be enabled, but no global limit will be enforced, only the per-connection maximum. maxStatements controls the total number of Statements cached, for all Connections. If set, it should be a fairly large number, as each pooled Connection requires its own, distinct flock of cached statements. As a guide, consider how many distinct PreparedStatements are used frequently in your application, and multiply that number by maxPoolSize to arrive at an appropriate value. Though maxStatements is the JDBC standard parameter for controlling statement caching, users may find c3p0's alternative maxStatementsPerConnection more intuitive to use. [See "Configuring Statement Pooling"]

maxStatementsPerConnection

Default: 0

The number of PreparedStatements c3p0 will cache for a single pooled Connection. If both maxStatements and maxStatementsPerConnection are zero, statement caching will not be enabled. If maxStatementsPerConnection is zero but maxStatements is a non-zero value, statement caching will be enabled, and a global limit enforced, but otherwise no limit will be set on the number of cached statements for a single Connection. If set, maxStatementsPerConnection should be set to about the number distinct PreparedStatements that are used frequently in your application, plus two or three extra so infrequently statements don't force the more common cached statements to be culled. Though maxStatements is the JDBC standard parameter for controlling statement caching, users may find maxStatementsPerConnection more intuitive to use. [See "Configuring Statement Pooling"]

minPoolSize

Default: 3

Minimum number of Connections a pool will maintain at any given time. [See "Basic Pool Configuration"]

numHelperThreads

Default: 3

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.

preferredTestQuery

Default: null

Defines the query that will be executed for all connection tests, if the default ConnectionTester (or some other implementation of QueryConnectionTester) is being used. Defining a preferredTestQuery that will execute quickly in your database may dramatically speed up Connection tests. (If no preferredTestQuery is set, the default ConnectionTester executes a getTables() call on the Connection's DatabaseMetaData. Depending on your database, this may execute more slowly than a "normal" database query.) NOTE: The table against which your preferredTestQuery will be run must exist in the database schema prior to your initialization of your DataSource. If your application defines its own schema, try automaticTestTable instead. [See "Configuring Connection Testing"]

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.

testConnectionOnCheckin

Default: false

If true, an operation will be performed asynchronously at every connection checkin to verify that the connection is valid. Use in combination with idleConnectionTestPeriod for quite reliable, always asynchronous Connection testing. Also, setting an automaticTestTable or preferredTestQuery will usually speed up all connection tests. [See "Configuring Connection Testing"]

testConnectionOnCheckout

Default: false

Use only if necessary. 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 idleConnectionTestPeriod. Also, setting an automaticTestTable or preferredTestQuery will usually speed up all connection tests. [See "Configuring Connection Testing"]

usesTraditionalReflectiveProxies

Default: false

c3p0 originally used reflective dynamic proxies for implementations of Connections and other JDBC interfaces. As of c3p0-0.8.5, non-reflective, code-generated implementations are used instead. As this was a major change, and the old codebase had been extensively used and tested, this parameter was added to allow users to revert of they had problems. The new, non-reflexive implementation is faster, and has now been widely deployed and tested, so it is unlikely that this parameter will be useful. Both the old reflective and newer non-reflective codebases are being maintained, but support for the older codebase may (or may not) be dropped in the future.

You can easily configure Apache's Tomcat web application server to use c3p0 pooled DataSources. Below is a Tomcat 5.0 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.

For Tomcat 5.5, try something like the following (thanks to Carl F. Hall for the sample!):

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!

The Oracle thin JDBC driver provides a non-standard API for creating temporary BLOBs and CLOBs that requires users to call methods on the raw, Oracle-specific Connection implementation. Advanced users might use the raw connection operations described above to access this functionality, but a convenience class is available in a separate jar file (c3p0-oracle-thin-extras-@c3p0.version@.jar) for easier access to this functionality. Please see the API docs for com.mchange.v2.c3p0.dbms.OracleUtils for details.

Hibernate's C3P0ConnectionProvider explicitly sets 7 c3p0 configuration properties, based on your hibernate configuration, overriding any configuration you may have set in a c3p0.properties file. If you are using Hibernate's C3P0ConnectionProvider you must set the following properties in your hibernate configuration, using hibernate-specific configuration keys. All other properties must be defined as usual in a c3p0.properties file. This is confusing, and will hopefully be simplified some time in the future, but for now...

The following properties must be set in your hibernate configuration:

Remember -- these, and only these, properties must be defined in your hibernate configuration, or else they will be set to hibernate-specified defaults. All other configuration properties that you wish to set should be defined in a c3p0.properties file. (See "Overriding c3p0 defaults via c3p0.properties".)

To use c3p0 with JBoss:

1. Place c3p0's jar file in the lib directory of your jboss server instance (e.g. ${JBOSS_HOME}/server/default/lib)
2. Modify the file below, and save it as c3p0-service.xml in the deploy directory of your jboss server (e.g. ${JBOSS_HOME}/server/default/deploy). Note that parameters must be capitalized in this file, but otherwise they are defined as described above.