DbUtils的使用

数据库的准备:
Stu表中3条数据:
1 马云 33
2 马化腾 44
3 雷军 55

JdbcUtils的准备

{
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConn(){

        try {
            return DriverManager.getConnection("jdbc:mysql://localhost:3306/xxx","root","root");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Test
    public void testConn(){
        System.out.println(getConn());
    }

    public static void  close(AutoCloseable ... autoCloseables){

        for (AutoCloseable auto : autoCloseables) {
            if (auto != null) {
                try {
                    auto.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        }
    }

QueryRunner的更新操作:

   /**
     * 插入操作
     */
    @Test
    public void insert(){

        //获取到执行sql 语句的对象 QueryRunner
        QueryRunner qr = new QueryRunner();

        //sql 语句
        String sql = "insert into stu(id,name,age) values (?,?,?)";
        //给参数
        Object [] params = {20,"张小龙",33};
        Connection conn = JdbcUtils.getConn();
        //用来完成 更新操作  增加 修改 删除
        int update = 0;
        try {
            update = qr.update(conn, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        if (update > 0){
            System.out.println("插入成功");
        }
    }

    @Test
    public void update(){

        //创建 queryRunner 对象  完成sql语句的执行
        QueryRunner qr = new QueryRunner();

        //执行 sql
        String sql = "update stu set name = ? where id = ?";

        Object [] params = {"张三",20};
        try {
            int update = qr.update(JdbcUtils.getConn(), sql, params);

            if (update > 0){
                System.out.println("修改成功...");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void delete(){

        //创建 QueryRunner 对象
        QueryRunner qr = new QueryRunner();
        //执行 sql
        String sql = "delete from stu where name = ?";
        Object [] params = {"aa"};
        int update = 0;
        try {
            update = qr.update(JdbcUtils.getConn(), sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (update > 0){
            System.out.println("删除成功...");
        }
    }

ResultSetHandler 的操作：
方法:query(Connection conn,String sql,ResultSetHandler rsh,Object… params) 用来完成表数据的查询操作

   /**
     * ArrayHandler：将结果集中的第一条记录封装到一个Object[]数组中，
     * 数组中的每一个元素就是这条记录中的每一个字段的值
     */
    @Test
    public void test01(){

        //获取 QueryRunner()
        QueryRunner qr = new QueryRunner();

        //执行 sql 操作
        String sql = "select * from stu where id > 3";
        Object [] params = {};

        Object[] objArray = new Object[0];
        try {
            objArray = qr.query(JdbcUtils.getConn(), sql, new ArrayHandler(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //处理集的处理
        System.out.println(Arrays.toString(objArray));
    }

    /**
     *  ArrayListHandler 操作 
     */
    @Test
    public void test02(){

        // QueryRunner 对象的操作
        QueryRunner qr = new QueryRunner();
        // 执行 sql语句
        String sql = "select * from stu where id > ?";
        Object [] params = {2};

        try {
            List<Object[]> list = qr.query(JdbcUtils.getConn(), sql, new ArrayListHandler(), params);

            for (Object[] objects : list) {
                System.out.println(Arrays.toString(objects));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * BeanHandler  结果集 第一条记录封装到 一个指定的javaBean中
     */
    @Test
    public void test01() {

        //获取QueryRunner
        QueryRunner qr = new QueryRunner();

        //执行sql语句
        String sql = "select * from stu where id > ?";
        Object[] params = {2};

        Student stu = null;
        try {
            stu = qr.query(JdbcUtils.getConn(), sql, new BeanHandler<Student>(Student.class), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(stu);
    }

    /**
     * BeanListHandler 的操作  返回 bean集合
     */
    @Test
    public void test02() {

        QueryRunner qr = new QueryRunner();

        String sql = "select * from stu where id > ?";
        Object[] params = {3};
        Connection conn = JdbcUtils.getConn();
        try {
            List<Student> list = qr.query(conn, sql, new BeanListHandler<Student>(Student.class), params);
            for (Student stu : list) {
                System.out.println(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

/**
     * 结果集返回map
     */
    @Test
    public void test01() {

        QueryRunner qr = new QueryRunner();
        String sql = "select * from stu where id > ?";
        Object[] params = {3};
        try {
            Map map = qr.query(JdbcUtils.getConn(), sql, new MapHandler(), params);
            System.out.println(map);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 结果集返回map集
     */
    @Test
    public void test02() {

        QueryRunner qr = new QueryRunner();

        String sql = "select * from stu where id > ?";
        Object[] params = {3};
        try {
            List<Map<String, Object>> query = qr.query(JdbcUtils.getConn(), sql, new MapListHandler(), params);

            for (Map<String, Object> map : query) {
                System.out.println(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

/**
     * ColumnListHandler：
     *    将结果集中指定的列的字段值，封装到一个List集合中
     */
    @Test
    public void test01(){

        //获取 QueryRuuner 对象
        QueryRunner qr = new QueryRunner();
        String sql = "select name from stu where id > ?";
        Object [] params = {3};

        List<String> list = null;
        try {
            list = qr.query(JdbcUtils.getConn(), sql, new ColumnListHandler<String>(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        list.forEach(v-> System.out.println(v));
    }

    /**
     * ：它是用于单数据。例如select max(age) from stu 表操作
     */
    @Test
    public void test02(){

        QueryRunner qr = new QueryRunner();
        String sql = "select max(age) from stu ";
        Object [] params = {};

        Integer maxAge = null;
        try {
            maxAge = qr.query(JdbcUtils.getConn(), sql, new ScalarHandler<Integer>(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(maxAge);

    }