28 数据库连接池和DBUtils

锦沐karas
528 阅读11分钟

1 数据库连接池

频繁进行“获得连接”和“释放资源”是非常消耗系统资源的过程，因此为了解决这个问题，提出了数据库连接池技术，连接池里的连接Connection是被共享使用的，这样我们就不需要每次创建连接和释放连接，这些工作都交给了连接池。

使用数据库连接池的好处：连接池管理Connection，可以重复使用这些Connection。当使用完Connection后，调用Connection的Close()方法来关闭连接，注意这里不是真的关闭连接，只是将Connection归还给连接池。

下面我们来看看JDBC方式和连接池方式之间的比较：

普通JDBC方式

jdbc.PNG

连接池技术

数据库连接池.jpg

Java为数据库连接池提供了公共的接口：java.sql.DataSource，各个数据库厂商需要让自己的连接池实现这个接口，常见的数据库连接池有：DBCP连接池、C3P0连接池、Druid连接池，接下来详细介绍下面这三种连接池技术。

先准备数据，具体地SQL代码如下：

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

2 DBCP连接池

DBCP是一个开源的连接池，tomcat内置的连接池。使用DBCP连接池的步骤是：

  1. 导入DBCP连接池对应的jar包
  2. 编写工具类
  3. 测试工具类

这里将采用DBCP连接池技术实现连接数据库的工具类，Java中提供的连接池规则接口是DataSource，在DBCP包中提供了这个接口的实现类BasicDataSource。工具类的具体代码如下：

package com.lagou.utils;

import org.apache.commons.dbcp.BasicDataSource;

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

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";
    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 {
        return dataSource.getConnection();
    }
    public static void close(Connection con, Statement st){
        if(null!=con&&null!=st){
            try {
                st.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }
    public static void close(Connection con, Statement st, ResultSet rs){
        if(null!=con&&null!=st&&null!=rs){
            try {
                rs.close();
                st.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    }




}

工具类测试代码

package com.lagou.testpool;

import com.lagou.utils.DBCPUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestDBCP {
    public static void main(String[] args) throws Exception {
        Connection connection = DBCPUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql="select ename from employee";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.println("员工姓名 "+resultSet.getString("ename"));
        }
        DBCPUtils.close(connection,statement,resultSet);


    }
}

数据库连接池常见配置项：

属性描述
driverClassName数据库驱动名
url数据库地址
username用户名
password密码
maxActive最大连接数量
maxldle最大空闲连接
minldle最小空闲连接
initialSize初始化连接

3 C3P0连接池

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

使用C3P0连接池技术的步骤：先导入对应的jar包和连接池配置文件(xml形式的)，在开始编写C3P0工具类。

C3P0连接池配置文件如下：

<c3p0-config>
  
  <!--默认配置-->
    <default-config>  
	
		<!-- 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?characterEncoding=UTF-8</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>
    <!--配置连接池2,可以配置多个-->

</c3p0-config>

C3P0提供的核心工具类ComboPooledDataSource，如果想使用连接池，就必须创建该类的对象，创建该对象有两种方式：

  • new ComboPooledDataSource(); 创建出来的数据库连接池使用默认的配置
  • new ComboPooledDataSource("mysql"); 创建出来的数据库连接池使用命名为mysql的配置

C3P0核心工具类具体代码如下：

package com.lagou.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class C3P0Utils {
    public static ComboPooledDataSource cp=new ComboPooledDataSource("mysql");
    public static Connection getConnection() throws Exception {
        return cp.getConnection();
    }
    public static void close(Connection con, Statement st){
        if(null!=con&&null!=st){
            try {
                st.close();
                con.close();
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }

        }

    }
    public static void close(Connection con, Statement st, ResultSet rs){
        if(null!=con&&null!=st&&null!=rs) {
            try {
                rs.close();
                st.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
    }
}

工具类测试代码

package com.lagou.testpool;

import com.lagou.utils.C3P0Utils;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestC3P0 {
    public static void main(String[] args) throws Exception {
        Connection con= C3P0Utils.getConnection();
        String sql="select * from employee where ename=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,"李白");
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            int eid=rs.getInt("eid");
            String ename=rs.getString("ename");
            int age=rs.getInt("age");
            String sex=rs.getString("sex");
            double salary=rs.getDouble("salary");
            Date empdate=rs.getDate("empdate");
            System.out.println(eid+" "+ename+" "+age+" "+sex+" "+salary+" "+empdate);

        }
        C3P0Utils.close(con,ps,rs);
    }
}

4 Driud连接池

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

和其他两个连接池的使用很类似，先导包和导入配置文件(properties形式的)，再编写相应的工具类，配置文件如下：

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

获取数据库连接池对象，通过工厂方法来获取DriudDataSourceFactory类的creatDataSource方法，creatDataSource(Properties p)方法参数是一个属性集对象，其中Druid工具类的代码如下：

package com.lagou.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DruidUtils {
    public static DataSource dataSource;
    static {

        try {
            Properties p=new Properties();
            InputStream res = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            p.load(res);
            dataSource= DruidDataSourceFactory.createDataSource(p);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
  public static DataSource getDataSource(){
        return dataSource;
    }
    public static void close(Connection con, Statement st){
        if(null!=con&&null!=st){
            try {
                st.close();
                con.close();
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }

        }

    }
    public static void close(Connection con, Statement st, ResultSet rs){
        if(null!=con&&null!=st&&null!=rs) {
            try {
                rs.close();
                st.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
    }

}

测试工具类代码：

package com.lagou.testpool;

import com.lagou.utils.DruidUtils;

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

public class TestDriud {
    public static void main(String[] args) throws SQLException {
        Connection con = DruidUtils.getConnection();
        Statement sta = con.createStatement();
        String sql="select ename from employee where salary between 3000 and 5000 ";
        ResultSet rs = sta.executeQuery(sql);
        while (rs.next()){
            String ename = rs.getString("ename");
            System.out.println(ename);
        }
        DruidUtils.close(con,sta,rs);


    }

}

5 DBUtils工具类

在实际开发中JDBC中存在大量重复冗余的代码，为了简化开发，可以使用工具类DBUtils，这是一个对JDBC进行简单封住的开源工具类库，使用它可以简化JDBC应用程序的开发，同时又不会影响到程序的性能，注意使用DBUtils工具类时也是需要导包的。

下面介绍DBUtils工具包的核心功能方法：

  • QueryRunner中提供对sql与操作的API
  • ResultSetHandler接口，用于定义select操作后，怎样封装结果集
  • DBUtils类，就是个工具类，定义了关闭资源和事务管理相关的方法

在正式用DBUtils之前，我们先看看表和类之间的关系：整个表可以看做一个类，表中的一行记录对应类的一个实例，表中的一列，对应类中的成员属性。

JavaBean组件

JavaBean就是一个类，开发中用于封装数据，有以下几个特点：

  • 需要实现序列化接口(Serializable)，一般可忽略
  • 提供私有字段：privtae 类型 变量名
  • 提供get和set方法
  • 提供无参构造

创建Empolyee类和数据库中employee表对应，代码是：

package com.lagou.entity;

import java.util.Date;

public class Employee {
    private int eid;
    private String ename;
    private int age;
    private String sex;
    private double salary;
    private Date empdate;

    public Employee() {
    }

    public Employee(int eid, String ename, int age, String sex, double salary, Date empdate) {
        this.eid = eid;
        this.ename = ename;
        this.age = age;
        this.sex = sex;
        this.salary = salary;
        this.empdate = empdate;
    }

    public int getEid() {
        return eid;
    }

    public void setEid(int eid) {
        this.eid = eid;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public Date getEmpdate() {
        return empdate;
    }

    public void setEmpdate(Date empdate) {
        this.empdate = empdate;
    }
    

    @Override
    public String toString() {
        return "Employee{" +
                "eid=" + eid +
                ", ename='" + ename + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                ", salary=" + salary +
                ", empdate=" + empdate +
                '}';
    }
}

使用DBUtils工具类之前，先介绍该类下的相关方法：

QueryRunner类，构造方法有两种：

  • QueryRunner()
  • QueryRunner(DataSource ds) ，提供数据库连接池，DBUtils底层会自动的维护连接Connection

除了构造方法外，常用方法有：

  • update(Connection con,String sql,Object... params)完成表数据的增加、删除和更新操作
  • query(Connection con,String sql, ResultSetHandler< T > rsh ,Object... params)，完成表数据的查询操作

接下啦将会使用DBUtils工具类实现CRUD操作

package com.lagou.testDBUtils;

import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

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

public class DBUtilsTest {
    @Test
    public void testInsert() throws SQLException {
        QueryRunner qr=new QueryRunner();
        String sql="insert into employee values(?,?,?,?,?,?)";
        Object[] ob={null,"张百万",22,"女",6000,"2006-03-09"};
        Connection con = DruidUtils.getConnection();
        int i=qr.update(con,sql,ob);
        DbUtils.closeQuietly(con);
    }
    @Test
    public void testUpdate() throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="update employee set salary=? where ename=?";
        Object[] ob={15000,"张百万"};
        qr.update(sql,ob);


    }
    @Test
    public void testDelete() throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="delete from employee where eid=?";
        qr.update(sql,1);
    }
}

6 ResultSetHandler接口

ResultSetHandler接口可以对查询出来的ResultSet结果集进行处理，从满足业务需求。ResultSetHandler结果集实现类有以下几种常见的：

类名说明
ArrayHandler结果集的第一条记录封装到Object[]数组中，数组中每个元素就是这条记录的每个字段值
ArrayListHandler将结果集的每条记录都封装到Object[]数组中，再将这些数组封装到List集合中
BeanHandler结果集的第一条记录封装到一个指定的JavaBean中
BeanListHandler将结果集的每条记录都封装到指定的JavaBean中，再将这些JavaBean封装到List集合中
ColumListHandler将结果集中指定列的字段值封装一个List集合中
KeyedHandler结果集的第一条记录封装到Map<Stirng,Object>集合中，再将这个map集合作为另一个Map的value，另一个Map集合的key是指定的字段的值
MapHandler结果集的第一条记录封装到Map<Stirng,Object>集合中，key就是字段名称，value就是字段值
MapListHandler结果集的第一条记录封装到Map<Stirng,Object>集合中，key就是字段名称，value就是字段值，再将这些Map封装到List集合中
ScalarHandler封装单个数据

使用DBUtils进行查询，ResultSetHandler接口的实现类对结果集进行处理，实例代码如下：

@Test
    public void testById() throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select * from employee where eid=?";
        Object[] query = qr.query(sql, new ArrayHandler(), 5);
        System.out.println(Arrays.toString(query));


    }
    @Test
    public void testFindAll() throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select * from employee";
        List<Object[]> query = qr.query(sql, new ArrayListHandler());
        for (Object[] ob:query) {
            System.out.println(Arrays.toString(ob));

        }

    }
    @Test
    public void testFindById() throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select * from employee where salary > ?";
        List<Employee> query = qr.query(sql, new BeanListHandler<Employee>(Employee.class), 3000);
        for(Employee em:query){
            System.out.println(em);
        }
    }
    @Test
    public void findByName() throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select * from employee where ename=?";
        Map<String, Object> query = qr.query(sql, new MapHandler(), "张百万");
        Set<Map.Entry<String, Object>> entries = query.entrySet();
        for(Map.Entry<String, Object> entry:entries){
            System.out.println(entry.getKey()+" = "+entry.getValue());
        }
    }
    @Test
    public void testSum() throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());
        String sql="select sum(salary) from employee";
        Double query = qr.query(sql, new ScalarHandler<Double>());
        System.out.println("总薪资是"+query);
    }

7 数据库批处理

批处理是指一次操作执行多条SQL语句，这就比一次一次执行的效率会高很多，一般向数据库中插入大量数据时就需要用到批处理。

Statement和PreparedStatement都是支持批处理，这里以PreparedStatement为例进行批处理的演示，PreparedStatement中批处理使用的成员方法有：

方法声明功能介绍
void addBatch()将给定的SQL语句添加到此Statement对象的当前命令列表中
int[] exeCuteBatch()提交一批命令到数据库执行，所有命令执行成功，就返回一个数组，书评每条命令所受影响的行数

注意：mysql中批处理是默认关闭的，要想使用批处理，就必须添加一个参数打开mysql数据库批处理，在url中添加如下内容：

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

想表中插入一万条记录，代码如下：

package com.lagou.testBatch;

import com.lagou.utils.DruidUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class testBatch {
    public static void main(String[] args) throws SQLException {
        Connection con= DruidUtils.getConnection();
        String sql="insert into testBatch(uname) values(?)";
        PreparedStatement ps = con.prepareStatement(sql);
        for(int i=1;i<=10000;i++){
            ps.setString(1,"小明"+i);
            ps.addBatch();
        }
        long start=System.currentTimeMillis();
        ps.executeBatch();
        long end=System.currentTimeMillis();
        System.out.println("插入数据耗时"+(end-start)+"毫秒");
    }
}

8 MySQL元数据

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

  • 查询结果信息：update或delete语句受影响的行数
  • 数据库和数据表信息：包括数据库和数据表的结构信息
  • MySQL服务器信息：服务器当前状态、版本号等信息

关于元数据常用命令有：

  1. select version();获取服务器的版本信息
  2. show status;查看服务器的状态信息
  3. show columns from 表名;显示表的字段信息
  4. show index from 表名;显示数据表的索引信息
  5. show databases;列出所有数据库
  6. show tables;显示当前数据库的所有表
  7. select database();获取当前的数据库名

除了上面的命令方式获取元数据外，我们还可以通过JDBC来获取元数据信息，JDBC中描述元数据的类有：

  • DatabaseMeteData 描述数据库的元数据对象
  • ResultSetMeteData 描述结果集的元数据对象

获取元数据对象的方法：getMetaData()

  • connection连接池对象，调用getMetaData()方法获取到的是DatabaseMeteData数据库元数据对象
  • PrepareStatement预处理对象，调用getMetaData()方法获取到的是ResultSetMeteData结果集的元数据对象

其中DatabaseMeteData数据库元数据对象的常用方法：

getURL() 	获取数据库的URL
getUserName() 获取数据库的用户名
getDatabaseProductName() 获取数据库的产品名称
getDatabaseProductVersion() 获取数据库的版本
getDriverName() 获取驱动名称
isReadOnly() 判断数据库是否只允许只读

ResultSetMeteData 结果集元数据对象的常用方法：

getColumnCount() 获取结果集有多少列
getColumnName(int i)  获取指定的列名
getColumnType(int i)  获取指定的列的参数类型
avatar
文章
阅读
粉丝