JDBC C3P0数据库连接池

配置数据库连接池C3P0两种方式

1.手动配置

    //方式一
    @Test
    public void testGetConnection() throws Exception{
      //获取C3P0数据库连接池
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass("com.mysql.jdbc.Driver");
        cpds.setJdbcUrl("jdbc:mysql://localhost:3306/firstDB?rewriteBatchedStatements=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8");
        cpds.setUser("root");
        cpds.setPassword("123");
        //通过设置相关的参数，对数据库连接池进行管理:
        //设置初始时数据库连接池中连接数
        cpds.setInitialPoolSize(10);
        Connection conn = cpds.getConnection();
        System.out.println(conn);

        //销毁c3p0数据库连接池
//        DataSources.destroy(cpds);
    }
//输出
十二月 22, 2019 9:07:23 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十二月 22, 2019 9:07:23 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 22, 2019 9:07:23 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: 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, dataSourceName -> helloc3p0, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1eslg1oa7coogetzjd128|e6ea0c6, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql:///firstDB?rewriteBatchedStatements=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 50, maxStatementsPerConnection -> 2, minPoolSize -> 10, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
com.mchange.v2.c3p0.impl.NewProxyConnection@3cda1055

2.通过XML文件配置

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    <named-config name="helloc3p0">
        <!-- 提供获取连接的4个基本信息 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///firstDB?rewriteBatchedStatements=true&amp;useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</property>
        <property name="user">root</property>
        <property name="password">123</property>

        <!-- 进行数据库连接池管理的基本信息 -->
        <!-- 当数据库连接池中的连接数不够时，c3p0一次性向数据库服务器申请的链接数 -->
        <property name="acquireIncrement">5</property>
        <!-- c3p0数据库连接池中初始化时的连接数 -->
        <property name="initialPoolSize">10</property>
        <!-- c3p0数据库连接池中维护的最少连接数 -->
        <property name="minPoolSize">10</property>
        <!-- c3p0数据库连接池中维护的最多连接数 -->
        <property name="maxPoolSize">100</property>
        <!-- c3p0数据库连接池最多维护的Statement的个数 -->
        <property name="maxStatements">50</property>
        <!-- 每个连接中最多可以使用的Statement的个数 -->
        <property name="maxStatementsPerConnection">2</property>
    </named-config>
</c3p0-config>

    //方式二:使用配置文件
    @Test
    public void testGetConnection1() throws Exception{
         ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
         Connection conn = cpds.getConnection();
        System.out.println(conn);
    }
//输出
十二月 22, 2019 9:14:51 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十二月 22, 2019 9:14:51 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 22, 2019 9:14:51 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: 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, dataSourceName -> helloc3p0, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1eslg1oa7coy242ipevg|e6ea0c6, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql:///firstDB?rewriteBatchedStatements=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 50, maxStatementsPerConnection -> 2, minPoolSize -> 10, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
com.mchange.v2.c3p0.impl.NewProxyConnection@3cda1055

使用C3P0数据库连接池技术

public class JDBCUtils {
    /**
    * @Description: 使用C3P0的数据库连接池技术
    * @Param: 
    * @return: 
    * @Author: fish
    * @Date: 9:20 PM 2019/12/22
    */
    //数据库连接池只需提供一个即可。
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
    public static Connection getConnection1() throws Exception{
        Connection conn = cpds.getConnection();
        return conn;
    }
}
    @Test
    public void getCustomerById(){
        Connection conn = null;
        try{
            conn = JDBCUtils.getConnection1();
            Customer customer = dao.getCustomerById(conn,8);
            System.out.println(customer);
            System.out.println("查询成功");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
//输出
十二月 22, 2019 9:26:21 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十二月 22, 2019 9:26:21 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 22, 2019 9:26:21 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: 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, dataSourceName -> helloc3p0, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1eslg1oa7cpcuno1i0imi9|e6ea0c6, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql:///firstDB?rewriteBatchedStatements=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 50, maxStatementsPerConnection -> 2, minPoolSize -> 10, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
Customer{id=8, name='陈道明', email='bdf@126.com', birth=1963-09-16 00:00:00.0}
查询成功