连接池（c3p0,Druid,DBCP）

1 、 C3P0连接池

不使用配置文件用C3p0连接池
所需jar包：c3p0-0.9.5.5.jar，mchange-commons-java-0.2.19.jar

代码：

import com.bean.Employee;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

public class C3P0Demo {
    public static void main(String[] args) throws Exception {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/qianfeng?useSSL=true");
        dataSource.setUser("root");
        dataSource.setPassword("zhu980510");
//        dataSource.setMinPoolSize(20);
//        dataSource.setMinPoolSize(5);
//        dataSource.setCheckoutTimeout(2);
        Connection conn = dataSource.getConnection();
        String sql = "select * from emp";
//        PreparedStatement pst = conn.prepareStatement("select * from emp");
//        ResultSet resultSet = pst.executeQuery();
        QueryRunner qr = new QueryRunner();
        List<Employee> query = qr.query(conn, sql, new BeanListHandler<>(Employee.class));
        for (Employee employee : query) {
            System.out.println(employee);
        }

//        System.out.println(query);
//
//        while (resultSet.next()){
//
//        }

    }
}

使用配置文件形式，两种配置文件，一种是properties格式，一种是xml格式
properties格式配置文件名称必须是：c3p0.properties
xml格式配置文件文件名必须是：c3p0-config.xml
否则加载不到配置文件，底层会自动加载配置文件。

c3p0配置参数：

使用配置文件形式:只配置普通连接配置，没有配置最大连接数等其他配置

<!-- c3p0-config.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>

<default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/qianfeng?useSSL=true</property>
    <property name="user">root</property>
    <property name="password">zhu980510</property>
</default-config>

</c3p0-config>

c3p0.properties

c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/qianfeng?useSSL=true
c3p0.user=root
c3p0.password=zhu980510

代码：

import com.bean.Employee;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class C3P0Demo1 {
    public static void main(String[] args) throws Exception {

        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        // 使用QueryRunner类对查询到的数据进行封装
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select * from emp";

        try {
            List<Employee> list  = qr.query(sql, new BeanListHandler<>(Employee.class));
            for (Employee employee : list) {
                System.out.println(employee);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

执行结果为：

2、Druid连接池

不使用配置文件方式，不使用DBUtils工具类
所需jar包是：druid-1.1.12.jar

代码：

import com.alibaba.druid.pool.DruidDataSource;
import com.bean.Employee;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;

/**
 * 不使用配置文件方式，不使用DBUtils工具类
 */
public class DruidDemo {
    public static void main(String[] args) throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql:///qianfeng?characterEncoding=UTF-8");
        dataSource.setUsername("root");
        dataSource.setPassword("zhu980510");

        QueryRunner qr = new QueryRunner(dataSource);

        List<Employee> employeeList = qr.query("select * from emp", new BeanListHandler<>(Employee.class));
        for (Employee employee : employeeList) {
            System.out.println(employee);
        }
    }
}

执行结果：

使用配置文件，使用DBUtils工具类,并对数据库表进行增删改查

代码：
配置文件：dbDruid.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///qianfeng?characterEncoding=UTF-8&useSSL=true
username=root
password=zhu980510

DbUtils工具类

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import java.util.Properties;

public class DbUtils {
    private static DruidDataSource dataSource;
    static{

        try {
            //需要使用工厂方式创建连接池
            Properties prop=new Properties();
            prop.load(DbUtils.class.getClassLoader().getResourceAsStream("dbDruid.properties"));
            dataSource= (DruidDataSource) DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static  DruidDataSource getDataSource(){
        return dataSource;
    }
}

import com.alibaba.druid.pool.DruidDataSource;
import com.bean.Employee;
import com.util.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;

/**
 * 使用配置文件，使用DBUtils工具类
 */

public class DruidDemo1 {
    public static void main(String[] args) throws SQLException {
        DruidDataSource dataSource = DbUtils.getDataSource();
        QueryRunner qr = new QueryRunner(dataSource);
        // 查询所有
        List<Employee> employeeList = qr.query("select * from emp", new BeanListHandler<>(Employee.class));

        for (Employee employee : employeeList) {
            System.out.println(employee);

        }
        // 插入数据
//        String sql = "insert into dept (deptno,deptname,deptloc) values (?,?,?)";
//        Object [] params = {90,"市场部","周口"};
//        int row = qr.update(sql, params);
//        System.out.println(row);
            // 更新数据
//        String sql = "update dept set deptname=? where deptno=?";
//        Object [] params = {"维稳部","90"};
//        int row = qr.update(sql, params);
//        System.out.println(row);
        // 删除一条数据
//        String sql = "delete from dept where deptno=90";
//        int i = qr.update(sql);
//        System.out.println(i);

    }
}

执行结果：

3、DBCP连接池

使用DButils工具类，使用配置文件：dbDruid.properties
所需jar包：
commons-dbcp2-2.8.0.jar
commons-logging-1.2.jar
commons-pool2-2.8.0.jar

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///qianfeng?characterEncoding=UTF-8&useSSL=true
username=root
password=zhu980510

工具类：DBCPUtils

package com.util;


import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.util.Properties;

public class DBCPUtils {
    private static BasicDataSource dataSource = null;
    static{

        try {
            //需要使用工厂方式创建连接池
            Properties prop=new Properties();
            prop.load(DBCPUtils.class.getClassLoader().getResourceAsStream("dbcp.properties"));
             dataSource = BasicDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static  BasicDataSource getDataSource(){
        return dataSource;
    }
}

代码：

import com.bean.Dept;
import com.util.DBCPUtils;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class DBCPDemp {
    public static void main(String[] args) throws Exception {

        BasicDataSource dataSource = DBCPUtils.getDataSource();
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select * from dept";
        // 增加一条数据
//        sql = "insert into dept (deptno,deptname,deptloc) values (?,?,?)";
//        Object [] params = {111,"环卫部","环卫局"};
//        int i = qr.update(sql,params);
//        System.out.println(i);
//        //  查询所有数据
        List<Dept> query = qr.query(sql, new BeanListHandler<>(Dept.class));
        for (Dept employee : query) {
            System.out.println(employee);
        }
    }
}