Commons DbUtils
概述
-
Apache Commons的一部分，用于封装JDBC代码，简化数据库操作。
-
Maven
<!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
简单使用
QueryRunner run = new QueryRunner(dataSource);
Object[] objArr = run.query("select * from user_info", new ArrayHandler());
System.out.println(Arrays.toString(objArr));
核心类
QueryRunner
- 线程安全
初始化
- 指定数据源
QueryRunner run = new QueryRunner(dataSource);
Object[] objArr = run.query("select * from user_info", new ArrayHandler());
- 不指定数据源
不指定数据源时，调用方法时，要指定Connection
QueryRunner run = new QueryRunner();
Object[] objArr = run.query(conn, "select * from user_info", new ArrayHandler());
query方法
- 带参数的查询
Object[] objArr = run.query("select * from user_info where userid = ?", new ArrayHandler(), "16465140643");
update方法
- 不带参数
int rows = run.update("update user_info set username = '123'");
- 带参数
int rows = run.update("update user_info set username = '12345' where userid = ?", "1646512dd7a");
insert方法
- Oracle驱动不支持
{@link ParameterMetaData#getParameterType(int) }，所以在QueryRunner的构造函数里设置pmdKnownBroken为true
QueryRunner run = new QueryRunner(dataSource, true);
ROWID rowid = run.insert("insert into USER_INFO(userId, userName) values (?, ?)", new ScalarHandler<ROWID>(),
suffix, "name" + suffix);
- insert方法 和 update方法的区别
- 参数不同
insert方法中需要带ResultSetHandler参数去解析主键，update方法不需要 - 返回值不同
insert方法返回的是主键，update方法返回影响的行数
batch方法
QueryRunner qr = new QueryRunner(dataSource, true);
String sql = "insert into USER_INFO(userId, userName) values (?, ?)";
Object[][] param = new Object[10][];
for (int i = 0; i < 10; i++) {
param[i] = new Object[2];
param[i][0] = "batchid-" + i * 11 + 0;
param[i][1] = "batchname-" + i * 11 + 1;
}
int[] batch = qr.batch(sql, param);
- 返回值应为受影响的行数，Oracle返回-2
ResultSetHandler
- 这个接口的实现将ResultSets转换成其他对象，用于查询后接受结果集
ScalarHandler
- 第一行的第一列 -> 一个对象
Number number = qr.query("select count(*) from USER_INFO", new ScalarHandler<Number>());
ArrayHandler
- 第一行 -> 数组
Object[] results = qr.query("select * from USER_INFO where userid = ?", new ArrayHandler(), "batchid-550");
ArrayListHandler
- 所有行 -> 数组的List
List<Object[]> results = qr.query("select * from USER_INFO ", new ArrayListHandler());
KeyedHandler
- 所有行 -> Map
- Map的键可指定为某一列，默认为第一列
Map<String, Map<String, Object>> map = qr.query("select * from USER_INFO ", new KeyedHandler<String>());
// 指定某一列为Map的键
Map<String, Map<String, Object>> map = qr.query("select * from USER_INFO ", new KeyedHandler<String>("username"));
KeyedHandler
- 所有行的某一列 -> List
- 列可指定，默认为第一列
List<String> list = qr.query("select username from USER_INFO ", new ColumnListHandler<String>());
// 指定列
List<String> list = qr.query("select * from USER_INFO ", new ColumnListHandler<String>("username"));
MapHandler
- 第一行 -> Map
- Map的键为列的名称，值为列的值
Map<String, Object> map = qr.query("select * from USER_INFO where userid = ?", new MapHandler(), "batchid-550");
MapListHandler
- 所有行 -> Map的List
- Map的键为列的名称，值为列的值
List<Map<String, Object>> list = qr.query("select * from USER_INFO ", new MapListHandler());
BeanHandler
- 第一行 -> JavaBean
UserInfo userInfo = qr.query("select * from USER_INFO where userid = ?",
new BeanHandler<UserInfo>(UserInfo.class), "batchid-550");
BeanListHandler
- 所有行 -> JavaBean的List
List<UserInfo> list = qr.query("select * from USER_INFO ", new BeanListHandler<UserInfo>(UserInfo.class));
BeanMapHandler
- 所有行 -> JavaBean的Map
- Map的键可指定，默认为第一列
Map<String, UserInfo> map = qr.query("select * from USER_INFO ",
new BeanMapHandler<String, UserInfo>(UserInfo.class));
// 指定Map的键
Map<String, UserInfo> map = qr.query("select * from USER_INFO ",
new BeanMapHandler<String, UserInfo>(UserInfo.class, "username"));
自定义ResultSetHandler
public class MyResultSetHandler implements ResultSetHandler<List<UserInfo>> {
@Override
public List<UserInfo> handle(ResultSet rs) throws SQLException {
List<UserInfo> list = new ArrayList<UserInfo>();
while (rs.next()) {
UserInfo u = new UserInfo();
u.setUserId(rs.getString("userid"));
u.setUserName(rs.getString("userid"));
list.add(u);
}
return list;
}
}
List<UserInfo> list = qr.query("select * from USER_INFO ", new MyResultSetHandler());
