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

2020 年 3 月 17 日
筆記

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();      }