XiaoMing's Blog
首页
时间轴
标签
分类
清单
友链
关于
avatar
文章
20
标签
33
分类
13
首页
时间轴
标签
分类
清单
友链
关于
目录
  1. 1. 1 C3P0简介
  2. 2. 2 使用步骤
    1. 2.1. 2.1 创建项目
    2. 2.2. 2.2 导入相关的jar包:
    3. 2.3. 2.3 C3P0初始化
      1. 2.3.1. 2.3.1 通过硬编码初始化C3P0
      2. 2.3.2. 2.3.2 通过自动加载配置文件初始化DBCP
        1. 2.3.2.1. 2.3.2.1 c3p0-config.xml
        2. 2.3.2.2. 2.3.2.2 c3p0.properties
    4. 2.4. 2.4 通过DBCP获取数据库连接
    5. 2.5. 2.5 关闭连接
    6. 2.6. 2.6 测试C3P0
数据库连接池C3P0连接池(mysql)
|数据库
字数总计:2.2k|阅读时长: 10 分钟

本仙鱼又更博了,我是勤劳的小蜜蜂嗡嗡嗡

1 C3P0简介

C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。

在开始之前,我们先看一下C3P0和DBCP的区别:
1.dbcp没有自动回收空闲连接的功能,c3p0有自动回收空闲连接功能

​ 2.dbcp需要手动加载配置文件,c3p0自动加载配置文件

好,接下来也是本仙鱼手把手带你来认识C3P0~

2 使用步骤

2.1 创建项目

本仙使用的是IDEA,个人觉得创建项目常规操作,略过.

2.2 导入相关的jar包:

导入如下两个jar包:c3p0-0.9.1.2.jar

​ mchange-commons-java-0.2.11.jar

​ mysql-connector-java-5.1.41.jar

本篇博客中不再进行数据库增删改查的操作,mysql-connector-java-5.1.41.jar可以不加,因为习惯问题,我加上了~哈哈.

如果你没有这几个jar包的话:点击进入下载传送门

C3P0所需要的jar包

想要去使用C3P0那我们就要对它的属性们进行一个初始化的设置,本篇博客中只举简单的例子,不再进行数据库增删改查的操作,数据库连接池之自定义连接池中有相关的操作,可以去那里查看呦.

2.3 C3P0初始化

先放一句很有魔性的话:

初始化C3P0连接池,如果没有配置文件(.xml,.properties),就自己设置,有配置文件自动加载.

2.3.1 通过硬编码初始化C3P0

通过硬编码直接初始化C3P0:就是在没有配置文件的情况下,手动在静态代码块中将要使用的属性(只列举部分)直接进行初始化赋值操作:

java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
public class C3P0 {
    //C3P0使用的是ComboPooledDataSource类
    public static ComboPooledDataSource dataSource;

    //初始化C3P0
    static {
        try {
            //1.初始化C3P0连接池(有xml配置文件会自动加载)
            dataSource = new ComboPooledDataSource();
            //四个基本属性
            dataSource.setDriverClass("com.mysql.jdbc.Driver");
            dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydatabase");
            dataSource.setUser("root");
            dataSource.setPassword("123456789");
            //其他属性
            //初始大小
            dataSource.setInitialPoolSize(10);
            //最大大小
            dataSource.setMaxPoolSize(50);
            //最小大小
            dataSource.setMinPoolSize(10);
            //连接池中连接不够时,每次增长个数
            dataSource.setAcquireIncrement(5);
            //检查时间
            dataSource.setCheckoutTimeout(5000);
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
    }
}

2.3.2 通过自动加载配置文件初始化DBCP

配置文件有两种方式:

1 .xml文件
2 .properties文件

如果xml配置文件和属性文件都存在时，xml优先级高于属性文件

有配置文件时,C3P0会自动加载配置文件完成初始化,所以我们给出配置文件就可以了

2.3.2.1 c3p0-config.xml

必须是c3p0-config.xml,不能改,不然自动加载的时候找不到配置文件!

必须是c3p0-config.xml,不能改,不然自动加载的时候找不到配置文件!

必须是c3p0-config.xml,不能改,不然自动加载的时候找不到配置文件!

重要的事情讲三遍,我就犯了这个错误嘿嘿

使用.xml配置文件有一个好处就是可以配置多个数据库, 包含命名配置文件和默认配置文件!默认是选择默认配置!如果需要切换命名配置可以在创建c3p0连接池的时候填入命名即可!

xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

<c3p0-config>
    
    <default-config>
        
        <property name="driverClass">com.mysql.jdbc.Driverproperty>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/mydatabaseproperty>
        <property name="user">rootproperty>
        <property name="password">123456789property>
        
        
        <property name="checkoutTimeout">10000property>
        
        <property name="idleConnectionTestPeriod">30property>
        
        <property name="initialPoolSize">10property>
        
        <property name="acquireIncrement">5property>
        
        <property name="maxIdleTime">20property>
        <property name="maxPoolSize">50property>
        <property name="minPoolSize">5property>
    default-config>
    
    <named-config name="database">
        <property name="driverClass">com.mysql.jdbc.Driverproperty>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/databaseproperty>
        <property name="user">rootproperty>
        <property name="password">123456789property>
        
        <property name="acquireIncrement">5property>
        <property name="initialPoolSize">20property>
        <property name="minPoolSize">10property>
        <property name="maxPoolSize">40property>
    named-config>
c3p0-config>

调用命名配置文件:

java
1
2
3
4
5
6
7
8
9
10
11
12
public class C3P0 {
    //C3P0使用的是ComboPooledDataSource类
    public static ComboPooledDataSource dataSource;

    //初始化C3P0
    static {
        //1.加载默认配置文件
		dataSource = new ComboPooledDataSource();
        //2.加载命名配置文件
        dataSource = new ComboPooledDataSource("database");
    }
}
2.3.2.2 c3p0.properties

c3p0.properties(必须加出c3p0这个前缀,避免属性被使用时发生异常)

properties
1
2
3
4
5
6
7
8
c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/mydatabase
c3p0.user=root
c3p0.password=123456789
c3p0.acquireIncrement=5
c3p0.initialPoolSize=20
c3p0.minPoolSize=10
c3p0.maxPoolSize=40

2.4 通过DBCP获取数据库连接

java
1
2
3
4
5
6
7
8
9
10
11
public class C3P0 {
     //2.获取连接
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

2.5 关闭连接

注意:我们这里的关闭连接并不是真的关闭了数据库连接,而是将连接回收,重新放入了DBCP中,和我们自定义写的数据库连接池不一样的欧~关的顺序问题我就不再强调了,本仙女的记忆方式,后开的先关

java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public class C3P0 {
    //3.关闭连接
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2.6 测试C3P0

就这么写完了当然要测试一下,那我就简单的用Junit测试一下,顺便带上测试结果,方便您理解这个过程:

测试代码:

java
1
2
3
4
5
6
7
8
9
@Test
   public void C3P0Test() {
       //超过最大限制或报"TimeoutException",每打开一个关闭一个就不会发生异常
       for (int i = 0; i < 45; i++) {
           Connection connection = C3P0.getConnection();
           System.out.println(connection.toString() + "\n------------------------------------");
           C3P0.closeAll(connection, null, null);
       }
   }

测试结果:

java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
四月 17, 2019 10:15:13 下午 com.mchange.v2.log.MLog 
信息: MLog clients using java 1.4+ standard logging.
四月 17, 2019 10:15:14 下午 com.mchange.v2.c3p0.C3P0Registry 
信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
四月 17, 2019 10:15:14 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 2s8x34a2c40utb1ifmklh|33e5ccce, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2s8x34a2c40utb1ifmklh|33e5ccce, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql:///mydatabase, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 50, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
com.mchange.v2.c3p0.impl.NewProxyConnection@5419f379 [wrapping: com.mysql.jdbc.JDBC4Connection@7dc7cbad]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@1753acfe [wrapping: com.mysql.jdbc.JDBC4Connection@7c16905e]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@2a5ca609 [wrapping: com.mysql.jdbc.JDBC4Connection@20e2cbe0]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@28feb3fa [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@482f8f11 [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@7823a2f9 [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@457e2f02 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@39aeed2f [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@4678c730 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@29ee9faa [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@cc285f4 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@8bd1b6a [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@cb5822 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@2b98378d [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@1d057a39 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@4c70fda8 [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@14acaea5 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@d8355a8 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@28d25987 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@523884b2 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@61832929 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@3ffc5af1 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@26653222 [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@68c4039c [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@59f99ea [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@6f7fd0e6 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@3e6fa38a [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@77caeb3e [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@42d80b78 [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@1bce4f0a [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@5c3bd550 [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@604ed9f0 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@685cb137 [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@7cd62f43 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@6093dd95 [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@4883b407 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@39c0f4a [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@42e26948 [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@343f4d3d [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@5442a311 [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@1a968a59 [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@77cd7a0 [wrapping: com.mysql.jdbc.JDBC4Connection@1593948d]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@e25b2fe [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@146ba0ac [wrapping: com.mysql.jdbc.JDBC4Connection@675d3402]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@6eebc39e [wrapping: com.mysql.jdbc.JDBC4Connection@464bee09]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@13deb50e [wrapping: com.mysql.jdbc.JDBC4Connection@464bee09]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@3abbfa04 [wrapping: com.mysql.jdbc.JDBC4Connection@464bee09]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@31ef45e3 [wrapping: com.mysql.jdbc.JDBC4Connection@464bee09]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@3c0ecd4b [wrapping: com.mysql.jdbc.JDBC4Connection@464bee09]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@5f341870 [wrapping: com.mysql.jdbc.JDBC4Connection@464bee09]
------------------------------------
com.mchange.v2.c3p0.impl.NewProxyConnection@4f7d0008 [wrapping: com.mysql.jdbc.JDBC4Connection@464bee09]
------------------------------------

Process finished with exit code 0

看到这里你明白C3P0是怎么实现的了吗?

最后,小仙?在这里感谢您看到最后,有收获的话给本仙鱼点个赞吧~

有问题留言呦,再见祝您生活愉快

文章作者: XiaoMing
文章链接: https://xiaoming0929.github.io/2020/04/02/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0C3P0%E8%BF%9E%E6%8E%A5%E6%B1%A0-mysql/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 XiaoMing's Blog！
数据库连接池 C3P0
打赏
  • 微信
    微信
  • 支付寶
    支付寶
相关推荐
2020-04-02
数据库连接池之自定义连接池(mysql)
2020-04-02
数据库连接池之Druid连接池(mysql)
2020-04-02
数据库连接池之DBCP连接池(mysql)
繁