mysql 连接池断开后自动连接

MySQL对所有连接的有效时间默认为28800秒，正好8小时，也就是说，如果一个连接8小时没有请求和操作，就会自动断开；但是对于 Hibernate来说，它的连接池并不知道它所管理的连接中是否有被MySQL断开的。如果一个程序要使用数据库连接，而Hibernte的连接池分配 一个已经被MySQL断开了的给程序使用，那么便会出现错误.以下是proxool连接池的解决方案

数据库连接池proxool(0.91)，它有两个属性：一个是test-before-use ，还有一个是test-after-use，这两个属性就是在使用前和使用后都要进行对连接的检查，如果连接无效就扔掉再创建一个新的连接，它们的解释如下： 
test-before-use :
If you set this to true then each connection is tested (with whatever is defined in house-keeping-test-sql) before being served. If a connection fails then it is discarded and another one is picked. If all connections fail a new one is built. If that one fails then you get an SQLException saying so.
test-after-use:
If you set this to true then each connection is tested (with whatever is defined in house-keeping-test-sql) after it is closed (that is, returned to the connection pool). If a connection fails then it is discarded. 

 由于项目是spring+ibatis+struts

有关proxool的配置片段如下

web.xml

  <servlet-name>ServletConfigurator</servlet-name>
  <servlet-class>
    org.logicalcobwebs.proxool.configuration.ServletConfigurator
  </servlet-class>
  <init-param>
    <param-name>xmlFile</param-name>
    <param-value>WEB-INF/proxool.xml</param-value>
  </init-param>
  <load-on-startup>1</load-on-startup>
</servlet>
 proxool.xml放在WEB-INF下

<?xml version="1.0" encoding="ISO-8859-1"?>
<!-- the proxool configuration can be embedded within your own application's.
Anything outside the "proxool" tag is ignored. -->
<something-else-entirely>
  <proxool>
    <alias>dbname </alias>
    <driver-url></driver-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <driver-properties>
      <property name="user" value=""/>
      <property name="password" value=""/>
    </driver-properties>
    <maximum-connection-count>100</maximum-connection-count>
    <minimum-connection-count>10</minimum-connection-count>
    <house-keeping-sleep-time>90000</house-keeping-sleep-time>
    <maximum-new-connections>10</maximum-new-connections>
    <prototype-count>5</prototype-count>
    <test-before-use>true</test-before-use>  关键代码
    <test-after-use>true</test-after-use> 关键代码
    <house-keeping-test-sql>select 1 from dual</house-keeping-test-sql> 关键代码
  </proxool>
</something-else-entirely>

spring的applicationContext.xml

<bean id="datasource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName"
        value="org.logicalcobwebs.proxool.ProxoolDriver" />
    <property name="url" value="proxool.dbname " /> 和proxool.xml中的alias相同
</bean>

测试OK

同理其他连接池的解决方案

DBCP增加以下配置信息:

//set to 'selecｔ 1'

validationQuery = "selecｔ 1"

//set to 'true'

testWhileIdle = "true"

//some positive integer

timeBetweenEvictionRunsMillis = 3600000 //失效检查线程运行时间间隔，如果小于等于0，不会启动检查线程

//set to something smaller than 'wait_timeout'

minEvictableIdleTimeMillis = 18000000 //大于0 ，进行连接空闲时间判断，或为0，对空闲的连接不进行验证

//if you don't mind a hit for every getConnection(), set to "true"

testOnBorrow = "true"

C3P0增加以下配置信息:

//获取connnection时测试是否有效

testConnectionOnCheckin = true

//自动测试的table名称

automaticTestTable=C3P0TestTable

//set to something much less than wait_timeout, prevents connections from going stale

idleConnectionTestPeriod = 18000   //置一段时间后连接池自动测试执行，保持连接开放，

//set to something slightly less than wait_timeout, preventing 'stale' connections from being handed out

maxIdleTime = 25000

//if you can take the performance 'hit', set to "true"

testConnectionOnCheckout = true