oracle 如何在休眠状态下配置 c3p0 以自动刷新陈旧的数据库连接

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

提问by Lokendra Chauhan

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.

我在我的应用程序中使用 hibernate 3、c3p0 9.1.2、Oracle 11g。如果我重新启动 Oracle，那么陈旧的连接就不会刷新，并且出现异常“java.sql.SQLRecoverableException: Closed Connection”。下面是我的 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.

我是 Hibernate API 的初学者。您能否建议如何配置休眠以在指定时间自动刷新过时的连接。

Here is my hibernate.cfg.xml

这是我的 hibernate.cfg.xml

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

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>

回答by A Lee

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

c3p0您需要配置的是您的数据库连接池 - 而不是休眠。尝试设置idleConnectionTestPeriod和适当的preferredTestQuery，例如select 1 from dual。该validate属性已被弃用，建议您不要使用它。

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

有关更多信息，请参阅http://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool。如果您在其中创建c3p0.properties文件，您将获得最大的控制权，WEB-INF/classes但您需要确保不要在hibernate.cfg.xml.

回答by Lokendra Chauhan

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>