Oracle auto-reconnect

xerix32 · **Joined:** Mon Feb 04, 2008 3:49 am **Posts:** 2

Hi folk,

how to obtain the same as "jdbc:mysql:/localhost/mydb?autoReconnect=true" with oracle jdbc driver? Assuming actually, even with MySql, using the autoReconnect parameter is not recommended (http://www.theserverside.com/news/thread.tss?thread_id=24137#111947).

I don't use Hibernate connection pool but DBCP (with tomcat) and this not help:
"<property name="connection.autoReconnect">true</property>": after 8 hours of inactivity I get "connection error" exception.

Can c3p0 help?
<property name="c3p0.idle_test_period">100</property>
Is c3p0 really "enterprise ready"?

Here my hibernate.cfg.xml file:

Code:

<?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 name="TestSessionFactory">
      <!-- Database connection settings -->
      <!-- 
         <property name="hibernate.connection.driver_class">
         oracle.jdbc.driver.OracleDriver
         </property>
         <property name="hibernate.connection.password">tiger</property>
         <property name="hibernate.connection.url">
         jdbc:oracle:thin:@10.0.0.14:1521:orcl
         </property>
         <property name="hibernate.connection.username">scott</property>
      -->

      <property name="connection.datasource">
         java:/comp/env/jdbc/SITIP
      </property>

      <!-- JDBC Connection pool -->
      <!-- <property name="connection.pool_size">1</property> -->

      <!-- SQL Dialect -->
      <property name="hibernate.dialect">
         org.hibernate.dialect.Oracle10gDialect
      </property>

      <!-- Echo alla executed sql to stdout -->
      <property name="show_sql">true</property>
      <property name="format_sql">true</property>
      <property name="use_sql_comments">true</property>

      <!-- Enable Hibernate's automatic session context management -->
      <!-- Use thread-bound persistence context propagation, scoped to the transaction  -->

      <property name="current_session_context_class">thread</property>

      <!-- ATTENZIONE: mettendo "create" le tabelle vengono CANCELLATE!!!  -->
      <!--  Drop and re-create the database schema on startup  -->
      <property name="hbm2ddl.auto">update</property>

      <!-- Seguono gli XML associati alle entità del progetto -->
      <mapping resource="entity_hbmxml/CcsUser.hbm.xml" />
      <mapping resource="entity_hbmxml/CcsUserCategory.hbm.xml" />
      <mapping resource="entity_hbmxml/CcsCompany.hbm.xml" />

      <mapping resource="entity_hbmxml/CcsMessage.hbm.xml" />
      <mapping resource="entity_hbmxml/CcsMessageCategory.hbm.xml" />
      <mapping resource="entity_hbmxml/CcsHarbour.hbm.xml" />

      <mapping resource="entity_hbmxml/CcsDocument.hbm.xml" />
      <mapping resource="entity_hbmxml/CcsDocumentCategory.hbm.xml" />
   </session-factory>
</hibernate-configuration>

cbredesen · **Posted:** Wed Feb 06, 2008 4:57 pm

What you need is to configure your pool to validate connections and replace them if they're bad. This is a DBCP thing, not Hibernate, so have a look here:

http://commons.apache.org/dbcp/configuration.html

Check out the validationQuery, testOnBorrow and testOnReturn parameters.

-Chris

xerix32 · **Joined:** Mon Feb 04, 2008 3:49 am **Posts:** 2

Hi Chris,

thnx for the reply. I've alredy read about DBCP but withiout success

I use a JNDI resource with this "context.xml" configuration:

Code:

<Resource name="jdbc/SITIP" auth="Container"
      type="javax.sql.DataSource" username="scott" password="tiger"
      driverClassName="oracle.jdbc.pool.OracleDataSource"
      factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
      url="jdbc:oracle:thin:@10.0.0.14:1521:orcl"
      removeAbandoned="true" removeAbandonedTimeout="120" 
      testOnBorrow="true" testOnReturn="true" validationQuery="select 'validationQuery' from dual"/>

but it doesn't seem to work!
From Tomcat LOG:

Code:

AbandonedObjectPool is used (org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool@1594a88)
   LogAbandoned: false
   RemoveAbandoned: true
   RemoveAbandonedTimeout: 120

I'm discouraged but I cannot think that a so basic thing is too difficult to setup! :(

kamalraja · **Joined:** Thu May 07, 2009 1:50 am **Posts:** 3

I got the same exception & resolved the issue after 3 hectic days.Check if you are using I hibernate3. In this version it is req to explicitly mention the connection class name. Also check if the jar is in classpath. Check steps & comments in below link

http://hibernatedb.blogspot.com/2009/05 ... te-to.html

autoReconnec=true doesnt seems to work..remove from config file.