DBUtils之QueryRunner的增删改查以及批量增删改查的使用

拼搏现实的明天。 2023-09-24 14:49 89阅读 0赞

package test;
     
    import bean.Student;
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.BeanHandler;
    import org.apache.commons.dbutils.handlers.BeanListHandler;
    import org.apache.commons.dbutils.handlers.ScalarHandler;
    import org.junit.Test;
    import utils.JdbcUtils;
     
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
     
    /**
     * @author qgh
     * @create 2021/3/4 16:44
     */
    public class QueryRunnerTest {
        //使用DButils操作数据库
        private QueryRunner queryRunner = new QueryRunner();
        @Test
        public void insert(){
    //        1 连接  2 sql语句  3 参数
            Connection connection = JdbcUtils.getConnection();
    //        String sql = "insert into student values('2','张三',19,'个人信息')";
            Student s = new Student(2,"张三",19,"个人信息");
            String sql = "insert into student values(?,?,?,?)";
            try {
                //增删改调用update方法
                int i = queryRunner.update(connection, sql, s.getId(), s.getName(), s.getAge(), s.getInfo());
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.close(connection);
            }
        }
        @Test
        public void insertBatch(){
    //        1 连接  2 sql语句  3 参数
            Connection connection = JdbcUtils.getConnection();
            ArrayList<Student> students = new ArrayList<>();
            students.add(new Student(2,"张三",19,"个人信息"));
            students.add(new Student(3,"lisi",19,"个人信息"));
            students.add(new Student(4,"lisi",18,"个人信息"));
            String sql = "insert into student values(?,?,?,?)";
            Object[][] objects = new Object[students.size()][];
            for (int i = 0; i < students.size(); i++) {
                Student student = students.get(i);
                objects[i] = new Object[4];
                objects[i][0] = student.getId();
                objects[i][1] = student.getName();
                objects[i][2] = student.getAge();
                objects[i][3] = student.getInfo();
            }
            try {
                //batch批量插入
                queryRunner.batch(connection,sql,objects);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.close(connection);
            }
        }
        @Test
        public void updateBatch(){
    //        1 连接  2 sql语句  3 参数
            Connection connection = JdbcUtils.getConnection();
            ArrayList<Student> students = new ArrayList<>();
            students.add(new Student(2,"张三1",19,"个人信息"));
            students.add(new Student(3,"lisi1",19,"个人信息"));
            students.add(new Student(4,"lisi1",18,"个人信息"));
            String sql = "update  student set name = ?,age=?,info=? where id =?";
            Object[][] objects = new Object[students.size()][];
            for (int i = 0; i < students.size(); i++) {
                Student student = students.get(i);
                objects[i] = new Object[4];
                objects[i][0] = student.getName();
                objects[i][1] = student.getAge();
                objects[i][2] = student.getInfo();
                objects[i][3] = student.getId();
            }
            try {
                //batch批量插入
                queryRunner.batch(connection,sql,objects);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.close(connection);
            }
        }
        @Test
        public void deleteBatch(){
    //        1 连接  2 sql语句  3 参数
            Connection connection = JdbcUtils.getConnection();
            String sql = "delete from student where id =?";
            Object[][] objectss = new Object[2][];
     
            objectss[0] = new Object[1];
            objectss[0][0]=3;
            objectss[1] = new Object[1];
            objectss[1][0]=4;
            try {
                //batch批量插入
                queryRunner.batch(connection,sql,objectss);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.close(connection);
            }
        }
        @Test
        public void delete(){
            Connection connection = JdbcUtils.getConnection();
            String sql ="delete from student where id =?";
            try {
                queryRunner.update(connection,sql,2);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.close(connection);
            }
        }
        @Test
        public void update(){
            Connection connection = JdbcUtils.getConnection();
            String sql ="update student set name =? where id =?";
            try {
                Student s = queryForBean(2);
                s.setName("张飞");
                queryRunner.update(connection,sql, s.getName(),s.getId());
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.close(connection);
            }
        }
        @Test
        public void queryList(){
            //查询集合
    //        1 连接  2 sql语句 3结果集  4 参数
            Connection connection = JdbcUtils.getConnection();
            String sql = "select * from student";
            try {
                //查询调用query方法  BeanListHandler
                List<Student> sl = queryRunner.query(connection, sql, new BeanListHandler<Student>(Student.class));
                for (Student student : sl) {
                    System.out.print(student.getId());
                    System.out.print(student.getAge());
                    System.out.print(student.getName());
                    System.out.print(student.getInfo());
                    System.out.println();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.close(connection);
            }
        }
        public Student queryForBean(Integer id) throws SQLException {
            //查询集合
    //        1 连接  2 sql语句 3结果集  4 参数
            Connection connection = JdbcUtils.getConnection();
            String sql = "select * from student where id =?";
            return queryRunner.query(connection, sql, new BeanHandler<Student>(Student.class),id);
        }
        @Test
        public void queryForOne(){
            //查询集合
    //        1 连接  2 sql语句 3结果集  4 参数
            Connection connection = JdbcUtils.getConnection();
            String sql = "select * from student where id =?";
            try {
                //查询调用query方法  BeanHandler
                Student student = queryRunner.query(connection, sql, new BeanHandler<Student>(Student.class),1);
                    System.out.print(student.getId());
                    System.out.print(student.getAge());
                    System.out.print(student.getName());
                    System.out.print(student.getInfo());
                    System.out.println();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.close(connection);
            }
        }
        @Test
        public void queryForSingleValue(){
            //查询集合
    //        1 连接  2 sql语句 3结果集
            Connection connection = JdbcUtils.getConnection();
            String sql = "select count(*) from student";
            try {
                //查询调用query方法 ScalarHandler
                Object query = queryRunner.query(connection, sql, new ScalarHandler());
                System.out.println("班级中有"+query+"个学生");
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtils.close(connection);
            }
        }
    }

使用DBUtils目的是为了简化jdbc的连接，短短两三行代码就可以总结jdbc里面的加载数据、连接数据、编译、编写sql、[遍历][Link 1]等问题。  
当然在使用之前我们必须要将DBUtils依赖包给导入

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5YWJ5aS05piv5Lya5Y-Y5by655qE_size_20_color_FFFFFF_t_70_g_se_x_16][]

第一、使用QueryRunner数据的增删改操作（QueryRunner是由DBUtils包提供的一个工具类，需要引入 commons-dbutils 依赖。它与JdbcTemplate功能类似，该类简单化了SQL查询，与ResultSetHandler协同工作将使编码量大为减少。）  
![22ba631131d24afdaf6c485f75ed31fe.png][]

![dd8b31217b1642bca10271a54e9459bd.png][]

![eea019b142af4259adb8e2db8c638e0b.png][]

2、使用QueryRunner一条或者多条数据及数据个数及最大等等等。

![df0d318a91b0414f982eae8cb1b5c87e.png][]

![711da7ba77b74ae5b0835ee7b95970c3.png][] ![2fa854635aac4288ac1b317575865b95.png][]

![11629c57ccbc4801ae23e177316540a8.png][]

[Link 1]: https://so.csdn.net/so/search?q=%E9%81%8D%E5%8E%86&spm=1001.2101.3001.7020
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5YWJ5aS05piv5Lya5Y-Y5by655qE_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/20210927150422388.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5YWJ5aS05piv5Lya5Y-Y5by655qE,size_20,color_FFFFFF,t_70,g_se,x_16
[22ba631131d24afdaf6c485f75ed31fe.png]: https://img-blog.csdnimg.cn/22ba631131d24afdaf6c485f75ed31fe.png
[dd8b31217b1642bca10271a54e9459bd.png]: https://img-blog.csdnimg.cn/dd8b31217b1642bca10271a54e9459bd.png
[eea019b142af4259adb8e2db8c638e0b.png]: https://img-blog.csdnimg.cn/eea019b142af4259adb8e2db8c638e0b.png
[df0d318a91b0414f982eae8cb1b5c87e.png]: https://img-blog.csdnimg.cn/df0d318a91b0414f982eae8cb1b5c87e.png
[711da7ba77b74ae5b0835ee7b95970c3.png]: https://img-blog.csdnimg.cn/711da7ba77b74ae5b0835ee7b95970c3.png
[2fa854635aac4288ac1b317575865b95.png]: https://img-blog.csdnimg.cn/2fa854635aac4288ac1b317575865b95.png
[11629c57ccbc4801ae23e177316540a8.png]: https://img-blog.csdnimg.cn/11629c57ccbc4801ae23e177316540a8.png