c3p0详细配置

约定不等于承诺〃 2021-09-15 05:18 329阅读 0赞

[c3p0详细配置][c3p0]

**官方文档 :**  [**http://www.mchange.com/projects/c3p0/index.html**][http_www.mchange.com_projects_c3p0_index.html]

<c3p0-config>  
<default-config>  
  
<property name="acquireIncrement">3</property>  
  
  
<property name="acquireRetryAttempts">30</property>  
  
  
<property name="acquireRetryDelay">1000</property>  
  
  
<property name="autoCommitOnClose">false</property>  
  
  
<property name="automaticTestTable">Test</property>  
  
  
<property name="breakAfterAcquireFailure ">false</property>  
  
  
<property name="checkoutTimeout">100</property>  
  
  
<property name="connectionTesterClassNam e"></property>  
  
  
<property name="factoryClassLocation">null</property>  
  
  
<property name="forceIgnoreUnresolvedTra nsactions">false</property>  
  
  
<property name="idleConnectionTestPeriod ">60</property>  
  
  
<property name="initialPoolSize">3</property>  
  
  
<property name="maxIdleTime">60</property>  
  
  
<property name="maxPoolSize">15</property>  
  
  
<property name="maxStatements">100</property>  
  
  
<property name="maxStatementsPerConnecti on"></property>  
  
  
<property name="numHelperThreads">3</property>  
  
  
<property name="overrideDefaultUser">root</property>  
  
  
<property name="overrideDefaultPassword">password</property>  
  
  
<property name="password"></property>  
  
  
<property name="preferredTestQuery">select id from test where id=1</property>  
  
  
<property name="propertyCycle">300</property>  
  
  
<property name="testConnectionOnCheckout ">false</property>  
  
  
<property name="testConnectionOnCheckin">true</property>  
  
  
<property name="user">root</property>  
  
  
<property name="usesTraditionalReflectiv eProxies">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>  
  
  
转： [http://www.wujianrong.com/archives/2007/08/c3p0.html][http_www.wujianrong.com_archives_2007_08_c3p0.html]

解决MYSQL 8小时问题

最近的一个项目在Hibernate使用C3P0的连接池，数据库为Mysql。开发测试没有问题，在运行中每个一段长的空闲时间就出现异常:

java 代码

1.  org.hibernate.exception.JDBCConnectionException: could 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 \*\*

查看了Mysql的文档，以及Connector/J的文档以及在线说明发现，出现这种异常的原因是：

Mysql服务器默认的“wait\_timeout”是8小时，也就是说一个connection空闲超过8个小时，Mysql将自动断开该 connection。这就是问题的所在，在C3P0 pools中的connections如果空闲超过8小时，Mysql将其断开，而C3P0并不知道该connection已经失效，如果这时有 Client请求connection，C3P0将该失效的Connection提供给Client，将会造成上面的异常。

解决的方法有3种：

1.  增加wait\_timeout的时间。
2.  减少Connection pools中connection的lifetime。
3.  测试Connection pools中connection的有效性。

当然最好的办法是同时综合使用上述3种方法，下面就DBCP和C3P0分别做一说明，假设wait\_timeout为默认的8小时

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"

C3P0增加以下配置信息:

1.  //获取connnection时测试是否有效
2.  testConnectionOnCheckin = true
3.  //自动测试的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 'stale' connections from being handed out
8.  maxIdleTime = 25000
9.  //if you can take the performance 'hit', set to "true"
10. testConnectionOnCheckout = **true**

更多的配置信息大家可以查看C3P0文档，Connector/J文档，以及DBCP的文档。

转: [http://www.javaeye.com/article/38506][http_www.javaeye.com_article_38506]

我自己的配置:

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>

[c3p0]: http://www.blogjava.net/Alpha/archive/2009/03/29/262789.html
[http_www.mchange.com_projects_c3p0_index.html]: http://www.mchange.com/projects/c3p0/index.html
[http_www.wujianrong.com_archives_2007_08_c3p0.html]: http://www.wujianrong.com/archives/2007/08/c3p0.html
[http_www.javaeye.com_article_38506]: http://www.javaeye.com/article/38506