Apache-DBUtils实现CRUD操作

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

QueryRunner测试插入数据

 public void testInsert() throws Exception {
        Connection conn = JDBCUtils.getConnection1();
        QueryRunner runner = new QueryRunner();
        String sql = "insert into customers(name,email,birth)values(?,?,?)";
        runner.update(conn,sql,"古德","gude@126.com","1987-08-08");
    }

QueryRunner测试插入数据

BeanHandler：是ResultSetHandler接口的实现类，用于封装表中的一条记录

public void testInsert1() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection1();
            QueryRunner runner = new QueryRunner();
            String sql = "select id,name,email,birth from customers where id = ?";
            BeanHandler<Customer> handle = new BeanHandler<>(Customer.class);
            Customer cust = runner.query(conn, sql, handle, 20);
            System.out.println(cust);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

BeanListHandler：是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合

public void testInsert2() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection1();
            QueryRunner runner = new QueryRunner();
            String sql = "select id,name,email,birth from customers where id < ?";
            BeanListHandler<Customer> handle = new BeanListHandler<>(Customer.class);
            List<Customer> list = runner.query(conn, sql, handle, 20);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

MapHandler：是ResultSetHandler接口的实现类，对应表中的一条记录，将字段及相应字段的值作为map中的key和value

public void testInsert3() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection1();
            QueryRunner runner = new QueryRunner();
            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.closeResource(conn,null);
        }
    }

MapListHandler：是ResultSetHandler接口的实现类，对应表中的多条记录，将字段及相应字段的值作为map中的key和value

public void testInsert4() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection1();
            QueryRunner runner = new QueryRunner();
            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.closeResource(conn,null);
        }
    }

用于查询特殊值，使用ScalarHandler

比如总数，平均数，最大数，最小数等

public void testquery() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection1();
            QueryRunner runner = new QueryRunner();
            String sql = "select count(*) from customers ";
            ScalarHandler handler = new ScalarHandler();
            Long count = (Long) runner.query(conn, sql, handler);
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }