前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >JDBC 使用DBUtils 查询、更新(15)

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

代码语言:javascript
复制
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的返回值

代码语言:javascript
复制
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()源码分析

代码语言:javascript
复制
//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参数对应的对象

代码语言:javascript
复制
    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对象对应的对象

代码语言:javascript
复制
    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查询的列名(不是别名),列的值

代码语言:javascript
复制
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的集合

代码语言:javascript
复制
    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等)返回

代码语言:javascript
复制
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 删除

本文分享自 作者个人站点/博客 前往查看

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

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • DBUtils 基本介绍
  • API介绍
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档