DBUtils详解

DBUtils: DBUtilsjar包.（首先导入jar包）

DBUtils三大核心功能

1.QueryRunner类

QueryRunner有三个方法：
1.query（）：用于执行查询语句
2.update（）：用于执行增删改语句
3.batch（）：用于执行批处理

构造函数有无参new QueryRunner（）和有参new QueryRunner（DataSource ds），如果使用了有参的构造方法就不需要再传入connection

1.insert语句：

    public static void insert(){
        QueryRunner qr = new QueryRunner();
        String sql = "insert into student values(112,'张三',19)";
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            int count = qr.update(conn,sql);
            System.out.println(count);
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

2.delete语句

    public static void delete(){
        QueryRunner qr = new QueryRunner();
        String sql = "delete from student where sno = 112";
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            int count = qr.update(conn,sql);
            System.out.println(count);
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

3.update语句：

    public static void update(){
        QueryRunner qr = new QueryRunner();
        String sql = "update student set sage= sage+10 where sno = 110";
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            int count = qr.update(conn,sql);
            System.out.println(count);
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

2.ResultSetHandler接口

1.ArrayHandler：将查询结果的第一行数据，保存到Object数组中

    public static void arrayHandler(){
        QueryRunner qr = new QueryRunner();
        String sql = "select * from student";

        try {
            Connection conn = JDBCUtils.getConnection();
            Object[] query = qr.query(conn, sql, new ArrayHandler());
            for(Object obj : query)
                System.out.print(obj);
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

2.arrayListHandler： 将查询的结果，每一行先封装到Object数组中，然后将数据存入List集合

    public static void arrayListHandler(){
        QueryRunner qr = new QueryRunner();
        String sql = "select * from student";
        try {
            Connection conn = JDBCUtils.getConnection();
            List<Object[]> query = qr.query(conn, sql, new ArrayListHandler());
            for(Object[] objs : query){
                for (Object obj : objs){
                    System.out.print(obj+"  ");
                }
                System.out.println();
                DbUtils.closeQuietly(conn);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

3.BeanHandler： 将查询结果的第一行数据，封装到student对象

    public static void BeanHandler(){
        QueryRunner qr = new QueryRunner();
        String sql = "select * from student";
        try {
            Connection conn = JDBCUtils.getConnection();
            Student query = qr.query(conn, sql, new BeanHandler<Student>(Student.class));
            System.out.println(query);
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

4.BeanLIstHandler： 将查询结果的每一行封装到student对象，然后再存入List集合

    public static void BeanListHandler(){
        QueryRunner qr = new QueryRunner();
        String sql = "select * from student";
        try {
            Connection conn = JDBCUtils.getConnection();
            List<Student> query = qr.query(conn, sql, new BeanListHandler<Student>(Student.class));
            for (Student student : query){
                System.out.println(student+"  ");
            }
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

5.ColumnListHandler：将查询结果的指定列的数据封装到List集合中

    public static void ColumnListHandler(){
        QueryRunner qr = new QueryRunner();
        String sql = "select * from student";
        try {
            Connection conn = JDBCUtils.getConnection();
            List<Object> sno = qr.query(conn, sql, new ColumnListHandler<>("sno"));
            for (Object obj : sno)
                System.out.println(obj);
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

6.ScalarHanlder：将结果集第一行的某一列放到某个对象中

    public static void ScalarHandler(){
        QueryRunner qr = new QueryRunner();
        String sql = "select count(*) from student";
        try {
            Connection conn = JDBCUtils.getConnection();
            long query = qr.query(conn, sql, new ScalarHandler<Long>());
            System.out.println(query);
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

7.MapHandler：将结果集中的第一行数据封装到一个Map

    public static void MapHandler(){
        QueryRunner qr = new QueryRunner();
        String sql = "select * from student where sno = ?";
        try {
            Connection conn = JDBCUtils.getConnection();
            Map<String, Object> query = qr.query(conn, sql, new MapHandler(), 110);
            System.out.println(query);
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

8.MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List

    public static void MapListHandler() {
        QueryRunner qr = new QueryRunner();
        String sql = "select * from student where sno = ?";
        try {
            Connection conn = JDBCUtils.getConnection();
            List<Map<String, Object>> query = qr.query(conn, sql, new MapListHandler(),110);
            for (Map<String, Object> map : query) {
                System.out.println(map);
            }
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

9. KeyedHandler:将结果集中的每一行数据都封装到一个Map里(List

    public static void KeyedHandler(){
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from student";
        Map<String,Map<String,Object>>map = null;
        try {
            Connection conn = JDBCUtils.getConnection();
            map = queryRunner.query(conn,sql,new KeyedHandler<String>("sname"));
            for(Map.Entry<String,Map<String,Object>> entry : map.entrySet()){
                System.out.println(entry.getKey());
                System.out.println(entry.getValue());
            }
            DbUtils.closeQuietly(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

3.DBUtils类

它提供关于关闭资源以及事务rollback,commit操作。里面的方法都是静态的。

DbUtils.closeQuietly(conn);//用来关闭安静地连接