after the user doesn't active for around 8 hours, if the user try to log in again, my application give the following exception:

javax.servlet.ServletException: net.sf.hibernate.JDBCException: Could not
execute query: Communication link failure: java.net.SocketException


after debug, I found out, it is related to the mysql wait_time, the default wait_time for mysql is 8 hours. If I change my.ini and set wai_time to 30 sec, this exception happen quickly.


I think the reason is because, in my executeJob method(see the attached code), I close the session after each job. It seems like tha application cannot connect to mysql after idle for a while. I have the following configuration in my hibernate.cfg.xml:

<property name="connection.url">jdbc:mysql://localhost:3306/author?autoReconnect=true</property>

<property name="connection.autoReconnect">true</property>
<property name="connection.autoReconnectForPools">true</property>
<property name="connection.is-connection-validation-required">true</property>

I think it should reconnect to mysql automatically, but it doesn't.

If I try to comments the closeSession(ses) line in exectureJob method, my application works fine, but I can see the WARNing message showed up in the log:

WARN SessionImpl - finalizing unclosed session with closed connection


It means I should close the session after each job. Where should I put my closeSession line? I could not figure it out. Any suggestion highly appreciated! !! I used hibernate 2.0.3 and mysql.




/**

* This is the hibernate session factory's singleton. It usually

* be called when application starts and will build the hibernate

* "Configuration" and then create a application scope "Session

* Factory". Other servlets, beans should get the session factory

* by this:

* <code>SessionFactory factory = HibernateUtil.getInstance().getSessionFactory()</code>

*/

package com.brainmedia.util;

import net.sf.hibernate.cfg.Configuration;

import net.sf.hibernate.MappingException;

import net.sf.hibernate.SessionFactory;

import net.sf.hibernate.HibernateException;

import net.sf.hibernate.Session;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

/**
*
* HibernateUtil is a commonly used helper class for DAO objects
*
* to easier access hibernate. it encapsulates some mostly used
*
* functioanlities include:
*
* <li>openSession</li>
*
* <li>loadObject</li>
*
* <li>saveObject</li>
*
* <li>deleteObject</li>
*
* <li>executeJob</li>-- excutes a generally job.
*
* <p>
* It's implemented in singleton. however, some functions are implemented
*
* as static.
* </p>
*
* It's first getInstance() call is called by the InitServlet.
*
* <p>
* Note that Transation is not supported here. however, mysql
*
* doesn't support transation, either.
* </p>
*
*/

public class HibernateUtil {

private static Log log = LogFactory.getLog(HibernateUtil.class);

private static HibernateUtil ourInstance = new HibernateUtil();

private Configuration configuration;

private SessionFactory factory;

/**
*
* get the instance of this util. does some initialization if required.
*
* configures the hibernate with parameters. (xml files from class path.)
*
* @return the util instance.
*
*/

public static HibernateUtil getInstance() {

return ourInstance;

}

private HibernateUtil() {

initConfiguration();

}

private void initConfiguration() {

try {

configuration = new Configuration().configure();//.addFile("mapping.hmb.xml");

factory = configuration.buildSessionFactory();

} catch (MappingException e) {

log.fatal("Can't configure hibernate: ", e);

} catch (HibernateException e) {

log.fatal("Can't configure hibernate: ", e);

}

}

/**
*
* get the global session factory of hibernate.
*
* @return the configured session factory.
*
*/

public SessionFactory getSessionFactory() {

return factory;

}

/**
*
* simply open a session with the existing session factory for operation.
*
* @return opened session.
*
* @throws HibernateException
*
*/

public static Session openSession() throws HibernateException {

return getInstance().getSessionFactory().openSession();

}

/**
*
* Load an object according to it's id. the id must be an integer.
*
* @param objClass
* the object's class.
*
* @param id
* the object's id
*
* @return the object. you need to cast it to your own type.
*
* @throws DataAccessException
* if any error occures.
*
*/

public static Object loadObject(final Class objClass, final int id)
throws DataAccessException {

Object obj = executeJob(new SessionJob() {

public Object execute(Session session) throws HibernateException {

return session.load(objClass, new Integer(id));

}

});

return obj;

}

/**
*
* save an object. the object must has been declared in the mapping file.
*
* @param obj
* the object to save.
*
* @throws DataAccessException
* if any error occures.
*
*/

public static void saveObject(final Object obj) throws DataAccessException {

executeJob(new SessionJob() {

public Object execute(Session session) throws HibernateException {

session.saveOrUpdate(obj);

session.flush();

return null;

}

});

}

/**
*
* delete an object from database.
*
* @param obj -
* the object instance.
*
* @throws DataAccessException
* if any error occures.
*
*/

public static void deleteObject(final Object obj)
throws DataAccessException {

executeJob(new SessionJob() {

public Object execute(Session session) throws HibernateException {

session.delete(obj);

session.flush();

return null;

}

});

}

/**
*
* excute a "session job". a job is passed by calling program, and the
*
* fuction will open a session, and call the job with that session, after
* that,
*
* safely close the session. the job is usually created as anonymous class.
*
* @param job -
* the job to execute.
*
* @return any return value that the job's excute method returns.
*
* @throws DataAccessException
* if any error occures.
*
*/

public static Object executeJob(SessionJob job) throws DataAccessException {

Session ses = null;

Object ret = null;

try {

ses = openSession();

ret = job.execute(ses);

} catch (HibernateException e) {

throw new DataAccessException(e);

} finally {

closeSession(ses);

}

return ret;

}

/**
*
* This is to close a session, but since usually we can't do anything
*
* if a session fails. just log it.
*
* @param ses
* the session to close.
*
*/

public static void closeSession(Session ses) {

if (ses != null) {

try {

ses.close();

} catch (HibernateException e) {

log.warn("unable to close session", e);

}

}

}

}