数据库连接池问题

最新推荐文章于 2021-01-19 07:17:58 发布

原创最新推荐文章于 2021-01-19 07:17:58 发布 · 193 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hibernate #jdbc #java

java 同时被 2 个专栏收录

10 篇文章

订阅专栏

spring

2 篇文章

订阅专栏

org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Cannot open connection
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:596)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
[yuehui] 2013-06-09 15:47:49.4749 - [INFO]:  @qq.com该邮件已标识！
	at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
	at yuehui.service.imp.MyService$$EnhancerByCGLIB$$8768e983.query(<generated>)
	at yuehui.util.Login.run(Login.java:162)
	at java.lang.Thread.run(Thread.java:619)
Caused by: org.hibernate.exception.JDBCConnectionException: Cannot open connection
[yuehui] 2013-06-09 15:47:49.4749 - [INFO]:  @qq.com该邮件已标识！
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
	at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
	at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:160)
	at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:81)
[yuehui] 2013-06-09 15:47:49.4749 - [INFO]:  @qq.com该邮件已标识！
	at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1473)
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:555)
	... 8 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
[yuehui] 2013-06-09 15:47:49.4749 - [INFO]:  @qq.com该邮件已标识！
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2214)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:781)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284)
	at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
	at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
	at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:840)
	at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96)
	at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
	at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
	... 13 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
	at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:675)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1078)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2142)
	... 29 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2497)
	at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:599)
	... 31 more

1.这几天下雨mysql服务器不稳定，总是连不上，然后程序就报上面的错，这时用的是dbcp连接池。

2.换成c3p0解决问题

3.dbcp配置

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" >
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		
		<property name="url" value="jdbc:mysql://www.xxx.com:3306/xxx?useUnicode=true&amp;characterEncoding=utf-8" />
		<property name="username" value="xxx" />
		<property name="password" value="xxx" />

		<property name="maxIdle" value="30" />
		<property name="minIdle" value="2" />
		<property name="maxWait" value="60000" />
		<property name="maxActive" value="1000" />
		<property name="testOnBorrow" value="true" />
		<property name="testWhileIdle" value="true" />
		<property name="validationQuery" value="SELECT COUNT(*) FROM DUAL" />
		<property name="minEvictableIdleTimeMillis" value="600000" />
		<property name="timeBetweenEvictionRunsMillis" value="600000" />
	</bean>

4.c3p0配置文件

	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" >
		<property name="driverClass" value="com.mysql.jdbc.Driver" />
		
		<property name="jdbcUrl" value="jdbc:mysql://www.xxx.com:3306/xxx?useUnicode=true&amp;characterEncoding=utf-8" />
		<property name="user" value="xxx" />
		<property name="password" value="xxx" />

        <property name="acquireIncrement"><value>5</value></property>
        <property name="acquireRetryAttempts" value ="30"/>
        <property name="acquireRetryDelay" value = "3000"/>
        <property name="automaticTestTable" value = "test"/> 和preferredTestQuery 二选一
        <property name="preferredTestQuery" value = "SELECT COUNT(*) FROM DUAL"/>
        <property name="idleConnectionTestPeriod"><value>3000</value></property>
        <property name="checkoutTimeout"><value>3000</value></property>
        <property name="maxPoolSize"><value>80</value></property>
        <property name="minPoolSize"><value>1</value></property>
        <property name="maxStatements"><value>6000</value></property>
        <property name="initialPoolSize"><value>5</value></property>
	</bean>

5.需要三个jar：c3p0-0.9.2.1.jar ； c3p0-oracle-thin-extras-0.9.2.1.jar ；mchange-commons-java-0.2.3.4.jar