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

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>

<!-- Transaction manager for a single JDBC DataSource -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>

<!-- ========================= DAO DEFINITIONS: DBUTILS IMPLEMENTATIONS ========================= -->

<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來判斷資訊，大家就別深究了！