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.
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:postgresql://localhost/testdb" );
cpds.setUser("swaldman");
cpds.setPassword("test-password");
// the settings below are optional -- c3p0 can work with defaults
cpds.setMinPoolSize(5);
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(20);
// The DataSource cpds is now a fully configured and usable pooled DataSource
...
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:
DataSource ds_unpooled = DataSources.unpooledDataSource("jdbc:postgresql://localhost/testdb",
"swaldman",
"test-password");
DataSource ds_pooled = DataSources.pooledDataSource( ds_unpooled );
// The DataSource ds_pooled is now a fully configured and usable pooled DataSource.
// The DataSource is using a default pool configuration, and Postgres' JDBC driver
// is presumed to have already been loaded via the jdbc.drivers system property or an
// explicit call to Class.forName("org.postgresql.Driver") elsewhere.
...
If you use the DataSources
factory class, and you want to programmatically override default configuration
parameters, make use of the PoolConfig class:
DataSource ds_unpooled = DataSources.unpooledDataSource("jdbc:postgresql://localhost/testdb",
"swaldman",
"test-password");
PoolConfig pc = new PoolConfig();
pc.setMaxStatements(200); //turn on Statement pooling
// pass our overriding PoolConfig to the DataSources.pooledDataSource() factory method.
ds_pooled = DataSources.pooledDataSource( ds_unpooled, pc );
// The DataSource ds_pooled is now a fully configured and usable pooled DataSource,
// with Statement caching enabled for a maximum of up to 200 statements.
...
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:
// fetch a JNDI-bound DataSource
InitialContext ictx = new InitialContext();
DataSource ds = (DataSource) ictx.lookup( "java:comp/env/jdbc/myDataSource" );
// make sure it's a c3p0 PooledDataSource
if ( ds instanceof PooledDataSource)
{
PooledDataSource pds = (PooledDataSource) ds;
System.err.println("num_connections: " + pds.getNumConnectionsDefaultUser());
System.err.println("num_busy_connections: " + pds.getNumBusyConnectionsDefaultUser());
System.err.println("num_idle_connections: " + pds.getNumIdleConnectionsDefaultUser());
System.err.println();
}
else
System.err.println("Not a c3p0 PooledDataSource!");
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.
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.
DataSource ds_pooled = null;
try
{
DataSource ds_unpooled = DataSources.unpooledDataSource("jdbc:postgresql://localhost/testdb",
"swaldman",
"test-password");
ds_pooled = DataSources.pooledDataSource( ds_unpooled );
// do all kinds of stuff with that sweet pooled DataSource...
}
finally
{
DataSources.destroy( ds_pooled );
}
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:
static void cleanup(DataSource ds) throws SQLException
{
// make sure it's a c3p0 PooledDataSource
if ( ds instanceof PooledDataSource)
{
PooledDataSource pds = (PooledDataSource) ds;
pds.close();
}
else
System.err.println("Not a c3p0 PooledDataSource!");
}
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.
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:
C3P0ProxyConnection castCon = (C3P0ProxyConnection) c3p0DataSource.getConnection();
Method m = CLOB.class.getMethod("createTemporary", new Class[]{Connection.class, boolean.class, int.class});
Object[] args = new Object[] {C3P0ProxyConnection.RAW_CONNECTION, Boolean.valueOf( true ), new Integer( 10 )};
CLOB oracleCLOB = (CLOB) castCon.rawConnectionOperation(m, null, args);
Note: C3P0 now includes special support for some Oracle-specific methods.
See Appendix C.
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);
...or both, depending upon whether the property is read-only, write-only, or read-writable.
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:
# turn on statement pooling
c3p0.maxStatements=150
# close pooled Connections that go unused for
# more than half an hour
c3p0.maxIdleTime=1800
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.
c3p0 Connection pools are very easy to configure via the following basic parameters:
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.
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:
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.
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 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.
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:
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.
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:
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.
See Appendix A for information about the following configuration properties:
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.
