c3p0连接池例子

c3p0连接池例子

2020-06-24 22:2:47发布 浏览数:879
概述:c3p0连接池例子

 先导入jia包 

驱动jar包
 
 
c3p0Jar包
 

 

写配置文件

定义配置文件
名称: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;

 



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,所以要用转移字符&amp;代替&,修改下配置
<c3p0-config>

  <!-- 使用默认的配置读取连接池对象 -->

  <default-config>

  	<!--  连接参数 -->

    <property name="driverClass">com.mysql.cj.jdbc.Driver</property>

    <property name="jdbcUrl">jdbc:mysql://localhost:3306/study?useSSL=false&amp;serverTimezone=UTC&amp;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&amp;serverTimezone=UTC&amp;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个时候,就要没有了,报了连接超时的异常。 


请先
登录
后评论
0 条评论
暂时没有评论