连接池（DBCP、C3P0）

1、DBCP

DBCP（DataBase Connection Pool）数据库连接池，由Apache公司开发。连接池的运用避免了反复建立连接造成的资源浪费，预先建立一些连接放在数据库连接池中，需要时取出，不需要时放入连接池。

（1）导包：需要导入两个jar包：commons-pool-1.5.6和commons-dbcp-1.4.jar

（2）DBCP工具类：

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class JDBCUtils {
    private static BasicDataSource datasource = new BasicDataSource();
    /*
     * BasicDataSource类，实现了datasource接口
     */

    static {// 静态代码块,对象BasicDataSource对象中的配置,自定义

        InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("DBCP.properties");
        Properties pro = new Properties();
        try {
            pro.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }

        // 数据库基本的连接信息，必须要设置（必须项）
        datasource.setDriverClassName(pro.getProperty("driver"));
        datasource.setUrl(pro.getProperty("url"));
        datasource.setUsername(pro.getProperty("username"));
        datasource.setPassword(pro.getProperty("password"));
        // 对象连接池中的连接数量配置,可以不设置（基本项）
        datasource.setInitialSize(100);// 初始化的连接数
        datasource.setMaxActive(90);// 最大连接数量
        datasource.setMaxIdle(10);// 最大空闲数
        datasource.setMinIdle(5);// 最小空闲
    }

    // 返回BasicDataSource类的对象
    public static BasicDataSource getDataSource() {
        return datasource;
    }
}

import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class Test {
    public static void main(String[] args) {

        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());//参数为数据源

        try {
            String sql = "select * from Student";
            List<Object[]> list = qr.query(sql, new ArrayListHandler());
            for (Object[] objs : list) {//list集合
                for (Object obj : objs) {
                    System.out.print(obj + "\t");
                }
                System.out.println();
            }
        } catch (SQLException ex) {
            System.out.println(ex);
            throw new RuntimeException("数据查询失败");
        }
    }

}

（3）配置文件：

2、C3P0

（1）导入jar包：

（2）配置文件：

<?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/student mangement system</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <named-config name="zhai">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/student mangement system</property>
        <property name="user">root</property>
        <property name="password">root</property>
    </named-config>

</c3p0-config>

（3）C3P0工具类：

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

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.jdbc.Statement;

public class C3P0Utils { private static ComboPooledDataSource dataSource = new ComboPooledDataSource( "zhai"); public static ComboPooledDataSource getDataSource() { return dataSource; } public static Connection getConnection() { try { return dataSource.getConnection(); } catch (SQLException e) { throw new RuntimeException(e); } } public static void close(Connection con, Statement stat) { if (stat != null) { try { stat.close(); } catch (SQLException e) { e.printStackTrace(); System.out.println("stat流关闭异常！"); } } if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); System.out.println("con流关闭异常！"); } } } public static void close(Connection con, Statement stat, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); System.out.println("rs流关闭异常！"); } } if (stat != null) { try { stat.close(); } catch (SQLException e) { e.printStackTrace(); System.out.println("stat流关闭异常！"); } } if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); System.out.println("con流关闭异常！"); } } } }

（4）测试：用Prepared实现查询

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Test {
    public static void main(String[] args) {
        Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { // 2.从池子中获取连接 con = C3P0Utils.getConnection(); String sql = "select * from student "; ps = con.prepareStatement(sql);// 获取预处理对象 rs = ps.executeQuery(); System.out.println(" " + "学号" + " " + "班级" + " " + "平时成绩" + " " + "期末成绩"); while (rs.next()) { System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4)); } } catch (Exception e) { throw new RuntimeException(e); } finally { C3P0Utils.close(con, null, rs); } } }

用JDBCUtils实现查询：

建立JavaBean：

public class Student {
private String studentno;
private String sname;
private String sex;
private String birthday;
private String classno; private String point; private String phone; private String email; public String getStudentno() { return studentno; } public void setStudentno(String studentno) { this.studentno = studentno; } public String getSname() { return sname; } public void setSname(String sname) { this.sname = sname; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getBirthday() { return birthday; } public void setBirthday(String birthday) { this.birthday = birthday; } @Override public String toString() { return "Student [studentno=" + studentno + ", sname=" + sname + ", sex=" + sex + ", birthday=" + birthday + ", classno=" + classno + ", point=" + point + ", phone=" + phone + ", email=" + email + "]"; } public String getClassno() { return classno; } public void setClassno(String classno) { this.classno = classno; } public String getPoint() { return point; } public void setPoint(String point) { this.point = point; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } }

测试：

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

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.BeanListHandler;

public class Test1 { public static void main(String[] args) { Connection con = null; try { con = C3P0Utils.getConnection(); QueryRunner qr = new QueryRunner(); String sql = "Select * from Student "; Object[] select = { }; List<Student> list = qr.query(con, sql, new BeanListHandler<Student>((Student.class)), select); // 将记录封装到一个装有Object[]的List集合中 for (Student s : list) { System.out.println(s); } } catch (SQLException e) { throw new RuntimeException(e); } } }

3、dbcp、c3p0和Druid

（1）dbcp与c3p0的区别

对数据库的处理方式上，c3p0提供最大空闲时间，当连接超过最大空闲时间时连接断开；dbcp提供最大的连接数，当连接超过最大连接数的时候连接断开

c3p0自动回收连接，dbcp需要手动释放资源，但是dbcp效率较高，c3p0较稳定。spring组织推荐使用dbcp、hibernate推荐使用c3p0

（2）Druid

阿里出品，淘宝和支付宝专用数据库连接池，但它不仅仅是一个数据库连接池，它还包含一个ProxyDriver，一系列内置的JDBC组件库，一个 SQL Parser。支持所有JDBC兼容的数据库，包括Oracle、MySql、Derby、Postgresql、SQL Server、H2等等。Druid针对Oracle和MySql做了特别优化，比如Oracle的PS Cache内存占用优化，MySql的ping检测优化。

转载自CSDNwawa3338：https://blog.csdn.net/wawa3338/article/details/81380662

文章永久链接：https://tech.souyunku.com/22630