QueryRunner的常用方法

package dbutils;

import my_jdbc_utils.jdbc_utils2.JdbcUtils2;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 导包：commons-dbutils-1.4.jar
 * QueryRunner的方法演示
 */
public class QueryRunnerDemo {
    //增删改操作
    @Test
    public void testUpdate() throws SQLException {
        //传入指定的线程池
//        QueryRunner qr = new QueryRunner(new ComboPooledDataSource());
        QueryRunner qr = new QueryRunner(JdbcUtils2.getDataSourceD());//同上
        String sql = "INSERT INTO table2 VALUES(?, ?)";
        Object[] params = {10001, "董小天"};
        qr.update(sql, params);
    }

    //增删改操作
    @Test
    public void testUpdate2() throws SQLException {
        //不使用线程池创建的Connection对象
        QueryRunner qr = new QueryRunner();

        String sql = "INSERT INTO table2 VALUES(?, ?)";
        Object[] params = {10002, "董小天二号"};
        //传入指定Connection对象
        Connection conn = JdbcUtils2.getConnection();
        qr.update(conn, sql, params);
    }

    //以下查询操作:： ResultSetHandler自定义转化类型
    @Test
    public void testResultSetHandler() throws SQLException {
        //传入指定的线程池
        QueryRunner qr = new QueryRunner(JdbcUtils2.getDataSourceD());
        String sql = "SELECT * FROM table2 WHERE id = ?";
        //创建ResultSetHandler接口的Student实现类
        ResultSetHandler<Student> rsh = new ResultSetHandler() {
            @Override
            public Student handle(ResultSet rs) throws SQLException {
                // 从结果集里面把数据得到并封装到对象里面
                rs.next();
                return new Student(rs.getInt(1), rs.getString(2));
            }
        };
        Student stu = qr.query(sql, rsh, 10002);
        System.out.println(stu);
    }

    //BeanHandler单行处理器！把结果集转换成Bean，该处理器需要Class参数，即Bean的类型；
    @Test
    public void testBeanHandler() throws SQLException {
        //传入指定的线程池
        QueryRunner qr = new QueryRunner(JdbcUtils2.getDataSourceD());

        String sql = "SELECT * FROM table2 WHERE id = ?";
        //使用BeanHandler要求 表中列名和Student属性名完全一致
        Student stu = qr.query(sql, new BeanHandler<>(Student.class), 10002);
        System.out.println(stu);
    }

    //BeanListHandler：多行处理器！把结果集转换成List<Bean>
    @Test
    public void testBeanListHandler() throws SQLException {
        //传入指定的线程池
        QueryRunner qr = new QueryRunner(JdbcUtils2.getDataSourceD());

        String sql = "SELECT * FROM table2";
        List<Student> studentList = qr.query(sql, new BeanListHandler<>(Student.class));
        System.out.println(studentList);
    }

    //MapHandler：单行处理器！把结果集转换成Map<String,Object>，其中列名为键！结果无序(HashMap)
    @Test
    public void testMapHandler() throws SQLException {
        //传入指定的线程池
        QueryRunner qr = new QueryRunner(JdbcUtils2.getDataSourceD());

        String sql = "SELECT * FROM table2 WHERE id = ?";
        Map<String, Object> stringObjectMap = qr.query(sql, new MapHandler(), 10001);
        /*可存入多个JavaBean 对应入座
        Book book = CommonUtils.toBean(map, Book.class);
        Category category = CommonUtils.toBean(map, Category.class);
            book.setCategory(category);*/
        System.out.println(stringObjectMap);
    }

    //MapListHandler：多行处理器！把结果集转换成List<Map<String,Object>>
    @Test
    public void testMapListHandler() throws SQLException {
        //传入指定的线程池
        QueryRunner qr = new QueryRunner(JdbcUtils2.getDataSourceD());

        String sql = "SELECT * FROM table2";
        List<Map<String, Object>> mapList = qr.query(sql, new MapListHandler());
        System.out.println(mapList);
    }

    //ScalarHandler：单行单列处理器！把结果集转换成Object。一般用于聚集查询，例如SELECT COUNT(*) FROM table2
    @Test
    public void testScalarHandler() throws SQLException {
        //传入指定的线程池
        QueryRunner qr = new QueryRunner(JdbcUtils2.getDataSourceD());

        String sql = "SELECT COUNT(*) FROM table2";
        //返回类型可能是: Integer、Long、BigInteger，所以用他们共同的父类Number接收，再转换为想要的类型
        Number number = (Number) qr.query(sql, new ScalarHandler());
        //将number转换为指定类型
        long l = number.longValue();
        System.out.println(l);
    }

    //batch：批处理
    @Test
    public void testbatch() throws SQLException {
        //传入指定的线程池
        QueryRunner qr = new QueryRunner(JdbcUtils2.getDataSourceD());
        String sql = "INSERT INTO table2 VALUES(?, ?)";
        //表示 要插入10行记录
        Object[][] params = new Object[10][];
        for (int i = 0; i < params.length; i++) {
            params[i] = new Object[]{i, "董小天" + i};
        }
        qr.batch(sql, params);
    }
}

Student

package dbutils;

public class Student {
    private int id;
    private String name;

    public Student() {

    }

    public Student(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}