java - hibernate 配置: connection timed out post inactivity on application end

Question

我正在使用 hibernate 进行数据库事务。当我们执行流程时，我们的应用程序每次都会第一次出现错误，我们每天都会遇到以下错误，并发布消息说我们不再遇到这些问题。

所以我的第一个疑问仍然是我们正在使用已经超时的空闲连接。公平地说，我们总是可以使用 hibernate.cfg.xml 来处理这部分。

所以我偶然发现了这样做的各种方法，即通过在 hibernate 配置中添加以下任何参数，我们应该能够解决相同的问题，但是它们都没有解决我在这里避免空闲连接的目的，这一直给我带来麻烦每天开始时都会出现“连接超时”故障:

<property name="hibernate.c3p0.testConnectionOnCheckout">true</property>
<property name="hibernate.c3p0.idle_test_period">30</property>

我收到的错误如下:

org.hibernate.exception.JDBCConnectionException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:132) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:61) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.getDatabaseSnapshot(AbstractEntityPersister.java:1520) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.engine.internal.StatefulPersistenceContext.getDatabaseSnapshot(StatefulPersistenceContext.java:316) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.engine.internal.ForeignKeys.isTransient(ForeignKeys.java:217) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.event.internal.AbstractSaveEventListener.getEntityState(AbstractSaveEventListener.java:497) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:100) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.internal.SessionImpl.fireSaveOrUpdate(SessionImpl.java:735) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.internal.SessionImpl.saveOrUpdate(SessionImpl.java:727) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at org.hibernate.internal.SessionImpl.saveOrUpdate(SessionImpl.java:723) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    at com.prod.ecmrs.dao.impl.AuditTrailDaoImpl.saveAuditTrail(AuditTrailDaoImpl.java:34) ~[classes:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_79]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_79]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_79]
    at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_79]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) [spring-aop-3.2.8.RELEASE.jar:3.2.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) [spring-aop-3.2.8.RELEASE.jar:3.2.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) [spring-aop-3.2.8.RELEASE.jar:3.2.8.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96) [spring-tx-3.2.8.RELEASE.jar:3.2.8.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260) [spring-tx-3.2.8.RELEASE.jar:3.2.8.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) [spring-tx-3.2.8.RELEASE.jar:3.2.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) [spring-aop-3.2.8.RELEASE.jar:3.2.8.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) [spring-aop-3.2.8.RELEASE.jar:3.2.8.RELEASE]
    at com.sun.proxy.$Proxy87.saveAuditTrail(Unknown Source) [na:na]
    at com.prod.ecmrs.processor.ECMReportProcessor.process(ECMReportProcessor.java:107) [classes:na]
    at org.apache.camel.processor.DelegateSyncProcessor.process(DelegateSyncProcessor.java:63) [camel-core-2.13.2.jar:2.13.2]
    at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:72) [camel-core-2.13.2.jar:2.13.2]
    at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:398) [camel-core-2.13.2.jar:2.13.2]
    at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:191) [camel-core-2.13.2.jar:2.13.2]
    at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:191) [camel-core-2.13.2.jar:2.13.2]
    at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:105) [camel-core-2.13.2.jar:2.13.2]
    at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:87) [camel-core-2.13.2.jar:2.13.2]
    at org.apache.camel.component.jms.EndpointMessageListener.onMessage(EndpointMessageListener.java:103) [camel-jms-2.12.3.jar:2.12.3]
    at org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:562) [spring-jms-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:500) [spring-jms-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:468) [spring-jms-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:325) [spring-jms-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:263) [spring-jms-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:1103) [spring-jms-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.executeOngoingLoop(DefaultMessageListenerContainer.java:1095) [spring-jms-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:992) [spring-jms-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_79]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_79]
    at java.lang.Thread.run(Thread.java:745) [na:1.7.0_79]
Caused by: java.sql.SQLRecoverableException: IO Error: Connection timed out
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:874) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) ~[commons-dbcp-1.4.jar:1.4]
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) ~[commons-dbcp-1.4.jar:1.4]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56) ~[hibernate-core-4.2.1.Final.jar:4.2.1.Final]
    ... 43 common frames omitted
Caused by: java.net.SocketException: Connection timed out
    at java.net.SocketInputStream.socketRead0(Native Method) ~[na:1.7.0_79]
    at java.net.SocketInputStream.read(SocketInputStream.java:152) ~[na:1.7.0_79]
    at java.net.SocketInputStream.read(SocketInputStream.java:122) ~[na:1.7.0_79]
    at oracle.net.ns.Packet.receive(Packet.java:282) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.net.ns.DataPacket.receive(DataPacket.java:103) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:230) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:175) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:100) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:85) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:122) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:78) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1179) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    ... 51 common frames omitted

我的 hibernate.cfg.xml 如下:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
        <!-- <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
        <property name="hibernate.connection.url">jdbc:oracle:thin:@127.0.0.1:1521:orcl</property> -->
        <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
        <property name="hibernate.default_schema">ECMREPORT</property>
        <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>
        <property name="hibernate.c3p0.timeout">600</property>
        <property name="hibernate.c3p0.maxIdleTimeExcessConnections">20</property>
        <property name="hibernate.c3p0.validate">false</property>
        <property name="hibernate.c3p0.idle_test_period">30</property>
        <property name="hibernate.c3p0.automaticTestTable">conTestTable</property>
        <property name="show_sql">true</property>
        <!--  <property name="hibernate.hbm2ddl.auto">update</property>-->
        <mapping class="com.prod.ecmrs.dao.entity.AuditTrail"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.ECMConfigDetail"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.BSCSFUP"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.BSCSRoaming"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.BSCSRatePlan"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.BSCSService"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.OFSPlans"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.OFSPacks"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.CRMPlans"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.CRMPacks"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.ProjectPlan"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.ProductionDumpReport"></mapping>
        <mapping class="com.prod.ecmrs.dao.entity.RAITReportDump"></mapping>
    </session-factory>
</hibernate-configuration>

Answer 1

这是关于与数据库的连接的建立。您是否尝试增加登录超时时间？

<property name="hibernate.c3p0.timeout">180</property>