dbUtils工具的使用

使用dbUtils需要倒入第三方包：commons-dbutils-1.4.jar(基本包)和commons-dbutils-ext.jar(扩展包)

public class DbUtilsDemo {

    @Test//普通方式---不用dbUtils的查询
    public void jdbcQuery() throws SQLException{
        Connection con = C3p0Pool.getConnection();
        String sql = "select * from student";
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        List<Student>students = new ArrayList<Student>();
        while(rs.next()){
            Student student = new Student();
            student.setId(rs.getString("id"));
            student.setSname(rs.getString("sname"));
            student.setSex(rs.getString("sex"));
            student.setAge(rs.getInt("age"));
            student.setCls(rs.getString("cls"));
            students.add(student);
        }

        System.out.println(students);
    }

    @Test
    public void dbUtilsQuery() throws SQLException{
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        String sql = "select * from student";
        List<Student>students = run.query(sql, new BeanListHandler<Student>(Student.class));
        System.out.println(students);
    }

    @Test
    public void dbUtilsQuery2() throws SQLException{
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        String sql = "select * from student";
        List<Map<String,Object>>students = run.query(sql, new MapListHandler());
        System.out.println(students);
    }

    @Test
    public void save() throws SQLException{
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        //String sql = "insert into person2(id,name,age,address) values('A001','张三',22,'湖南长沙')";
        String sql = "insert into person2(id,name,age,address) values(?,?,?,?)";
        run.update(sql,"A002","李四",23,"湖北武汉");

    }

    @Test
    public void saveTx() throws SQLException{
        QueryRunner run = new QueryRunner();
        Connection con = C3p0Pool.getConnection();
        try{
            con.setAutoCommit(false);
            String sql1 = "insert into person2(id,name,age,address) values(?,?,?,?)";
            run.update(con,sql1,"A005","钱七",27,"广东广州");
            String sql2 = "insert into person2(id,name,age,address) values('A004','赵六',25,'湖南宁乡')";
            run.update(con,sql2);
            System.out.println("事务已提交...");
        }catch (Exception e) {
            con.rollback();
            System.out.println("事务已回滚...");
        }finally{
            con.setAutoCommit(true);
            con.close();
        }
    }

    @Test//演示批处理功能
    public void batch() throws Exception{
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        for(int i=1;i<=300;i++){
            String sql = "insert into person2(id,name,age,address) values(?,?,?,?) ";
            String str= "000"+i;
            str = str.substring(str.length()-3, str.length());
            String id1 = "B"+ str;
            String id2 = "C"+ str;
            Object params[][] = new Object[][]{{id1,"Tom"+str,(i+10)%100,"湖南"+str},{id2,"Jack"+str,(i+10)%100,"湖北"+str}};
            run.batch(sql, params);
        }
    }

    @Test
    public void extQuery(){
        ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());
        List<Person>persons = run.query(Person.class);
        System.out.println(persons);
    }
    @Test
    public void extSave(){
        ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());
        Person p = new Person();
        p.setId("C301");
        p.setName("Jack301");
        p.setAge(21);
        p.setAddress("湖北301");
        run.update(p);
    }
}