Hibernate连接MySQL数据库超时问题

使用hibernate.cfg.xml进行数据库连接配置会有mysql连接超时的问题。如果连接闲置8小时 (8小时内没有进行数据库操作), mysql就会自动断开连接, 要重启tomcat。

而直观的错误就是500内部错误。

 

org.hibernate.exception.JDBCConnectionException: could not execute query using iterate

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 37,282,389 milliseconds ago.  The last packet sent successfully to the server was 37,282,389 milliseconds ago. 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.

java.net.SocketException: Software caused connection abort: socket write error

为了避免此问题，最好的方式就是在获取连接时检查一下，看看连接是否有效，如果无效就重新连接一个。

需要在hibernate.cfg.xml中进行c3p0的属性配置，其中c3p0在使用的hibernate的版本中自带。

	    <!-- c3p0 -->
	    <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
	    <!-- 最小连接数 -->
	    <property name="hibernate.c3p0.min_size">5</property>
	    <!-- 最大连接数 -->
	    <property name="hibernate.c3p0.max_size">20</property>
	    <!-- 超时时间 -->
	    <property name="hibernate.c3p0.timeout">1800</property>
	    <!-- 支持最大statement数 -->
	    <property name="hibernate.c3p0.max_statements">50</property>
	    <!-- 连接检查，检查该连接是否被关闭 -->
	    <property name="hibernate.c3p0.testConnectionOnCheckout">true</property><!-- c3p0 -->
	    <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
	    <!-- 最小连接数 -->
	    <property name="hibernate.c3p0.min_size">5</property>
	    <!-- 最大连接数 -->
	    <property name="hibernate.c3p0.max_size">20</property>
	    <!-- 超时时间 -->
	    <property name="hibernate.c3p0.timeout">1800</property>
	    <!-- 支持最大statement数 -->
	    <property name="hibernate.c3p0.max_statements">50</property>
	    <!-- 连接检查，检查该连接是否被关闭 -->
	    <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>