JDBC dbutils的使用

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,封装了针对于数据库的增删改查操作

主要使用的类：
１．QueryRunner
２．ResultSetHandler的具体实现类

public class QueryRunnerTest {

    //测试插入
    @Test
    public void testInset(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="insert into customers(name,email,birth)values(?,?,?)";
            int insertCount  = runner.update(conn, sql, "蔡徐坤", "caixukun@126.com", "1997-09-08");
            System.out.println("添加了"+insertCount+"条记录");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,null);
        }
    }

    /*测试查询
    *
    * BeanHandler：是ResultSetHandler接口的实现类，用于封装表中的一条记录
    * */
    @Test
    public void testQuery(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
            Customer customer = runner.query(conn, sql, handler, 20);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,null);
        }

    }
    /*测试查询
     *
     * BeanListHandler：是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合
     * */
    @Test
    public void testQuery1() throws Exception {
        Connection conn=null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select name,email,birth from customers where id<?";
            BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
            List<Customer> list = runner.query(conn, sql, handler, 20);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,null);
        }

    }


    /*
    * MapHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录
    * 将字段及相应字段的值作为map中的key和value
    * */
    @Test
    public void testQuery2(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id=?";
            MapHandler handler= new MapHandler();
            Map<String, Object> map = runner.query(conn, sql, handler, 20);
            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,null);
        }
    }

    /*
    * MapListHandler:是ResultSetHandler接口的实现类，对应表中的多条记录
    * 将字段及相应字段的值作为map中的key和value。将这些map添加到List中
    * */
    @Test
    public void testQuery3(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id<?";
            MapListHandler handler= new MapListHandler();
            List<Map<String, Object>> list = runner.query(conn, sql, handler, 20);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,null);
        }
    }

    /*
    * ScalarHandler:用于查询特殊值
    * */
    @Test
    public void testQuery4(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select count(*) from customers";
            ScalarHandler handler = new ScalarHandler();
            Long count = (Long) runner.query(conn, sql, handler, 20);
            System.out.println(count);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,null);
        }
    }
    /*
     * ScalarHandler:用于查询特殊值
     * */
    @Test
    public void testQuery5(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select max(birth) from customers";
            ScalarHandler handler = new ScalarHandler();
            Date maxBirth = (Date) runner.query(conn, sql, handler);
            System.out.println(maxBirth);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,null);
        }
    }


    /*
    * 自定义ResultSetHandler的实现类
    * */
    @Test
    public void testQuery6(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql="select id,name,email,birth from customers where id = ? ";
            ResultSetHandler<Customer> handler = new ResultSetHandler<>() {

                @Override
                public Customer handle(ResultSet rs) throws SQLException {
                    if(rs.next()){
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        String email = rs.getString("email");
                        Date birth = rs.getDate("birth");
                        Customer customer = new Customer(id, name, email, birth);
                        return customer;
                    }
                    return null;
                }
            };
            Customer customer = runner.query(conn, sql, handler, 20);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(conn,null);
        }
    }
}