C3P0数据库连接池使用

配置文件：（注意：c3p0的xml配置文件名必须为 c3p0-config ，否则会报空指针）

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    c3p0的缺省（默认）配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省（默认）
    -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <!--
    c3p0的命名配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就表示使用的是name是MySQL
    -->
    <name-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </name-config>
</c3p0-config>

工具类提取：

package com.hyc.study06.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class C3P0Utils {
    public static DataSource dataSource = null;

    static {
        try {
            //代码的方式配置
//            dataSource = new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setJdbcUrl();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setMaxPoolSize();
//            dataSource.setMinPoolSize();

            //配置文件写法（XML文件不用读取，加载的时候会自动匹配）
            dataSource = new ComboPooledDataSource("MySQL");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        //从数据源中获取连接
        return dataSource.getConnection();//与DBCP都是实现了DataSource接口
    }

    //释放连接
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试：

package com.hyc.study06;
import com.hyc.study06.utils.C3P0Utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestC3P0 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //3、获取数据库连接
            connection = C3P0Utils.getConnection();//原来是自己实现的，现在是用别人实现的
            //使用？占位符替代参数
            String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) " +
                    "VALUES (?,?,?,?,?);";
            //4、获取执行sql的对象
            //预编译sql，先写sql，然后不执行
            preparedStatement = connection.prepareStatement(sql);
            //手动给参数赋值
            preparedStatement.setInt(1, 5);
            preparedStatement.setString(2, "hhh");
            preparedStatement.setString(3, "1135656");
            preparedStatement.setString(4, "123456789@qq.com");
            //java.sql.Date 是数据库的Date ，只包含年月日信息 ，它是java.util.Date（包含年月日和时分秒信息）的子类
            preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));

            //5、执行sql语句
            //6、返回执行结果集
            int num = preparedStatement.executeUpdate();
            if (num > 0) {
                System.out.println("插入数据成功！");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //7、释放连接
            C3P0Utils.release(connection, preparedStatement, resultSet);
        }
    }
}