JDBC 使用DBUtils 查询、更新（15）

桑鱼

发布于 2020-03-17 17:29:33

1.1K0

发布于 2020-03-17 17:29:33

文章被收录于专栏：学习笔记持续记录中...

DBUtils 基本介绍

commons-dbutils是Apache组织提供的一个开源JDBC工具类库，它是对JDBC的简单封装。简化了jdbc编码的工作量

API介绍

org.apache.commons.dbutils.QueryRunner org.apache.commons.dbutils.ResultSetHandler 工具类：org.apache.commons.dbutils.DbUtils

代码

update()方法可用于Insert、update、delete

public class JDBCTest06 {
    // 删除delete
    public JDBCTest06() throws SQLException {
        // 1. 创建QueryRunner 的实现类
        QueryRunner queryRunner = new QueryRunner();
        // 2. 使用其update 方法
        String sql = "DELETE FROM customers " + "WHERE id IN(?,>)";
        DataSource dataSource = new ComboPooledDataSource("helloc3p0");
        // 3. 在连接池获取连接
        Connection connection =  dataSource.getConnection();
        queryRunner.update(connection,sql,12,13); // update方法可用于删除、更新、添加
        connection.close();
    }
}

通过实现ResultSetHandler接口实现查询

创建 ResultSetHandler接口的实现类，实现handle方法，queryRunner.query()的返回值取决于handle的返回值

public class DBUtilsTest {
    QueryRunner queryRunner = new QueryRunner();
    // 1. 创建 ResultSetHandler接口 的实现类，实现handle方法，queryRunner.query()的返回值取决于handle的返回值
    class MyResultSetHandler implements ResultSetHandler{
        @Override
        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);
                Date birth = rs.getDate(4);
                Customer customer = new Customer(id,name,email,birth);
                customers.add(customer);
            }
            return customers;
        }
    }
    public void testQuery() throws SQLException {
        Connection connection = null;
        String sql = "select id,name,email,birth" + "from customers";
        Object object = queryRunner.query(connection, sql, new MyResultSetHandler());
        System.out.println(object);
        connection.close();
    }
}

queryRunner.query()源码分析

//1. QueryRunner 类的query()方法
public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        return this.<T>query(conn, false, sql, rsh, params);// 返回值是调用当前的类query的重载方法
    }
// 2.当前的类query的重载方法
private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
            throws SQLException {        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        T result = null;

        try {
            stmt = this.prepareStatement(conn, sql);
            this.fillStatement(stmt, params);
            rs = this.wrap(stmt.executeQuery()); // wrap() 返回的是ResultSet
            result = rsh.handle(rs); //  handle()是ResultSetHandler接口定义的方法

        } catch (SQLException e) {
            this.rethrow(e, sql, params);

        } finally {
            try {
                close(rs);
            } finally {
                close(stmt);
                if (closeConn) {
                    close(conn);
                }
            }
        }
        return result;
    }

通过BeanHandler类实现查询

把结果集的第一条记录转为创建BeanHandler对象时传入的class参数对应的对象

    public void testBeanHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = null;
        String sql = "select id,name,email,birth" + "from customers where id >= ?";
        Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);
        System.out.println(object);
        connection.close();
    }

通过BeanListHandler类实现查询

把结果集转为一个List，该List不为null，但可能为空集合（size()方法返回为0），若SQL语句有查询记录，List中存放创建BeanListHandler转入Class对象对应的对象

    public void testBeanListHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = null;
        String sql = "select id,name,email,birth" + "from customers";
        List<Customer> customers = queryRunner.query(connection,sql,new BeanListHandler<Customer>(Customer.class));
        Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);
        System.out.println(object);
        connection.close();
    }

通过MapHandler类实现查询

返回SQL对应的第一条记录对应的Map对象，键值对：键SQL查询的列名(不是别名)，列的值

public void testMapHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = null;
        String sql = "select id,name,email,birth" + "from customers";
        Map<String,Object> customers = queryRunner.query(connection,sql,new MapHandler());
        Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);
        System.out.println(object);
        connection.close();
    }

通过MapListHandler类实现查询

将结果集转为一个Map的List，Map对应查询的一条记录：键值堆：键SQL查询的列名（不是列的别名），值：列的值，而MapListHandler：返回的多条记录对应的Map的集合

    public void testMapListHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = null;
        String sql = "select id,name,email,birth" + "from customers";
        List<Map<String,Object>> result = queryRunner.query(connection,sql,new MapListHandler());
        Object object = queryRunner.query(connection, sql, new BeanHandler(Customer.class),5);
        System.out.println(object);
        connection.close();
    }

ScalarHandler：把结果集转为一个数值（可以是任意基本数据类型和字符串，Date等）返回

public void ScalarHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = null;
        String sql = "select name" + "from customers"; // 如果是两列的情况返回一列
        List<Map<String,Object>> result = queryRunner.query(connection,sql,new MapListHandler());
        Object object = queryRunner.query(connection, sql, new ScalarHandler<>(),5);
        System.out.println(object);
        connection.close();
    }

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

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

sql