Detailed configuration of the C3P0 (reproduced herein)

Official documents: http://www.mchange.com/projects/c3p0/index.html<c3p0-config>
<default-config>
<!--c3p0 The number of connections that are fetched at the same time when the connection in the connection pool is exhausted. Default:3--
<property name= "Acquireincrement" >3</property>

<!--defines the number of repeated attempts to obtain a new connection from the database after a failure. Default:30 --
<property name= "Acquireretryattempts" >30</property>

<!--the interval between two connections, in milliseconds. default:1000 --
<property name= "Acquireretrydelay" >1000</property>

< All uncommitted actions are rolled back by default when the connection is closed!--. Default:false --
<property name= "Autocommitonclose" >false</property>

<!--c3p0 will build a blank table named Test and test it with its own query statement. If this parameter is defined, then
Property Preferredtestquery will be ignored. You cannot do anything on this test sheet, it will be for C3P0 testing only
Use. Default:null-->
<property name= "Automatictesttable" >Test</property>

<!--getting a connection failure will cause any thread that waits for the connection pool to get the connection to throw an exception. But the data source is still valid
Reserved and continue trying to get the connection the next time you call Getconnection (). If set to true, then try to
When a connection failure is obtained, the data source declares that it has been fractured and closed permanently. Default:false-->
<property name= "Breakafteracquirefailure" >false</property>

<!--The time when the client calls getconnection () after the connection pool has run out, and then throws
SQLException, if set to 0, waits indefinitely. Unit milliseconds. Default:0--
<property name= "Checkouttimeout" >100</property>

<!--by implementing classes of Connectiontester or Queryconnectiontester totest the connection. The class name needs to be set to the full path.
Default:com.mchange.v2.c3p0.impl.defaultconnectiontester-->
<property name= "Connectiontesterclassname" ></property>

<!--Specify the path to the C3P0 libraries, if (as is often the case) you can get it locally without setting, default null
Default:null-->
<property name= "Factoryclasslocation" >null</property>

<!--strongly disrecommended. Setting this to true may leads to subtle and bizarre bugs.
Author strongly recommends not to use a property--
<property name= "Forceignoreunresolvedtransactions" >false</property>

<!--Check for idle connections in all connection pools every 60 seconds. Default:0--
<property name= "Idleconnectiontestperiod" >60</property>

<!--get three connections when initializing, the value should be between Minpoolsize and Maxpoolsize. Default:3--
<property name= "Initialpoolsize" >3</property>

<!--maximum idle time, unused in 60 seconds, the connection is discarded. If 0, it will never be discarded. Default:0--
<property name= "MaxIdleTime" >60</property>

<!--The maximum number of connections that are kept in the connection pool. Default:15--
<property name= "Maxpoolsize" >15</property>

<!--the standard parameters of JDBC to control the number of preparedstatements loaded within the data source. But due to the statements of the pre-cache
belong to a single connection instead of the entire connection pool. So setting this parameter takes into account a variety of factors.
If both maxstatements and maxstatementsperconnection are 0, the cache is closed. Default:0-->
<property name= "Maxstatements" >100</property>

<!--maxstatementsperconnection defines the maximum number of cache statements that a single connection in a connection pool has. Default:0--
<property name= "Maxstatementsperconnection" ></property>

<!--c3p0 is asynchronous, and slow JDBC operations are done through the help process. Extending these operations can effectively improve performance
Multiple operations are performed at the same time through multithreading. Default:3-->
<property name= "Numhelperthreads" >3</property>

<!--When a user calls getconnection () to make the root user the user who is going to get the connection. Primarily for connection pooling connections non-C3P0
Data source. Default:null-->
<property name= "Overridedefaultuser" >root</property>

<!--A parameter that corresponds to the Overridedefaultuser parameter. Default:null-->
<property name= "Overridedefaultpassword" >password</property>

<!--password. Default:null-->
<property name= "Password" ></property>

<!--Define test statements that are executed by all connection tests. This one significantly improves the test speed in the case of connection testing. Attention:
The test table must exist at the time of the initial data source. Default:null-->
<property name= "preferredtestquery" >select ID from test where id=1</property>

<!--the user to wait up to 300 seconds before modifying the system configuration parameters. DEFAULT:300--
<property name= "Propertycycle" >300</property>

<!--use it only when you need it, due to its high performance consumption. If set to true then each connection commits the
Officer the validity of the test. It is recommended to use Idleconnectiontestperiod or automatictesttable
and other methods to improve the performance of the connection test. Default:false--
<property name= "Testconnectiononcheckout" >false</property>

<!--If set to true then officer the validity of the connection while the connection is made. Default:false--
<property name= "Testconnectiononcheckin" >true</property>

<!--user name. Default:null-->
<property name= "User" >root</property>

<!--Earlier versions of C3P0 use dynamic reflection proxies for JDBC interfaces. In the case of a wide range of previous versions, this parameter
Allows the user to revert to the dynamic reflection agent to resolve an unstable failure. The newest non-reflection agent is faster and has started
Widely used, so this parameter may not be useful. Now the original dynamic reflection and the new non-reflection agent are
Supported, but possible future versions may not support dynamic reflection agents. Default:false-->
<property name= "Usestraditionalreflectiveproxies" >false</property>

<property name= "Automatictesttable" >con_test</property>
<property name= "Checkouttimeout" >30000</property>
<property name= "Idleconnectiontestperiod" >30</property>
<property name= "Initialpoolsize" >10</property>
<property name= "MaxIdleTime" >30</property>
<property name= "Maxpoolsize" >25</property>
<property name= "Minpoolsize" >10</property>
<property name= "Maxstatements" >0</property>
<user-overrides user= "Swaldman" >
</user-overrides>
</default-config>
<named-config name= "Dumbtestconfig" >
<property name= "Maxstatements" >200</property>
<user-overrides user= "Poop" >
<property name= "Maxstatements" >300</property>
</user-overrides>
</named-config>
</c3p0-config>


Turn:http://www.wujianrong.com/archives/2007/08/c3p0.htmlSolve MySQL 8-hour problem

A recent project in Hibernate uses the C3P0 connection pool, which is MySQL database. There is no problem with development testing, and there is an exception for each long idle time in the run:

Java code

2. Org.hibernate.exception.JDBCConnectionException:could not execute Query
4. At Org.hibernate.exception.SQLStateConverter.convert (Sqlstateconverter.java:)
6. At Org.hibernate.exception.JDBCExceptionHelper.convert (Jdbcexceptionhelper.java: +)
8. .......
10. caused By:com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:No operations allowed after connection Closed. Connection is implicitly closed due to underlying exception/error:
12. * * BEGIN NESTED EXCEPTION * *
14. Com.mysql.jdbc.CommunicationsException
16. Message:communications link failure due to underlying exception:
18. * * BEGIN NESTED EXCEPTION * *
20. Java.net.SocketException
22. Message:broken Pipe
24. STACKTRACE:
26. Java.net.SocketException:Broken Pipe
28. At Java.net.SocketOutputStream.socketWrite0 (Native Method)
30. ......
32. * * END NESTED EXCEPTION * *

Viewed the MySQL documentation, as well as the documentation for CONNECTOR/J and the online instructions found that the reason for this exception is:

MySQL server default "Wait_timeout" is 8 hours, which means that a connection idle for more than 8 hours, MySQL will automatically disconnect the connection. This is the problem, in the C3P0 pools connections if idle for more than 8 hours, MySQL will disconnect it, and c3p0 do not know that the connection has failed, if there is a client request connection, C3P0 the failure of the connection to the client, will cause the above exception.

There are 3 ways to solve this problem:

1. Increase the time of wait_timeout.
2. Reduce the lifetime of connection in connection pools.
3. Test the effectiveness of connection in connection pools.

Of course, the best way is the combination of the above 3 methods, the following DBCP and C3P0 to do a description, assuming wait_timeout for the default of 8 hours

DBCP Add the following configuration information:

1. //set to ' select 1 '
2. validationquery = 
3. //set to ' true '
4. testwhileidle = 
5. //some positive integer
6. Timebetweenevictionrunsmillis = 3600000
7. //set to something smaller than ' wait_timeout '
8. minevictableidletimemillis =  18000000
9. //if You don't mind a hits for Every getconnection (), set to "true"
10. testonborrow =  "true"

C3P0 Add the following configuration information:

1. Whether the test is valid when getting connnection
2. Testconnectiononcheckin = True
3. Table name of the automated test
4. Automatictesttable=c3p0testtable
5. Set to something much less than wait_timeout, prevents connections from going stale
6. Idleconnectiontestperiod = 18000
7. Set to something slightly less than wait_timeout, preventing ' stale ' connections from being handed out
8. MaxIdleTime = 25000
9. If you can take the performance ' hits ', set to ' true '
10. Testconnectiononcheckout = true

For more configuration information, you can view C3P0 documents, connector/j documents, and DBCP documents.

Ext.: http://www.javaeye.com/article/38506

My own configuration:

Jdbc.driverclass=com.mysql.jdbc.driver
Jdbc.jdbcurl = Jdbc:mysql://localhost:3306/test
Jdbc.user = root
Jdbc.password = 12345
Jdbc.minipoolsize = 1
Jdbc.maxpoolsize = 20
Jdbc.initialpoolsize = 1
Jdbc.maxidletime = 25000
Jdbc.acquireincrement = 1

Jdbc.acquireretryattempts = 30
Jdbc.acquireretrydelay = 1000
Jdbc.testconnectiononcheckin = True
Jdbc.automatictesttable = c3p0testtable
Jdbc.idleconnectiontestperiod = 18000
jdbc.checkouttimeout=3000

<bean id= "DataSource" class= "Com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method= "Close" >
<property name= "Driverclass" value= "${jdbc.driverclass}"/>
<property name= "Jdbcurl" value= "${jdbc.jdbcurl}"/>
<property name= "user" value= "${jdbc.user}"/>
<property name= "Password" value= "${jdbc.password}"/>
<property name= "minpoolsize" value= "${jdbc.minipoolsize}"/>
<property name= "maxpoolsize" value= "${jdbc.maxpoolsize}"/>
<property name= "initialpoolsize" value= "${jdbc.initialpoolsize}"/>
<property name= "MaxIdleTime" value= "${jdbc.maxidletime}"/>
<property name= "acquireincrement" value= "${jdbc.acquireincrement}"/>

<property name= "acquireretryattempts" value= "${jdbc.acquireretryattempts}"/>
<property name= "Acquireretrydelay" value= "${jdbc.acquireretrydelay}"/>
<property name= "Testconnectiononcheckin" value= "${jdbc.testconnectiononcheckin}"/>
<property name= "automatictesttable" value= "${jdbc.automatictesttable}"/>
<property name= "Idleconnectiontestperiod" value= "${jdbc.idleconnectiontestperiod}"/>
<property name= "Checkouttimeout" value= "${jdbc.checkouttimeout}"/>

</bean>

Detailed configuration of the C3P0 (reproduced herein)