一、简介

commons-dbutils是Apache组织提供的一个开源JDBC工具类库

二、使用

1.需要的jar包

image image.gif

​

2.一些准备工作

数据库数据表如下：

image image.gif

​

创建一个customer类：

public class Customer {
    private Integer id;
    private String name;
    private String email;
    private String birth;

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getBirth() {
        return birth;
    }
    public void setBirth(String birth) {
        this.birth = birth;
    }

    public Customer(Integer id, String name, String email, String birth) {
        super();
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
    }
    public Customer() {

    }
}

image.gif

编写的JDBCTools工具类代码如下：

public class JDBCTools {

    private static DataSource dataSource = null;
    static {
        dataSource =  new ComboPooledDataSource();  
    }

    //获取数据库连接
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        return dataSource.getConnection();
    }

    public static void releaseSource(ResultSet res,Statement statement,Connection connection) {
        if(res != null) {
            try {
                res.close();
            }catch(Exception e) {
                e.printStackTrace();
            }
        }
        if(statement != null) {
            try {
                statement.close();
            }catch(Exception e) {
                e.printStackTrace();
            }
        }
        if(connection != null) {
            try {
                connection.close();
            }catch(Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 
     * 关闭连接
     * @param statement
     * @param connection
     */
    public static void releaseSource(Statement statement,Connection connection) {
        if(statement != null) {
            try {
                statement.close();
            }catch(Exception e) {
                e.printStackTrace();
            }
        }
        if(connection != null) {
            try {
                connection.close();
            }catch(Exception e) {
                e.printStackTrace();
            }
        }
    }
}

image.gif

---

3.QueryRunner

(1).使用update方法进行更新操作

创建一个单元测试类，然后编写测试代码：

    @Test
    public void testQueryRunner() {
        //1.创建QueryRunner的实现类
        QueryRunner qr = new QueryRunner();
        //2.使用其update方法
        String sql = "DELETE FROM customers "
                + "WHERE ID = ?";
        Connection conn = null;
        try {
            conn = JDBCTools.getConnection();
            qr.update(conn, sql, 1);
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null, conn);
        }
    }

image.gif

(2).使用query方法进行查询操作

query方法：

1).先得到结果集ResultSet

2).调用query的第三个参数：ResultSetHandler对象的handle方法，对结果集进行操作

query()方法的返回值取决于其ResultSetHandler参数的handle方法的返回值

示例代码如下：

    //创建一个内部类实现ResultSetHandler接口
    class MyResultSetHandler implements ResultSetHandler{
        //对接口的handle方法进行实现
        public Object handle(ResultSet rs) throws SQLException {
            //对结果集进行操作....
            List<Customer> customers = new ArrayList<>();
            while(rs.next()) {
                Integer id = rs.getInt(1);
                String name = rs.getString(2);
                String email = rs.getString(3);
                String birth = rs.getString(4);
                Customer customer = new Customer(id, name, email, birth);
                customers.add(customer);
            }
            return  customers;//返回
        }
    }

    @Test
    public void testQueryRunner() {
        Connection conn = null;
        //1.创建QueryRunner示例类
        QueryRunner qr = new QueryRunner();
        try {
            conn = JDBCTools.getConnection();
            String sql = "SELECT ID,NAME,EMAIL,BIRTH "
                    + "FROM customers";
            //2.使用其query方法,query()方法的返回值取决于其ResultSetHandler参数的handle方法的返回值
            Object a = qr.query(conn, sql,new MyResultSetHandler());
            System.out.println(a);

        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null, conn);
        }
    }

image.gif

4.BeanHandler

把结果集的第一条记录转为创建BeanHandler对象时传入的Class参数对应的对象，并返回

示例代码：

    @Test
    public void testBeanHandler() {
        QueryRunner qs = new QueryRunner();
        Connection connection = null;
        try {
            connection = JDBCTools.getConnection();
            String sql = "SELECT * FROM customers WHERE id = ?";
            Customer customer = (Customer) qs.query(connection, sql,new BeanHandler(Customer.class) , 5);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null, null, connection);
        }
    }

image.gif

5.BeanListHandler

把结果集转为一个List，该List不会为null,但可能为空集合

空集合即是该集合里面没有元素，size()返回0

若SQL语句的确能够查询到记录，List中存放创建BeanListHandler传入的Class对象对应的对象

示例代码：

    @Test
    public void testBeanListHandler() {
        QueryRunner qs = new QueryRunner();
        Connection connection = null;
        try {
            connection = JDBCTools.getConnection();
            String sql = "SELECT * FROM customers WHERE id < 5 ";
            List<Customer> customers = (List<Customer>) qs.query(connection, sql,new BeanListHandler(Customer.class));
            System.out.println(customers);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null, null, connection);
        }
    }

image.gif

6.MapHandler

返回SQL对应的第一条记录对应的Map对象

Map的键是SQL查询的列名（不是列的别名），Map的值是列的值

查询的列名应该和要创建的类的属性名一致

这里的属性名指的是某类里面的get/set方法后面的名字（首字母改为小写）

例如getUserName方法，那么该属性名即是userName

示例代码：

    @Test
    public void testMapHandler() {
        QueryRunner qs = new QueryRunner();
        Connection connection = null;
        try {
            connection = JDBCTools.getConnection();
            String sql = "SELECT * FROM customers WHERE id = ? ";
            Map<String, Object> customers = (Map<String, Object>) qs.query(connection, sql,new MapHandler(),5);
            System.out.println(customers);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null, null, connection);
        }

    }

image.gif

7.MapListHandler

将结果集转为一个Map的List

返回的是多条记录对应的Map集合，一个Map对应查询的一条记录

Map的键：对应查询的列名(不是列的别名)

Map的值：对应列的值

示例代码：

    @Test
    public void testMapListHandler() {
        QueryRunner qs = new QueryRunner();
        Connection connection = null;
        try {
            connection = JDBCTools.getConnection();
            String sql = "SELECT * FROM customers WHERE id < ? ";
            List<Map<String, Object>> customers = (List<Map<String, Object>>) qs.query(connection, sql,new MapListHandler(),5);
            System.out.println(customers);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null, null, connection);
        }
    }

image.gif

8.ScalarHandler

把结果集转为一个数值(可以是任意基本数据类型和字符串、Date等)返回

示例代码：

    @Test
    public void testScalarHandler() {
        QueryRunner qs = new QueryRunner();
        Connection connection = null;
        try {
            connection = JDBCTools.getConnection();
            String sql = "SELECT count(ID) FROM customers";
            Object result = qs.query(connection, sql,new ScalarHandler());
            System.out.println(result);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null, null, connection);
        }
    }

image.gif