java.sql.SQLRecoverableException：关闭的连接

java.sql.SQLRecoverableException: Closed Connection

提问by Ram

I'm working on Struts 2 Framework with Hibernate 3.3 and using Oracle 11g. My web project was working fine since 5 months. But recently I'm facing the java.sql.SQLRecoverableException: Closed Connection during some idle time. I'll explain the scenario follows.. My hibernate.cfg.xml configuration is

我正在使用 Hibernate 3.3 和 Oracle 11g 开发 Struts 2 框架。我的网络项目从 5 个月开始就运行良好。但最近我在空闲时间遇到了 java.sql.SQLRecoverableException: Closed Connection 问题。我将解释以下场景.. 我的 hibernate.cfg.xml 配置是

<property name="hibernate.current_session_context_class">
        thread
    </propert
<property name="connection.url">jdbc:oracle:thin:@localhost:1521:orcl</property>
<property name="connection.username">user</property>
<property name="connection.password">user</property>
<property name="connection.driver_class">
        oracle.jdbc.OracleDriver
    </property>
<property name="myeclipse.connection.profile">Oracle</property>
<property name="hibernate.connection.autoReconnect">true</property>
<property name="dialect"> org.hibernate.dialect.Oracle10gDialect </property>

And my HibernateSessionFactory conf is

我的 HibernateSessionFactory conf 是

private static String CONFIG_FILE_LOCATION = "/hibernate.cfg.xml";
private static final ThreadLocal<Session> threadLocal = new ThreadLocal<Session>();
private static Configuration configuration = new Configuration();
private static org.hibernate.SessionFactory sessionFactory;
private static String configFile = CONFIG_FILE_LOCATION;
static {
    try {
        configuration.configure(configFile);
        sessionFactory = configuration.buildSessionFactory();
    } catch (Exception e) {
        System.err
                .println("%%%% Error Creating SessionFactory %%%%");
        //e.printStackTrace();
    }
}
private HibernateSessionFactory() {
}
public static Session getSession() throws HibernateException {
    Session session = (Session) threadLocal.get();

    if (session == null || !session.isOpen()) {
        if (sessionFactory == null) {
            rebuildSessionFactory();
        }
        session = (sessionFactory != null) ? sessionFactory.openSession()
                : null;
        threadLocal.set(session);
    }

    return session;
}
public static void rebuildSessionFactory() {
    try {
        configuration.configure(configFile);
        sessionFactory = configuration.buildSessionFactory();
    } catch (Exception e) {
        System.err
                .println("%%%% Error Creating SessionFactory %%%%");
        //e.printStackTrace();
    }
}
public static void closeSession() throws HibernateException {
    Session session = (Session) threadLocal.get();
    threadLocal.set(null);

    if (session != null) {
        session.close();
    }
}
public static org.hibernate.SessionFactory getSessionFactory() {
    return sessionFactory;
}
public static void setConfigFile(String configFile) {
    HibernateSessionFactory.configFile = configFile;
    sessionFactory = null;
}
public static Configuration getConfiguration() {
    return configuration;
}

Then the error comes when the following code runs

然后当下面的代码运行时出现错误

session = HibernateSessionFactory.getSession();
Query query = session.createQuery(SQL_QUERY);
try {
                // session.connection().close();
                System.out.println("CLOSED :"
                        + session.connection().isClosed());
                if (session.connection().isClosed()) {
                    System.out.println("RECONNECTING.......");
                    session.reconnect();
                }
                System.out.println("CLOSED :"
                        + session.connection().isClosed());
            } catch (Exception e1) {

                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
try {
for (Iterator it = query.iterate(); it.hasNext();) {
                    chk = true;
                    ur = (EmagEnterpriseLogin) it.next();
} catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

When the above snippet runs even though able to session was opened and created query successfully, But fails at Iterating query. First time comes as

当上面的代码片段运行时，即使能够打开会话并成功创建查询，但在迭代查询时失败。第一次来作为

Caused By:java.sql.SQLRecoverableException: Closed Connection

But before this error in first try i captured the session.Connection.isClosed() was false. But after first error then keeps on coming as SQL State=null and from second time i could get session.Connection.isClosed() was true. And then reconnecting executes but still the same error repeats.I also tried rebuilding sessionfactory, that too failed. Please help me to solve this issue.

但在第一次尝试出现此错误之前，我捕获了 session.Connection.isClosed() 为 false。但是在第一个错误之后继续出现 SQL State=null 并且从第二次我可以得到 session.Connection.isClosed() 是真的。然后重新连接执行但仍然重复相同的错误。我也尝试重建 sessionfactory，但也失败了。请帮我解决这个问题。

回答by Ram

Finally i found the solution for my problem is using third party Connection Provider instead of using Hibernate Native connection provider. I used c3p0 Connection provider , that also given me some problems and i solved that too. The reference link for c3p0 is here.. Hibernate c3p0 Connection NewPooledConnection close Exception

最后我发现我的问题的解决方案是使用第三方连接提供程序而不是使用 Hibernate Native 连接提供程序。我使用了 c3p0 Connection provider ，这也给我带来了一些问题，我也解决了这个问题。c3p0 的参考链接在这里.. Hibernate c3p0 Connection NewPooledConnection close Exception