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

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);
        }
    }
}