c3p0 - JDBC3 Connection and Statement Pooling
version 0.9.2.1
- Contents
- Quickstart
- What is c3p0?
- Prerequisites
- Installation
- Using c3p0
- Configuration
- Introduction
- Basic Pool Configuration
- Managing Pool Size and Connection Age
- Configuring Connection Testing
- Configuring Statement Pooling
- Configuring Recovery From Database Outages
- Managing Connection Lifecycles with Connection Customizers
- Configuring Unresolved Transaction Handling
- Configuring to Debug and Workaround Broken Client Applications
- Other DataSource Configuration
- Configuring and Managing c3p0 via JMX
- Configuring Logging
- Performance
- Known shortcomings
- Feedback and support
- Appendix A: Configuration Properties
- Appendix B: Configuation Files, etc.
- Appendix C: Hibernate-specific notes
- Appendix D: Configuring c3p0 pooled DataSources for Apache Tomcat
- Appendix E: JBoss-specific notes
- Appendix F: Oracle-specific API: createTemporaryBLOB() and createTemporaryCLOB()
Quickstart
c3p0 was designed to be butt-simple to use. Just putthe files lib/c3p0-0.9.2.1.jar andlib/mchange-commons-java-0.2.3.4.jar in your application'seffectiveCLASSPATH, then make a DataSource like this:
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:postgresql://localhost/testdb" );
cpds.setUser("dbuser");
cpds.setPassword("dbpassword");
[Optional] If you want to turn on PreparedStatement pooling, you must also setmaxStatements and/ormaxStatementsPerConnection (both default to 0):
Do whatever you want with your DataSource, which will be backedby a Connection pool set up with default parameters. Youcan bind the DataSource to a JNDI name service, or use itdirectly, as you prefer.
When you are done, you can clean up the DataSource you've createdlike this:
That's it! The rest is detail.
What is c3p0?
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 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. All JDBC-defined internal interfaces are implemented (ConnectionPoolDataSource, PooledConnection, ConnectionEvent-generating Connections, etc.) You can mix c3p0 classes with compliant third-party implementations (although not all c3p0 features will work with external implementations).
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, andthe JDBC 2.x or above javax.sql libraries. c3p0 works fine underJava 1.4.x and 1.5.x as well.
Put the file lib/c3p0-0.9.2.1.jarsomewhere in your CLASSPATH (or any other place where your application'sclassloader will find it). That's it!
From a users' perspective, c3p0 simply provides standard jdbc2 DataSourceobjects. When creating these DataSources, users can control pooling-related, naming-related, and other properties (SeeAppendix A). All pooling is entirelytransparent to users once a DataSource has been created.
There are three ways of acquiring c3p0 pool-backed DataSources: 1) directly instantiate and configure aComboPooledDataSource bean; 2) use the DataSources factory class; or 3) "build your own" pool-backedDataSource by directly instantiatingPoolBackedDataSource and setting itsConectionPoolDataSource. Mostusers will probably find instantiatingComboPooledDataSourceto 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 thatyou do not specify programmatically. c3p0 has built-in, hard-coded defaults, but you can override these by creatinga file calledc3p0.properties and storing it as a top-level resource in the same CLASSPATH (or ClassLoader)that loads c3p0's jar file. As of c3p0-0.9.1, you can also supply a file calledc3p0-config.xml for more advancedconfiguration. SeeConfiguration below.
Perhaps the most straightforward way to create a c3p0 pooling DataSource is to instantiate an instance ofcom.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 propertyjdbcUrl. You may alsowant to setuser andpassword, and if you have not externally preloaded the old-style JDBC driver you'lluse you should set thedriverClass.
The defaults of any c3p0 DataSource are determined by configuration you supply, orelse revert to hard-coded defaults [seeconfiguration properties].c3p0-0.9.1 and above supportsmultiple, named configurations.If you wish to use a named configuration, construct yourcom.mchange.v2.c3p0.ComboPooledDataSourcewith the configuration name as a constructor agument:
Of course, you can still override any configuration properties programmatically, as above.
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 DataSourcesfactory class, and you want to programmatically override default configurationparameters, you can supply a map of override properties:
If you are using named configurations, you can specify the configurationthat defines the default configuration for your DataSource:
Show deprecated PoolConfig approach...
Deprecated! Programmatic configuration via PoolConfig
If you use the DataSources factory class, and you want to programmatically override default configuration parameters, make use of thePoolConfig class:
RARE: Forcing authentication information, regardless of (mis)configuration of the underlying (unpooled) DataSource
You can wrap any DataSouce using DataSource.pooledDataSource( ... ), usually with no problem whatsoever. DataSources are supposed to indicate the username and password associated by default with Connections via standard propertiesuser andpassword. Some DataSource implementations do not offer these properties. Usually this is not at all a problem.c3p0 works around this by acquiring "default" Connections from the DataSource if it can't find default authentication information, and a client has not specified the authentification information viagetConnection( user, password ).
However, in rare circumstances, non-c3p0 unpooled DataSources provide a user property, but not a password property, or you have access to a DataSource that you wish to wrap behind a pool, but you wish to override its build-in authentification defaults without actually modifying the user or password properties.
c3p0 provides configuation properties overrideDefaultUser andoverrideDefaultPassword. If you set these properties, programmatically as above, or via any of c3p0'sconfiguration mechanisms,c3p0 PooledDataSources will ignore the user and password property associated with the underlying DataSource, and use the specified overrides instead.
Querying a PooledDataSource's current status
c3p0 DataSources backed by a pool, which include implementations of ComboPooledDataSource andthe objects returned by DataSources.pooledDataSource( ... ), all implement the interfacecom.mchange.v2.c3p0.PooledDataSource, which makes available a number of methods for querying the status ofDataSource 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 distinctauthentications. The various methods let you query the status of pools individually,or aggregate statistics for all authentifications for which your DataSource is maintainingpools.Note that pool configuration parmeters such as maxPoolSize are enforced on a per-authentification basis! For example, if you have setmaxPoolSize to20, and if the DataSource is managing connections under two username-password pairs [thedefault, and one other pair established via a call togetConnection(user, password), you should expect to see as many as 40 Connections fromgetNumConnectionsAllUsers().
Most applications only acquire default-authenticated Connections from DataSources, and can typically just use thegetXXXDefaultUser() to gather Connection statistics.
As well as Connection pool realted statistics, you can retrieve status information about each DataSource's Thread pool. Please seePooledDataSource for a complete list of available operations.
Using C3P0Registry to get a reference to a DataSource
If it's inconvenient or impossible to get a reference to your DataSource via JNDI or some other means, you can find all live c3p0 DataSources using theC3P0Registry class, which includes three static methods to help you out:
- public static Set getPooledDataSources()
- public static Set pooledDataSourcesByName( String dataSourceName )
- public static PooledDataSource pooledDataSourceByName( String dataSourceName )
The first method will hand you the Set of all live c3p0 PooledDataSources. If you are sure your application only makes one PooledDataSources, or you can distinguish between the DataSources by their configuration properties (inspected via "getters"), the first method may be sufficient. Because this will not always be the case, c3p0PooledDataSources have a special property calleddataSourceName. You can set the dataSourceName property directly when you construct your DataSource, or dataSourceName can be set like any other property in a named or the default config. Otherwise,dataSourceName will default to either 1) the name of your DataSource's configuration, if you constructed it with anamed configuration; or 2) a unique (but unpredicatble) name if you are using the default configuration.
There is no guarantee that a dataSourceName will be unique. For example, if two c3p0 DataSources share the samenamed configuration, and you have not set thedataSourceName programmatically, the two data sources will both share the name of the configuration. To get all of the DataSources with a particulardataSourceName, usepooledDataSourcesByName( ... ). If you've ensured that your DataSource's name is unique (as you will generally want to do, if you intend to useC3P0Registry to find your DataSources), you can use the convenience methodpooledDataSourceByName( ... ), which will return your DataSource directly, ornull if no DataSource with that name is available. If you usepooledDataSourceByName( ... ) and more than one DataSource shares the name supplied, which one it will return is undefined.
Cleaning up after c3p0 PooledDataSources
The easy way to clean up after c3p0-created DataSources is to use the static destroy methoddefined by the classDataSources. OnlyPooledDataSources need to be cleaned up, butDataSources.destroy( ... ) does no harm if it is called on an unpooled or non-c3p0DataSource.
Alternatively, c3p0's PooledDataSourceinterface contains a close() methodthat you can call when you know you are finished with a DataSource. So, you can cast a c3p0derived DataSource to aPooledDataSource and close it:
Unreferenced instances of PooledDataSourcethat are not close()ed by clientsclose() themselves prior to garbage collection in theirfinalize() methods. As always, finalization should be considereda 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 PooledDataSource in astep-by-step way by instantiating and configuring an unpooledDriverManagerDataSource, instantiating aWrapperConnectionPoolDataSource and setting the unpooled DataSource as itsnestedDataSource property,and then using that to set theconnectionPoolDataSource property of a newPoolBackedDataSource.
This sequence of events is primarily interesting if your driver offers an implementation of ConnectionPoolDataSource, and you'dlike c3p0 to use that. Rather than using c3p0'sWrapperConnectionPoolDataSource, you can create aPoolBackedDataSourceand set itsconnectionPoolDataSource property. Statement pooling,ConnectionCustomizers, and many c3p0-specific propertiesare unsupported with third party implementations ofConnectionPoolDataSource. (Third-partyDataSource implementationscan be substituted for c3p0'sDriverManagerDataSource with no significant loss offunctionality.)
Advanced: Raw Connection and Statement Operations
JDBC drivers sometimes define vendor-specific, non-standard API on Connection and Statement implementations. C3P0 wrapsthese Objects behind a proxies, so you cannot cast C3P0-returned Connections or Statements to the vendor-specific implementationclasses. C3P0 does not provide any means of accessing the raw Connections and Statements directly, because C3P0 needs to keeptrack of Statements and ResultSets created in order to prevent resource leaks and poolcorruption.
C3P0 does provide an API that allows you to invoke non-standard methods reflectively on an underlyingConnection. To use it, first cast the returned Connection to aC3P0ProxyConnection. Then callthe methodrawConnectionOperation, supplying the java.lang.reflect.Method object forthe non-standard method you wish to call as an argument. TheMethod you supply will be invokedon the target you provide on the second argument (null for static methods), and using the arguments yousupply in the third argument to that function. For the target, and for any of the method arguments, youcan supply the special tokenC3P0ProxyConnection.RAW_CONNECTION, which will be replaced withthe underlying vendor-specific Connection object before theMethod is invoked.
C3P0ProxyStatement offersan exactly analogous API.
Any Statements (including Prepared and CallableStatements) and ResultSets returned by raw operationswill be c3p0-managed, and will be properly cleaned-up onclose() 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. SeeAppendix F.
Configuration
Introduction
While c3p0 does not require very much configuration, it is very tweakable. Most of the interestingknobs and dials are represented as JavaBean properties. Following JavaBean conventions, we note thatif an Object has a property of typeT calledfoo, it will have methods that looklike...
public T getFoo();...or both, depending upon whether the property is read-only, write-only, or read-writable.
public void setFoo(T foo);
There are several ways to modify c3p0 properties: You can directly alter the property values associated with a particular DataSource in your code, or you can configure c3p0 externally, via asimple Java properties file,via anXML configuration file, or via System properties. Configuration filesare normally looked up under standard names (c3p0.properties orc3p0-config.xml)at the top level of an application's classpath, but the XML configuration can be placedanywhere in an application's file system or classpath, if the system propertycom.mchange.v2.c3p0.cfg.xml is set.
DataSources are usually configured before they are used, eitherduring or immediately following their construction. c3p0 doessupport property modifications midstream, however.
If you obtain a DataSource by instantiating a ComboPooledDataSource, configure it by simply calling appropriate setter methods offered by that classbefore attempting a call togetConnection(). See the example above.
If you obtain a DataSource by using factory methods ofthe utility class com.mchange.v2.c3p0.DataSources,and wish to use a non-default configuration, you can supply a Map of property names (beginning with lower-case letters)to property values (either as Strings or "boxed" Java primitives like Integer or Boolean).
All tweakable properties are documented for referencein Appendix A. The most basic and important c3p0 configurationtopics are discussed below.
Basic Pool Configuration
c3p0 Connection pools are very easy to configure via the following basic parameters:
initialPoolSize, minPoolSize, maxPoolSizedefine the number of Connections that will be pooled. Please ensure thatminPoolSize <= maxPoolSize. Unreasonable values ofinitialPoolSize willbe ignored, andminPoolSize will be used instead.
Within the range between minPoolSize and maxPoolSize, the number of Connections ina pool varies according to usage patterns. The number of Connections increases whenever a Connectionis requested by a user, no Connections are available, and the pool has not yet reached maxPoolSizein the number of Connections managed. Since Connection acquisition is very slow, it is almost always useful toincrease the number of Connections eagerly, in batches, rather than forcing each client to wait for a newConnection to provoke a single acquisition when the load is increasing.acquireIncrement determineshow many Connections a c3p0 pool will attempt to acquire when the pool has run out of Connections. (RegardlessofacquireIncrement, 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 (seeConfiguring Connection Testing below), or when a Connection is expiredby the pool after sitting idle for a period of time, or for being too old (See Managing Pool Size and Connection Age.)
Managing Pool Size and Connection Age
Different applications have different needs with regard to trade-offs between performance, footprint, and reliability. C3P0offers a wide variety of options for controlling how quickly pools that have grown large under load revert tominPoolSize,and whether "old" Connections in the pool should be proactively replaced to maintain their reliablity.
By default, pools will never expire Connections. If you wishConnections to be expired over time in order to maintain "freshness", setmaxIdleTime and/ormaxConnectionAge.maxIdleTime defines how many seconds aConnection should be permitted to go unused before being culled from the pool.maxConnectionAgeforces the pool to cull any Connections that were acquired from the database more than the set number ofseconds in the past.
maxIdleTimeExcessConnections is about minimizing the number of Connections held by c3p0 pools when the pool is not under load. By default, c3p0 pools grow under load, but only shrink if Connections fail a Connection test or are expired away via the parameters described above. Some users want their pools to quickly release unnecessary Connections after a spike in usage that forces a large pool size. You can achieve this by settingmaxIdleTimeExcessConnections to a value much shorter thanmaxIdleTime, forcing Connections beyond your set minimum size to be released if they sit idle for more than a short period of time.
Some general advice about all of these timeout parameters: Slow down! The point of Connection pooling is to bear the cost of acquiring a Connection only once, and then to reuse the Connection many, many times. Most databases support Connections that remain open for hours at a time. There's no need to churn through all your Connections every few seconds or minutes. SettingmaxConnectionAge ormaxIdleTime to 1800 (30 minutes) is quite aggressive. For most databases, several hours may be more appropriate. You can ensure the reliability of your Connections by testing them, rather than by tossing them. (seeConfiguring Connection Testing.) The only one of these parameters that should generally be set to a few minutes or less ismaxIdleTimeExcessConnections.
Configuring Connection Testing
c3p0 can be configured to test the Connections that it pools in a variety of ways, tominimize the likelihood that your application will see broken or "stale" Connections.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 dueto resource leaks, driver bugs, or other causes.
c3p0 provides users a great deal of flexibility in testing Connections, via the followingconfiguration parameters:
- automaticTestTable
- connectionTesterClassName
- idleConnectionTestPeriod
- preferredTestQuery
- testConnectionOnCheckin
- testConnectionOnCheckout
idleConnectionTestPeriod, testConnectionOnCheckout, andtestConnectionOnCheckin controlwhen Connections will be tested.automaticTestTable,connectionTesterClassName, andpreferredTestQuery controlhow they will be tested.
When configuringConnection testing, first try to minimize the cost of each test. By default, Connections are testedby calling thegetTables() method on a Connection's associatedDatabaseMetaDataobject. This has the advantage of working with any database, and regardless of the database schema. However, empiricallyaDatabaseMetaData.getTables() call is often much slower than a simple database query.
The most convenient way to speed up Connection testing is to define the parameterautomaticTestTable. Using the nameyou provide, c3p0 will create an empty table, and make a simple query against it to test the database. Alternatively, if your database schemais fixed prior to your application's use of the database, you can simply define a test query with thepreferredTestQueryparameter. Be careful, however. SettingpreferredTestQuery will lead to errors as Connection tests failif the query target table does not exist in your database tableprior to initialization of your DataSource.
Advanced users may define any kind of Connection testing they wish, by implementing aConnectionTester and supplying thefully qualified name of the class asconnectionTesterClassName. If you'd like your custom ConnectionTestersto honor and support thepreferredTestQuery andautomaticTestTable parameters, implementUnifiedConnectionTester, most conveniently by extendingAbstractConnectionTester. See theapi docsfor more information.
The most reliable time to test Connections is on check-out. But this is also the most costly choicefrom a client-performance perspective. Most applications should work quite reliably using a combination ofidleConnectionTestPeriod andtestConnectionsOnCheckIn. 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 longidleConnectionTestPeriod, and not testing on checkout and check-in at all is an excellent, high-performanceapproach.
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 ofstatement pooling can slightly harmperformance. 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 widelyin 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 followingconfiguration parameters:
maxStatements is JDBC's standard parameter for controlling statement pooling.maxStatements defines thetotal numberPreparedStatements a DataSource will cache. The pool will destroy the least-recently-used PreparedStatementwhen it hits this limit. This sounds simple, but it's actually a strange approach, becausecached statements conceptually belong to individual Connections; they are not global resources. To figure out a sizeformaxStatements that does not "churn" cached statements, you need to consider the number offrequently used PreparedStatements in your application, and multiply that by the number of Connections you expect in the pool (maxPoolSizein a busy application).
maxStatementsPerConnection is a non-standard configuration parameter that makes a bit moresense conceptually. It defines how many statements each pooled Connection is allowed to own.You can set this to a bit more than the number ofPreparedStatements your application frequentlyuses, to avoid churning.
If either of these parameters are greater than zero, statement pooling will be enabled. If bothparameters are greater than zero, both limits will be enforced. If only one is greater than zero, statement poolingwill be enabled, but only one limit will be enforced.
If statementCacheNumDeferredCloseThreads is greater than zero, the Statement pool will defer physically close()ing cached Statements untilits parent Connection is not in use by any client or internally (in e.g. a test) by the pool itself. For some JDBC drivers(especially Oracle), attempts to close a Statement freeze if the parent Connection is in use. This parameter defaults to 0.Set it to a positive value if you observe "APPARENT DEADLOCKS" realted to Connection close tasks. Almost always, that value should be one: if you need more than one Thread dedicated solely to Statement destruction, you probably should setmaxStatements and/ormaxStatementsPerConnection to higher values so you don't churn through cached Statements so quickly.
Configuring Recovery From Database Outages
c3p0 DataSources are designed (and configured by default) to recover from temporary database outages, such asthose which occur during a database restart or brief loss of network connectivity.You can affect how c3p0 handles errors in acquiring Connections via the followingconfigurable properties:
When a c3p0 DataSource attempts and fails to acquire a Connection, it will retry uptoacquireRetryAttempts times, with a delay ofacquireRetryDelaybetween each attempt. If all attempts fail, any clients waiting for Connections fromthe 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, whichmay be some time after the initial Connection attempt. IfacquireRetryAttempts is set to 0, c3p0 will attempt to acquire new Connections indefinitely, and calls togetConnection() may block indefinitely waiting for a successful acquisition.
Once a full round of acquisition attempts fails, there are two possible policies. Bydefault, the c3p0 DataSource will remain active, and will try again to acquire Connectionsin response to future requests for Connections. If you setbreakAfterAcquireFailuretotrue, the DataSource will consider itself broken after a failed round ofConnection attempts, and future client requests will fail immediately.
Note that if a database restart occurs, a pool may contain previously acquired but nowstale Connections. By default, these stale Connections will only be detected andpurged lazily, when an application attempts to use them, and sees an Exception. SettingmaxIdleTime or maxConnectionAge can help speed up the replacement ofbroken Connections. (SeeManaging ConnectionAge.)If you wish to avoid application Exceptions entirely, you must adopt a connection testing strategy thatis likely to detect stale Connections prior to their delivery to clients. (See "Configuring Connection Testing".) Evenwith active Connection testing (testConnectionsOnCheckout set totrue, ortestConnectionsOnCheckin and a short idleConnectionTestPeriod), yourapplication may see occasional Exceptions on database restart, for example if the restart occurs after a Connection to the database has already been checked out.
Managing Connection Lifecycles with Connection Customizer
Application frequently wish to set up Connections in some standard, reusable way immediately afterConnection acquisitions. Examples of this include setting-up character encodings, or date and timerelated behavior, using vendor-specific APIs or non-standard SQL statement executions. Occasionallyit is useful to override the default values of standard Connection properties such astransactionIsolation,holdability, orreadOnly. c3p0 provides a "hook" interface that you can implement,which gives you the opportunity to modify or track Connections just after they are checked out from thedatabase, immediately just prior to being handed to clients on checkout, just prior to being returnedto the pool on check-in, and just prior to final destruction by the pool. The Connections handedto ConnectionCustomizers are raw, physical Connections, with all vendor-specific API accessible.See the API docs forConnectionCustomizer.
To install a ConnectionCustomizer just implement the interface, make your class accessible to c3p0's ClassLoader, and set the configuration parameter below:
ConnectionCustomizers are required to be immutable classes with public no argument constructors.They shouldn't store any state. For (rare) applications that wish to track the behavior of individualDataSources with ConnectionCustomizers, the lifecycle methods each accept a DataSource-specific "identityToken", which is unique to each PooledDataSource.
Below is a sample ConnectionCustomizer. Implementations that do not need to override allfourConnectionCustomizer methods can extendAbstractConnectionCustomizerto inherit no-op implementations of all methods.
Configuring Unresolved Transaction Handling
Connections checked into a pool cannot have any unresolved transactional work associated with them.If users have setautoCommit tofalse on a Connection, and c3p0 cannot guaranteethat there is no pending transactional work, c3p0 must eitherrollback() orcommit()on check-in (when a user calls close()). The JDBC spec is (unforgivably) silent on the questionof whether unresolved work should be committed or rolled back on Connection close. By default, c3p0rolls 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, setautoCommitOnCloseto true. If you wish c3p0 to leave transaction management to you, and neither commit nor rollback (nor modifythe state of ConnectionautoCommit), you may set forceIgnoreUnresolvedTransactions to true. SettingforceIgnoreUnresolvedTransactions is strongly discouraged, because if clients are not careful tocommit or rollback themselves prior to close(), or do not set ConnectionautoCommit consistently, bizarreunreproduceable behavior and database lockups can occur.
Configuring to Debug and Workaround Broken Client Applications
Sometimes client applications are sloppy about close()ing all Connections they check out. Eventually, the pool grows tomaxPoolSize, and then runs out of Connections, because of these bad clients.
The right way to address this problem is to fix the client application. c3p0 can help you debug, by letting you know where Connections are checked out that occasionally don't get checked in. In rare and unfortunate situations, development of the client application is closed, and even though it is buggy, you cannot fix it. c3p0 can help you work around the broken application, preventing it from exhausting the pool.
The following parameters can help you debug or workaround broken client applications.
unreturnedConnectionTimeout defines a limit (in seconds) to how long a Connection may remain checked out. If set to a nozero value, unreturned, checked-out Connections that exceed this limit will be summarily destroyed, and then replaced in the pool. Obviously, you must take care to set this parameter to a value large enough that all intended operations on checked out Connections have time to complete. You can use this parameter to merely workaround unreliable client apps that fail to close() Connections.
Much better than working-around is fixing. If, in addition to settingunreturnedConnectionTimeout, you setdebugUnreturnedConnectionStackTraces totrue,then a stack trace will be captured each time a Connection is checked-out. Whenever an unreturned Connection times out, that stack trace will be printed, revealing where a Connection was checked out that was not checked in promptly.debugUnreturnedConnectionStackTraces is intended to be used only for debugging, as capturing a stack trace can slow down Connection check-out.
Other DataSource Configuration
See Appendix A for information about the following configuration properties:
- checkoutTimeout
- factoryClassLocation
- maxAdministrativeTaskTime
- numHelperThreads
usesTraditionalReflectiveProxies
numHelperThreads and maxAdministrativeTaskTime help to configure the behavior of DataSource thread pools. By default, each DataSource has only three associated helper threads. If performance seems to drag under heavy load, or if you observe via JMX or direct inspection of a PooledDataSource, that the number of "pending tasks" is usually greater than zero, try increasingnumHelperThreads.maxAdministrativeTaskTime may be useful for users experiencing tasks that hang indefinitely and "APPARENT DEADLOCK" messages. (See Appendix A for more.)
checkoutTimeout limits how long a client will wait for a Connection, if all Connections are checked out and one cannot be supplied immediately.usesTraditionalReflectiveProxies is of little practical use and is now formally deprecated. It permits you to use an old, now superceded implementation of C3P0-generated proxy objects. (C3P0 used to use reflective, dynamic proxies. Now, for enhanced performance, it uses code-generated, nonrefective implementations.)factoryClassLocation can be used to indicate where a URL from which c3p0 classes can be downloaded, if c3p0 DataSources will be retrieved as References from a JNDI DataSource by clients who do not have c3p0 locally installed.
Configuring and Managing c3p0 via JMX
If JMX libraries and a JMX MBeanServer are available in your environment (they are include in JDK 1.5 and above), you can inspect and configure your c3p0 datasources via a JMX administration tool (such as jconsole, bundled withjdk 1.5). You will find that c3p0 registers MBeans under com.mchange.v2.c3p0, one with statistics about thelibrary as a whole (calledC3P0Registry), and an MBean for eachPooledDataSource you deploy. You can view andmodify your DataSource's configuration properties, track the activity of Connection, Statement, and Thread pools, and resetpools and DataSources via thePooledDataSource MBean. (You may wish to view the API docs ofPooledDataSource for documentation of the available operations.)
The name under which mbeans for c3p0 PooledDataSources will be registered includes the propertydataSourceName. So, if you set this property, you can ensure that semanically equivalent data sourcesare identifiable across application restarts.
If you do not want c3p0 to register MBeans with your JMX environment, you can suppress this behavior with the following, set either as a System property or inc3p0.properties:
Configuring Logging
c3p0 uses a custom logging library similar to jakarta commons-logging. Log messages can be directed tothe popular log4j logging library, to the standard logging facility introduced with jdk1.4, or toSystem.err. Nearly all configuration should be done at the level of your preferred logginglibrary. There are a very few configuration options specific to c3p0's logging, and usually the defaultswill be fine. Logging-related parametersmay be placed in yourc3p0.properties file, in a file calledmchange-log.properties atthe top-level of your classpath, or they may be defined as System properties. (The logging properties definedbelow maynot be defined inc3p0-config.xml!) See thebox below.
c3p0's logging behavior is affected by certain build-time options. If build-optionc3p0.debug is settofalse, all messages at a logging level below INFO will be suppressed. Build-optionc3p0.trace controls how fine-grained c3p0's belowINFO level reporting will be. For the moment, distributedc3p0 binaries are compiled withdebug set totrue andtrace set to its maximum level of10.But binaries may eventually bedistributed withdebug set tofalse. (For the moment, the performance impact of the logging level-checks seemsvery small, and it's most flexible to compile in all the messages, and let your logging library control which are emitted.) Whenc3p0 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
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
Performance
Enhanced performance is the purpose of Connection and Statement pooling, and amajor goal of the c3p0 library. For most applications, Connection poolingwill provide a significant performance gain, especially if you are acquiringan 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 orpassivated. 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 unclosedResultSets andStatements when parent resources are returned to pools, all client-visibleConnections,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. Inmy environment, the wrapper overhead amounts from several hundreths to severalthousandths of the cost of Connection acquisition, so unless you are makingmany, 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 (whereone might iterate through a table with thousands of records) appears to be negligibly small.
Known Shortcomings
-
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 themaxPoolSize property.
This fact is a natural consequence of the definition of the DataSource spec (which allows Connections to be acquired with multiple user authentications), and the requirement that all Connections in a single pool be functionally identical. This "issue" will not be changed or fixed. It's noted here just so you understand what's going on.
-
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 preprocessPreparedStatements, 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 propertymaxStatements ormaxStatementsPerConnection to a value greater than zero.).
Feedback and Support
Please provide any and all feedback to <swaldman@mchange.com>! Also, feel free to join and ask questions on thec3p0-users mailing list.Sign up athttp://sourceforge.net/projects/c3p0/
Thank you for using c3p0!!!
Appendix A: Configuration Properties
c3p0 configuration properties can be divided into JavaBeans-style Properties andOther Properties.
JavaBeans-style Properties
The following properties can be set directly in code as JavaBeans properties, via aSystem properties or ac3p0.properties file(withc3p0. prepended tothe property name), or in ac3p0-config.xml file. See the section onConfiguration above.Click on the property name for a full description.
-
acquireIncrement
-
Default: 3Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted.
(当连接池中的连接耗尽的时候c3p0一次同时获取的连接数) [See "Basic Pool Configuration"]
acquireRetryAttempts
-
Default: 30Defines 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: 1000Milliseconds, time c3p0 will wait between acquire attempts.
(两次连接中间隔时间，单位毫秒。) [See "Configuring Recovery From Database Outages"]
autoCommitOnClose
-
Default: falseThe 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: nullIf 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.)
c3p0将建一张名为Test的空表，并使用其自带的查询语句进行测试。如果定义了这个参数那么属性preferredTestQuery将被忽略。你不能在这张Test表上进行任何操作，它将只供c3p0测试使用。[See "Configuring Connection Testing"]
breakAfterAcquireFailure
-
Default: falseIf 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().
获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效保留，并在下次调用getConnection()的时候继续尝试获取连接。如果设为true，那么在尝试获取连接失败后该数据源将申明已断开并永久关闭。 [See "Configuring Recovery From Database Outages"]
checkoutTimeout
-
Default: 0The 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.
当连接池用完时客户端调用getConnection()后等待获取新连接的时间，超时后将抛出SQLException,如设为0则无限期等待。单位毫秒。
connectionCustomizerClassName
-
Default: nullThe fully qualified class-name of an implememtation of the ConnectionCustomizer interface, which users can implement to set up Connections when they are acquired from the database, or on check-out, and potentially to clean things up on check-in and Connection destruction. If standard Connection properties (holdability, readOnly, or transactionIsolation) are set in the ConnectionCustomizer's onAcquire() method, these will override the Connection default values.
hook方法，在对相关资源做操作的时候，''他所操作的connection是真实的数据库连接，而不是proxy过的connection''
connectionTesterClassName
-
Default: com.mchange.v2.c3p0.impl.DefaultConnectionTesterThe 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.
通过实现ConnectionTester或QueryConnectionTester的类来测试连接。类名需制定全路径。Default: com.mchange.v2.c3p0.impl.DefaultConnectionTester [See "Configuring Connection Testing"]
dataSourceName
-
Default: if configured with a named config, the config name, otherwise the pool's "identity token"Every c3p0 pooled data source is given a dataSourceName, which serves two purposes. It helps users find DataSources via C3P0Registry, and it is included in the name of JMX mBeans in order to help track and distinguish between multiple c3p0 DataSources even across application or JVM restarts. dataSourceName defaults to the pool's configuration name, if a named config was used, or else to an "identity token" (an opaque, guaranteed unique String associated with every c3p0 DataSource). You may update this property to any name you find convenient. dataSourceName is not guaranteed to be unique — for example, multiple DataSource created from the same named configuration will share the same dataSourceName. But if you are going to make use of dataSourceName, you will probably want to ensure that all pooled DataSources within your JVM do have unique names.
debugUnreturnedConnectionStackTraces
-
Default: falseIf true, and if unreturnedConnectionTimeout is set to a positive value,then the pool will capture the stack trace (via an Exception) of all Connection checkouts, and the stack traces will beprinted when unreturned checked-out Connections timeout. This is intended to debug applications with Connection leaks, thatis applications that occasionally fail to return Connections, leading to pool growth, and eventually exhaustion (when thepool hits maxPoolSize with all Connections checked-out and lost). This parameter should only be set while debugging,as capturing the stack trace will slow down every Connection check-out. [See "Configuring to Debug and Workaround Broken Client Applications"]
factoryClassLocation
-
Default: nullDataSources 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.
指定c3p0 libraries的路径，如果（通常都是这样）在本地即可获得那么无需设置，默认null即可Does Not Support Per-User Overrides.
forceIgnoreUnresolvedTransactions
-
Default: falseStrongly 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: 0If 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: 3Number of Connections a pool will try to acquire upon startup. Should be between minPoolSize and maxPoolSize.
初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间 [See "Basic Pool Configuration"]
maxAdministrativeTaskTime
-
Default: 0Seconds before c3p0's thread pool will try to interrupt an apparently hung task. Rarely useful. Many of c3p0's functions are not performed by client threads, but asynchronously by an internal thread pool. c3p0's asynchrony enhances client performance directly, and minimizes the length of time that critical locks are held by ensuring that slow jdbc operations are performed in non-lock-holding threads. If, however, some of these tasks "hang", that is they neither succeed nor fail with an Exception for a prolonged period of time, c3p0's thread pool can become exhausted and administrative tasks backed up. If the tasks are simply slow, the best way to resolve the problem is to increase the number of threads, via numHelperThreads. But if tasks sometimes hang indefinitely, you can use this parameter to force a call to the task thread's interrupt() method if a task exceeds a set time limit. [c3p0 will eventually recover from hung tasks anyway by signalling an "APPARENT DEADLOCK" (you'll see it as a warning in the logs), replacing the thread pool task threads, and interrupt()ing the original threads. But letting the pool go into APPARENT DEADLOCK and then recover means that for some periods, c3p0's performance will be impaired. So if you're seeing these messages, increasing numHelperThreads and setting maxAdministrativeTaskTime might help. maxAdministrativeTaskTime should be large enough that any resonable attempt to acquire a Connection from the database, to test a Connection, or two destroy a Connection, would be expected to succeed or fail within the time set. Zero (the default) means tasks are never interrupted, which is the best and safest policy under most circumstances. If tasks are just slow, allocate more threads. If tasks are hanging forever, try to figure out why, and maybe setting maxAdministrativeTaskTime can help in the meantime.Does Not Support Per-User Overrides.
maxConnectionAge
-
Default: 0Seconds, effectively a time to live. A Connection older than maxConnectionAge will be destroyed and purged from the pool. This differs from maxIdleTime in that it refers to absolute age. Even a Connection which has not been much idle will be purged from the pool if it exceeds maxConnectionAge. Zero means no maximum absolute age is enforced.
设置一个连接在池中最长的时间，如果时间超过，将会从池中清除
maxIdleTime
-
Default: 0Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.
最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。 [See "Basic Pool Configuration"]
maxIdleTimeExcessConnections
-
Default: 0Number of seconds that Connections in excess of minPoolSize should be permitted to remain idle in the poolbefore being culled. Intended for applications that wish to aggressively minimize the number of open Connections,shrinking the pool back towards minPoolSize if, following a spike, the load level diminishes and Connectionsacquired are no longer needed. If maxIdleTime is set, maxIdleTimeExcessConnections should besmaller if the parameter is to have any effect. Zero means no enforcement, excess Connections are not idled out.
maxPoolSize
-
Default: 15Maximum number of Connections a pool will maintain at any given time.
连接池中保留的最大连接数。 [See "Basic Pool Configuration"]
maxStatements
-
Default: 0The 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.
JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。 [See "Configuring Statement Pooling"]
maxStatementsPerConnection
-
Default: 0The 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.
maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。 [See "Configuring Statement Pooling"]
minPoolSize
-
Default: 3Minimum number of Connections a pool will maintain at any given time. [See "Basic Pool Configuration"]
numHelperThreads
-
Default: 3c3p0 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.
c3p0是异步操作的，缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能通过多线程实现多个操作同时被执行。Does Not Support Per-User Overrides.
overrideDefaultUser
-
Default: nullForces the username that should by PooledDataSources when a user calls the default getConnection() method. This is primarily useful when applications are pooling Connections from a non-c3p0 unpooled DataSource. Applications that use ComboPooledDataSource, or that wrap any c3p0-implemented unpooled DataSource can use the simple
当用户调用getConnection()时使root用户成为去获取连接的用户。主要用于连接池连接非c3p0的数据源时。 user property.Does Not Support Per-User Overrides.
overrideDefaultPassword
-
Default: nullForces the password that should by PooledDataSources when a user calls the default getConnection() method. This is primarily useful when applications are pooling Connections from a non-c3p0 unpooled DataSource. Applications that use ComboPooledDataSource, or that wrap any c3p0-implemented unpooled DataSource can use the simple password property.
与overrideDefaultUser参数对应使用的一个参数。Does Not Support Per-User Overrides.
password
-
Default: nullFor applications using ComboPooledDataSource or any c3p0-implemented unpooled DataSources — DriverManagerDataSource or the DataSource returned by DataSources.unpooledDataSource( ... ) — defines the password that will be used for the DataSource's default getConnection() method. (See also user.)
密码。Does Not Support Per-User Overrides.
preferredTestQuery
-
Default: nullDefines the query that will be executed for all connection tests, if the default ConnectionTester (or some other implementation of QueryConnectionTester, or better yet FullQueryConnectionTester) 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 yourpreferredTestQuery will be run must exist in the database schemaprior to your initialization of your DataSource. If your application defines its own schema, tryautomaticTestTable instead.
定义所有连接测试都执行的测试语句。在使用连接测试的情况下这个一显著提高测试速度。注意：测试的表必须在初始数据源的时候就存在。 [See "Configuring Connection Testing"]
propertyCycle
-
Default: 0Maximum time in seconds before user configuration constraints are enforced.Determines how frequently maxConnectionAge, maxIdleTime, maxIdleTimeExcessConnections, unreturnedConnectionTimeout are enforced. c3p0 periodically checks the age of Connections tosee whether they've timed out. This parameter determines the period. Zero means automatic: A suitable periodwill be determined by c3p0. [You can call getEffectivePropertyCycle...() methods on a c3p0 PooledDataSource to find the periodautomatically chosen.]
用户修改系统配置参数执行前最多等待300秒。
statementCacheNumDeferredCloseThreads
-
Default: 0If set to a value greater than 0, the statement cache will track when Connections are in use, and only destroy Statements when their parent Connections are not otherwise in use. Although closing of a Statement while the parent Connection is in use is formally within spec, some databases and/or JDBC drivers, most notably Oracle, do not handle the case well and freeze, leading to deadlocks. Setting this parameter to a positive value should eliminate the issue. This parameter should only be set if you observe that attempts by c3p0 to close() cached statements freeze (usually you'll see APPARENT DEADLOCKS in your logs). If set, this parameter should almost always be set to 1. Basically, if you need more than one Thread dedicated solely to destroying cached Statements, you should set maxStatements and/or maxStatementsPerConnection so that you don't churn through Statements so quickly. [See "Configuring Statement Pooling"]Does Not Support Per-User Overrides.
testConnectionOnCheckin
-
Default: falseIf 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.
如果设为true那么在取得连接的同时将校验连接的有效性。 [See "Configuring Connection Testing"]
testConnectionOnCheckout
-
Default: falseUse 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.
因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable等方法来提升连接测试的性能。 [See "Configuring Connection Testing"]
unreturnedConnectionTimeout
-
Default: 0Seconds. If set, if an application checks out but then fails to check-in [i.e. close()] a Connectionwithin the specified period of time, the pool will unceremoniously destroy() the Connection. This permitsapplications with occasional Connection leaks to survive, rather than eventually exhausting the Connectionpool. And that's a shame. Zero means no timeout, applications are expected to close() their own Connections.Obviously, if a non-zero value is set, it should be to a value longer than any Connection should reasonablybe checked-out. Otherwise, the pool will occasionally kill Connections in active use, which is bad. This is basically a bad idea, but it's a commonly requested feature. Fix your $%!@% applications so they don't leak Connections! Use this temporarily in combination withdebugUnreturnedConnectionStackTraces to figure out where Connections are being checked-out that don't make it back into the pool!
一个checkout连接的超时设置，一旦一个checkout连接超时，他将物理的关闭，而不是返回池中，主要是防止连接被长期使用不释放，这个设置也是比较危险的 [See "Configuring to Debug and Workaround Broken Client Applications"]
user
-
Default: nullFor applications using ComboPooledDataSource or any c3p0-implemented unpooled DataSources — DriverManagerDataSource or the DataSource returned by DataSources.unpooledDataSource() — defines the username that will be used for the DataSource's default getConnection() method. (See also password.)
用户名。Does Not Support Per-User Overrides. -
Default: falseDeprecated. 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.
早期的c3p0版本对JDBC接口采用动态反射代理。在早期版本用途广泛的情况下这个参数允许用户恢复到动态反射代理以解决不稳定的故障。最新的非反射代理更快并且已经开始广泛的被使用，所以这个参数未必有用。现在原先的动态反射与新的非反射代理同时受到支持，但今后可能的版本可能不支持动态反射代理。
Other Properties
The following configuration properties affect the behavior of the c3p0 library as a whole. Theymay be set as system properties, or in ac3p0.properties file.
Locating Configuration Information
Normally, c3p0's configuration information is placed in a either a c3p0-config.xml or c3p0.properties file at the top-level of an application's CLASSPATH. However, if you wish to place configuration information elsewhere, you may place c3p0 configuration information (in the XML file format only!) anywhere you'd like in the filesystem visible to your application. Just set the following property to the full, absolute path of the XML config file:
- com.mchange.v2.c3p0.cfg.xml
If you set this property to a value beginning with "classloader:", c3p0 will search for an XML config file as a ClassLoader resource, that is, in any location you specify under your classpath, including jar-fileMETA-INF directories.
Logging-related properties
The following properties affect c3p0's logging behavior. Please see Configuring Logging above for specific information.
- com.mchange.v2.log.MLog
- com.mchange.v2.log.NameTransformer
- com.mchange.v2.log.FallbackMLog.DEFAULT_CUTOFF_LEVEL
Configuring JMX
The following property controls c3p0's JMX management interface. Plese see Configuring and Managing c3p0 via JMX above for more information.
- com.mchange.v2.c3p0.management.ManagementCoordinator
Configuring the VMID
Is it better to be beautiful or correct? Beginning with c3p0-0.9.1, c3p0 opts somewhat reluctantly for correctness.
Here's the deal. Every c3p0 DataSource is allocated a unique "identity token", which is used to ensure that multiple JNDI lookups of the same PooledDataSource always return the same instance, even if the JNDI name-server stores a Serialized or Referenced instance. Previously, c3p0 was happy for generated IDs to be unique within a single VM (and it didn't even get that quite right, before c3p0-0.9.1). But in theory, one VM might look up two different DataSources, generated by two different VMs, that by unlikely coincidence have the same "identity token", leading to errors as one of the two DataSources sneakily substitutes for the second. Though this hypothetical issue has never been reported in practice, c3p0 resolves it by prepending a VMID to its identity tokens. This makes them long and ugly, but correct.
If you don't like the long and ugly VMID, you can set your own, or you can turn off this solution to a hypothetical non-problem entirely with the following property:
- com.mchange.v2.c3p0.VMID
Set it to NONE to turn off the VMID, set it to AUTO to let c3p0 generate a VMID, or provide any other String to set the VMID that will be used directly. The default isAUTO.
Experimental properties
c3p0-0.9.1 includes a new implementation of asynchronous Connection acquisition that should improve c3p0's performance and resource utilization in cases where database acquisition attempts, for whatever reason, occasionally fail. The new implementation should be significantly better than the "traditional" Connection acquisition strategy, but was added too late in the c3p0-0.9.1 development cycle to be fully tested and enabled by default. Users are encouraged to try the new implementation, both because it is better, and to help iron out any unanticipated problems.
For a full description of the new implementation and the resource bottleneck it is designed to overcome, please see theCHANGELOG entry forc3p0-0.9.1-pre11.
As of c3p0-0.9.2 this feature is enabled by default. To revert to the traditional Connection acquisition behavior, set the following parameter tofalse.
- com.mchange.v2.resourcepool.experimental.useScatteredAcquireTask
Appendix B: Configuration Files, etc.
c3p0 configuration parameters can be set directly in Java code, via a simple Java properties file, via anXML configuration file, or viaSystem properties. Any which way works (the the XML configuration is most powerful, though, as it supports multiple named configurations and per-user overrides. Choose whatever works best for you.
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 yourCLASSPATH environment variable. For a typical web-application, the file should be placed inWEB-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 (especillygetResource... methods) of java.lang.Class,java.lang.ClassLoader, andjava.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. SeeAppendix A. for the specifics. An examplec3p0.properties file is produced below:
Overriding c3p0 defaults with System properties
c3p0 properties can also be defined as System properties, using the same "c3p0." prefix for properties specified in ac3p0.properties file.
System properties override settings in c3p0.properties. Please see Precedence of Configuration Settings for more information.
Named and Per-User configuration: Overriding c3p0 defaults viac3p0-config.xml
As of c3p0-0.9.1, you can define multiple configurations in an XML configuration file, and specify in your code which configuration to use. For any configurations (including the unnamed default configuration), you can define overrides for a particular database user. For example, if several applications access your database under different authentication credentials, you might definemaxPoolSize to be 100 for userhighVolumeApp, but only 10 for userlowLoadApp. (Recall that Connections associated with different authentication credentials are of necessity separated into separate pools, so it makes sense that these could be configured separately.)
You can use the XML config file for all c3p0 configuration, including configuration of defaults. However, for users who don't want or need the extra complexity, the c3p0.properties file will continue to be supported.
By default, c3p0 will look for an XML configuration file in its classloader's resource path under the name "/c3p0-config.xml". That means the XML file should be placed in a directly or jar file directly named in your applications CLASSPATH, in WEB-INF/classes, or some similar location.
If you prefer not to bundle your configuration with your code, you can specify an ordinary filesystem location for c3p0's configuration file via the system propertycom.mchange.v2.c3p0.cfg.xml. (You can also use this property to specify an alternative location in the ClassLoader resource path, e.g.META-INF. See Locating Configuration Information.)
Here is an example c3p0-config.xml file:
To use a named configuration, you specify the config name when creating your DataSource. For example, usingComboPooledDataSource:
Or using the DataSources factory class:
638
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
