文章目录



1. Commons-dbutils简介

​​commons-dbutils​​​ 是 ​​Apache​​​ 组织提供的一个开源 ​​JDBC​​​工具类库，它是对​​JDBC​​​的简单封装，学习成本极低，并且使用dbutils能极大简化​​jdbc​​编码的工作量，同时也不会影响程序的性能。

API介绍

org.apache.commons.dbutils.QueryRunner
org.apache.commons.dbutils.ResultSetHandler

工具类

org.apache.commons.dbutils.DbUtils

2.QueryRunner类使用讲解

该类简单化了SQL查询，它与​​ResultSetHandler​​组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。

​​QueryRunner​​类提供了两个构造方法：

  • 默认的构造方法
  • 需要一个​​javax.sql.DataSource​​ 来作参数的构造方法。

2.1.QueryRunner类的主要方法

  • ​​public Object query(Connection conn, String sql, Object[] params, ResultSetHandler rsh) throws SQLException​​​：执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 ​​PreparedStatement​​ 和 ​​ResultSet​​ 的创建和关闭。
  • ​​public Object query(String sql, Object[] params, ResultSetHandler rsh) throws SQLException​​​:　几乎与第一种方法一样；唯一的不同在于它不将数据库连接提供给方法，并且它是从提供给构造方法的数据源(​​DataSource​​) 或使用的​​setDataSource​​ 方法中重新获得 ​​Connection​​。
  • ​​public Object query(Connection conn, String sql, ResultSetHandler rsh) throws SQLException​​ : 执行一个不需要置换参数的查询操作。
  • ​​public int update(Connection conn, String sql, Object[] params) throws SQLException​​:用来执行一个更新（插入、更新或删除）操作。
  • ​​public int update(Connection conn, String sql) throws SQLException​​：用来执行一个不需要置换参数的更新（插入、更新或删除）操作。

2.2.使用QueryRunner类实现CRUD

2.2.1.JdbcUtils

jdbc.properties

# key=value
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/singerdb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
jdbc.username=root
jdbc.password=123456

JdbcUtils

package com.bruce.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            //读取db.properties文件中的数据库连接信息
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties prop = new Properties();
            prop.load(in);
            //获取数据库连接驱动
            driver = prop.getProperty("jdbc.driverClassName");
            //获取数据库连接URL地址
            url = prop.getProperty("jdbc.url");
            //获取数据库连接用户名
            username = prop.getProperty("jdbc.username");
            //获取数据库连接密码
            password = prop.getProperty("jdbc.password");
            //加载数据库驱动
            Class.forName(driver);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * @return Connection数据库连接对象
     * @throws SQLException
     * @Method: getConnection
     * @Description: 获取数据库连接对象
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * @param conn
     * @param st
     * @param rs
     * @Method: release
     * @Description: 释放资源，
     * 要释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象
     */
    public static void release(Connection conn, Statement st, ResultSet rs) {
        try {
            if (rs != null) {
                //关闭存储查询结果的ResultSet对象
                rs.close();
            }
            if (st != null) {
                //关闭负责执行SQL命令的Statement对象
                st.close();
            }
            if (conn != null) {
                //关闭Connection数据库连接对象
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2.2.2.RunnerCRUDTest

maven依赖：

<!--导入dbutils包-->
 <dependency>
     <groupId>commons-dbutils</groupId>
     <artifactId>commons-dbutils</artifactId>
     <version>1.6</version>
 </dependency>

测试表：

-- 测试表
 create table users(
     id int primary key auto_increment,
     name varchar(40),
     password varchar(40),
     email varchar(60),
     birthday date
 );
package com.bruce.test;

import com.bruce.pojo.Users;
import com.bruce.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

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

public class QueryRunnerCRUDTest {

    @Test
    public void add() throws SQLException {
        //将数据源传递给QueryRunner，QueryRunner内部通过数据源获取数据库连接
        QueryRunner qr = new QueryRunner();
        String sql = "insert into users(name,password,email,birthday) values(?,?,?,?)";
        Object params[] = {"tom", "123", "bruce@sina.com", new Date()};
        qr.update(JdbcUtils.getConnection(), sql, params);
    }

    @Test
    public void delete() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "delete from users where id=?";
        qr.update(JdbcUtils.getConnection(), sql, 1);
    }

    @Test
    public void update() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "update users set name=? where id=?";
        Object params[] = {"ddd", 5};
        qr.update(JdbcUtils.getConnection(), sql, params);
    }

    @Test
    public void find() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select * from users where id=?";
        Object params[] = {2};
        Users user = (Users) qr.query(JdbcUtils.getConnection(), sql, params, new BeanHandler(Users.class));
        System.out.println(user);
    }

    @Test
    public void getAll() throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "select * from users";
        List<Users> list = (List) qr.query(JdbcUtils.getConnection(),sql, new BeanListHandler(Users.class));
        System.out.println(list);
    }
}

3.DButis整合Druid数据库连接池

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

<!--导入druid数据库连接池-->
  <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.10</version>
  </dependency>

​​jdbc.properties​​配置类

# key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/singerdb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
username=root
password=123456
filters=stat
initialSize=2
maxActive=300
maxWait=60000

timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
maxPoolPreparedStatementPerConnectionSize=200

参数说明：
Commons-dbutils框架_DBUtis
Commons-dbutils框架_apache_02

package com.bruce.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;


public class JdbcUtils {

    private static Properties properties = null;
    private static DataSource dataSource = null;

    private volatile static JdbcUtils instatce = null;
    private Connection connection = null;

    //私有构造函数,防止实例化对象
    private JdbcUtils() {

    }

    static {
        try {
            properties = new Properties();
            // 1.加载properties文件
            InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
            // 2.加载输入流
            properties.load(is);
            // 3.获取数据源
            dataSource = getDatasource();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 用简单单例模式确保只返回一个链接对象
     *
     * @return
     */
    public static JdbcUtils getInstace() {
        if (instatce == null) {
            synchronized (JdbcUtils.class) {
                if (instatce == null) {
                    instatce = new JdbcUtils();
                }
            }
        }
        return instatce;
    }

    // 返回一个数据源
    public DataSource getDataSource() {
        return dataSource;
    }

    // 返回一个链接
    public Connection getConnection() {
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    // 加载数据源
    public static DataSource getDatasource() {
        DataSource source = null;
        try {
            source = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return source;
    }
}

4.改造QueryRunnerCRUDTest

DruidUtils

package com.bruce.utils;

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

import javax.sql.DataSource;

public class DruidUtils {

    private static Connection connection = null;

    //获取元数据
    public static DataSource getDatasource() {
        DataSource dataSource = JdbcUtils.getInstace().getDataSource();
        return dataSource;
    }

    //获取链接
    public static Connection getConnection() {
        connection = JdbcUtils.getInstace().getConnection();
        return connection;
    }

    //归还资源
    public void release() {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
package com.bruce.test;

import com.bruce.pojo.Users;
import com.bruce.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

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

public class QueryRunnerCRUDTest {


    //将数据源传递给QueryRunner，QueryRunner内部通过数据源获取数据库连接
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    @Test
    public void add() throws SQLException {
        String sql = "insert into users(name,password,email,birthday) values(?,?,?,?)";
        Object params[] = {"tom", "123", "bruce@sina.com", new Date()};
        qr.update(sql, params);
    }

    @Test
    public void delete() throws SQLException {
        String sql = "delete from users where id=?";
        qr.update(sql, 1);
    }

    @Test
    public void update() throws SQLException {
        String sql = "update users set name=? where id=?";
        Object params[] = {"ddd", 5};
        qr.update(sql, params);
    }

    @Test
    public void find() throws SQLException {
        String sql = "select * from users where id=?";
        Object params[] = {2};
        Users user = (Users) qr.query(sql, new BeanHandler(Users.class), params);
        System.out.println(user);
    }

    @Test
    public void getAll() throws SQLException {
        String sql = "select * from users";
        List<Users> list = (List) qr.query(sql, new BeanListHandler(Users.class));
        System.out.println(list);
    }
}

5.ResultSetHandler接口使用讲解

该接口用于处理​​java.sql.ResultSet​​​，将数据按要求转换为另一种形式。
​​​ResultSetHandler​​​接口提供了一个单独的方法：​​Object handle (java.sql.ResultSet .rs)​​

5.1.ResultSetHandler接口的实现类

  • ​​ArrayHandler​​：把结果集中的第一行数据转成对象数组。
  • ​​ArrayListHandler​​：把结果集中的每一行数据都转成一个数组，再存放到List中。
  • ​​BeanHandler​​​：将结果集中的第一行数据封装到一个对应的​​JavaBean​​实例中。
  • ​​BeanListHandler​​：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
  • ​​ColumnListHandler​​​：将结果集中某一列的数据存放到​​List​​中。
  • ​​KeyedHandler(name)​​​：将结果集中的每一行数据都封装到一个​​Map​​​里，再把这些​​map​​​再存到一个​​map​​里，其key为指定的key。
  • ​​MapHandler​​​：将结果集中的第一行数据封装到一个​​Map​​​里，​​key​​是列名，value就是对应的值。
  • ​​MapListHandler​​​：将结果集中的每一行数据都封装到一个​​Map​​​里，然后再存放到​​List​​

5.2.测试dbutils各种类型的处理器

package com.bruce.test;

import com.bruce.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

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

public class ResultSetHandlerTest {

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

    /**
     * 把结果集中的第一行数据转成对象数组
     *
     * @throws SQLException
     */
    @Test
    public void testArrayHandler() throws SQLException {
        String sql = "select * from users";
        Object result[] = (Object[]) qr.query(sql, new ArrayHandler());
        System.out.println(Arrays.asList(result));  //list  toString()
    }

    /**
     * 把结果集中的每一行数据都转成一个数组，再存放到List中。
     *
     * @throws SQLException
     */
    @Test
    public void testArrayListHandler() throws SQLException {
        String sql = "select * from users";
        List<Object[]> list = (List) qr.query(sql, new ArrayListHandler());
        for (Object[] o : list) {
            System.out.println(Arrays.asList(o));
        }
    }

    /**
     * 将结果集中某一列的数据存放到List中。
     *
     * @throws SQLException
     */
    @Test
    public void testColumnListHandler() throws SQLException {
        String sql = "select * from users";
        List list = (List) qr.query(sql, new ColumnListHandler("id"));
        System.out.println(list);
    }

    /**
     * KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
     *
     * @throws Exception
     */
    @Test
    public void testKeyedHandler() throws Exception {
        String sql = "select * from users";
        Map<Integer, Map> map = (Map) qr.query(sql, new KeyedHandler("id"));
        for (Map.Entry<Integer, Map> me : map.entrySet()) {
            int id = me.getKey();
            Map<String, Object> innermap = me.getValue();
            for (Map.Entry<String, Object> innerme : innermap.entrySet()) {
                String columnName = innerme.getKey();
                Object value = innerme.getValue();
                System.out.println(columnName + "=" + value);
            }
            System.out.println("----------------");
        }
    }

    /**
     * 将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
     * @throws SQLException
     */
    @Test
    public void testMapHandler() throws SQLException {
        String sql = "select * from users";
        Map<String, Object> map = (Map) qr.query(sql, new MapHandler());
        for (Map.Entry<String, Object> me : map.entrySet()) {
            System.out.println(me.getKey() + "=" + me.getValue());
        }
    }


    /**
     * 结果集中的每一行数据都封装到一个Map里，然后再存放到List
     * @throws SQLException
     */
    @Test
    public void testMapListHandler() throws SQLException {
        String sql = "select * from users";
        List<Map> list = (List) qr.query(sql, new MapListHandler());
        for (Map<String, Object> map : list) {
            for (Map.Entry<String, Object> me : map.entrySet()) {
                System.out.println(me.getKey() + "=" + me.getValue());
            }
        }
    }

    @Test
    public void testScalarHandler() throws SQLException {
        String sql = "select count(*) from users";  //[13]  list[13]
        int count = ((Long) qr.query(sql, new ScalarHandler(1))).intValue();
        System.out.println(count);
    }

}

5.5.事务处理

在开发中，对数据库的多个表或者对一个表中的多条数据执行更新操作时要保证对多个更新操作要么同时成功，要么都不成功，这就涉及到对多个更新操作的事务管理问题了。比如银行业务中的转账问题，A用户向B用户转账100元，假设A用户和B用户的钱都存储在Account表，那么A用户向B用户转账时就涉及到同时更新Account表中的A用户的钱和B用户的钱，用SQL来表示就是：

update account set money=money-100 where name='A'
update account set money=money+100 where name='B'

在数据访问层(Dao)中处理事务

对于这样的同时更新一个表中的多条数据的操作，那么必须保证要么同时成功，要么都不成功，所以需要保证这两个update操作在同一个事务中进行。在开发中，我们可能会在​​AccountDao​​写一个转账处理方法，如下：

/**
    * @Method: transfer
    * @Description:这个方法是用来处理两个用户之间的转账业务
    * 在开发中，DAO层的职责应该只涉及到CRUD，
    * 而这个transfer方法是处理两个用户之间的转账业务的，已经涉及到具体的业务操作，应该在业务层中做，不应该出现在DAO层的
    * 所以在开发中DAO层出现这样的业务处理方法是完全错误的
    * @param sourceName
    * @param targetName
    * @param money
    * @throws SQLException
    */
    public void transfer(String sourceName,String targetName,float money) throws SQLException{
        Connection conn = null;
        try{
            conn = DruidUtils.getConnection();
            //开启事务
            conn.setAutoCommit(false);
            /**
             * 在创建QueryRunner对象时，不传递数据源给它，是为了保证这两条SQL在同一个事务中进行，
             * 我们手动获取数据库连接，然后让这两条SQL使用同一个数据库连接执行
             */
            QueryRunner runner = new QueryRunner();
            String sql1 = "update account set money=money-100 where name=?";
            String sql2 = "update account set money=money+100 where name=?";
            Object[] paramArr1 = {sourceName};
            Object[] paramArr2 = {targetName};
            runner.update(conn,sql1,paramArr1);
            //模拟程序出现异常让事务回滚
            int x = 1/0;
            runner.update(conn,sql2,paramArr2);
            //sql正常执行之后就提交事务
            conn.commit();
        }catch (Exception e) {
            e.printStackTrace();
            if(conn!=null){
                //出现异常之后就回滚事务
                conn.rollback();
            }
        }finally{
            //关闭数据库连接
            conn.close();
        }
    }

5.6.自定义处理程序

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `firstName` varchar(255) DEFAULT NULL,
  `lastName` varchar(255) DEFAULT NULL,
  `uname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', '18', '张', '三', '张三');
INSERT INTO `employee` VALUES ('2', '19', '李', '四', '李四');

实体类

package com.bruce.pojo;

public class Employee {

    private int id;
    private int age;
    private String first;
    private String last;
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getAge() {
        return age;
    }

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

    public String getFirst() {
        return first;
    }

    public void setFirst(String first) {
        this.first = first;
    }

    public String getLast() {
        return last;
    }

    public void setLast(String last) {
        this.last = last;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Employee() {
    }
}

自定义​​EmployeeHandler​​

package com.bruce.pojo;

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

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

public class EmployeeHandler extends BeanHandler<Employee> {

    public EmployeeHandler(Class<Employee> type) {
        super(type);
    }

    @Override
    public Employee handle(ResultSet rs) throws SQLException {
        Employee employee = super.handle(rs);
        employee.setFirst(rs.getString("firstName"));
        employee.setLast(rs.getString("lastName"));
        employee.setName(rs.getString("uname"));
        return employee;
    }
}

测试查询

@Test
 public void testEmp() throws SQLException {
     String sql = "SELECT * FROM employee WHERE firstName=?";
     Employee employee =  qr.query(sql, new EmployeeHandler(Employee.class),"李");
     System.out.println(employee);
 }

自定义​​ResultSetHandler​​

@Test
 public void testEmpList() throws SQLException {
     String sql = "SELECT * FROM employee";
     List<Employee> list = (List) qr.query(sql, new ResultSetHandler<List<Employee>>(){
         public List<Employee> handle(ResultSet resultSet) throws SQLException {
             List<Employee> employeees = new ArrayList<Employee>();
             while (resultSet.next()){
                 int id = resultSet.getInt("id");
                 int age = resultSet.getInt("age");
                 String firstName=resultSet.getString("firstName");
                 String lastName=resultSet.getString("lastName");
                 String uname=resultSet.getString("uname");
                 Employee employee=new Employee(id,age,firstName,lastName,uname);
                 employeees.add(employee);
             }
             return employeees;
         }
     });
     for (Employee employee : list) {
         System.out.println(employee);
     }
 }

 @Test
 public void testEmp1() throws SQLException {
     String sql = "SELECT * FROM employee WHERE firstName=?";
     Employee employee =  qr.query(sql, new ResultSetHandler<Employee>(){

         public Employee handle(ResultSet resultSet) throws SQLException {
             Employee employee=null;
             while (resultSet.next()){
                 int id = resultSet.getInt("id");
                 int age = resultSet.getInt("age");
                 String firstName=resultSet.getString("firstName");
                 String lastName=resultSet.getString("lastName");
                 String uname=resultSet.getString("uname");
                 employee=new Employee(id,age,firstName,lastName,uname);
             }
             return  employee;
         }
     },"李");
     System.out.println(employee);