每日访问数据库失败问题

一、

经过N多试验，终于自己把这个问题搞定了。

网上关于C3P0在spring中的配置，几乎没有完全正确的（至少我还没发现）。查了c3p0的文档，又试验过N次。得出如下配置是正确的：

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"   destroy-method="close">         <property name="driverClass"><value>${jdbc.driverClassName}</value></property>         <property name="jdbcUrl"><value>${jdbc.url}</value></property>                        <property name="user"><value>${jdbc.username}</value></property>         <property name="password"><value>${jdbc.password}</value></property>                   <property name="minPoolSize"><value>1</value></property>         <property name="maxPoolSize"><value>20</value></property>         <property name="maxIdleTime"><value>1800</value></property>         <property name="acquireIncrement"><value>2</value></property>         <property name="maxStatements"><value>0</value></property>         <property name="initialPoolSize"><value>2</value></property>         <property name="idleConnectionTestPeriod"><value>1800</value></property>         <property name="acquireRetryAttempts"><value>30</value></property>         <property name="breakAfterAcquireFailure"><value>true</value></property>         <property name="testConnectionOnCheckout"><value>false</value></property>                  <!--             <property name="properties">          <props>                            <prop key="c3p0.minPoolSize">1</prop>               <prop key="c3p0.maxPoolSize">10</prop>               <prop key="c3p0.maxIdleTime">1800</prop>                            <prop key="c3p0.acquireIncrement">2</prop>               <prop key="c3p0.maxStatements">0</prop>                     <prop key="c3p0.initialPoolSize">2</prop>              <prop key="c3p0.idleConnectionTestPeriod">1800</prop>              <prop key="c3p0.acquireRetryAttempts">30</prop>              <prop key="c3p0.breakAfterAcquireFailure">true</prop>              <prop key="c3p0.testConnectionOnCheckout">true</prop>              <prop key="user">root</prop>              <prop key="password">999999</prop>                        </props>         </property>        -->      </bean><!-- Hibernate SessionFactory --><bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">  <property name="dataSource" ref="dataSource"/>  <property name="mappingDirectoryLocations">      <list>   <value>classpath:/com/licaionline/domain/</value>      </list>  </property>  <property name="hibernateProperties">   <props>    <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>     <prop key="hibernate.show_sql">true</prop>    <prop key="hibernate.generate_statistics">true</prop>                <prop key="hibernate.connection.release_mode">auto</prop>                                      <prop key="hibernate.autoReconnect">true</prop>                <prop key="hibernate.cglib.use_reflection_optimizer">true</prop>                <!--     <prop key="hibernate.useUnicode"></prop>     <prop key="hibernate.characterEncoding"></prop>    <prop key="hibernate.default-lazy-init"></prop>                <prop key="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>         -->                                                <!--                <prop key="hibernate.c3p0.acquire_increment">2</prop>                <prop key="hibernate.c3p0.idle_test_period">1800</prop>                <prop key="hibernate.c3p0.timeout">1800</prop>                <prop key="hibernate.c3p0.max_size">30</prop>                <prop key="hibernate.c3p0.min_size">2</prop>                <prop key="hibernate.c3p0.max_statements">50</prop>    -->            </props>  </property>

注意：注释掉的那些，都是错误的。网上流行的，基本上跟注释掉的那些差不多。配错了，并无异常，还是能正常使用。但是所作的配置不起作用。


二、

起作用的，是datasource里面的这一句：

<property name="maxIdleTime"><value>1800</value></property>

这儿设置成每隔1800秒就扫描一次，检查一下空闲的链接。所以，用户基本上不会得到空闲的链接了。

如果再不放心，

<property name="testConnectionOnCheckout"><value>false</value></property>

这儿设置成true。每次连接之前，都要测一下。但是这样会影响效率。


三、

解决的方法有3种：

增加wait_timeout的时间。 
减少Connection pools中connection的lifetime。 
测试Connection pools中connection的有效性。 
当然最好的办法是同时综合使用上述3种方法，下面就DBCP和C3P0分别做一说明，假设wait_timeout为默认的8小时

DBCP增加以下配置信息:

//set to 'SELECT 1'    validati   //set to 'true'    testWhileIdle = "true"     //some positive integer    timeBetweenEvictionRunsMillis = 3600000   //set to something smaller than 'wait_timeout'    minEvictableIdleTimeMillis = 18000000   //if you don't mind a hit for every getConnection(), set to "true"    

C3P0增加以下配置信息:

//set to 'SELECT 1'       preferredTestQuery = 'SELECT 1'     //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   

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