DBUtils：只是帮我们简化了CRUD 的代码。数据库连接的创建以及获取工作，不在其工作范围
一、DBUtils应用
①、DBUtils的jar文件的下载
②、Sql Server 、C3P0、DBUtils环境搭建(导入相应jar文件)
Sql Server ：sqljdbc42.jar(JDBC驱动)
C3P0：c3p0-0.9.5.5.jar，mchange-commons-java-0.2.19.jar，c3p0-config.xml文件
DBUtilts：commons-dbutils-1.7.jar
③、增删改
QueryRunner queryRunner = new QueryRunner(newComboPooledDataSource());//增加
queryRunner.update("insert into account values (null , ? , ? )", "aa" ,1000);//删除
queryRunner.update("delete from account where id = ?", 5);//更新
queryRunner.update("update account set money = ? where id = ?", 10000000 , 6);
④、查询
1、直接new ResultSetHandler接口的匿名实现类
public voidtestMain() {
ComboPooledDataSource dataSource= newComboPooledDataSource();
QueryRunner runner= newQueryRunner(dataSource);try{
Account result= runner.query("Select * from account where id =?", new ResultSetHandler() {
@Overridepublic Account handle(ResultSet rs) throwsSQLException {
Account account= newAccount();while(rs.next()) {
account.setId(rs.getInt("Id"));
account.setName(rs.getString("Name"));
account.setMoney(rs.getBigDecimal("Money"));
}returnaccount;
}
},1);
System.out.println(result.toString());
}catch(SQLException e) {
e.printStackTrace();
}
}
2、使用框架实现ResultSetHandler 接口的类
2.1、实现ResultSetHandler接口的类有：
BeanHandler, 查询到的单个数据封装成一个对象
BeanListHandler, 查询到的多个数据封装 成一个List
这两个使用频率最高
ArrayHandler, 查询到的单个数据封装成一个数组
ArrayListHandler, 查询到的多个数据封装成一个集合 ，集合里面的元素是数组
MapHandler, 查询到的单个数据封装成一个map
MapListHandler,查询到的多个数据封装成一个集合 ，集合里面的元素是map
ColumnListHandler
KeyedHandler
ScalarHandler(一个单元格--Select Count(1) from T_Name)
2.2、代码实现
2.2.1、查询单个对象
1 QueryRunner queryRunner = new QueryRunner(newComboPooledDataSource());2 //查询单个对象
3 Account account = queryRunner.query("select * from account where id = ?",4 new BeanHandler(Account.class), 8);
2.2.2、查询多个对象
1 QueryRunner queryRunner = new QueryRunner(newComboPooledDataSource());2 List list = queryRunner.query("select * from account ",3 new BeanListHandler(Account.class));
二、自定义DBUtils
Ⅰ、update方法
1 public intupdate(String sql, Object... args) {2 Connection connection = null;3 PreparedStatement ps = null;4 int result = 0;5 try{6 connection =JDBCUtil.getConnection();7 ps =connection.prepareStatement(sql);8
9 ParameterMetaData metaData =ps.getParameterMetaData();10 //根据参数个数，循环给参数赋值
11 for (int i = 0; i < metaData.getParameterCount(); i++) {12 ps.setObject(i + 1, args[i]);13 }14
15 result =ps.executeUpdate();16 } catch(SQLException e) {17
18 e.printStackTrace();19 } finally{20
21 }22 returnresult;23 }
Ⅱ、查询(query)
1、查询结果操作接口
1 //用于查询结果集，封装到指定类型对象中,并返回对象实例
2 public interface ResultSetHandler{3 T handle(ResultSet resultSet);4 }
2、通用查询
1 public T query(String sql,ResultSetHandlerhandler, Object... args) {2 Connection connection = null;3 PreparedStatement ps = null;4 ResultSet resultSet=null;5 try{6 connection =JDBCUtil.getConnection();7 ps =connection.prepareStatement(sql);8
9 ParameterMetaData metaData =ps.getParameterMetaData();10 //根据参数个数，循环给参数赋值
11 for (int i = 0; i < metaData.getParameterCount(); i++) {12 ps.setObject(i + 1, args[i]);13 }14 //查询结果集
15 resultSet =ps.executeQuery();16 //调用实现ResultSetHandler接口的类中handle方法
17 returnhandler.handle(resultSet);18 } catch(SQLException e) {19
20 e.printStackTrace();21 } finally{22
23 }24 return null;25 }
3、调用自定义通用查询
1 public voidtestMain() {2 Account account=CommonCRUDUtils.query("select * from account where id=?",new
3 ResultSetHandler() {4
5 @Override6 publicAccount handle(ResultSet resultSet) {7 Account account = newAccount();8 try{9 if(resultSet.next()) {10 account.setId(resultSet.getInt("Id"));11 account.setName(resultSet.getString("Name"));12 account.setMoney(resultSet.getBigDecimal("Money"));13 returnaccount;14 }15 } catch(SQLException e) {16
17 e.printStackTrace();18 }19 return null;20 }21
22 },3);23
24 System.out.println(account.toString());25 }
