背景,接到故障上报,hibernate 链接mysql 每天早晨 数据库链接超时 08S01 错误
解决方案:
1、使用hibernate4 + proxool链接池
2、pom配置
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>4.2.21.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>4.2.21.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-proxool</artifactId>
<version>4.2.21.Final</version>
<exclusions>
<exclusion>
<groupId>proxool</groupId>
<artifactId>proxool</artifactId>
</exclusion>
</exclusions>
</dependency>
hibernate4 默认使用的proxool8.3.0.jar 这个版本的有问题,所以要屏蔽掉,然后使用proxool-0.9.1.jar和proxool-cglib.jar这俩包
3、proxool.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<something-else-entirely>
<proxool>
<alias>mysql</alias>
<driver-url>jdbc:mysql://127.0.0.1:3306/cqcmcc-o2server?useUnicode=true&characterEncoding=UTF-8</driver-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<driver-properties>
<property name="user" value="root" />
<property name="password" value="" />
</driver-properties>
<!-- proxool自动侦察各个连接状态的时间间隔(毫秒),侦察到空闲的连接就马上回收,超时的销毁 -->
<house-keeping-sleep-time>6000</house-keeping-sleep-time>
<!-- 指因未有空闲连接可以分配而在队列中等候的最大请求数,超过这个请求数的用户连接就不会被接受 -->
<maximum-new-connections>100</maximum-new-connections>
<!-- 最少保持的空闲连接数 -->
<prototype-count>3</prototype-count>
<maximum-connection-count>20</maximum-connection-count>
<!-- 最小连接数 -->
<minimum-connection-count>3</minimum-connection-count>
<!-- 在分配连接前后是否进行有效性测试,这个是解决本问题的关键 -->
<test-before-use>true</test-before-use>
<test-after-use>true</test-after-use>
<!-- 用于测试的SQL语句 -->
<house-keeping-test-sql>SELECT CURRENT_USER</house-keeping-test-sql>
</proxool>
</something-else-entirely>
4、hibernate.cfg.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.proxool.xml">proxool.xml</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="connection.provider_class">org.hibernate.service.jdbc.connections.internal.ProxoolConnectionProvider</property>
<property name="hibernate.proxool.pool_alias">mysql</property>
<property name="show_sql">true</property>
<property name="hibernate.format_sql">true</property>
<mapping resource="hbmxml/SMSBean.hbm.xml"/>
<!-- mapping resource="hbmxml/MoaBean.hbm.xml"/-->
</session-factory>
</hibernate-configuration>
这样完美解决了 mysql 8小时连接超时问题