一.DBUtils

1.什么是DBUtils

DBUtils是java编程中的数据库操作实用工具，小巧简单实用。DBUtils封装了对JDBC的操作，简化了JDBC操作，可以简化代码。Dbutils三个核心功能介绍
1.QueryRunner中提供对sql语句（insert，update，delete）操作的API;
2.ResultSetHandler接口，用于定义select操作后，怎样封装结果集.;
3.DbUtils类，它就是一个工具类,定义了关闭资源与事务处理的方法;

2.API 文档

http://commons.apache.org/proper/commons-dbutils/apidocs/org/apache/commons/dbutils/package-summary.html

二.核心API

1.QueryRunner类 和 ResultSetHandler接口

http://commons.apache.org/proper/commons-dbutils/apidocs/org/apache/commons/dbutils/QueryRunner.html

①增删改

@Test
public void insertDemo() throws SQLException {
    QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());
    String sql = "insert into table Values(?,?)";
    queryRunner.update(sql, 2, "222");
}
//删除
@Test
public void deleteDemo() throws SQLException {
    QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());
    String sql = "delete from table where id = ?";
    queryRunner.update(sql, 2);
}
//修改
@Test
public void updateDemo() throws SQLException {
    QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());
    String sql = "delete table set param1=?,param2=? where id = ?;
    queryRunner.update(sql, 2, new Date(),3);
}

②.查询

BeanHandler
将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler
将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。

// 查找单条记录
@Test
public void find() throws SQLException {
    String sql = "select * from `column` where id = ?";
    //传入数据封装的类
    Column col = queryRunner.query(sql, new BeanHandler<>(Column.class),1);
    System.out.println(col);
}
 // 查找所有记录
@Test
public void getAll() throws SQLException {
    List<Column> list = queryRunner.query("select * from `column`",new BeanListHandler<Column>(Column.class));
    for(Column c : list){
        System.out.println(c);
    }
}

ArrayHandler：把结果集中的第一行数据转成对象数组。
ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。

 @Test
//ArrayHandler
public void test2() throws SQLException {
    String sql = "select * from `column`";
    //ArrayHandler
    Object[] col = queryRunner.query(sql, new ArrayHandler());
    System.out.println(Arrays.toString(col));
    //ArrayListHandler
    List list = queryRunner.query(sql,new ArrayListHandler());
    for(Object obj:list){
        System.out.println(Arrays.toString((Object[]) obj));
    }
}

ColumnListHandler：将结果集中某一列的数据存放到List中。

 //ColumnHandler
    public void test4() throws SQLException {
        String sql = "select * from `column`";
        //
        List<String> list = (List) queryRunner.query(sql,new ColumnListHandler<>("name"));
        for(String name : list){
            System.out.println(name);
        }
    }

MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List

//MapHandler
@Test
public void test3() throws SQLException {
    String sql = "select * from `column`";
    //ArrayHandler
    Map<String,Object> map = queryRunner.query(sql, new MapHandler());
    for(String key : map.keySet()){
        System.out.println(key);
    }
}

ScalarHandler:用来获得聚合函数的值,返回类型是Object

@Test
public void test45() throws SQLException {
    String sql = "select Count(*) from `column`";
    Long count = (Long) queryRunner.query(sql,new ScalarHandler());
    System.out.println(count);
}

③泛型实现

//查询的泛型实现
public static <T> T find(Class<T> cls, String sql, Object... param) {
    try {
        QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());
        return queryRunner.query(sql, new BeanHandler<>(cls), param);
    } catch (SQLException exception) {
        exception.printStackTrace();
    }
    return null;
}
public static <T> List<T> query(Class<T> cls, String sql, Object... param) {
    try {
        QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());
        return queryRunner.query(sql, new BeanListHandler<T>(cls), param);
    } catch (SQLException exception) {
        exception.printStackTrace();
    }
    return new ArrayList<T>();
}
//更新
public static int update(String sql, Object... param) throws SQLException {
    QueryRunner queryRunner = new QueryRunner(DBUtil.getDataSource());
    return queryRunner.update(sql, param);
}
public static int update(Connection conn, String sql, Object... param) throws SQLException {
    QueryRunner queryRunner = new QueryRunner();
    return queryRunner.update(conn, sql, param);
}