C3p0连接池的使用

连接池是将已经创建好的连接保存在池中，当有请求来时，直接获取连接池中的有效连接对数据库进行访问，省略了创建连接和销毁连接的过程，进而提高性能。

c3p0连接池使用步骤如下：

1、引入c3p0的依赖

<dependency>
     <groupId>com.mchange</groupId>
     <artifactId>c3p0</artifactId>
     <version>0.9.5.2</version>
</dependency>

2、在resource下添加c3p0-config.xml配置

<c3p0-config>

    <!--默认配置-->
    <default-config>

        <!-- initialPoolSize：初始化时获取三个连接，
              取值应在minPoolSize与maxPoolSize之间。 -->
        <property name="initialPoolSize">3</property>

        <!-- maxIdleTime：最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。-->
        <property name="maxIdleTime">60</property>

        <!-- maxPoolSize：连接池中保留的最大连接数 -->
        <property name="maxPoolSize">100</property>
        <!-- minPoolSize: 连接池中保留的最小连接数 -->
        <property name="minPoolSize">10</property>

    </default-config>

    <!--配置连接池mysql-->

    <named-config name="mysql">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db_test?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
    </named-config>
    <!--配置连接池2,可以配置多个-->

</c3p0-config>

3、创建C3p0Utils连接池工具

package com.chen.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3p0Utils {

    public static ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql");

    public static Connection getConnection(){
        try {
            Connection connection = dataSource.getConnection();
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void close(Connection con, Statement statement){
        try {
            if (con!= null && statement!= null){
                statement.close();
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Connection con, Statement statement, ResultSet resultSet){
        try {
            if (con!= null && statement!= null && resultSet!=null){
                resultSet.close();
                statement.close();
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

4、测试使用

public String c3p0Test(){
        Connection connection = null;
        Statement statement = null;
        PreparedStatement ps = null;
        try {
            connection = C3p0Utils.getConnection();
            statement = connection.createStatement();

            String sql = "insert into tb_test(time,remark) values(?,?)";
            //3.必须在自定义的connection类中重写prepareStatement(sql)方法
            ps = connection.prepareStatement(sql);
            ps.setString(1, DateUtil.format(new Date(),"yyyy-MM-dd HH:mm:ss"));
            ps.setString(2, "1234");
            int rows = ps.executeUpdate();
            System.out.println("rows:"+rows);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            C3p0Utils.close(connection,statement);
        }

        return "OK";
    }