title: hibernate configurations : connection timed out post inactivity on application end
body: <p>i'm using hibernate for transactions towards a database. Our application gives an error every first time when we execute a flow we get below error everyday and post that we stop getting these issues.</p>
  
  <p>So my first doubt remained on the fact that somehow we're using an idle connection which has already timed out. Fair enough we can always handle this part using hibernate.cfg.xml. </p>
  
  <p>So i stumbled upon the various ways for doing so i.e. by adding any of below parameters in our configuration for hibernate we should be able to resolve same, however none of them is solving my purpose here of avoiding idle connections which keeps giving me "connection timed out" failure at the start of every day : </p>
  
  <pre><code>&lt;property name="hibernate.c3p0.testConnectionOnCheckout"&gt;true&lt;/property&gt;
  &lt;property name="hibernate.c3p0.idle_test_period"&gt;30&lt;/property&gt;
  </code></pre>
  
  <p>The error i'm getting is as below :</p>
  
  <pre><code>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
  </code></pre>
  
  <p>my hibernate.cfg.xml is as below : </p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"&gt;
  &lt;hibernate-configuration&gt;
  &lt;session-factory&gt;
          &lt;!-- &lt;property name="hibernate.connection.driver_class"&gt;oracle.jdbc.driver.OracleDriver&lt;/property&gt;
          &lt;property name="hibernate.connection.url"&gt;jdbc:oracle:thin:@127.0.0.1:1521:orcl&lt;/property&gt; --&gt;
          &lt;property name="hibernate.dialect"&gt;org.hibernate.dialect.Oracle10gDialect&lt;/property&gt;
          &lt;property name="hibernate.default_schema"&gt;ECMREPORT&lt;/property&gt;
          &lt;property name="hibernate.c3p0.testConnectionOnCheckout"&gt;true&lt;/property&gt;
          &lt;property name="hibernate.c3p0.timeout"&gt;600&lt;/property&gt;
          &lt;property name="hibernate.c3p0.maxIdleTimeExcessConnections"&gt;20&lt;/property&gt;
          &lt;property name="hibernate.c3p0.validate"&gt;false&lt;/property&gt;
          &lt;property name="hibernate.c3p0.idle_test_period"&gt;30&lt;/property&gt;
          &lt;property name="hibernate.c3p0.automaticTestTable"&gt;conTestTable&lt;/property&gt;
          &lt;property name="show_sql"&gt;true&lt;/property&gt;
          &lt;!--  &lt;property name="hibernate.hbm2ddl.auto"&gt;update&lt;/property&gt;--&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.AuditTrail"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.ECMConfigDetail"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.BSCSFUP"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.BSCSRoaming"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.BSCSRatePlan"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.BSCSService"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.OFSPlans"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.OFSPacks"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.CRMPlans"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.CRMPacks"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.ProjectPlan"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.ProductionDumpReport"&gt;&lt;/mapping&gt;
          &lt;mapping class="com.prod.ecmrs.dao.entity.RAITReportDump"&gt;&lt;/mapping&gt;
      &lt;/session-factory&gt;
  &lt;/hibernate-configuration&gt;
  </code></pre>