Apache-DBUtils实现mysql数据库的CRUD操作

不念不忘少年蓝@ 2023-10-04 10:02 9阅读 0赞

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