java - Hibernate 关闭 MySQL 连接

Question

我在几个小时不活动后无法登录我的应用程序。我正在使用 Java、Spring Security、Struts 1.3 和 Hibernate。

这是我找到的与此问题相关的日志

2014-07-30 14:11:40 INFO LocalSessionFactoryBean:246 - Closing Hibernate SessionFactory

java.io.EOFException
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1388)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1532)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1923)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1555)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)

/var/lib/tomcat7/logs# 2014-07-30 14:11:40 INFO  SessionFactoryImpl:769 - closing
    at org.hibernate.loader.Loader.doQuery(Loader.java:674)
    at  org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
    at org.hibernate.loader.Loader.doList(Loader.java:2220)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
    at org.hibernate.loader.Loader.list(Loader.java:2099)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
    at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)
    at se.etm.ewo.daoImpl.UserDaoImpl.getUserByUsername(Unknown Source)
    at se.etm.ewo.daoImpl.UserDaoImpl.loadUserByUsername(Unknown Source)
    at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:82)
    at org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:124)
    at org.springframework.security.authentication.ProviderManager.doAuthentication(ProviderManager.java:120)
    at org.springframework.security.authentication.AbstractAuthenticationManager.authenticate(AbstractAuthenticationManager.java:48)
    at org.springframework.security.authentication.ProviderManager.doAuthentication(ProviderManager.java:138)

这是我的 hibernate 配置文件属性

  <property name="hibernate.dialect">se.etm.ewo.hibernate.CustomDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://my_ip:my_port/my_database</property>
    <property name="hibernate.connection.username">username</property>
    <property name="hibernate.connection.password">password</property>
    <property name="hibernate.connection.autoReconnect">true</property>
    <property name="hibernate.connection.autoReconnectForPools">true</property>
    <property name="hibernate.c3p0.idle_test_period">100</property>
    <!-- Echo all executed SQL to stdout -->
    <property name="show_sql">true</property>

这是spring的配置文件属性

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    destroy-method="close">
    <property name="driverClass">
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <property name="jdbcUrl">
        <value>jdbc:mysql://my_ip:my_port/my_database</value>
    </property>
    <property name="user">
        <value>username</value>
    </property>
    <property name="password">
        <value>password</value>
    </property>
    <property name="initialPoolSize">
        <value>10</value>
    </property>
    <property name="maxPoolSize">
        <value>30</value>
    </property>
    <property name="minPoolSize">
        <value>10</value>
    </property>
    <property name="maxConnectionAge">
        <value>3600</value>
    </property>
    <property name="maxIdleTime">
        <value>3600</value>
    </property>
    <property name="maxIdleTimeExcessConnections">
        <value>1800</value>
    </property>
    <property name="acquireRetryAttempts">
        <value>3</value>
    </property>
    <property name="acquireRetryDelay">
        <value>3000</value>
    </property>
    <property name="breakAfterAcquireFailure">
        <value>false</value>
    </property>
    <property name="preferredTestQuery">
        <value>SELECT 1</value>
    </property>
</bean>

请帮我解决这个问题。

Answer 1

MySQL 默认会在 8 小时后关闭空闲连接(即 wait_timeout 文件中的 my.ini 参数)。

• 因此，您自然可以编辑 my.ini文件并更改 wait_timeout 的值，或者只需在 mysql shell 中键入以下命令:SET GLOBAL wait_timeout=86400

• 您还可以设置属性 validationQuery在你的 hibernate 配置文件中:
<property name="validationQuery">select 1</property>
我更喜欢这个。

了解更多 validationQuery : http://people.apache.org/~fhanik/tomcat/jdbc-pool.html