- commons-dbutils是Apache组织提供的一个开源JDBC工具类库,他是对JDBC的简单封装,能极大的简化jdbc编码的工作量。
API介绍:org.apache.commons.dbutils.QueryRunner
org.apache.commons.dbutils.ResultSetHandler
工具类:org.apache.commons.dbutils.DbUtils
使用DButils工具类进行删除操作:
//1.创建QueryRunner的实现类
QueryRunner queryRunner =new QueryRunner();
String sql="delete from customers" +"where id in(?,?)";//删除
Connection connection = JdbcTool.getConnection();
//2.使用其update方法
queryRunner.update(connection,sql,12,13);
JdbcTool.release(null,null,connection);
使用DButils工具类进行查询操作:
//1.创建QueryRunner的实现类
QueryRunner queryRunner =new QueryRunner();
class MyResultHandler implements ResultSetHandler{
@Override
public Object handle(ResultSet resultSet) throws Exception{
List<Customer> customers = new ArrayList<>();
while (resultSet.next()){
Integer id=resultSet.getInt(1);
String name =resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
Customer customer = new Customer(id,name,email,birth);
customers.add(customer);
}
return customers;
}
Connection connection = JdbcTool.getConnection();
String sql=" select id,name,email,birth" +"from customers";
Object obj = queryRunner.query(connection,sql,null);
JdbcTool.release(null,null,connection);
QueryRunner:
query(Connection conn,String sql,ResultSetHandler){
stmt=this.prepareStatement(conn,sql); //得到结果集对象
this.fillStatement(stmt,parent);
rs=this.wrap(stmt.executeQuery());
result =rsh.handle(rs);//调用传入的ResultSetHandler对象的handle方法,并且把前面得到的ResultSet对象作为参数传入
return result;
}
*QueryRunner的query方法的返回值取决于其ResultSetHandler参数的handle方法的返回值
1.BeanHandler:把结果集的第一条记录转为创建BeanHandler对象时传入的class参数对应的对象。
Connection connection =JdbcTool.getConnection();
String sql="select id,name,email,birth"+"from customers where id=?";
Customer customer = queryRunner.query(connection,sql,new BeanHandler(Customer.class),5);
System.out.println(customer);
JdbcTool.release(null,null,connection);
2.BeanListHandler:把结果集转为List,该List不为空,但是可能为空集合。若SQL语句能够查询到记录,List中存放创建.BeanListHandler传入的class对象对应的对象。
Connection connection =JdbcTool.getConnection();
String sql="select id,name,email,birth"+"from customers";
List<Customer> customer = queryRunner.query(connection,sql,new BeanListHandler(Customer.class));
System.out.println(customer);
JdbcTool.release(null,null,connection);
3.MapHandler:返回SQL对应的第一条记录对应的Map对象。键:SQL查询的列名,不是别名。 值:列的值
Connection connection =JdbcTool.getConnection();
String sql="select id,name,email,birth"+"from customers";
Map<String,Object> result = queryRunner.query(connection,sql,new MapHandler());
System.out.println(result);
JdbcTool.release(null,null,connection);
4..MapListHandler:将结果集转换为一个Map的List,Map对应查询的一条记录:键:SQL查询的列名,不是别名。 值:列的值
而MapListHandle返回的多条记录对应的Map的集合。
Connection connection =JdbcTool.getConnection();
String sql="select id,name,email,birth"+"from customers";
List<Map<String,Object>> result = queryRunner.query(connection,sql,new MapListHandler());
System.out.println(result);
JdbcTool.release(null,null,connection);
5.ScalarHandler:把结果集转换为一个数值(可以是任意基本数据类型,字符串,Date)返回
Connection connection =JdbcTool.getConnection();
String sql="select name"+"from customers where id=?";
Object result = queryRunner.query(connection,sql,new ScalarHandler(),5);
System.out.println(result);
JdbcTool.release(null,null,connection);