How to configure c3p0 in hibernate to auto-refresh stale DB connections

Question

I am using hibernate 3, c3p0 9.1.2, Oracle 11g in my application. If I restart the Oracle then the stale connections are not getting refresh and I am getting exception "java.sql.SQLRecoverableException: Closed Connection". Below is my hibernate.cfg.xml.

I am a beginner in Hibernate API. Can you please suggest how to configure hibernate to automatically refresh the stale connections on a specified time.

Here is my hibernate.cfg.xml

oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@localhost:1521:ems emsman

<property name="hibernate.c3p0.idle_test_period">60</property> <!-- seconds -->
    <property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.timeout">1800</property>
    <property name="hibernate.c3p0.max_statements">50</property>
    <property name="show_sql">false</property>
    <property name="dialect">org.hibernate.dialect.OracleDialect</property>

    <property name="c3p0.validate">true</property>

<mapping resource="<package-name>/GroupOpWorkflow.hbm.xml"/>
    <mapping resource="<package-name>/GroupOperation.hbm.xml"/>
    <mapping resource="<package-name>/GroupOpNode.hbm.xml"/>
    <mapping resource="<package-name>/NodeStatusLog.hbm.xml"/>
</session-factory>

Answer 1

You can try the following, it's a simple advice from the author of c3p0 taken here:

The best thing to do is usually to try Step 3, see if it helps (however you measure performance), see if it hurts (is your application troubled by broken Connections? does it recover from database restarts well enough?), and then decide.

Step 3:
If you'd like to improve performance by eliminating Connection testing from clients' code path:

Set testConnectionOnCheckout to false

Set testConnectionOnCheckin to true

Set idleConnectionTestPeriod to 30, fire up you application and observe. This is a pretty robust setting, all Connections will tested on check-in and every 30 seconds thereafter while in the pool. Your application should experience broken or stale Connections only very rarely, and the pool should recover from a database shutdown and restart quickly. But there is some overhead associated with all that Connection testing.

If database restarts will be rare so quick recovery is not an issue, consider reducing the frequency of tests by idleConnectionTestPeriod to, say, 300, and see whether clients are troubled by stale or broken Connections. If not, stick with 300, or try an even bigger number. Consider setting testConnectionOnCheckin back to false to avoid unnecessary tests on checkin. Alternatively, if your application does encounter bad Connections, consider reducing idleConnectionTestPeriod and set testConnectionOnCheckin back to true. There are no correct or incorrect values for these parameters: you are trading off overhead for reliability in deciding how frequently to test. The exact numbers are not so critical. It's usually easy to find configurations that perform well. It's rarely worth spending time in pursuit of "optimal" values here.

Answer 2

After gone through the document { http://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool } I found C3P0 was not a all used by hibernate. 

So wrote a new C3P0 xml file and used the below system properties:

C3P0_SYS_PROPS="-Dcom.mchange.v2.c3p0.cfg.xml=<FILE-PATH>/c3p0-config.xml -Dcom.mchange.v2.log.MLog=com.mchange.v2.log.FallbackMLog -Dcom.mchange.v2.log.FallbackMLog.DE
FAULT_CUTOFF_LEVEL=WARNING"


So here is the final working configuration


hibernate.cfg.xml 


 <session-factory>
<property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
        <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:ems</property>
<property name="hibernate.connection.username">emsman</property>
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.connection.autoReconnect">true</property>
<property name="show_sql">false</property>
<property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>
<property name="hibernate.c3p0.idle_test_period">300</property> <!-- In seconds -->
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_statements">50</property>
.....


<c3p0-config>
<default-config>
<!-- Configuring Connection     Testing -->
<!-- property name="automaticTestTable">TEST_EMS_HIBERNATE_CONN</property -->
<property name="checkoutTimeout">0</property>
<property name="testConnectionOnCheckout">true</property>
<property name="testConnectionOnCheckin">false</property>
<property name="preferredTestQuery">SELECT 1 from dual</property>
<!-- Configuring Recovery From Database Outages -->
<property name="acquireRetryAttempts">0</property>
<property name="acquireRetryDelay">1000</property>
<property name="breakAfterAcquireFailure">false</property>
<!-- Configuring to     Debug and Workaround Broken     Client Apps     -->
<property name="unreturnedConnectionTimeout">1800</property>
<property name="debugUnreturnedConnectionStackTraces">true</property>
</default-config>


c3p0-config.xml


<c3p0-config>
<default-config>
<!-- Configuring Connection     Testing -->
<!-- property name="automaticTestTable">TEST_EMS_HIBERNATE_CONN</property -->
<property name="checkoutTimeout">0</property>
<property name="testConnectionOnCheckout">true</property>
<property name="testConnectionOnCheckin">false</property>
<property name="preferredTestQuery">SELECT 1 from dual</property>
<!-- Configuring Recovery From Database Outages -->
<property name="acquireRetryAttempts">0</property>
<property name="acquireRetryDelay">1000</property>
<property name="breakAfterAcquireFailure">false</property>
<!-- Configuring to     Debug and Workaround Broken     Client Apps     -->
<property name="unreturnedConnectionTimeout">1800</property>
<property name="debugUnreturnedConnectionStackTraces">true</property>
</default-config>

Answer 3

It's c3p0, your database connection pool, that you need to configure - not hibernate. Try setting idleConnectionTestPeriod and an appropriate preferredTestQuery, e.g., select 1 from dual. The validate property has been deprecated and it's recommended that you not use that.

See http://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool for more information. You'll get the most control if you create a c3p0.properties file in WEB-INF/classes but you need to make sure not to override those properties in your hibernate.cfg.xml.