c3p0连接池例子

原创

wx5b4881866d732 2022-02-25 10:56:41 博主文章分类：数据库连接池 ©著作权

文章标签 java mysql c3p0 l数据库连接池 sql 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者wx5b4881866d732的原创作品，请联系作者获取转载授权，否则将追究法律责任

先导入jia包

驱动jar包

c3p0Jar包

c3p0连接池例子_sql_03

写配置文件

定义配置文件（自动加载）

名称：c3p0.properties或者c3p0-config.xml
路径：根路径，直接把文件放在src目录下即可

这里我们使用c3p0-config.xml

<c3p0-config>  <default-config>  <property name="driverClass">com.mysql.cj.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/study?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8</property> <property name="user">root</property> <property name="password">password</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/study?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8</property> <property name="user">root</property> <property name="password">password</property>  <property name="initialPoolSize">5</property> <property name="maxPoolSize">8</property> <property name="checkoutTimeout">1000</property> </named-config> </c3p0-config>

使用例子demo1

例子有2个方法

testMaxPoolSize()：验证连接池最大连接个数的方法
test（String configName）：查询方法，configName是配置名称，为空则使用默认的配置，传参则使用参数名对应的配置

package com.lingaolu.dataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.cj.util.StringUtils;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 林高禄
 * @create 2020-06-24-17:00
 */
public class C3p0Demo {
    public static void main(String[] args) throws SQLException {
        System.out.println("==========默认配置==========");
        test(null);
        System.out.println("==========特定配置==========");
        test("otherc3p0");
    }

    public static void testMaxPoolSize(DataSource ds) throws SQLException {
        List<Connection> connectionList = new ArrayList<>();
        // 验证获取连接处最大个数
        for(int i=0;i<10;i++){
            Connection connection = ds.getConnection();
            System.out.println(i+"："+connection);
            connectionList.add(connection);
        }
        int size = connectionList.size();
        // 释放连接
        for(int i=0;i<size;i++){
            connectionList.get(i).close();
        }
    }
    public static void test(String configName) throws SQLException {
        DataSource ds = null;

        // 创建数据库连接池对象
        if(StringUtils.isNullOrEmpty(configName)){
            ds = new ComboPooledDataSource();
        }else{
            ds = new ComboPooledDataSource(configName);
        }
        // 验证连接池最大个数
        testMaxPoolSize(ds);
        // 获取连接对象
        Connection connection = ds.getConnection();
        String sql = "select * from account";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            System.out.println(id+name);
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

运行输出：
==========默认配置==========
6 24, 2020 5:31:01 午後 com.mchange.v2.log.MLog
信息: MLog clients using java 1.4+ standard logging.
[Fatal Error] :6:91: 对实体 "serverTimezone" 的引用必须以 ';' 分隔符结尾。
6 24, 2020 5:31:02 午後 com.mchange.v2.c3p0.cfg.C3P0Config
警告: XML configuration disabled! Verify that standard XML libs are available.
org.xml.sax.SAXParseException; lineNumber: 6; columnNumber: 91; 对实体 "serverTimezone" 的引用必须以 ';' 分隔符结尾。
   at com.sun.org.apache.xerces.internal.parsers.DOMParser.parse(DOMParser.java:257)
   at com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderImpl.parse(DocumentBuilderImpl.java:348)
   at javax.xml.parsers.DocumentBuilder.parse(DocumentBuilder.java:121)
   at com.mchange.v2.c3p0.cfg.C3P0ConfigXmlUtils.extractXmlConfigFromInputStream(C3P0ConfigXmlUtils.java:148)
   at com.mchange.v2.c3p0.cfg.C3P0ConfigXmlUtils.extractXmlConfigFromDefaultResource(C3P0ConfigXmlUtils.java:131)
   at com.mchange.v2.c3p0.cfg.DefaultC3P0ConfigFinder.findConfig(DefaultC3P0ConfigFinder.java:73)
   at com.mchange.v2.c3p0.cfg.C3P0Config.findLibraryC3P0Config(C3P0Config.java:188)
   at com.mchange.v2.c3p0.cfg.C3P0Config.<clinit>(C3P0Config.java:144)
   at com.mchange.v2.c3p0.impl.PoolBackedDataSourceBase.<init>(PoolBackedDataSourceBase.java:54)
   at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.<init>(AbstractPoolBackedDataSource.java:74)
   at com.mchange.v2.c3p0.AbstractComboPooledDataSource.<init>(AbstractComboPooledDataSource.java:142)
   at com.mchange.v2.c3p0.AbstractComboPooledDataSource.<init>(AbstractComboPooledDataSource.java:138)
   at com.mchange.v2.c3p0.ComboPooledDataSource.<init>(ComboPooledDataSource.java:47)
   at com.lingaolu.dataSource.C3p0Demo.test(C3p0Demo.java:41)
   at com.lingaolu.dataSource.C3p0Demo.main(C3p0Demo.java:18)

这是因为我们c3p0的jdbcUrl配置里使用了&符号，而我们的配置文件是.xml，所以要用转移字符&代替&，修改下配置

<c3p0-config>
  <!-- 使用默认的配置读取连接池对象 -->
  <default-config>
    <!--  连接参数 -->
    <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/study?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8</property>
    <property name="user">root</property>
    <property name="password">password</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/study?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8</property>
    <property name="user">root</property>
    <property name="password">password</property>

    <!-- 连接池参数 -->
    <property name="initialPoolSize">5</property>
    <property name="maxPoolSize">8</property>
    <property name="checkoutTimeout">1000</property>
  </named-config>
</c3p0-config>

运行输出：
==========默认配置==========
0：com.mchange.v2.c3p0.impl.NewProxyConnection@c818063 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@3f0ee7cb]
1：com.mchange.v2.c3p0.impl.NewProxyConnection@7d417077 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@7dc36524]
2：com.mchange.v2.c3p0.impl.NewProxyConnection@2c8d66b2 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@5a39699c]
3：com.mchange.v2.c3p0.impl.NewProxyConnection@56cbfb61 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1134affc]
4：com.mchange.v2.c3p0.impl.NewProxyConnection@129a8472 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1b0375b3]
5：com.mchange.v2.c3p0.impl.NewProxyConnection@2d209079 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@6bdf28bb]
6：com.mchange.v2.c3p0.impl.NewProxyConnection@2752f6e2 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@e580929]
7：com.mchange.v2.c3p0.impl.NewProxyConnection@7c75222b [wrapping: com.mysql.cj.jdbc.ConnectionImpl@4c203ea1]
8：com.mchange.v2.c3p0.impl.NewProxyConnection@1d251891 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@48140564]
9：com.mchange.v2.c3p0.impl.NewProxyConnection@7c30a502 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@49e4cb85]
1张三
2张三
7张三
11张三
==========特定配置==========
0：com.mchange.v2.c3p0.impl.NewProxyConnection@4909b8da [wrapping: com.mysql.cj.jdbc.ConnectionImpl@3a03464]
1：com.mchange.v2.c3p0.impl.NewProxyConnection@617c74e5 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@6537cf78]
2：com.mchange.v2.c3p0.impl.NewProxyConnection@34b7bfc0 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@366e2eef]
3：com.mchange.v2.c3p0.impl.NewProxyConnection@3cbbc1e0 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@35fb3008]
4：com.mchange.v2.c3p0.impl.NewProxyConnection@54a097cc [wrapping: com.mysql.cj.jdbc.ConnectionImpl@36f6e879]
5：com.mchange.v2.c3p0.impl.NewProxyConnection@3551a94 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@531be3c5]
6：com.mchange.v2.c3p0.impl.NewProxyConnection@735b478 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@2c9f9fb0]
7：com.mchange.v2.c3p0.impl.NewProxyConnection@9f70c54 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@234bef66]
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 com.lingaolu.dataSource.C3p0Demo.testMaxPoolSize(C3p0Demo.java:27)
   at com.lingaolu.dataSource.C3p0Demo.test(C3p0Demo.java:47)
   at com.lingaolu.dataSource.C3p0Demo.main(C3p0Demo.java:20)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@6b143ee9 -- 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)
   ... 4 more

从结果可以看出，我们的默认配置最大连接个数是10.所以没问题，而我们特定配置的最大连接个数是8，所以取到第9个时候，就要没有了，报了连接超时的异常。