Jdbc学习之使用DbUtils框架

1. 作用

使用 Java 对数据库进行增、删、改、查操作

2. 环境

• commons-dbutils.jar
• c3p0.jar
• mchange-commons.jar
  
  

3. 使用

update()

• int update(String sql,Object… params)
• 此方法可以应用于 INSERT、UPDATE、DELETE 操作

@Test
public void testUpdate() {
    //1. 创建 QueryRunner 的实现类
    QueryRunner queryRunner = new QueryRunner();
        
    //2. 使用其 update 方法
//  String sql = "DELETE FROM customers WHERE id IN(?)";    //删除
        
//  String sql = "INSERT INTO customers(name, email, birth) VALUES(?,?,?)";     //插入
        
    String sql = "UPDATE customers SET name = ? WHERE id = ?";
        
    Connection connection = null;
        
    try {
        connection = JDBCTools.getConnection();
        queryRunner.update(connection, sql, "luw", 11);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCTools.release(connection, null, null);
    }
}

query()

• 此方法取决于其 ResultSetHandler 参数的 handel 方法的返回值

class MyResultHandler implements ResultSetHandler{

    @Override
    public Object handle(ResultSet resultSet) throws SQLException {
        List<Customer> customers = new ArrayList<>();
            
        while(resultSet.next()){
            Integer id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String email = resultSet.getString(3);
            Date birth = resultSet.getDate(4);
                
            Customer customer = new Customer(id, name, email, birth);
            customers.add(customer);
        }
        return customers;
    }
}

@Test
public void testQuery() {
    QueryRunner queryRunner = new QueryRunner();
    Connection connection = null;
        
    try {
        connection = JDBCTools.getConnection();
        String sql = "SELECT id, name, email, birth FROM customers";
        Object obj = queryRunner.query(connection, sql, new MyResultHandler());
        System.out.println(obj);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCTools.release(connection, null, null);
    }
}

ResultSetHandler 接口

• BeanHandler：用来把结果集的一条记录（默认为第一条记录）转为创建 BeanHandler 对象时传入的参数对应的对象，如下面的 Employee 对象

@Test
public void testBeanHandler(){
    QueryRunner queryRunner = new QueryRunner();
    Connection connection = null;
        
    try {
        connection = JDBCTools.getConnection();
        String sql = "SELECT id, name, email, birth FROM customers WHERE id = ?";
            
        Customer customer = queryRunner.query(connection, sql, new BeanHandler<Customer>(Customer.class), 23);
            
        System.out.println(customer);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCTools.release(connection, null, null);
    }
}

• BeanListHandler：

1. 把结果集转为一个 List，该 List 中包含的是对象的集合（该 List 不为 null，但可
   能为空集合，即 size() 方法返回 0）
2. 若 SQL 语句的确能够查询到记录，List 中存放的对象为创建 BeanListHandler 时
   传入的对象

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

• MapHandler：返回 SQL 语句的第一条记录对应的 Map 对象
  键：SQL 查询的列名（不是列的别名） 值：列的值

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

• MapListHandler：

1. 将结果集转为一个 Map 的 List，返回多条记录的 Map 集合（List<Map<Object，Object>>）
2. Map 对应的查询的一条记录：键：SQL 查询的列名（不是列的别名） 值：列的值

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

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

@Test
public void testScalarHandler(){
    QueryRunner queryRunner = new QueryRunner();
    Connection connection = null;
        
    try {
        connection = JDBCTools.getConnection();
        String sql = "SELECT name FROM customers WHERE id = ?";
            
        Object customers = queryRunner.query(connection, sql, new ScalarHandler<Customer>(), 2);
        System.out.println(customers);
            
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCTools.release(connection, null, null);
    }
}