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

RendaZhang

发布于 2020-09-08 15:33:37

9950

发布于 2020-09-08 15:33:37

文章被收录于专栏：RendaRenda

Database Connection Pool

Introduction to Database Connection Pool

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

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

How to Use Database Connection Pool?

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

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

Data Preparation

在 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');

DBCP 连接池

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

创建项目并导入 jar包

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

编写工具类

连接数据库表的工具类，采用 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 = "root";

    // 创建连接池对象 (有 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();
            }
        }
    }
}

测试工具类

/*
 * 查询所有员工的姓名
 **/
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);
    }
}

C3P0 连接池

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

导入 jar 包及配置文件

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

然后导入配置文件 c3p0-conﬁg.xml。c3p0-conﬁg.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">root</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>

编写 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();
            }
        }

    }
}

测试工具类

// 查询姓名为李白的记录
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);
    }
}

Druid 连接池

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

导入 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=root
initialSize=5
maxActive=10
maxWait=3000

编写 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();
            }
        }
    }
}

测试工具类

// 查询薪资在 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);
    }
}

`Commons DbUtils`

Introduction to `DbUtils`

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

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

DbUtils 核心功能：

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

表和类之间的关系

整个表可以看做是一个类。

表中的一列，对应类中的一个成员属性。

表中的一行记录，对应一个类的实例（对象）。

JavaBean 组件

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

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

创建一个 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
    ...
}

Use `DbUtils` for CRUD Operations

`QueryRunner` 的创建

手动模式

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

自动模式

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

自动模式需要传入连接池对象

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

`QueryRunner` 实现增、删、改操作

步骤：

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

添加：

@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);
}

修改：

@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);
}

`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);
}

Database Batch Process

What is Database Batch Process？

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

Implement Batch Processing

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

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

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

创建一张表

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

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

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();
        }
    }
}

MySQL Metadata

What is Metadata in MySQL

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

查询结果信息，UPDATE 或 DELETE语句受影响的记录数。
数据库和数据表的信息，包含了数据库及数据表的结构信息。
MySQL服务器信，包含了数据库服务器的当前状态，版本号等。

常用命令

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

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

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

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

show databases: 列出所有数据库

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

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

使用 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);
    }
}

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-07-13，如有侵权请联系 cloudcommunity@tencent.com 删除

jdbc

本文分享自 Renda 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度