数据库技术五：数据库连接池，Commons DbUtils，批处理，元数据

目录

1. 数据库连接池

1.1 连接池介绍

1.2 如何使用数据库连接池

 1.2.1 DBCP 连接池

 1.2.2 C3P0 连接池

 1.2.3 Druid 连接池

2. DBUtils工具类

2.1 DBUtils简介

2.2 DBUtils完成 CRUD

2.2.1 QueryRunner 的创建

2.2.2 QueryRunner 实现增、删、改操作

2.2.3 QueryRunner实现查询操作

3. 数据库批处理

3.1 什么是批处理

3.2 实现批处理

4. MySql元数据

4.1 什么是元数据

4.2 常用命令

4.3 使用 JDBC 获取元数据

---

1. 数据库连接池

1.1 连接池介绍

实际开发中“获得连接”或“释放资源”是非常消耗系统资源的两个过程，为了解决此类性能问题，通常情况我们采用连接池技术，来共享连接 Connection。这样我们就不需要每次都创建连接、释放连接了，因为这些操作都交给了连接池。

连接池的好处：使用池来管理 Connection，这样可以重复使用 Connection。当使用完 Connection 后，调用 Connection 的 close() 方法也不会真的关闭 Connection，而是把 Connection “归还”给池。

1.2 如何使用数据库连接池

Java 为数据库连接池提供了公共的接口：javax.sql.DataSource，各个厂商需要让自己的连接池实现这个接口，这样应用程序可以方便的切换不同厂商的连接池。

常见的连接池有 DBCP 连接池，C3P0 连接池，Druid 连接池。

数据准备

在 MySQL 中准备好以下数据

-- 创建数据库
CREATE DATABASE db5 CHARACTER SET utf8;
 
-- 使用数据库
USE db5;
 
-- 创建员工表
CREATE TABLE employee (
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR (20), -- 员工姓名
    age INT,            -- 员工年龄
    sex VARCHAR (6),    -- 员工性别
    salary DOUBLE,      -- 薪水
    empdate DATE        -- 入职日期
);
 
-- 插入数据
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'李清照',22,'女',4000,'2018-11-12');
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'林黛玉',20,'女',5000,'2019-03-14');
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'杜甫',40,'男',6000,'2020-01-01');
INSERT INTO employee (eid, ename, age, sex, salary, empdate) VALUES(NULL,'李白',25,'男',3000,'2017-10-01');

 1.2.1 DBCP 连接池

DBCP 是一个开源的连接池，是 Apache 成员之一，在企业开发中比较常见，Tomcat 内置的连接池。

1. 创建项目并导入 jar包
首先将 commons-dbcp 和 commons-pool 两个 jar 包添加到 myJar 文件夹中，然后添加 myJar 库到项目的依赖中。

2.编写工具类
连接数据库表的工具类，采用 DBCP 连接池的方式来完成。

在 DBCP 包中提供了 DataSource 接口的实现类，我们要用的具体的连接池 BasicDataSource 类。

public class DBCPUtils {
    // 定义常量 保存数据库连接的相关信息
    public static final String DRIVERNAME = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "123456";

    // 创建连接池对象 (有 DBCP 提供的实现类)
    public static BasicDataSource dataSource = new BasicDataSource();

    // 使用静态代码块进行配置
    static{
        dataSource.setDriverClassName(DRIVERNAME);
        dataSource.setUrl(URL);
        dataSource.setUsername(USERNAME);
        dataSource.setPassword(PASSWORD);
    }

    // 获取连接的方法
    public static Connection getConnection() throws SQLException {
        // 从连接池中获取连接
        Connection connection = dataSource.getConnection();
        return connection;
    }

    // 释放资源方法

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

    }

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

3. 测试工具类

/*
 * 查询所有员工的姓名
 **/
public class TestDBCP {
    public static void main(String[] args) throws SQLException {
        // 从 DBCP 连接池中拿到连接
        Connection con = DBCPUtils.getConnection();

        // 获取 Statement 对象
        Statement statement = con.createStatement();

        // 查询所有员工的姓名
        String sql = "select ename from employee";
        ResultSet resultSet = statement.executeQuery(sql);

        // 处理结果集
        while(resultSet.next()){
            String ename = resultSet.getString("ename");
            System.out.println("员工姓名: " + ename);
        }

        // 释放资源
        DBCPUtils.close(con, statement, resultSet);
    }
}

 1.2.2 C3P0 连接池

C3P0 是一个开源的 JDBC 连接池，支持 JDBC 3 规范和 JDBC 2 的标准扩展。目前使用它的开源项目有 Hibernate、Spring 等。

1. 导入 jar 包及配置文件
首先将 c3p0 和 mchange-commons-java 两个 jar 包复制到 myJar 文件夹即可，IDEA 会自动导入。

然后导入配置文件 c3p0-config.xml。c3p0-config.xml 文件名不可更改，可以直接放到 src 下，也可以放到到资源文件夹中。

最后在项目下创建一个 resource 文件夹（专门存放资源文件），将配置文件放在 resource 目录下即可，创建连接池对象的时候会自动加载这个配置文件。

<c3p0-config>

    <!--默认配置-->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db5?characterEncoding=UTF-8</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <!-- 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.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db5</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>

    <!--可以配置多个连接池-->

</c3p0-config>

2. 编写 C3P0 工具类
C3P0 提供的核心工具类 ComboPooledDataSource，如果想使用连接池，就必须创建该类的对象。

使用默认配置：new ComboPooledDataSource();

使用命名配置：new ComboPooledDataSource("mysql");

public class C3P0Utils {
    // 使用指定的配置
    public static ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql");

    // 获取连接的方法
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    // 释放资源

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

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

    }
}

3. 测试工具类

// 查询姓名为李白的记录
public class TestC3P0 {
    public static void main(String[] args) throws SQLException {
        // 获取连接
        Connection con = C3P0Utils.getConnection();

        // 获取预处理对象
        String sql = "select * from employee where ename = ?";
        PreparedStatement ps = con.prepareStatement(sql);

        // 设置占位符的值
        ps.setString(1,"李白");
        ResultSet resultSet = ps.executeQuery();

        // 处理结果集
        while (resultSet.next()){
            int eid = resultSet.getInt("eid");
            String ename = resultSet.getString("ename");
            int age = resultSet.getInt("age");
            String sex = resultSet.getString("sex");
            double salary = resultSet.getDouble("salary");
            Date date = resultSet.getDate("empdate");
            System.out.println(eid+" "+ename+" "+age+" "+sex+" "+salary+" "+date);
        }

        // 释放资源
        C3P0Utils.close(con, ps, resultSet);
    }
}

 1.2.3 Druid 连接池

Druid（德鲁伊）是阿里巴巴开发的为监控而生的数据库连接池，Druid 是目前最好的数据库连接池。在功能、性能、扩展性方面，都超过其他数据库连接池，同时加入了日志监控，可以很好的监控 DB 池连接和 SQL 的执行情况。

1. 导入 jar 包及配置文件
首先导入 druid jar 包。然后导入 properties 配置文件，可以叫任意名称，可以放在任意目录下，但是这里统一放到 resources 资源目录。

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db5?characterEncoding=UTF-8
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000

2. 编写 Druid 工具类
通过工厂来来获取 DruidDataSourceFactory 类的 createDataSource(Properties p) 方法，其参数可以是一个属性集对象。

public class DruidUtils {

    // 定义成员变量
    public static DataSource dataSource;

    // 静态代码块
    static{
        try {
            // 创建属性集对象
            Properties p = new Properties();
            // 加载配置文件 Druid 连接池不能够主动加载配置文件，需要指定文件
            InputStream inputStream = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            // 使用 Properties 对象的 load 方法从字节流中读取配置信息
            p.load(inputStream);
            // 通过工厂类获取连接池对象
            dataSource = DruidDataSourceFactory.createDataSource(p);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取连接的方法
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    // 释放资源

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

    }

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

3. 测试工具类

// 查询薪资在 3000 - 5000 元之间的员工姓名
public class TestDruid {
    public static void main(String[] args) throws SQLException {
        // 获取连接
        Connection con = DruidUtils.getConnection();

        // 获取 Statement 对象
        Statement statement = con.createStatement();

        // 执行查询
        ResultSet resultSet = statement.executeQuery("select ename from employee where salary between 3000 and 5000");

        // 处理结果集
        while(resultSet.next()){
            String ename = resultSet.getString("ename");
            System.out.println(ename);
        }

        // 释放资源
        DruidUtils.close(con,statement,resultSet);
    }
}

---

2. DBUtils工具类

2.1 DBUtils简介

Commons DbUtils 是 Apache 组织提供的一个对 JDBC 进行简单封装的开源工具类库，使用它能够简化 JDBC 应用程序的开发，同时也不会影响程序的性能。

DbUtils 就是 JDBC 的简化开发工具包，需要在项目导入 commons-dbutils jar 包。

DbUtils 核心功能：

1. QueryRunner 中提供对 SQL 语句操作的 API。
2. ResultSetHandler 接口用于定义 select 操作后封装结果集。
3. DbUtils 类是一个定义了关闭资源与事务处理相关方法的工具类。

表和类之间的关系：

整个表可以看做是一个类。
表中的一列，对应类中的一个成员属性。
表中的一行记录，对应一个类的实例（对象）。

JavaBean 组件
JavaBean 是一个开发中通常用于封装数据的类：

1. 需要实现序列化接口，Serializable（暂时可以省略）
2. 提供私有字段：private 类型变量名
3. 提供 getter 和 setter
4. 提供空参构造

创建一个 entity 包，专门用来存放 JavaBean 类，然后在 entity 包中创建一个和数据库的 Employee 表对应的 Employee 类。

public class Employee implements Serializable {
    private int eid;
    private String ename;
    private int age;
    private String  sex;
    private double salary;
    private Date empdate;
    // getter setter
    ...
}

2.2 DBUtils完成 CRUD

2.2.1 QueryRunner 的创建

手动模式

// 创建 QueryRunner 对象
QueryRunner qr = new QueryRunner();

自动模式

// 传入数据库连接池对象
QueryRunner qr2 = new QueryRunner(DruidUtils.getDataSource());

自动模式需要传入连接池对象（在DruidUtils工具类中添加）：

// 获取连接池对象
public static DataSource getDataSource(){
    return dataSource;
}

2.2.2 QueryRunner 实现增、删、改操作

步骤：

1. 创建 QueryRunner（手动或自动）
2. 占位符方式编写SQL
3. 设置占位符参数
4. 执行

添加：

@Test
public void testInsert() throws SQLException {
    // 手动模式创建 QueryRunner
    QueryRunner qr = new QueryRunner();

    // 编写占位符方式 SQL
    String sql = "insert into employee values(?,?,?,?,?,?)";

    // 设置占位符的参数
    Object[] param = {null,"布莱尔",20,"女",10000,"1990-12-26"};

    // 执行 update 方法
    Connection con = DruidUtils.getConnection();
    int i = qr.update(con, sql, param);

    // 释放资源
    DbUtils.closeQuietly(con);
}

注意：

Connection con 为数据库连接对象, 自动模式创建QueryRun 可以不传 ,手动模式必须传递。

修改：

@Test
public void testUpdate() throws SQLException {
    // 自动模式创建 QueryRunner 对象，传入数据库连接池
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    // 编写 SQL
    String sql = "update employee set salary = ? where ename = ?";

    // 设置占位符参数
    Object[] param = {0, "布莱尔"};

    // 执行 update，不需要传入连接对象
    qr.update(sql, param);
}

删除：

@Test
public void testDelete() throws SQLException {
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    String sql = "delete from employee where eid = ?";

    //只有一个参数，不需要创建数组
    qr.update(sql, 1);
}

2.2.3 QueryRunner实现查询操作

ResultSetHandler 可以对查询出来的 ResultSet 结果集进行处理，达到一些业务上的需求。

query 方法的返回值都是泛型，具体的返回值类型会根据结果集的处理方式发生变化。

-- query(String sql, ResultSetHandler rsh, Object[] param) ： 自动模式创建QueryRunner, 执行查询。

-- query(Connection con, String sql, ResultSetHandler rsh, Object[] param) ： 手动模式创建 QueryRunner, 执行查询。

/*
 * 查询 id 为 5 的记录，封装到数组中
 *
 * ArrayHandler：
 * 将结果集的第一条数据封装到 Object[] 数组中，
 * 数组中的每一个元素就是这条记录中的每一个字段的值
 **/
@Test
public void testFindById() throws SQLException {
    // 创建 QueryRunner
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    // 编写 SQL
    String sql = "select * from employee where eid = ?";

    // 执行查询
    Object[] query = qr.query(sql, new ArrayHandler(), 5);

    // 获取数据
    System.out.println(Arrays.toString(query));
}

/**
 * 查询所有数据，封装到 List 集合中
 *
 * ArrayListHandler：
 * 可以将每条数据先封装到 Object[] 数组中,
 * 再将数组封装到集合中
 */
@Test
public void testFindAll() throws SQLException {

    //1.创建QueryRunner
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    //2.编写SQL
    String sql = "select * from employee";

    //3.执行查询
    List<Object[]> query = qr.query(sql, new ArrayListHandler());

    //4.遍历集合获取数据
    for (Object[] objects : query) {
        System.out.println(Arrays.toString(objects));
    }
}

/**
 * 查询 id 为 3 的记录,封装到指定 JavaBean 中
 *
 * BeanHandler：
 * 将结果集的第一条数据封装到 JavaBean 中
 **/
@Test
public void testFindByIdJavaBean() throws SQLException {

    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    String sql = "select * from employee where eid = ?";

    Employee employee = qr.query(sql, new BeanHandler<Employee>(Employee.class), 3);

    System.out.println(employee);
}

/*
 * 查询薪资大于 3000 的所员工信息，
 * 封装到 JavaBean 中再封装到 List 集合中
 *
 * BeanListHandler：
 * 将结果集的每一条和数据封装到 JavaBean 中，
 * 再将 JavaBean 放到 List 集合中
 * */
@Test
public void testFindBySalary() throws SQLException {
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    String sql = "select * from employee where salary > ?";

    List<Employee> list = qr.query(sql, new BeanListHandler<Employee>(Employee.class), 3000);

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

/*
 * 查询姓名是布莱尔的员工信息，
 * 将结果封装到 Map 集合中
 *
 * MapHandler：
 * 将结果集的第一条记录封装到 Map 中，
 * key 对应的是列名，
 * value 对应的是列的值
 **/
@Test
public void testFindByName() throws SQLException {
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    String sql = "select * from employee where ename = ?";

    Map<String, Object> map = qr.query(sql, new MapHandler(), "布莱尔");

    Set<Map.Entry<String, Object>> entries = map.entrySet();
    for (Map.Entry<String, Object> entry : entries) {
        System.out.println(entry.getKey() +" = " +entry.getValue());
    }
}

/*
 * 查询所有员工的薪资总额
 *
 * ScalarHandler：
 * 用于封装单个的数据
 **/
@Test
public void testGetSum() throws SQLException {
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    String sql = "select sum(salary) from employee";

    Double sum = (Double) qr.query(sql, new ScalarHandler<>());

    System.out.println("员工薪资总额: " + sum);
}

---

3. 数据库批处理

3.1 什么是批处理

批处理操作数据库：批处理指的是一次操作中执行多条 SQL 语句，批处理相比于一次一次执行效率会提高很多。当向数据库中添加大量的数据时，需要用到批处理。

3.2 实现批处理

Statement 和 PreparedStatement 都支持批处理操作。

MySQL 批处理是默认关闭的，所以需要加一个参数才打开 MySQL 数据库批处理，在 url 中添加 rewriteBatchedStatements=true。

url=jdbc:mysql://127.0.0.1:3306/db5?characterEncoding=UTF-8&rewriteBatchedStatements=true

1. 创建一张表

CREATE TABLE testBatch (
    id INT PRIMARY KEY AUTO_INCREMENT,
    uname VARCHAR(50)
)

2. 测试向表中插入一万条数据

public class TestBatch {
    public static void main(String[] args) {
        try {
            // 获取连接
            Connection con = DruidUtils.getConnection();

            // 获取预处理对象
            String sql ="insert into testBatch(uname) values(?)";
            PreparedStatement ps = con.prepareStatement(sql);

            // 创建 for 循环来设置占位符参数
            for (int i = 0; i < 10000 ; i++) {
                ps.setString(1, "小明"+i);
                // 将 SQL 添加到批处理列表
                ps.addBatch();
            }

            long start = System.currentTimeMillis();

            // 统一批量执行
            ps.executeBatch();
            
            long end = System.currentTimeMillis();
            System.out.println("插入10000条数据使用: "+(end-start)+" 毫秒!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

---

4. MySql元数据

4.1 什么是元数据

除了表之外的数据都是元数据，可以分为三类：

1. 查询结果信息，UPDATE 或 DELETE语句 受影响的记录数。

2. 数据库和数据表的信息，包含了数据库及数据表的结构信息。

3. MySQL服务器信，包含了数据库服务器的当前状态，版本号等。

4.2 常用命令

select version()：获取 MySQL 服务器的版本信息

show status：查看服务器的状态信息

show columns from table_name： 显示表的字段信息等，和 desc table_name 一样

show index from table_name： 显示数据表的详细索引信息，包括主键

show databases：列出所有数据库

show tables： 显示当前数据库的所有表

select database()：获取当前的数据库名

4.3 使用 JDBC 获取元数据

通过 JDBC 也可以获取到元数据，比如，数据库的相关信息，或者，使用程序查询一个不熟悉的表时，可以通过获取元素据信息来了解表中有多少个字段、字段的名称、字段的类型。

DatabaseMetaData ：描述数据库的元数据对象

ResultSetMetaData ：描述结果集的元数据对象

public class TestMetaData {

    // 获取数据库相关的元数据信息
    @Test
    public void testDataBaseMetaData() throws SQLException {
        Connection connection = DruidUtils.getConnection();

        // 获取代表数据库的元数据对象
        DatabaseMetaData metaData = connection.getMetaData();

        // 获取数据库相关的元数据信息
        String url = metaData.getURL();
        System.out.println("数据库URL: " + url);
        String userName = metaData.getUserName();
        System.out.println("当前用户: " + userName );
        String productName = metaData.getDatabaseProductName();
        System.out.println("数据库产品名: " + productName);
        String version = metaData.getDatabaseProductVersion();
        System.out.println("数据库版本: " + version);
        String driverName = metaData.getDriverName();
        System.out.println("驱动名称: " + driverName);

        // 判断当前数据库是否只允许只读
        boolean b = metaData.isReadOnly();
        if(b){
            System.out.println("当前数据库只允许读操作!");
        }else{
            System.out.println("不是只读数据库");
        }

        connection.close();
    }

    // 获取结果集中的元数据信息
    @Test
    public void testResultSetMetaData() throws SQLException {
        Connection con = DruidUtils.getConnection();
        PreparedStatement ps = con.prepareStatement("select * from employee");
        ResultSet resultSet = ps.executeQuery();

        // 获取结果集元素据对象
        ResultSetMetaData metaData = ps.getMetaData();

        // 获取当前结果集共有多少列
        int count = metaData.getColumnCount();
        System.out.println("当前结果集中共有: "+count+"列");

        // 获结果集中列的名称和类型
        for (int i = 1; i <=  count; i++) {
            String columnName = metaData.getColumnName(i);
            System.out.println("列名: "+columnName);

            String columnTypeName = metaData.getColumnTypeName(i);
            System.out.println("类型: "+columnTypeName);
        }

        DruidUtils.close(con, ps, resultSet);
    }
}