【c3p0】 java.sql.SQLException: An attempt by a client to checkout a Connection...

最新推荐文章于 2022-06-30 15:38:37 发布

原创最新推荐文章于 2022-06-30 15:38:37 发布 · 2.9w 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#c3p0 #exception #spring #junit #class #测试

Java EE 专栏收录该内容

12 篇文章

订阅专栏

本文详细介绍了在使用Spring框架配置c3p0数据库连接池时遇到的连接超时问题，并提供了解决方案。通过移除配置文件中的checkoutTimeout属性，问题得以解决。

java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.

错误信息：

An attempt by a client to checkout a Connection has timed out.

java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:527)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
at services.callcenter.common.DBUtilHelper.searchToMap(DBUtilHelper.java:278)
at services.callcenter.test.PersonDaoImplTest.testPersonDaoImpl(PersonDaoImplTest.java:36)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:35)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@4ac216 -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1317)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
... 22 more

是Spring中配置c3p0的时候，有一个配置属性是checkoutTimeout，把这个配置属性去掉就正常了。

源代码如下：

Spring配置文件：applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans
xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">

<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
     <property name="location">
      <value>classpath:jdbc.properties</value>
</property>
</bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="oracle.jdbc.driver.OracleDriver" />
     <property name="jdbcUrl" value="${jdbc.url}" />
     <property name="user" value="${jdbc.username}" />
     <property name="password" value="${jdbc.password}" />

     <property name="autoCommitOnClose" value="true"/>

     <property name="checkoutTimeout" value="${cpool.checkoutTimeout}"/>

     <property name="initialPoolSize" value="${cpool.minPoolSize}"/>
     <property name="minPoolSize" value="${cpool.minPoolSize}"/>

     <property name="maxPoolSize" value="${cpool.maxPoolSize}"/>
     <property name="maxIdleTime" value="${cpool.maxIdleTime}"/>
     <property name="acquireIncrement" value="${cpool.acquireIncrement}"/>
     <property name="maxIdleTimeExcessConnections" value="${cpool.maxIdleTimeExcessConnections}"/>
</bean>


<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>



<bean id="dbConn" class="services.callcenter.common.DBConnection">
<property name="ds" ref="dataSource"/>
</bean>

<bean id="DBUtilHelper" class="services.callcenter.common.DBUtilHelper">
<property name="ds" ref="dataSource"></property>
</bean>
</beans>public class DBUtilHelper {

private DataSource ds;

    public void setDs(DataSource ds) {
           this.ds = ds;
   }

    public Map searchToMap(String sql) {
        Connection conn = null;
        Map result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new MapHandler();
     try {
      conn = ds.getConnection();
         result = (Map) run.query(conn,sql, h);

     }
     catch (Exception e){
      log.warn(sql);
      log.warn(e.getMessage(),e);

     }
     finally {
      try{
       DbUtils.close(conn);
      }
      catch(Exception e){
       log.warn(e.getMessage(),e);
      }
     }

        return result;
    }

}

Junit测试代码如下：

package ***;

import java.util.Map;

import junit.framework.TestCase;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class PersonDaoImplTest extends TestCase{

ApplicationContext ac = null;
@Test
public void testPersonDaoImpl() throws Exception {
DBUtilHelper db=(DBUtilHelper)ac.getBean("DBUtilHelper");
String sql = "select user_id from ur_users where user_id = 371 ";
Map m = db.searchToMap(sql);
System.out.println(m.toString());
}

protected void setUp() throws Exception{
System.out.println("DDDDD");
ac=new ClassPathXmlApplicationContext("applicationContext.xml");
}
}

关于Spring代码的测试，因为Spring采用配置文件来注入某些属性，而Junit在默认的测试情况下，是不会读取这些配置文件的。所以测试Spring类的时候，需要手工指定配置文件，然后通过ClassPathXmlApplicationContext类来加载这些配置信息。

要注意把applicationContext.xml文件以及jdbc.properties文件放到src目录下。

当然，这里的测试代码只是用System.out.println直接打印出来信息，没有采用assert来判断信息，大家就别深究了！