7.DbUtils实现增删改查

1.概述

2.实现增删改操作

2.1 添加操作

//    测试插入语句
    @Test
    public void test1() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils2.getConnection3();
            String sql="insert into customers(name,email,birth) values(?,?,?)";
            runner.update(conn,sql,"ccc","ccc.com","2020-12-09");
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);//ps的连接封装在queryrunner里面，关闭也在里面
//            这里关闭资源只是范湖到数据库连接池并不是真正的关闭
        }
    }

2.2 删除操作

//    测试删除语句
    @Test
    public void test2() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils2.getConnection3();
            String sql="delete from customers where id=?";
            runner.update(conn,sql,48);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

2.3 更改操作

//    测试更改语句
    @Test
    public void test3(){
        Connection conn=null;
        try {
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils2.getConnection3();
            String sql="update customers set name=? where id=?";
            runner.update(conn,sql,"sda",52);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

3.实现查询操作

3.1 以对象的形式返回

//    测试返回一条查询语句
//    ResultSetHandler是一个接口，BeanHandler是一个具体实现类
//    用于封装表中的一条记录
    @Test
    public void test4(){
        Connection conn=null;
        try {
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils2.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            BeanHandler<Customers> handler =new BeanHandler<>(Customers.class);
            Customers cus=runner.query(conn,sql,handler,10);
            System.out.println(cus);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
//    返回多条查询语句
//    BeanListHandler是一个具体实现类,用于封装表中的多条记录
    @Test
    public void test5(){
        Connection conn=null;
        try {
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils2.getConnection3();
            String sql="select id,name,email,birth from customers where id<?";
            BeanListHandler<Customers> handler=new BeanListHandler<>(Customers.class);
            List<Customers> list=runner.query(conn,sql,handler,10);
            list.forEach(o->System.out.println(o));
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

3.2 以键值对的形式返回

//    MapHandler返回一个对象，将一个对象的具体属性以键值对来呈现
    @Test
    public void test6(){
        Connection conn=null;
        try {
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils2.getConnection3();
            String sql="select id,name,email,birth from customers where id<?";
            MapHandler handler=new MapHandler();
            Map<String ,Object>map=runner.query(conn,sql,handler,10);
            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
//    MapListHandler返回多个对象，将一个对象的具体属性以键值对来呈现，
//      多个对象存放在list集合里面
    @Test
    public void test7(){
        Connection conn=null;
        try {
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils2.getConnection3();
            String sql="select id,name,email,birth from customers where id<?";
            MapListHandler handler=new MapListHandler();
            List<Map<String ,Object> >list=runner.query(conn,sql,handler,10);
            list.forEach(o->System.out.println(o));
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

3.3 返回特殊值

//    查询表中的特殊值
//    ScalarHandler
    @Test
    public void test8(){
        Connection conn=null;
        try {
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils2.getConnection3();
            String sql="select count(*) from customers";
            ScalarHandler handler=new ScalarHandler();
            Long cnt=(Long)runner.query(conn,sql,handler);//查询返回Obj对象
            System.out.println(cnt);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }
    @Test
    public void test9(){
        Connection conn=null;
        try {
            QueryRunner runner=new QueryRunner();
            conn=JDBCUtils2.getConnection3();
            String sql="select max(birth) from customers";
            ScalarHandler handler=new ScalarHandler();
            Date d =(Date)runner.query(conn,sql,handler);//查询返回Obj对象
            System.out.println(d);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

3.4 自定义ResultSetHandle接口实现类

//    当没有自己想要的实现类是可以自己创建匿名实现类（Lambda表达式）
    @Test
    public void test10() throws Exception {
        Connection conn=JDBCUtils2.getConnection3();
        QueryRunner runner=new QueryRunner();
        String sql="select id,name,email,birth from customers where id=?";
        ResultSetHandler<Customers> handler= resultSet -> {
            if(resultSet.next()){
                int id=resultSet.getInt("id");
                String name=resultSet.getString("name");
                String email=resultSet.getString("email");
                Date birth=resultSet.getDate("birth");
                return new Customers(id,name,email,birth);
            }
            return null;
        };
//        ResultSetHandler<Customers> handler=new ResultSetHandler<Customers>() {
//            @Override
//            public Customers handle(ResultSet resultSet) throws SQLException {
//                if(resultSet.next()){
//                    int id=resultSet.getInt("id");
//                    String name=resultSet.getString("name");
//                    String email=resultSet.getString("email");
//                    Date birth=resultSet.getDate("birth");
//                    return new Customers(id,name,email,birth);
//                }
//                return null;
//            };
//        };
        Customers cus=runner.query(conn,sql,handler,6);
        System.out.println(cus);
    }

4.资源的关闭

    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs){
//        正常关闭需要处理异常
        try {
            DbUtils.close(conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
//        这种异常默认自己处理了
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }