JDBC 使用 DBUtils 进行查询的实现原理和具体的 Handler

最新推荐文章于 2022-08-18 11:03:42 发布

weixin_33980459

最新推荐文章于 2022-08-18 11:03:42 发布

阅读量98

点赞数

文章标签： python

原文链接：https://my.oschina.net/u/2260265/blog/348935

版权

2019独角兽企业重金招聘Python工程师标准>>>

/**
* JDBC 使用 DBUtils 进行操作
* 测试 QueryRunner 类的 update 方法
* 该方法可用于 INSERT， UPDATE 和 DELETE
*/

QueryRunner queryRunner = new QueryRunner();

class MyResultSetHandler implements ResultSetHandler{
  @Override
  public Object handle(ResultSet resultSet) throws SQLException {
//   System.out.println("handle...");
//   return "passionfly";

   List<Customer> customers = new ArrayList<Customer>();
   while(resultSet.next()){
    Integer id = resultSet.getInt(1);
    String name = resultSet.getString(2);
    String email = resultSet.getString(3);
    Date hiredate = resultSet.getDate(4);
    Customer customer = new Customer(id, name, email, hiredate);
    customers.add(customer);
   }
   return customers;
  }
}

@Test
public void testUpdate() {
  //1. 创建 QueryRunner 的实现类
   String sql = "DELETE FROM customer WHERE id IN (?, ?)";
  Connection connection = null;
   try {
   connection = JDBCTools.GetConnection();
     //2. 使用其 update 方法
   queryRunner.update(connection, sql, 2, 4);
  } catch (Exception e) {
   e.printStackTrace();
  } finally{
   JDBCTools.release(null, null, connection);
  }
}

/**
* QueryRunner 的 query 方法的返回值取决于其 ResultSetHandler 参数的 handle 方法的返回值
*/
@Test
public void testQuery(){
  Connection connection = null;
  try {
   connection = JDBCTools.GetConnection();
   String sql = "SELECT id, name, email, hiredate " +
     "FROM customer";

   Object obj = queryRunner.query(connection, sql,
     new MyResultSetHandler()); //query 方法的返回值即為handle 方法的返回值

   System.out.println(obj);
  } catch (Exception e) {

  } finally{
   JDBCTools.release(null, null, connection);
  }

/**
* BeanHandler: 把结果集的第一条记录转为创建 BeamHandler 对象时传入 Class
* 的参数对应的对象。
*/
@Test
public void testBeanHandler(){
  Connection connection = null;
  try {
   connection = JDBCTools.GetConnection();
   String sql = "SELECT id, name, email, hiredate FROM customer" +
     " WHERE id = ?";
   Customer customer = queryRunner.query(connection, sql,
     new BeanHandler(Customer.class), 1);

   System.out.println(customer);
  } catch (Exception e) {
   e.printStackTrace();
  } finally{
   JDBCTools.release(null, null, connection);
  }
}
}

/**
* BeanListHandler: 把结果集转为一个 List，该 List 不为 null，但可能为空集合（size（）方法返回 0）
* 若SQL 语句的确能够查询到记录，List 中存放创建 BeanListHandler 传入的 Class 对象对应的对象。
*/
@Test
public void testBeamListHandler(){
  Connection connection = null;
  try {
   connection = JDBCTools.GetConnection();
   String sql = "SELECT id, name, email, hiredate FROM customer" ;
   List<Customer>
   customers = queryRunner.query(connection, sql,
     new BeanListHandler(Customer.class));

   System.out.println(customers);
  } catch (Exception e) {
   e.printStackTrace();
  } finally{
   JDBCTools.release(null, null, connection);
  }
}

/**
* MapHandler: 返回 SQL 对应的第一条记录对应的 Map 对象
* Map 对应查询的一条记录：键：SQL查询的列名（不是列的别名），值：列的值
* 而 MapListHandler：返回的对条记录对应的 Map 集合
*/
@Test
public void testMapListHandler(){
  Connection connection = null;
  try {
   connection = JDBCTools.GetConnection();
   String sql = "SELECT id, name, email, hiredate FROM customer" ;
   List<Map<String, Object>> result = queryRunner.query(connection, sql,
     new MapListHandler());

   System.out.println(result);
  } catch (Exception e) {
   e.printStackTrace();
  } finally{
   JDBCTools.release(null, null, connection);
  }
}
@Test
public void testMapHandler(){
  Connection connection = null;
  try {
   connection = JDBCTools.GetConnection();
   String sql = "SELECT id, name, email, hiredate FROM customer" ;
   Map<String, Object> result = queryRunner.query(connection, sql,
     new MapHandler());

   System.out.println(result);
  } catch (Exception e) {
   e.printStackTrace();
  } finally{
   JDBCTools.release(null, null, connection);
  }

}

@Test
public void testScalarHandler(){
  Connection connection = null;
  try {
   connection = JDBCTools.GetConnection();
   String sql = "SELECT name" +
     " FROM customer WHERE id = ?" ;
   Object result = queryRunner.query(connection, sql,
     new ScalarHandler(), 1);

   System.out.println(result);
  } catch (Exception e) {
   e.printStackTrace();
  } finally{
   JDBCTools.release(null, null, connection);
  }
}