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);//使用该方法不需要处理异常
}
}