JDBC笔记 09：Apache-DBUtils实现CRUD操作

1. Apache-DBUtils简介

• commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。
• ResultSetHandler 接口用于处理数据库查询操作得到的结果集。不同结果集的情形，由其不同的子类来实现
• QueryRunner 提供数据库操作的一系列重载的 update() 和 query() 操作

2. 使用

增删改操作

public class Demo {
	@Test
	public void test() {
		Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection();
            String sql = "insert into customers (name,email,birth) values (?,?,?)";
            int count = runner.update(connection, sql, "费渡", "feidu@qq.com", "1997-12-08");
            System.out.println("添加了" + count + "条记录！");
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
        	JDBCUtils.closeResource(connection, null);
        }
	}
}

查询一条数据（BeanHandler）

public class Demo {
	@Test
	public void test() throws Exception {
		Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers where id = ?";
            BeanHandler<Customers> handler = new BeanHandler<>(Customers.class);
            Customers customer = runner.query(connection, sql, handler, 21);
            System.out.println(customer);
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
        	JDBCUtils.closeResource(connection, null);
        }
	}
}

查询多条数据（BeanListHandler）

public class Demo {
	@Test
	public void test() throws Exception {
		Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers";
            BeanListHandler<Customers> handler = new BeanListHandler<>(Customers.class);
            List<Customers> customersList = runner.query(connection, sql, handler);
            for (Customers customers : customersList) {
            	System.out.println(customers);
            }
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
        	JDBCUtils.closeResource(connection, null);
        }
	}
}

MapHandler

public class Demo {
	@Test
	public void test() throws Exception {
		Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers where id = ?";
            MapHandler handler = new MapHandler();
            Map<String, Object> map = runner.query(connection, sql, handler, 21);
            System.out.println(map);//输出为{name=费渡, birth=1997-12-08, id=21, email=feidu@qq.com}
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
        	JDBCUtils.closeResource(connection, null);
        }
	}
}

MapListHandler

public class Demo {
	@Test
	public void test() throws Exception {
		Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers";
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> mapList = runner.query(connection, sql, handler);
            for (Map<String, Object> objectMap : mapList) {
            	System.out.println(objectMap);
            }
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
        	JDBCUtils.closeResource(connection, null);
        }
	}
}

查询特殊值（ScalarHandler）

public class Demo {
	@Test
	public void test() throws Exception {
		Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection();
            String sql = "select count(*) from customers";
            ScalarHandler handler = new ScalarHandler();
            Long count = (Long) runner.query(connection, sql, handler);
            System.out.println(count);
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
        	JDBCUtils.closeResource(connection, null);
        }
	}
}

自定义 ResultSetHandler 的实现类

public class Demo {
	@Test
	public void test() throws Exception {
		Connection connection = null;
        try {
            QueryRunner runner = new QueryRunner();
            connection = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers where id = ?";
            Customers customers = runner.query(connection, sql, new ResultSetHandler<Customers>() {
            	//该方法的返回值即为查询结果
            	@Override
                public Customers handle(ResultSet resultSet) throws SQLException {
                	//在这里实现resultSet的处理
                    return null;
                }
            }, 21);
            System.out.println(customers);
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
        	JDBCUtils.closeResource(connection, null);
        }
	}
}

关闭资源

public class JDBCUtils {
	public static void closeResource(Connection connection, Statement statement, ResultSet resultSet) {
		try {
            DbUtils.close(connection);
            
            DbUtils.close(statement);
            DbUtils.close(resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //DbUtils.closeQuietly(connection);//使用该方法不需要处理异常
    }
}