C3p0使用及参数配置

import java.sql.Connection;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class GetC3p0Connection
{
	static final String url = "jdbc:h2:tcp://localhost/~/db";
	static final String user = "sa";
	static final String pass = "";
	static final String driver = "org.h2.Driver";

	public static void main(String[] args) throws Exception
	{
		ComboPooledDataSource cpds = new ComboPooledDataSource();
		// cpds.setDriverClass("net.sourceforge.jtds.jdbc.Driver");
		// cpds.setJdbcUrl("jdbc:jtds:sqlserver://127.0.0.1:1433/mesplatformnew;SelectMethod=Cursor");
		// cpds.setUser("sa");
		// cpds.setPassword("hust81");
		cpds.setDriverClass(driver);
		cpds.setJdbcUrl(url);
		cpds.setUser(user);
		cpds.setPassword(pass);
		
		//Basic Pool Configuration
		cpds.setMinPoolSize(3);//Default: 3
		cpds.setMaxPoolSize(50);//Default: 15 ensure that minPoolSize <= maxPoolSize.
		cpds.setAcquireIncrement(3);//默认值3,一次获取的连接个数
		cpds.setInitialPoolSize(3);//Default: 3

		//Managing Pool Size and Connection Age. 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. Setting maxConnectionAge or maxIdleTime to 1800 (30 minutes) is quite aggressive. For most databases, several hours may be more appropriate. 
		//By default, pools will never expire Connections.In order to maintain "freshness", set maxIdleTime and/or maxConnectionAge.
		//You can ensure the reliability of your Connections by testing them, rather than by tossing them. 
		cpds.setMaxIdleTime(600);//Default: 0 how many seconds a Connection should be permitted to go unused before being culled from the pool. 
		cpds.setMaxConnectionAge(1800);//Default: 0 forces the pool to cull any Connections that were acquired from the database more than the set number of seconds in the past. 
		//The only one of these parameters that should generally be set to a few minutes or less is maxIdleTimeExcessConnections. 
		cpds.setMaxIdleTimeExcessConnections(60);//Default: 0   much shorter than maxIdleTime, forcing Connections beyond your set minimum size to be released if they sit idle for more than a short period of time. 

		//Configuring Connection Testing
		// 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. 
		//idleConnectionTestPeriod, testConnectionOnCheckout, and testConnectionOnCheckin control when Connections will be tested. 
		//automaticTestTable, connectionTesterClassName, and preferredTestQuery control how they will be tested. 
		//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 regardless of the database schema. However, empirically a DatabaseMetaData.getTables() call is often much slower than a simple database query. 
		cpds.setAutomaticTestTable("test_c3p0_db");//默认为null,speed up Connection testing set table. c3p0 will create an empty table
		cpds.setConnectionTesterClassName("com.mchange.v2.c3p0.impl.DefaultConnectionTester");//默认值 com.mchange.v2.c3p0.impl.DefaultConnectionTester
		cpds.setIdleConnectionTestPeriod(30);//Default: 0  if greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.
		// idleConnectionTestPeriod and testConnectionsOnCheckIn. Both the idle test and the check-in test are performed asynchronously, which leads to better performance, both perceived and actual. 
		cpds.setPreferredTestQuery("select now();");//Default: null Defines the query that will be executed for all connection tests
		//本注释是针对下面二个参数.Better choice: verify connections periodically using idleConnectionTestPeriod. Also, setting an automaticTestTable or preferredTestQuery will usually speed up all connection tests.
		cpds.setTestConnectionOnCheckin(false);//Default: false   If true, an operation will be performed asynchronously at every connection checkin to verify that the connection is valid. 
		cpds.setTestConnectionOnCheckout(false);//Default: false  Use only if necessary. Expensive. If true, an operation will be performed at every connection checkout to verify that the connection is valid.  
		
		//Configuring Statement Pooling
		//Under some circumstances, statement pooling can dramatically improve application performance. Under other circumstances, the overhead of statement pooling can slightly harm performance. 
		cpds.setMaxStatements(100);//Default: 0  defines the total number PreparedStatements a DataSource will cache. maxStatements is JDBC's standard parameter for controlling statement pooling. 
		cpds.setMaxStatementsPerConnection(20);//Default: 0 a non-standard configuration parameter.defines how many statements each pooled Connection is allowed to own.
		//MaxStatements  & MaxStatementsPerConnection  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. 
		//statementCacheNumDeferredCloseThreads	Default: 0
		
		//Configuring Recovery From Database Outages,	stale Connections
		// Setting maxIdleTime or maxConnectionAge can help speed up the replacement of broken Connections.
		cpds.setAcquireRetryAttempts(30);//默认30尝试获取连接的次数.
		cpds.setAcquireRetryDelay(1000);//默认1000，Milliseconds 在acquire attempts.中的时间间隔
		cpds.setBreakAfterAcquireFailure(false);//默认False 获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效保留，并在下次调用getConnection()的时候继续尝试获取连接。如果设为true，那么在尝试获取连接失败后该数据源将申明已断开并永久关闭。
		
		//Managing Connection Lifecycles with Connection Customizer
		//connectionCustomizerClassName   Default: null 
		
		//Configuring Unresolved Transaction Handling
		cpds.setAutoCommitOnClose(true);//默认false,By default, c3p0 rolls back unresolved transactional work when a user calls close(). 
		cpds.setForceIgnoreUnresolvedTransactions(false);//strongly discouraged, because if clients are not careful to commit or rollback themselves prior to close(), 
		
		//Configuring to Debug and Workaround Broken Client Applications
		//The right way to address this problem is to fix the client application. c3p0 can help you debug,
		cpds.setDebugUnreturnedConnectionStackTraces(true);//默认false, intended to be used only for debugging, then a stack trace will be captured each time a Connection is checked-out. 
		cpds.setUnreturnedConnectionTimeout(60);//Default: 0  secondes   how long a Connection may remain checked out. 
		
		//Other DataSource Configuration
		//numHelperThreads and maxAdministrativeTaskTime help to configure the behavior of DataSource thread pools. 
		//checkoutTimeout limits how long a client will wait for a Connection,
		cpds.setCheckoutTimeout(60);//默认值 0, getConnection()后等待获取新连接的时间，超时后将抛出SQLException,如设为0则无限期等待。单位毫秒。
//		factoryClassLocation
//		maxAdministrativeTaskTime  Default: 0 Seconds before c3p0's thread pool will try to interrupt an apparently hung task.
//		numHelperThreads  Default: 3
		//overrideDefaultUser	Default: null
		//overrideDefaultPassword	Default: null
		//password	Default: null
		//user	Default: null
		//propertyCycle	Default: 0
		
		//dataSourceName		Default: if configured with a named config, the config name, otherwise the pool's "identity token"
		//factoryClassLocation	Default: null
		
		//Configuring and Managing c3p0 via JMX
		//com.mchange.v2.c3p0.management.ManagementCoordinator=com.mchange.v2.c3p0.management.NullManagementCoordinator 
		//com.mchange.v2.c3p0.management.ManagementCoordinator  
		
		//Configuring the VMID
		//Every c3p0 DataSource is allocated a unique "identity token",  com.mchange.v2.c3p0.VMID
		
		//Performance
		//there are performance costs to c3p0 as well. In order to implement automatic cleanup of unclosed ResultSets and Statements when parent resources are returned to pools, all client-visible Connections, ResultSets, Statements are really wrappers around objects provided by an underlying unpooled DataSource or "traditional" JDBC driver. Thus, there is some extra overhead to all JDBC calls. 
		
		//Known Shortcomings
		//1.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, 
		//2.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.
		
		//useage  ComboPooledDataSource cpds = new ComboPooledDataSource("intergalactoApp"); 
		// DataSource ds_pooled = DataSources.pooledDataSource( ds_unpooled, "intergalactoApp" );
		
		Connection c = cpds.getConnection();
		System.out.println(c);
		// PoolBackedDataSource pd = cpds.pbds;

		Properties p = cpds.getProperties();
		Set<Map.Entry<Object, Object>> ts = p.entrySet();
		for (Map.Entry<Object, Object> t : ts)
		{
			System.out.println(t.getKey() + "_" + t.getValue());
		}

		// DataSources.destroy(cpds);

	}
}

 

 使用示例

/**
	 * PooledDataSource  close
	 * @throws SQLException
	 */
	static void PooledDataSource() throws SQLException
	{
		DataSource ds = null;
		try
		{
			DataSource ds_unpooled = DataSources.unpooledDataSource(url, user, pass);
			ds = DataSources.pooledDataSource(ds_unpooled);
			System.out.println(ds);
			System.out.println(ds.getConnection());
			// do all kinds of stuff with that sweet pooled DataSource...
		} finally
		{
			if (ds instanceof PooledDataSource)
			{
				PooledDataSource pds = (PooledDataSource) ds;
				pds.close();
				System.out.println("PooledDataSource closed");
			} else
				System.err.println("Not a c3p0 PooledDataSource!");

		}
		// make sure it's a c3p0 PooledDataSource

	}

	static void DataSourceClose() throws SQLException
	{
		DataSource ds_pooled = null;
		try
		{
			DataSource ds_unpooled = DataSources.unpooledDataSource(url, user, pass);
			ds_pooled = DataSources.pooledDataSource(ds_unpooled);
			System.out.println(ds_pooled);
			System.out.println(ds_pooled.getConnection());
			// do all kinds of stuff with that sweet pooled DataSource...
		} finally
		{
			DataSources.destroy(ds_pooled);// static destroy method

		}
	}

 

配置

Configuring c3p0 DataSources in Tomcat
	<Resource name="jdbc/pooledDS" auth="Container"
		type="com.mchange.v2.c3p0.ComboPooledDataSource" />
	<ResourceParams name="jdbc/pooledDS">
		<parameter>
			<name>factory</name>
			<value>org.apache.naming.factory.BeanFactory</value>
		</parameter>
		<parameter>
			<name>driverClass</name>
			<value>org.postgresql.Driver</value>
		</parameter>
		<parameter>
			<name>jdbcUrl</name>
			<value>jdbc:postgresql://localhost/c3p0-test</value>
		</parameter>
		<parameter>
			<name>user</name>
			<value>swaldman</value>
		</parameter>
		<parameter>
			<name>password</name>
			<value>test</value>
		</parameter>
		<parameter>
			<name>minPoolSize</name>
			<value>5</value>
		</parameter>
		<parameter>
			<name>maxPoolSize</name>
			<value>15</value>
		</parameter>
		<parameter>
			<name>acquireIncrement</name>
			<value>5</value>
		</parameter>
	</ResourceParams>


Tomcat 5.5	
	declare your DataSource reference in your web.xml file: 
	<resource-ref>
		<res-ref-name>jdbc/pooledDS</res-ref-name>
		<res-type>javax.sql.DataSource</res-type>
		<res-auth>Container</res-auth>
	</resource-ref>
	
access your DataSource from code within your web application like this: 
	InitialContext ic = new InitialContext(); 
	DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/pooledDS");

 

LGPLV2 or EPL 
http://www.mchange.com/projects/c3p0/
c3p0-0.9.2.1.jar + mchange-commons-java-0.2.3.4.jar 即可使用c3p0连接池
c3p0 requires a level 1.3.x or above Java Runtime Environment, and the JDBC 2.x or above javax.sql libraries.
c3p0 works fine under Java 1.4.x and 1.5.x as well.