JDBC(五)_C3P0

最新推荐文章于 2022-06-26 14:17:47 发布

原创最新推荐文章于 2022-06-26 14:17:47 发布 · 325 阅读

0 ·

CC 4.0 BY-SA版权

JDBC 专栏收录该内容

8 篇文章

订阅专栏

package zr.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * c3p0的演示
 */
public class C3P0Demo1 {
    public static void main(String[] args) {
        //1.创建数据库连接池对象
        DataSource ds = new ComboPooledDataSource();
        //2.获取连接对象
        try {
            Connection conn = ds.getConnection();

            //3.打印
            System.out.println(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2.如下代码：（多个连接）

package zr.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * c3p0演示
 */
public class C3P0Demo2 {
    public static void main(String[] args) throws SQLException {
        //1.获取DataSource
        DataSource ds=new ComboPooledDataSource();
        //2.获取连接
        for (int i = 1;i<=10;i++){
            Connection conn=ds.getConnection();
            System.out.println(i+":"+conn);
        }
    }
}

注意这里面大的for循环是获取了十个，再看看配置文件

<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
  <default-config>
    <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/db1?serverTimezone=UTC</property>
    <property name="user">root</property>
    <property name="password">root</property>

    <!--初始化申请的连接数量-->
    <property name="initialPoolSize">5</property>
    <!--最大的连接数量-->
    <property name="maxPoolSize">10</property>
    <!--超时时间-->
    <property name="checkoutTimeout">3000</property>
  </default-config>

  <named-config name="otherc3p0">
    <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/db1?serverTimezone=UTC</property>
    <property name="user">root</property>
    <property name="password">root</property>

    <!--初始化申请的连接数量-->
    <property name="initialPoolSize">5</property>
    <!--最大的连接数量-->
    <property name="maxPoolSize">8</property>
    <!--超时时间-->
    <property name="checkoutTimeout">1000</property>
  </named-config>
</c3p0-config>

这里面maxPoolSize设置为10，没有超过，但是如果把for循环给改成11的话会是以下的情况，这里面由于checkoutTimeout设置的为3000ms，所以在发生异常时，服务器会等待三秒，出现以下的情况

"C:\Program Files\Java\jdk-12.0.2\bin\java.exe" "-javaagent:D:\电脑软件安装位置\ij idea\IntelliJ IDEA 2019.1.3\lib\idea_rt.jar=51240:D:\电脑软件安装位置\ij idea\IntelliJ IDEA 2019.1.3\bin" -Dfile.encoding=UTF-8 -classpath D:\编程存储空间\intellij_workspace\jdbcDataSource_jdbcTemplate\out\production\jdbcDataSource_jdbcTemplate;D:\编程存储空间\intellij_workspace\jdbcDataSource_jdbcTemplate\libs\c3p0-0.9.5.2.jar;D:\编程存储空间\intellij_workspace\jdbcDataSource_jdbcTemplate\libs\mchange-commons-java-0.2.12.jar;D:\编程存储空间\intellij_workspace\jdbcDataSource_jdbcTemplate\libs\mysql-connector-java-8.0.17.jar zr.c3p0.C3P0Demo2
7月 30, 2019 11:58:45 上午 com.mchange.v2.log.MLog 
信息: MLog clients using java 1.4+ standard logging.
7月 30, 2019 11:58:46 上午 com.mchange.v2.c3p0.C3P0Registry 
信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
7月 30, 2019 11:58:46 上午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 3000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge0yta4i1kjt216cn56b|548a9f61, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge0yta4i1kjt216cn56b|548a9f61, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/db1?serverTimezone=UTC, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
1:com.mchange.v2.c3p0.impl.NewProxyConnection@65466a6a [wrapping: com.mysql.cj.jdbc.ConnectionImpl@4ddced80]
2:com.mchange.v2.c3p0.impl.NewProxyConnection@78e117e3 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@2ea227af]
3:com.mchange.v2.c3p0.impl.NewProxyConnection@363ee3a2 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@4690b489]
4:com.mchange.v2.c3p0.impl.NewProxyConnection@3eb7fc54 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@7f552bd3]
5:com.mchange.v2.c3p0.impl.NewProxyConnection@460d0a57 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@47d90b9e]
6:com.mchange.v2.c3p0.impl.NewProxyConnection@3aefe5e5 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@149e0f5d]
7:com.mchange.v2.c3p0.impl.NewProxyConnection@2f7c2f4f [wrapping: com.mysql.cj.jdbc.ConnectionImpl@6af93788]
8:com.mchange.v2.c3p0.impl.NewProxyConnection@36c88a32 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@7880cdf3]
9:com.mchange.v2.c3p0.impl.NewProxyConnection@1c93084c [wrapping: com.mysql.cj.jdbc.ConnectionImpl@6ef888f6]
10:com.mchange.v2.c3p0.impl.NewProxyConnection@7ce3cb8e [wrapping: com.mysql.cj.jdbc.ConnectionImpl@78b66d36]
Exception in thread "main" java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
	at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
	at zr.c3p0.C3P0Demo2.main(C3P0Demo2.java:18)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@4b5a5ed1 -- timeout at awaitAvailable()
	at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
	at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
	... 2 more

Process finished with exit code 1

前十个都正常，最后，第十一个不正常，会报出一个时间out，这里面的解决办法有两种，第一种就是把xml配置文件给改一下，当然这个要看你装的服务器的版本来改，还有你的服务器的硬件配置是否支持；还有一个就是可以在第i次连接完毕后，比如第五次连接后，调用conn.close();来归还连接资源，这样就可以多用一个，其结果如下：

package zr.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * c3p0演示
 */
public class C3P0Demo2 {
    public static void main(String[] args) throws SQLException {
        //1.获取DataSource
        DataSource ds=new ComboPooledDataSource();
        //2.获取连接
        for (int i = 1;i<=11;i++){
            Connection conn=ds.getConnection();
            System.out.println(i+":"+conn);
            if (i == 5){
                conn.close();//归还连接到连接池中
            }
        }
    }
}

"C:\Program Files\Java\jdk-12.0.2\bin\java.exe" "-javaagent:D:\电脑软件安装位置\ij idea\IntelliJ IDEA 2019.1.3\lib\idea_rt.jar=51303:D:\电脑软件安装位置\ij idea\IntelliJ IDEA 2019.1.3\bin" -Dfile.encoding=UTF-8 -classpath D:\编程存储空间\intellij_workspace\jdbcDataSource_jdbcTemplate\out\production\jdbcDataSource_jdbcTemplate;D:\编程存储空间\intellij_workspace\jdbcDataSource_jdbcTemplate\libs\c3p0-0.9.5.2.jar;D:\编程存储空间\intellij_workspace\jdbcDataSource_jdbcTemplate\libs\mchange-commons-java-0.2.12.jar;D:\编程存储空间\intellij_workspace\jdbcDataSource_jdbcTemplate\libs\mysql-connector-java-8.0.17.jar zr.c3p0.C3P0Demo2
7月 30, 2019 12:02:32 下午 com.mchange.v2.log.MLog 
信息: MLog clients using java 1.4+ standard logging.
7月 30, 2019 12:02:32 下午 com.mchange.v2.c3p0.C3P0Registry 
信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
7月 30, 2019 12:02:32 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 3000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge0yta4i1pf0tv2fn8g|548a9f61, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge0yta4i1pf0tv2fn8g|548a9f61, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/db1?serverTimezone=UTC, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {password=******, user=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
1:com.mchange.v2.c3p0.impl.NewProxyConnection@4ddced80 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1534f01b]
2:com.mchange.v2.c3p0.impl.NewProxyConnection@2ea227af [wrapping: com.mysql.cj.jdbc.ConnectionImpl@4386f16]
3:com.mchange.v2.c3p0.impl.NewProxyConnection@4690b489 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@79b06cab]
4:com.mchange.v2.c3p0.impl.NewProxyConnection@7f552bd3 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@3c22fc4c]
5:com.mchange.v2.c3p0.impl.NewProxyConnection@47d90b9e [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1184ab05]
6:com.mchange.v2.c3p0.impl.NewProxyConnection@6af93788 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1184ab05]
7:com.mchange.v2.c3p0.impl.NewProxyConnection@36c88a32 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@7880cdf3]
8:com.mchange.v2.c3p0.impl.NewProxyConnection@1c93084c [wrapping: com.mysql.cj.jdbc.ConnectionImpl@6ef888f6]
9:com.mchange.v2.c3p0.impl.NewProxyConnection@7ce3cb8e [wrapping: com.mysql.cj.jdbc.ConnectionImpl@78b66d36]
10:com.mchange.v2.c3p0.impl.NewProxyConnection@bef2d72 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@69b2283a]
11:com.mchange.v2.c3p0.impl.NewProxyConnection@6fe7aac8 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1d119efb]

Process finished with exit code 0

注意第五个和第六个连接的hashcode都一样