一、简介
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,它是对 JDBC 的简单封装,学习成本极低,使用 dbutils 可以简化 jdbc 编码的工作量,同时也不会影响程序的性能。
使用 DBUtils 需要导入 commons-dbutils-1.7.jar。DBUtils 有三个核心的组件:
- QueryRunner:该类提供了 DML 和 DQL 的 API。
- ResultSetHandler:该接口定义如何封装结果集。
- DbUtils:一个简单的工具类,简化了关闭资源和事务处理,可以简化JDBC操作的模板代码。
二、QueryRunner
1.构造方法
QueryRunner(); // 需要指定连接
QueryRunner(DataSource ds); //从数据源中获取连接
2.常用 API
<T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params);
<T> T query(Connection conn, String sql, ResultSetHandler<T> rsh);
<T> T query(String sql, ResultSetHandler<T> rsh, Object... params);
<T> T query(String sql, ResultSetHandler<T> rsh);
int update(Connection conn, String sql, Object... params);
int update(Connection conn, String sql);
int update(String sql, Object... params);
int update(String sql);
int[] batch(Connection conn, String sql, Object[][] params);
int[] batch(String sql, Object[][] params);
3.实现
@Test
public void testInsert() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection conn = getConnection();
String sql = "insert into t_user(username,password,balance) values(?,?,?)";
int rows = qr.update(conn, sql, "Thomas", "123456", 1000);
Assert.assertEquals(1, rows);
}
@Test
public void testUpdate() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "update t_user set balance=balance-100 where id=?";
int rows = qr.update(sql, 1);
Assert.assertEquals(1, rows);
}
@Test
public void testDelete() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "delete from t_user where id=?";
int rows = qr.update(sql, 1);
Assert.assertEquals(1, rows);
}
@Test
public void testBatch() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "insert into t_user(username,password,balance) values(?,?,?)";
Object[][] params = new Object[10000][3];
for (int i = 0; i < 10000; i++) {
params[i] = new Object[]{"user" + i, "123456", i};
}
int[] rows = qr.batch(sql, params);
}
@Test
public void testQuery() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "select * from t_user where id=?";
User user = qr.query(sql, new BeanHandler<>(User.class), 1);
System.out.println(user);
}
private Connection getConnection() {
Properties info = new Properties();
try (Reader reader = new FileReader("db.properties")) {
info.load(reader);
} catch (IOException e) {
e.printStackTrace();
}
String url = info.getProperty("url");
String user = info.getProperty("user");
String password = info.getProperty("password");
Connection conn = null;
try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
private DataSource getDataSource() {
Properties info = new Properties();
DataSource ds = null;
try (Reader reader = new FileReader("druid.properties")) {
info.load(reader);
ds = DruidDataSourceFactory.createDataSource(info);
} catch (Exception e) {
e.printStackTrace();
}
return ds;
}
三、ResultSetHandler
该接口用于处理 ResultSet,将查询返回的 ResultSet 按要求转换为另一种形式。该接口的定义如下:
public interface ResultSetHandler<T> {
T handle(ResultSet rs) throws SQLException;
}
1.该接口的实现类:
ArrayHandler: 把结果集中的第一行数据转成对象数组。
ArrayListHandler:把结果集中的每一行数据都转成一个数组,再存放到List中。
BeanHandler:将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler:将结果集中的每一行数据都封装到一个对应的JavaBean实例中,存放到List里。
ColumnListHandler: 将结果集中一列的数据存放到List 中 。
KeyedHandler(name):将结果集中的每一行数据都封装到一个Map里,再把这些map再存到一个map里,其key为指定的key。
MapHandler:将结果集中的第一行数据封装到一个Map里,key是列名,value就是对应的值。
MapListHandler:将结果集中的每一行数据都封装到一个Map里,然后再存放到List。
2.实现
@Test
public void testArrayHandler() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "select * from t_user";
Object[] arr = qr.query(sql, new ArrayHandler());
System.out.println(Arrays.toString(arr));
}
@Test
public void testArrayListHandler() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "select * from t_user";
List<Object[]> list = qr.query(sql, new ArrayListHandler());
for (Object[] arr : list) {
System.out.println(Arrays.toString(arr));
}
}
@Test
public void testBeanHandler() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "select id, username as name, password, balance from t_user";
User user = qr.query(sql, new BeanHandler<>(User.class));
System.out.println(user);
}
@Test
public void testBeanListHandler() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "select id, username as name, password, balance from t_user";
List<User> list = qr.query(sql, new BeanListHandler<>(User.class));
for(User user : list) {
System.out.println(user);
}
}
@Test
public void testColumnListHandler() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "select id, username as name, password, balance from t_user";
// List<Integer> list = qr.query(sql, new ColumnListHandler<>());
// List<String> list = qr.query(sql, new ColumnListHandler<>(2));
List<String> list = qr.query(sql, new ColumnListHandler<>("name"));
for (String name : list) {
System.out.println(name);
}
}
@Test
public void testMapHandler() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "select id, username as name, password, balance from t_user";
Map<String, Object> map = qr.query(sql, new MapHandler());
System.out.println(map);
}
@Test
public void testKeyedHandler() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "select id, username as name, password, balance from t_user";
// Map<Integer, Map<String, Object>> map = qr.query(sql, new KeyedHandler<>());
// Map<String, Map<String, Object>> map = qr.query(sql, new KeyedHandler<>(2));
Map<String, Map<String, Object>> map = qr.query(sql, new KeyedHandler<>("name"));
Set<Map.Entry<String, Map<String, Object>>> entries = map.entrySet();
for (Map.Entry<String, Map<String, Object>> e : entries) {
String key = e.getKey();
Map<String, Object> value = e.getValue();
System.out.println(key + "=" + value);
}
}
@Test
public void testMapListHandler() throws SQLException {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "select id, username as name, password, balance from t_user";
List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
for(Map<String, Object> map : list) {
System.out.println(map);
}
}
private DataSource getDataSource() {
Properties info = new Properties();
DataSource ds = null;
try (Reader reader = new FileReader("druid.properties")) {
info.load(reader);
ds = DruidDataSourceFactory.createDataSource(info);
} catch (Exception e) {
e.printStackTrace();
}
return ds;
}
使用 BeanHandler 和 BeanListHandler 需要注意以下几点:
- 对应的JavaBean必须提供无参构造方法。
- JavaBean的属性名应该和返回结果的字段名相同。
- JavaBean中必须提供公共的Setter方法。
四、DbUtils
提供如关闭连接、装载JDBC驱动程序等常规工作的工具类,里面的所有方法都是静态的。 主要有以下API:
void close(Connection conn); //可以避免空指针异常void close(ResultSet rs);
void close(Statement stmt);
void closeQuietly(Connection conn); //不需要判空, 处理异常
void closeQuietly(ResultSet rs); void closeQuietly(Statement stmt);
void closeQuietly(Connection conn, Statement stmt, ResultSet rs);
void commitAndClose(Connection conn); // 提交事务并且关闭连接
void commitAndCloseQuietly(Connection conn);
boolean loadDriver(String driverClassName)
#个人学习记录,如发现有错误之处,欢迎与我交流