在数据库重启之后无法重新连接数据，无法正常提供服务问题解决

来源：互联网发布：python自动化测试代码编辑：程序博客网时间：2024/04/27 13:30

在Tomcat中配置c3p0数据库连接池的时候，如果数据库重启，或者网络原因造成服务器和数据库断开连接，Tomcat便再也不能和数据库连接，除非Tomcat服务重启。

解决办法是在c3p0的配置中增加两个参数

<parameter>
<name>testConnectionOnCheckin</name>
<value>true</value>
</parameter>
<parameter>
<name>idleConnectionTestPeriod</name>
<value>60</value>
</parameter>

这样配置之后，连接池每隔60秒自动检测数据库连接情况，如果断开则自动重连。

\etc\conf\spring\applicationcontext-datasource.xml

具体：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
   xmlns:aop="http://www.springframework.org/schema/aop"
   xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
   http://www.springframework.org/schema/tx
   http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
   http://www.springframework.org/schema/aop
   http://www.springframework.org/schema/aop/spring-aop-2.5.xsd"
   default-lazy-init="true">
   <bean
       class="com.xxxxxx.dhm.portalMS.common.util.PortalMSPropertyPlaceholderConfigurer">
       <property name="locations">
           <list>
               <value>datasource.properties</value>
           </list>
       </property>
   </bean>
   
   <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
       destroy-method="close">
       <property name="driverClass">
           <value>${db.driverclass}</value>
       </property>
       <property name="jdbcUrl">
           <value>${db.jdbcurl}</value>
       </property>
       <property name="user">
           <value>${db.user}</value>
       </property>
       <property name="password">
           <value>${db.password}</value>
       </property>
       <property name="minPoolSize">
           <value>${db.minpoolsize}</value>
       </property>
       <property name="maxPoolSize">
           <value>${db.maxpoolsize}</value>
       </property>
       <property name="initialPoolSize">
           <value>${db.initialpoolsize}</value>
       </property>
       <property name="maxIdleTime">
           <value>${db.maxidletime}</value>
       </property>
       <property name="acquireIncrement">
           <value>${db.acquireincrement}</value>
       </property>
       <property name="maxStatements">
           <value>${db.maxstatements}</value>
       </property>
       <property name="testConnectionOnCheckin">
           <value>${db.testConnectionOnCheckin}</value>
       </property>
       <property name="idleConnectionTestPeriod">
           <value>${db.idleconnectiontestperiod}</value>
       </property>
       <property name="acquireRetryAttempts">
           <value>${db.acquireretryattempts}</value>
       </property>
       <property name="breakAfterAcquireFailure">
           <value>${db.breakafteracquirefailure}</value>
       </property>
       <property name="testConnectionOnCheckout">
           <value>${db.testconnectiononcheckout}</value>
       </property>
       <property name="numHelperThreads">
           <value>${db.numhelperthreads}</value>
       </property>
       <property name="maxStatementsPerConnection">
           <value>${db.maxstatementsperconnection}</value>
       </property>
       <property name="debugUnreturnedConnectionStackTraces">
           <value>${db.debugunreturnedconnectionstacktraces}</value>
       </property>
       <property name="description">
           <value>${db.description}</value>
       </property>
       <property name="checkoutTimeout">
           <value>${db.checkouttimeout}</value>
       </property>
       <property name="autoCommitOnClose">
           <value>${db.autocommitonclose}</value>
       </property>
       <property name="acquireRetryDelay">
           <value>${db.acquireretrydelay}</value>
       </property>
   </bean>

   <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
       <property name="configLocation">
           <value>
               classpath:ibatis/${dbType}/sqlMapConfig.xml
            </value>
       </property>
       <property name="dataSource" ref="dataSource" />
   </bean>
   <bean id="dialect" class="com.xxxxxx.dhm.portalMS.base.dialect.${dialect}">
       <property name="limit" value="true" />
   </bean>
   <bean id="sqlExecutor" class="com.xxxxxx.dhm.portalMS.base.executor.LimitSqlExecutor"
       scope="prototype">
       <property name="dialect" ref="dialect" />
       <property name="enableLimit" value="true"></property>
   </bean>
   <bean id="baseDao" abstract="true"
       class="com.xxxxxx.dhm.portalMS.base.dao.ibatis.IbatisDAO" init-method="initialize">
       <property name="sqlMapClient">
           <ref bean="sqlMapClient" />
       </property>
       <property name="sqlExecutor">
           <ref bean="sqlExecutor" />
       </property>
   </bean>

   <bean id="iBatisTransactionManager"
       class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
       <property name="dataSource" ref="dataSource" />
   </bean>
   <tx:advice id="txAdvice" transaction-manager="iBatisTransactionManager">
       <tx:attributes>
       
           <tx:method name="*" propagation="REQUIRED" rollback-for="Exception"></tx:method>
       </tx:attributes>
   </tx:advice>

   <aop:config>
       <aop:pointcut id="tesConntpoint"
           expression="execution(* com.xxxxxx.dhm.portalMS.*.dao..*.*(..)) or execution(* com.xxxxxx.dhm.portalMS.*.service..*.*(..)) " />
       <aop:pointcut id="iepgPoint"
           expression="execution(* com.xxxxxx.dhm.portalMS.*.service..*.*(..)) or execution(* com.xxxxxx.createPortal.*.service..*.*(..)) or execution(* com.xxxxxx.dhm.common.uif.service.impl.MessageSyncServiceImpl.*(..)) " />
       <aop:pointcut id="logpoint"
           expression="execution(* com.xxxxxx.dhm.portalMS.*.service..*.*(..))
           or execution(* com.xxxxxx.dhm.portalMS.*.dao..*.*(..))" />
       <aop:advisor advice-ref="txAdvice" pointcut-ref="iepgPoint" />
       <aop:aspect id="logging" ref="logAspect">
           <aop:around pointcut-ref="logpoint" method="invoke" />
       </aop:aspect>
       <aop:aspect id="testConn" ref="testConnAspect">
           <aop:before method="before" pointcut-ref="tesConntpoint" />
       </aop:aspect>
   </aop:config>
   <bean id="testConnAspect" class="com.xxxxxx.dhm.portalMS.common.aop.JdbcTestAspect" />
   <bean id="logAspect" class="com.xxxxxx.dhm.portalMS.common.aop.LogAspect" />


   <bean id="serviceParaCheckInterceptor"
       class="com.xxxxxx.miss.util.intercaptor.ServiceParaCheckInterceptor">
   </bean>

   <bean id="RegexAdvisor"
       class="org.springframework.aop.support.RegexpMethodPointcutAdvisor">
       <property name="advice">
           <ref local="serviceParaCheckInterceptor" />
       </property>
       <property name="patterns">
           <list>
               <value>com.xxxxxx.dhm.portalMS.sync.portalms.service.*\.execute</value>
               <value>com.xxxxxx.dhm.portalMS.sync.service.*\.execute</value>
               <value>com.xxxxxx.dhm.portalMS.sync.action.*\.execute</value>
               <value>com.xxxxxx.dhm.portalMS.sync.cms.*\.execute</value>
               <value>com.xxxxxx.dhm.portalMS.sync.sme.*\.execute</value>
           </list>
       </property>
   </bean>

   <bean id="paraCheckAop"
       class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
       <property name="beanNames">
           <list>
               <value>SyncColumnServiceImpl</value>
               <value>SyncColumnRankServiceImpl</value>
               <value>SyncUpShelfRankServiceImpl</value>
               <value>DelColumnServiceImpl</value>
               <value>SyncColumnImageServiceImpl</value>
               <value>DelColumnImageServiceImpl</value>
               <value>SyncRelGoodsServiceImpl</value>

               <value>DelUpShelfResourceServiceImpl</value>
               <value>SyncAllUpShelfResourceServiceImpl</value>
               <value>SyncUpShelfResourceServiceImpl</value>
               <value>SyncRecommendAssetServiceImpl</value>
               <value>DelRecommendAssetServiceImpl</value>
               <value>SyncRecommendContentServiceImpl</value>

               <value>SyncVirtualNetWorkServiceImpl</value>
               <value>DelVirtualNetworkServiceImpl</value>
               <value>SyncWidgetServiceImpl</value>
               <value>DelWidgetServiceImpl</value>
               <value>SyncHotelServiceImpl</value>
               <value>DelHotelServiceImpl</value>
               <value>CacheModServiceImpl</value>
               <value>ModNumServiceImpl</value>
               <value>VrecomNumServiceImpl</value>

               <value>SyncTemplateStatusServiceImpl</value>
               <value>SyncTemplateServiceImpl</value>
               <value>DelTemplateServiceImpl</value>
               <value>SyncSiteServiceImpl</value>
               <value>UpdateSiteStatusServiceImpl</value>
               <value>SyncCpServiceImpl</value>
               <value>DelCpServiceImpl</value>
               <value>AssetTypeAddService</value>
               <value>AssetTypeDeleteService</value>
               <value>AssetTypeRelationService</value>
               <value>ResourcePosterAddAction</value>
               <value>ResourcePosterDeleteAction</value>
               <value>POResourceAddAction</value>
               <value>POResourceDeleteAction</value>

               <value>SyncAssetServiceImpl</value>
               <value>DelAssetPackageServiceImpl</value>
               <value>SyncAssetFileServiceImpl</value>
               <value>DelAssetFileServiceImpl</value>
               <value>SyncAssetPackageServiceImpl</value>

               <value>SyncChannelServiceImpl</value>
               <value>DelChannelServiceImpl</value>
               <value>SyncChannelTypeServiceImpl</value>
               <value>DelChannelTypeServiceImpl</value>
               <value>SyncGoodsServiceImpl</value>
               <value>SyncGoodsStatusServiceImpl</value>
               <value>SyncProdOfferingServiceImpl</value>
               <value>SyncProdOfferingStatusServiceImpl</value>

               <value>SyncProviderServiceImpl</value>
               <value>SyncProviderStatusServiceImpl</value>
               <value>SyncUserGroupServiceImpl</value>
               <value>DelUserGroupServiceImpl</value>
           </list>
       </property>
       <property name="interceptorNames">
           <list>
               <value>serviceParaCheckInterceptor</value>
           </list>
       </property>
   </bean>
</beans>

\conf\datasource.properties

#\u93c1\u7248\u5d41\u6434\u64b9\u7e5b\u93ba\ue672RL
db.jdbcurl=${dbUrl}
#\u93c1\u7248\u5d41\u6434\u64b9\u7e5b\u93ba\u30e7\u6564\u93b4\u5cf0\u6095
db.user=${dbUserName}
#\u93c1\u7248\u5d41\u6434\u64b9\u7e5b\u93ba\u30e5\u7611\u942e\ufffd
db.password=${dbPassword}
#\u6769\u70b4\u5e34\u6939\u535e\u59e9\u7ecb\u5b2a\u7c2d\u7eeb\ufffd
db.driverclass=${dbDriverclass}
#\u6769\u70b4\u5e34\u59f9\u72b1\u8151\u6dc7\u6fc6\u74e8\u93c8\ufffd\u76ac\u9428\u52ee\u7e5b\u93ba\u30e6\u669f
db.minpoolsize=1
#\u6769\u70b4\u5e34\u59f9\u72b1\u8151\u6dc7\u6fc8\u6680\u9428\u52ec\u6e36\u6fb6\u0446\u7e5b\u93ba\u30e6\u669f
db.maxpoolsize=5
#\u9352\u6fc6\ue78a\u9356\u6828\u6902\u947e\u5cf0\u5f47\u6d5c\u65be\u91dc\u6769\u70b4\u5e34\u951b\u5c7d\u5f47\u934a\u714e\u7c32\u9366\u2569inPoolSize\u6d93\u5dd1axPoolSize\u6d94\u5b2e\u68ff
db.initialpoolsize=2
#\u6769\u70b4\u5e34\u9428\u52ee\u7e43\u93c8\u71b8\u6902\u95c2\ufffd
db.maxidletime=120
#JDBC\u9428\u52ec\u7223\u9351\u55d7\u5f2c\u93c1\u5e2e\u7d1d\u9422\u3124\u4e92\u93ba\u0443\u57d7\u93c1\u7248\u5d41\u5a67\u612c\u5534\u9354\u72ba\u6d47\u9428\u51f1reparedStatements\u93c1\u4f34\u567a\u9286\u5099\u7d7e\u9422\u53d8\u7c2c\u68f0\u52ed\u7d26\u701b\u6a3c\u6b91statements
#\u705e\u70b0\u7c2c\u9357\u66da\u91dcconnection\u9470\u5c7c\u7b09\u93c4\ue21b\u66a3\u6d93\ue047\u7e5b\u93ba\u30e6\u775c\u9286\u509b\u588d\u6d60\u30e8\ue195\u7f03\ue1bf\u7e56\u6d93\ue044\u5f2c\u93c1\u4f34\u6e36\u7455\u4f7d\ufffd\u94cf\u621d\u57cc\u6fb6\u6c2d\u67df\u95c8\u3222\u6b91\u9365\u72b5\u790c\u9286\ufffd
#\u6fe1\u509b\u7049maxStatements\u6d93\u5dd1axStatementsPerConnection\u9367\u56e6\u8d1f0\u951b\u5c7d\u57af\u7f02\u64b3\u74e8\u741a\ue0a2\u53e7\u95c2\ue15c\ufffdDefault: 0
db.maxstatements=20

db.testConnectionOnCheckin = true
#\u59e3\ufffd0\u7ec9\u6393\ue5d1\u93cc\u30e6\u588d\u93c8\u590e\u7e5b\u93ba\u30e6\u775c\u6d93\ue160\u6b91\u7ecc\u6d2a\u68fd\u6769\u70b4\u5e34
db.idleconnectiontestperiod=60
#\u7039\u6c2b\u7b9f\u9366\u3124\u7ca0\u93c1\u7248\u5d41\u6434\u64b9\u5e4f\u9359\u6828\u67ca\u6769\u70b4\u5e34\u6fb6\u8fab\u89e6\u935a\u5ea8\u5678\u6fb6\u5d85\u76be\u7487\u66e0\u6b91\u5a06\u2103\u669f
db.acquireretryattempts=30
#\u8930\u64b9\u7e5b\u93ba\u30e6\u775c\u6d93\ue160\u6b91\u6769\u70b4\u5e34\u9470\u6940\u6556\u9428\u52ec\u6902\u934a\u6a863p0\u6d93\ufffd\ue0bc\u935a\u5c7e\u6902\u947e\u5cf0\u5f47\u9428\u52ee\u7e5b\u93ba\u30e6\u669f
db.acquireincrement=3
#\u947e\u5cf0\u5f47\u6769\u70b4\u5e34\u6fb6\u8fab\u89e6\u704f\u55d5\u7d30\u5bee\u66e1\u6363\u93b5\ufffd\u6e41\u7edb\u590a\u7ddf\u6769\u70b4\u5e34\u59f9\u72b3\u6f75\u947e\u5cf0\u5f47\u6769\u70b4\u5e34\u9428\u52ed\u568e\u7ecb\u5b2b\u59cf\u9351\u54c4\u7d13\u752f\u641e\ufffd\u6d63\u55d8\u69f8\u93c1\u7248\u5d41\u5a67\u612a\u7c9b\u93c8\u590b\u6665
#\u6dc7\u6fc8\u6680\u951b\u5c7d\u82df\u9366\u3124\u7b05\u5a06\u00a4\u769f\u9422\u2563etConnection()\u9428\u52ec\u6902\u934a\u6b11\u6237\u7f01\ue15e\u76be\u7487\u66e1\u5e4f\u9359\u682c\u7e5b\u93ba\u30e3\ufffd\u6fe1\u509b\u7049\u7481\u53e5\u8d1ftrue\u951b\u5c84\u5045\u6d94\u581d\u6e6a\u704f\u6fca\u762f
#\u947e\u5cf0\u5f47\u6769\u70b4\u5e34\u6fb6\u8fab\u89e6\u935a\u5ea4\ue1da\u93c1\u7248\u5d41\u5a67\u612c\u76a2\u9422\u866b\u69d1\u5bb8\u53c9\u67c7\u5bee\ufffd\u82df\u59d8\u9550\u7b99\u934f\u62bd\u68f4\u9286\u4fb1efault: false
db.breakafteracquirefailure=false
#\u7039\u6c2b\u7b9f\u6d5c\u55da\u7e5b\u93ba\u30e6\u775c\u9350\u546d\u5d1f\u6d93\ue047\u7e5b\u93ba\u30e6\u588d\u93b7\u30e6\u6e41\u9428\u52ec\u6e36\u6fb6\u0445\u7d26\u701b\u69aetatements\u93c1\ufffd
db.maxstatementsperconnection=20
#c3p0\u93c4\ue21a\u7d13\u59dd\u30e6\u6437\u6d63\u6ec5\u6b91\u951b\u5c80\u7d26\u93b1\u3222\u6b91JDBC\u93bf\u5d84\u7d94\u95ab\u6c33\u7e43\u752f\ue1bc\u59ea\u6769\u6d9a\u25bc\u7039\u5c7e\u579a\u9286\u509b\u58bf\u705e\u66e1\u7e56\u6d5c\u6d99\u6437\u6d63\u6ec3\u5f72\u6d60\u30e6\u6e41\u93c1\u5822\u6b91\u93bb\u612c\u5d0c\u93ac\u0446\u5158
#\u95ab\u6c33\u7e43\u6fb6\u6c31\u568e\u7ecb\u5b2a\u7584\u941c\u677f\ue64b\u6d93\ue045\u6437\u6d63\u6ec3\u6093\u93c3\u60f0\ue586\u93b5\u0446\ue511\u9286\u4fb1efault: 3
db.numhelperthreads=3
#\u9365\u72b3\ufffd\u9473\u82a5\u79f7\u9470\u6940\u3047\u7487\u5cf0\u5f67\u9366\u3129\u6e36\u7455\u4f7a\u6b91\u93c3\u8dfa\ufffd\u6d63\u8de8\u6564\u7039\u51a6\ufffd\u6fe1\u509b\u7049\u7481\u53e5\u8d1ftrue\u95ad\uff44\u7b9e\u9366\u3126\u7621\u6d93\u731aonnection\u93bb\u612a\u6c26\u9428\ufffd
#\u93c3\u8dfa\ufffd\u95ae\u85c9\u76a2\u93cd\uffe0\u7359\u934f\u8235\u6e41\u93c1\u581f\ufffd\u9286\u509a\u7f13\u7481\ue1bb\u5a07\u9422\u2565dleConnectionTestPeriod\u93b4\u673cutomaticTestTable
#\u7edb\u590b\u67df\u5a09\u66df\u6f75\u93bb\u612c\u5d0c\u6769\u70b4\u5e34\u5a34\u5b2d\u762f\u9428\u52ec\ufffd\u9473\u59d0\ufffdDefault: false
db.testconnectiononcheckout=false
#\u6d93\u3086\ue0bc\u6769\u70b4\u5e34\u6d93\ue162\u68ff\u95c5\u65c0\u6902\u95c2\u8fbe\u7d1d\u9357\u66da\u7d85\u59e3\ue0a4\ue791
db.acquireretrydelay=1000
#\u6769\u70b4\u5e34\u934f\u62bd\u68f4\u93c3\u5815\u7caf\u7481\u3085\u76a2\u93b5\ufffd\u6e41\u93c8\ue045\u5f41\u6d5c\u3087\u6b91\u93bf\u5d84\u7d94\u9365\u70b4\u7cb4\u9286\u4fb1efault: false
db.autocommitonclose=false
#\u8930\u64b9\u7e5b\u93ba\u30e6\u775c\u9422\u3125\u756c\u93c3\u8dfa\ue179\u93b4\u98ce\ue06c\u748b\u51aa\u6564getConnection()\u935a\u5ea3\u74d1\u5bf0\u5470\u5e4f\u9359\u6828\u67ca\u6769\u70b4\u5e34\u9428\u52ec\u6902\u95c2\u8fbe\u7d1d\u74d2\u546e\u6902\u935a\u5ea1\u76a2\u93b6\u6d98\u56ad
#SQLException,\u6fe1\u509d\ue195\u6d93\ufffd\u9352\u6b10\u68e4\u95c4\u612d\u6e61\u7edb\u590a\u7ddf\u9286\u509a\u5d1f\u6d63\u5d86\ue1e0\u7ec9\u638b\ufffdDefault: 0
db.checkouttimeout=30000
#\u6769\u70b4\u5e34\u59f9\u72b3\u5f3f\u6769\ufffd
db.description=A pooled c3p0 DataSource
#\u93c4\ue21a\u60c1\u93b5\u64b3\u5d43c3p0\u6769\u70b4\u5e34\u93c3\u30e5\u7e54,Default: false
db.debugunreturnedconnectionstacktraces=false
dbType=${dbType}

0 0