Detailed c3p0 Configuration

From: http://blog.sina.com.cn/s/blog_53345e4c0100b6zk.html

 

Official documentation: Http://www.mchange.com/projects/c3p0/index.html<C3p0-config>
<Default-config>
<! -- The number of connections that c3p0 obtains at the same time when connections in the connection pool are exhausted. Default: 3 -->
<Property name = "acquireincrement"> 3 </property>

<! -- Defines the number of repeated attempts after a new connection fails to be obtained from the database. Default: 30 -->
<Property name = "acquireretryattempts"> 30 </property>

<! -- Interval between two connections, in milliseconds. Default: 1000 -->
<Property name = "acquireretrydelay"> 1000 </property>

<! -- When the connection is closed, all uncommitted operations are rolled back by default. Default: false -->
<Property name = "autocommitonclose"> false </property>

<! -- C3p0 creates an empty table named test and uses its own query statement for testing. If this parameter is defined
The preferredtestquery attribute is ignored. You cannot perform any operation on this test table. It will only be used for c3p0 testing.
. Default: NULL -->
<Property name = "automatictesttable"> test </property>

<! -- Failed to obtain the connection will cause all threads waiting for the connection pool to obtain the connection to throw an exception. However, the data source is still valid.
Retained, and continue to get the connection when getconnection () is called the next time. If it is set to true
After the connection fails, the data source is declared disconnected and permanently closed. Default: false -->
<Property name = "breakafteracquirefailure"> False </property>

<! -- When the connection pool is used up, the client calls getconnection () and waits for the time to obtain the new connection.
Sqlexception. If it is set to 0, it will wait indefinitely. Unit: milliseconds. Default: 0 -->
<Property name = "checkouttimeout"> 100 </property>

<! -- Test the connection by implementing the connectiontester or queryconnectiontester class. The full path must be specified for the class name.
Default: COM. mchange. v2.c3p0. impl. defaultconnectiontester -->
<Property name = "connectiontesterclassnmE "> </property>

<! -- Specify the path of c3p0 libraries. If (usually like this) can be obtained locally, you do not need to set it. The default value is null.
Default: NULL -->
<Property name = "factoryclasslocation"> null </property>

<! -- Stronugly disrecommended. setting this to true may lead to subtle and bizarre bugs.
(Original document) an attribute that the author strongly recommends not to use -->
<Property name = "forceignoreunresolvedtraNsactions "> false </property>

<! -- Check all idle connections in the connection pool every 60 seconds. Default: 0 -->
<Property name = "idleconnectiontestperiod"> 60 </property>

<! -- Three connections are obtained during initialization. The value must be between minpoolsize and maxpoolsize. Default: 3 -->
<Property name = "initialpoolsize"> 3 </property>

<! -- Maximum idle time. connections are dropped if they are not used within 60 seconds. If it is 0, it will never be discarded. Default: 0 -->
<Property name = "maxidletime"> 60 </property>

<! -- The maximum number of connections retained in the connection pool. Default: 15 -->
<Property name = "maxpoolsize"> 15 </property>

<! -- JDBC standard parameter, used to control the number of preparedstatements loaded in the data source. However, due to the pre-Cache statements
Belongs to a single connection pool rather than the entire connection pool. Therefore, you need to consider many factors when setting this parameter.
If maxstatements and maxstatementspersperiIf the on value is 0, the cache is disabled. Default: 0 -->
<Property name = "maxstatements"> 100 </property>

<! -- MaxstatementsperconnectiOn defines the maximum number of statements cached for a single connection in the connection pool. Default: 0 -->
<Property name = "maxstatementsperconnectiOn "> </property>

<! -- C3p0 is asynchronous, and slow JDBC operations are completed by helping the process. Scaling these operations can effectively improve performance
Multiple operations are executed simultaneously through multiple threads. Default: 3 -->
<Property name = "numhelperthreads"> 3 </property>

<! -- When the user calls getconnection (), the root user becomes the user who gets the connection. Used to connect a connection pool to non-c3p0
. Default: NULL -->
<Property name = "overridedefaultuser"> root </property>

<! -- This parameter corresponds to the overridedefaultuser parameter. Default: NULL -->
<Property name = "overridedefaultpassword"> password </property>

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

<! -- Define the test statement executed for all connection tests. This significantly increases the testing speed when connection tests are used. Note:
The test table must exist at the initial data source. Default: NULL -->
<Property name = "preferredtestquery"> select ID from test where id = 1 </property>

<! -- The user can wait up to 300 seconds before modifying system configuration parameters. Default: 300 -->
<Property name = "propertycycle"> 300 </property>

<! -- Because of high performance consumption, use it only when needed. If set to true
Will verify its validity. Idleconnectiontestperiod is recommendedOr automatictesttable
To improve the connection test performance. Default: false -->
<Property name = "testconnectiononcheckout"> False </property>

<! -- If it is set to true, the connection validity will be verified when the connection is obtained. Default: false -->
<Property name = "testconnectiononcheckin"> true </property>

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

<! -- Early versions of c3p0 used dynamic reflection proxy for JDBC interfaces. This parameter is widely used in earlier versions.
Allows the user to recover to the dynamic reflection proxy to solve unstable faults. The latest non-reflection proxy is faster and has started
This parameter may not be useful because it is widely used. Currently, the original dynamic reflection and the new non-reflection proxy are both subject
Yes. However, in future versions, dynamic reflection proxy may not be supported. 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 the Problem of MySQL 8 hours

The latest project uses the connection pool of c3p0 in hibernate, and the database is MySQL. There is no problem with development and testing, and an exception occurs in each idle period of time during running:

Source: (http://blog.sina.com.cn/s/blog_53345e4c0100b6zk.html)-c3p0 detailed configuration _ mashimaro _ Sina Blog

Java code

1. Org. hibernate. Exception. jdbcconnectionexception: cocould not execute Query
2. At org. hibernate. Exception. sqlstateconverter. Convert (sqlstateconverter. Java: 74)
3. At org. hibernate. Exception. jdbcexceptionhelper. Convert (jdbcexceptionhelper. Java: 43)
4. .......
5. Caused by: COM. MySQL. JDBC. Exceptions. mysqlnontransientconnectIonexception: No operations allowed after Connection closed. Connection was implicitly closed due to underlying exception/error:
6. ** Begin nested exception **
7. Com. MySQL. JDBC. communicationsexception
8. Message: communications link failure due to underlying exception:
9. ** Begin nested exception **
10. Java.net. socketexception
11. Message: broken pipe
12. Stacktrace:
13. Java.net. socketexception: broken pipe
14. At java.net. socketoutputstream. socketwrite0 (native method)
15. ......
16. ** End nested exception **

I checked the MySQL documentation, the connector/J documentation, and online instructions. The cause of this exception is:

The default "wait_timeout" of the MySQL server is 8 hours. That is to say, if a connection is idle for more than 8 hours, MySQL will automatically disconnect the connection. This is the problem. If c3ons in c3p0 pools is idle for more than 8 hours, MySQL disconnects it, while c3p0 does not know that the connection has expired. If a client requests a connection, c3p0 provides the invalid connection to the client, which will cause the above exception.

There are three solutions:

1. Increase the wait_timeout time.
2. Reduce the life time of the connection in connection pools.
3. Test the validity of the connection in connection pools.

Of course, the best way is to use the above three methods at the same time. Below we will describe DBCP and c3p0 respectively, assuming wait_timeout is the default 8 hours.

Add the following configuration information to DBCP:

1. // Set to 'select 1'
2. Validationquery = "select 1"
3. // Set to 'true'
4. Testwhileidle = "true"
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 hit for every getconnection (), set to "true"
10. Testonborrow = "true"

Add the following configuration information to c3p0:

1. // Test validity when obtaining connnection
2. Testconnectiononcheckin = true
3. // Name of the automatically tested table
   
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 'stamp' connections from being handed out
8. Maxidletime = 25000
9. // If you can take the performance 'hit', set to "true"
10. Testconnectiononcheckout =True

For more configuration information, see the c3p0 document, connector/J document, and DBCP document.

Turn: 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>

Source: (http://blog.sina.com.cn/s/blog_53345e4c0100b6zk.html)-c3p0 detailed configuration _ mashimaro _ Sina Blog