Apache-DBUtils简介
- commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,它是对JDBC的简单封装,学习成本极低,并且使用dbutils能极大简化jdbc编码的工作量,同时也不会影响程序的性能。
主要API的使用
QueryRunner类
-
该类简单化了SQL查询,它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作,能够大大减少编码量。
-
QueryRunner类提供了两个构造器:
- 默认的构造器
- 需要一个 javax.sql.DataSource 来作参数的构造器
-
QueryRunner类的主要方法:
-
代码1:
//增加数据测试
@Test
public void testInsert() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = util.getConnection3();
String sql = "insert into customers(name,email,birth)values(?,?,?)";
runner.update(conn, sql, "基尼太美", "caixukun@qq.com", "1998-2-3");
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.closeResource(conn, null);
}
}
查询类:
ResultSetHandler接口及实现类
该接口用于处理 java.sql.ResultSet,将数据按要求转换为另一种形式。
ResultSetHandler 接口提供了一个单独的方法:Object handle (java.sql.ResultSet .rs)。
//查询测试
//测试查询:查询一条记录
// 使用ResultSetHandler的实现类:BeanHandler.用于封装表中的一条记录
@Test
public void testQuery1() {
Connection conn = null;
Customer query = null;
try {
QueryRunner runner = new QueryRunner();
conn = util.getConnection3();
String sql = "select * from customers where id = ?";
BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
query = runner.query(conn, sql, handler, 32);
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.closeResource(conn, null);
}
System.out.println(query);
}
//返回多条记录
//使用ResultSetHandler的实现类:BeanListHandler,用于封装表中的多条记录构成的集合。
@Test
public void testQuery2() {
Connection conn = null;
List<Customer> query = null;
try {
QueryRunner runner = new QueryRunner();
conn = util.getConnection3();
String sql = "select * from customers where id < ?";
BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
query = runner.query(conn, sql, handler, 32);
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.closeResource(conn, null);
}
query.forEach(System.out::println);
}
//使用ResultSetHandler的实现类:MapHandler,对应表中的一条记录,
// 将将字段及相应字段的值作为map中的key和value
@Test
public void testQuery3() {
Connection conn = null;
Map<String, Object> query = null;
try {
QueryRunner runner = new QueryRunner();
conn = util.getConnection3();
String sql = "select * from customers where id = ?";
MapHandler handler = new MapHandler();
query = runner.query(conn, sql, handler, 32);
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.closeResource(conn, null);
}
System.out.println(query);
}
//使用ResultSetHandler的实现类:MapHandler,对应表中的多条记录,
// 将字段及相应字段的值作为map中的key和value
@Test
public void testQuery4() {
Connection conn = null;
List<Map<String, Object>> query = null;
try {
QueryRunner runner = new QueryRunner();
conn = util.getConnection3();
String sql = "select * from customers where id < ?";
MapListHandler handler = new MapListHandler();
query = runner.query(conn, sql, handler, 32);
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.closeResource(conn, null);
}
query.forEach(System.out::println);
}
如何查询类似于最大的,最小的,平均的,总和,个数相关的数据,
使用ScalarHandler
// 使用ScalarHandler
//如何查询类似于最大的,最小的,平均的,总和,个数相关的数据,
// 求表中的记录数
@Test
public void testQuery5() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = util.getConnection3();
String sql = "select count(*) from customers ";
ScalarHandler handler = new ScalarHandler();
Object query = runner.query(conn, sql, handler);
System.out.println(query);
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.closeResource(conn, null);
}
}
// 求表中的最大生日
@Test
public void testQuery6() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = util.getConnection3();
String sql = "select MAX(birth) from customers ";
ScalarHandler handler = new ScalarHandler();
Object query = runner.query(conn, sql, handler);
System.out.println(query);
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.closeResource(conn, null);
}
}
自定义ResultSetHandler的实现类
@Test
public void testQuery7() {
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = util.getConnection3();
String sql = "select * from customers where id = ?";
ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {
@Override
public Customer handle(ResultSet resultSet) throws SQLException {
if (resultSet.next()) {
/*int id = (int) resultSet.getObject(1);
String name = (String) resultSet.getObject(2);
String email = (String) resultSet.getObject(3);
Date birth = (Date) resultSet.getObject(4);*/
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
Date birth = resultSet.getDate("birth");
Customer customer = new Customer(id, name, email, birth);
return customer;
}
return null;
}
};
Object query = runner.query(conn, sql, handler, 32);
System.out.println(query);
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.closeResource(conn, null);
}
}