JDBC -- Common - DBUtils

JDBC – Common - DBUtils

DBUtils 是Apache Commons组件的一员 对JDBC进行简单的封装

主要功能：用来操作数据库，简化JDBC的操作

主要的类和方法

QueryRunner 执行SQl语句的类

​ 创建QueryRunner

​ 构造器：QueryRunner（） 在事务中使用 — 后面学习事务再学习

​ 构造器：QueryRunner（连接池对象）

方法：

update（）：执行增删改

query（）：执行查询

步骤：

第一步 ： 初始文件配置和搭建— 配置文件、连接池、

第二步 写代码

 //添加
    @Test
    public void testAdd() throws SQLException {
        //SQl语句
        String sql = "insert into `tb_stu`(`sname`, `sage`, `sgender`) values(?, ?, ?)";
        //参数 --- 目的：为？赋值
        Object[] parmas = {"张三",20,"男"};
        //创建QueryRunner -- 发送SQL语句
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        //发送SQL语句
        int result = qr.update(sql, parmas);
        System.out.println(result);
    }


    //删除
    @Test
    public void testDel() throws SQLException {
        //SQl语句
        String sql = "delete from `tb_stu` where `sid`=?";
        //参数 --- 目的：为？赋值
        Object[] parmas = {7};
        //创建QueryRunner -- 发送SQL语句
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        //发送SQL语句
        int result = qr.update(sql, parmas);
        System.out.println(result);
    }

    //修改
    @Test
    public void testChg() throws SQLException {
        //SQl语句
        String sql = "update `tb_stu` set `sname`= ?, `sage`= ?, `sgender`= ? where `sid`= ?";
        //参数 --- 目的：为？赋值
        Object[] parmas = {"老肖",10,"男",5};
        //创建QueryRunner -- 发送SQL语句
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        //发送SQL语句
        int result = qr.update(sql, parmas);
        System.out.println(result);
    }

查询

• 单表查询

 //查询
    @Test
    public void testFindById() throws SQLException {
        String sql = "select * from `tb_stu` where sid = ?";
        Object[] parmas = {6};
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        Student student = qr.query(sql, new BeanHandler<Student>(Student.class), parmas);
        System.out.println(student);
    }

    @Test
    public void testFindAll() throws SQLException {
        String sql = "select * from `tb_stu`";
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        List<Student> list = qr.query(sql, new BeanListHandler<Student>(Student.class));
        list.stream().forEach(System.out::println);
    }

    @Test
    public void testFindByAge() throws SQLException{
        String sql = "select * from `tb_stu` where `sage` > ?";
        Object[] parmas = {10};
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        List<Student> studentList = qr.query(sql, new 			      BeanListHandler<Student>(Student.class), parmas);
        studentList.stream().forEach(System.out::println);
    }

• 多表查询

 //MapHandler和 MapListHandler  --- 多表查询
    @Test
    public void testEnameAndDnameById() throws SQLException {
        String sql = "select e.ename, d.dname from `emp` e, `dept` d where e.deptno = d.id and e.id = ?";
        Object[] parmas = {1};
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        Map<String, Object> map = qr.query(sql, new MapHandler(),parmas);
        System.out.println(map);
    }

    @Test
    public void testEnameAndDname() throws SQLException {
        String sql = "select e.ename, d.dname from `emp` e, `dept` d where e.deptno = d.id";
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        List<Map<String, Object>> maps = qr.query(sql, new MapListHandler());
       maps.stream().forEach(System.out::println);
    }

课后作业：将DBUtils 运用到之前的通讯录管理系统中