Hibernate version: hibernate2.1

Name and version of the database you are using: MySQL 4.0.18

I am facing the issue, where the Database connection is lost after a prolonged period of inactivity.

I would like to configure hibernate to reconnect to the Database when the activity resumes.

I am using the hibernate.properties as follows.

hibernate.dialect net.sf.hibernate.dialect.MySQLDialect
hibernate.connection.driver_class org.gjt.mm.mysql.Driver
hibernate.connection.driver_class com.mysql.jdbc.Driver
hibernate.connection.url jdbc:mysql://localhost/MyDatabase
hibernate.connection.username root
hibernate.connection.password

I have not modified any other configuration files.

Any help would be greatly appreciated.

this is the third question about pooling i have today, please search the site,
enter c3p0 in the site search engine

Thanks for the rapid response. The information was very helpful. I will be trying it shortly.

Meanwhile, I am facing another issue, where I get errors when the database connection is lost while retreiving the data.

I found out that the error occurs, when I try to close the session.

I have the following questions.
1. How to verify if the connection is lost?
2. If I have to, how to close session after the connection has been lost?
3. What is the implication on not closing the session after executing the query?
4. What is the best way to recover from database connection lost?

Your help is greatly appreciated.

I use the following code to get the data:

//GET THE DATA FROM THE DATABASE
public List get(String retrieveQuery) throws DBException,HibernateException
{

try
{
Configuration cfg = new Configuration();
SessionFactory sf = cfg.buildSessionFactory();
Session hibernateSession = sf.openSession();

Transaction tx = hibernateSession.beginTransaction();
hibernateSession.setFlushMode(FlushMode.COMMIT);
Query q = hibernateSession.createQuery(retrieveQuery);
List l = q.list();
tx.commit();
return l;
}
catch (HibernateException e)
{
throw new DBException(e.getCause());
}
//IF I COMMENT THIS PIECE, THERE IS NO ERROR
finally
{
hibernateSession.close();
}
}

same answer, all this stuff is managed by connexion pool.

The session should have a short live so closing it is not really a big problem. I hope you know about threadlocal session pattern

I still havent succeeded in recovering from a lost database connection.

Based on your suggestion, I have tried including both ThreadLocal Session and c3p0 Connection pool. In addition to this, I also set autoReconnect properties. Still my application does not recover, it keeps throwing Exceptions at the console.

A part of the trace is below:


CONNECTION ERROR OCCURRED!

probably 'cuz we're closed -- java.lang.NullPointerException
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.close(NewProxyPrep
aredStatement.java:1866)
at net.sf.hibernate.impl.BatcherImpl.closePreparedStatement(BatcherImpl.
java:241)
at net.sf.hibernate.impl.BatcherImpl.closeStatement(BatcherImpl.java:132
)
at net.sf.hibernate.impl.BatcherImpl.closeQueryStatement(BatcherImpl.jav
a:148)
at net.sf.hibernate.impl.BatcherImpl.closeQueryStatement(BatcherImpl.jav
a:99)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:803)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
(Loader.java:132)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1476)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)




THREADLOCAL SESSION CODE.

public static final ThreadLocal session = new ThreadLocal();

public static Session currentSession() throws HibernateException {
Session s = (Session) session.get();
if (s == null) {
s = hibernateSessionFactory.openSession();
session.set(s);
}
return s;
}

public static void closeSession() throws HibernateException {
Session s = (Session) session.get();
session.set(null);
if (s != null)
s.close();
}

DATA RETRIEVAL CODE

public List get(String retrieveQuery) throws DBException
{
Session hibernateSession = null;
try
{
hibernateSession = hibernateSessionHelper.currentSession();
Transaction tx = hibernateSession.beginTransaction();
hibernateSession.setFlushMode(FlushMode.COMMIT);
Query q = hibernateSession.createQuery(retrieveQuery);
List l = q.list();
tx.commit();
return l;
}
catch (HibernateException e)
{
throw new DBException(e.getMessage());
}

finally
{
hibernateSessionHelper.closeSession();
}

My hibernate.properties includes the following:

hibernate.c3p0.max_size 3
hibernate.c3p0.min_size 2


hibernate.connection.autoReconnect true
hibernate.connection.autoReconnectForPools true
hibernate.connection.is-connection-validation-required true

SIMULATION
I try to simulate the database connection loss by stopping the mysql server temporarily.


Am I missing anything or anything wrong with the above code and configurations. I am not very well versed with hibernate. Some illustrative examples would be very helpful.

Please help me overcome this problem.

Thanks.

hibernate.connection.autoReconnect true
hibernate.connection.autoReconnectForPools true
hibernate.connection.is-connection-validation-required true
what is it?

check this to see what can be put in configuration file or "externalize" pool properties using their property file
http://www.hibernate.org/hib_docs/api/n ... nment.html

Thanks for the correction. I could see that they are invalid properties.

Removing these properties does not give a different result either.

How to "externalize" pool properties ?

I would like to set the properties of c3p0 other than the ones configured from the hibernate.properties file.

Thanks.

are you enough curious to visit c3p0 site?

Sorry for bothering you..

I tried the c3p0 site. but could not locate the right one. May be you can provide some pointers.

mali,

Please try the latest version of c3p0 -- c3p0-0.8.5-pre7a. The stack trace you reported above may be a c3p0 issue that is resolved in this release. You'll find the latest version on sourceforge:

http://sourceforge.net/projects/c3p0/

For the moment, the sourceforge project is c3p0's website. You'll find more information on c3p0 in the docs directory of the distribution.

smiles,
Steve (c3p0 maintainer)

p.s. you'll find more information about setting non-hibernate mapped c3p0 parameters here:

http://forum.hibernate.org/viewtopic.ph ... highlight=

Thanks, Steve.

I have been using c3p0-0.8.5pre4.

I will try the c3p0-0.8.5pre7a to see if I can get it working.

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

Remove autoReconnec=true