沃梦达 / 编程技术 / 编程语言 / Java开发 / 正文

java – 在超时连接关闭后,hibernate无法重新连接到mysql

Java开发
2023-11-05

我有一个Java应用程序,我想创建池连接.我在Jboss AS 7.0.2上使用Hibernate 4.0_final和c3p0,MySQLhibernate.cfg.xml中：hibernate-configurationsession-factory property name=hibernate.connection.driver_...

我有一个Java应用程序,我想创建池连接.我在Jboss AS 7.0.2上使用Hibernate 4.0_final和c3p0,MySQL

hibernate.cfg.xml中：

<hibernate-configuration>
 <session-factory >
  <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property>
  <property name="hibernate.connection.username">root</property>
  <property name="hibernate.connection.password"></property>
  <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
  <property name="hibernate.show_sql">true</property>

  <property name="hibernate.c3p0.acquire_increment">1</property>
  <property name="hibernate.c3p0.idle_test_period">100</property>
  <property name="hibernate.c3p0.max_size">100</property>
  <property name="hibernate.c3p0.max_statements">0</property>
  <property name="hibernate.c3p0.min_size">5</property>
  <property name="hibernate.c3p0.timeout">30</property>
 <!--  <property name="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property> -->

  <mapping class="com.test.Test"/>

c3p0.properties：

c3p0.testConnectionOnCheckout=true
c3p0.acquireRetryDelay=1000
c3p0.acquireRetryAttempts=1

我从中获得Session的课程,用于处理数据库

public class HibernateSessionFactory {

    private static String CONFIG_FILE_LOCATION = "/hibernate.cfg.xml";
    private static final ThreadLocal<Session> threadLocal = new ThreadLocal<Session>();
    private  static org.hibernate.cfg.Configuration configuration = new org.hibernate.cfg.AnnotationConfiguration();    
    private static org.hibernate.SessionFactory sessionFactory;
    private static String configFile = CONFIG_FILE_LOCATION;

    static {
        rebuildSessionFactory();
    }

    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) {
            e.printStackTrace();
        }
    }
}

在创建SessionFactory之后,我在MySQL_WorkBench中看到了与我的数据库的5个连接.之后,我发送请求并等待50秒(在MySql_Server中wait_timeout,我设置为测试).
在Workbench中,我看到已经打开4连接.我发送请求并获得错误,但在工作台中我看到已经有5个连接.

错误：

org.hibernate.exception.JDBCConnectionException: No operations allowed after connection closed.
    org.hibernate.exception.internal.SQLStateConverter.convert(SQLStateConverter.java:107)
    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:146)
    org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    $Proxy18.prepareStatement(Unknown Source)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
    org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1739)
    org.hibernate.loader.Loader.doQuery(Loader.java:828)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    org.hibernate.loader.Loader.doList(Loader.java:2463)
    org.hibernate.loader.Loader.doList(Loader.java:2449)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
    org.hibernate.loader.Loader.list(Loader.java:2274)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115)
    org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150)
    com.test.servlets.PostFilter.doFilter(PostFilter.java:75)
    com.test.servlets.PathFilter.doFilter(PathFilter.java:57)


root cause 
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    com.mysql.jdbc.Util.getInstance(Util.java:386)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
    com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1205)
    com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1197)
    com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4186)
    com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4152)
    com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:213)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:601)
    org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138)
    org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    $Proxy18.prepareStatement(Unknown Source)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
    org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
    org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1739)
    org.hibernate.loader.Loader.doQuery(Loader.java:828)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    org.hibernate.loader.Loader.doList(Loader.java:2463)
    org.hibernate.loader.Loader.doList(Loader.java:2449)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
    org.hibernate.loader.Loader.list(Loader.java:2274)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115)
    org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150)
    com.test.servlets.PostFilter.doFilter(PostFilter.java:75)
    com.test.servlets.PathFilter.doFilter(PathFilter.java:57)


root cause 
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 96 189 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3102)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
    com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:601)
    org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    $Proxy19.executeQuery(Unknown Source)
    org.hibernate.loader.Loader.getResultSet(Loader.java:1978)
    org.hibernate.loader.Loader.doQuery(Loader.java:829)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    org.hibernate.loader.Loader.doList(Loader.java:2463)
    org.hibernate.loader.Loader.doList(Loader.java:2449)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
    org.hibernate.loader.Loader.list(Loader.java:2274)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115)
    org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150)
    com.test.servlets.PostFilter.doFilter(PostFilter.java:75)
    com.test.servlets.PathFilter.doFilter(PathFilter.java:57)


root cause 
java.net.SocketException: Software caused connection abort: recv failed
    java.net.SocketInputStream.socketRead0(Native Method)
    java.net.SocketInputStream.read(SocketInputStream.java:150)
    java.net.SocketInputStream.read(SocketInputStream.java:121)
    com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
    com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
    com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
    com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2549)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
    com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:601)
    org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    $Proxy19.executeQuery(Unknown Source)
    org.hibernate.loader.Loader.getResultSet(Loader.java:1978)
    org.hibernate.loader.Loader.doQuery(Loader.java:829)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    org.hibernate.loader.Loader.doList(Loader.java:2463)
    org.hibernate.loader.Loader.doList(Loader.java:2449)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
    org.hibernate.loader.Loader.list(Loader.java:2274)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115)
    org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:890)
    com.test.dao.UserDAO.getTimeLastActivity(UserDAO.java:150)
    com.test.servlets.PostFilter.doFilter(PostFilter.java:75)
    com.test.servlets.PathFilter.doFilter(PathFilter.java:57)

如何解决？

解决方法:

我遇到过这样的问题,我从服务器日志中得到了解决方案.

    com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was56936 milliseconds ago.The last packet sent successfully to the server was 56936 milliseconds ago, 
which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, 
or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

尝试将连接URL设置为：

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

但是,在使用任何事务存储引擎时,他们不建议使用它.

本文标题为：java – 在超时连接关闭后,hibernate无法重新连接到mysql

上一篇： java – Netbeans 7.1 Hibernate Reverse Engineering向导找不到数据库驱动程序
下一篇： Java连接Mysql由于版本更新报错

基础教程推荐

学习HTML
学习jQuery
学习Laravel
学习CSS3
学习Vue.js
学习Bootstrap5
学习ThinkPHP
学习AJAX
猜你喜欢
最新文章
热门文章
编程基础
热门标签
网站首页 HTML/CSS 菜单 Layui Yii2