一、DBUtils介绍 apache
什么是dbutils，它的作用
DBUtils是java编程中的数据库操作实用工具，小巧简单实用。
用前导包！！！DBUtils包！！！
二、DBUtils的三个核心对象
> QueryRunner类
>ResultSetHandler接口
> DBUtils类
QueryRunner类
QueryRunner中提供对sql语句操作的API.
它主要有三个方法
query() 用于执行select
update() 用于执行insert update delete
batch() 批处理
ResultSetHandler接口
用于定义select操作后，怎样封装结果集.
DbUtils类
它就是一个工具类,定义了关闭资源与事务处理的方法
三、QueryRunner对象
1.1 构造函数：
> new QueryRunner(); 它的事务可以手动控制。
也就是说此对象调用的方法（如：query、update、batrch）参数中要有Connection对象。
> new QueryRunner(DataSource ds); 它的事务是自动控制的。一个sql一个事务。
此对象调用的方法（如：query、update、batrch）参数中无需Connection对象。
1.2 使用案例
private void doQueryAll() {
//ArrayHandler
// QueryRunner queryRunner = new QueryRunner(UtilC3P0.getDataSource());
// try {
// Object[] objects = queryRunner.query("select * from user", newArrayHandler());
// for(Object object : objects) {
// System.out.println("object = " + object);
//
// }
// } catch(SQLException e) {
// e.printStackTrace();
// }
//--------------------------------------------------------------------------------------------------------
//ArrayListHandler
// QueryRunner queryRunner = new QueryRunner(UtilC3P0.getDataSource());
// try {
// List<Object[]> list = queryRunner.query("select * fromuser", new ArrayListHandler());
// for(Object[] objects : list) {
// for (Object object : objects) {
// System.out.print(object+" ");
// }
// System.out.println();
//
// }
// } catch (SQLException e) {
// e.printStackTrace();
// }
//--------------------------------------------------------------------------------------------------------
//ColumnListHandler
// QueryRunner queryRunner = new QueryRunner(UtilC3P0.getDataSource());
//
// try {
// List<Object> list = queryRunner.query("select * fromuser", new ColumnListHandler(1));
// for(Object o : list) {
// System.out.println(o);
//
// }
// } catch(SQLException e) {
// e.printStackTrace();
// }
//--------------------------------------------------------------------------------------------------------
//KeyedHandler
//大map的key，是一行数据的一个标识，这个标识取当前行数据的某一列值
//小map就是当前行的数据 ，以键值对形式封装
// QueryRunner queryRunner = new QueryRunner(UtilC3P0.getDataSource());
// try {
// Map<Object, Map<String, Object>> map =queryRunner.query("select * from user", new KeyedHandler(1));
// Set<Object> objects = map.keySet();
// for(Object object : objects) {
// System.out.println("key "+object);
// Map<String, Object> stringObjectMap = map.get(object);
// System.out.println("value: "+stringObjectMap);
// }
//
// } catch(SQLException e) {
// e.printStackTrace();
// }
//--------------------------------------------------------------------------------------------------------
//常用1-----ScalarHandler查询某行某列数据
// QueryRunner queryRunner = new QueryRunner(UtilC3P0.getDataSource());
// try {
// Object o = queryRunner.query("select money from user", newScalarHandler(1));
// System.out.println(o);
// } catch(SQLException e) {
// e.printStackTrace();
// }
//--------------------------------------------------------------------------------------------------------
//常用2-----BeanHandler 查询一行数据(一个对象)
// QueryRunner queryRunner = new QueryRunner(UtilC3P0.getDataSource());
// try {
// Useruser = queryRunner.query("select * from user whereusername='dahuang1'", new BeanHandler<User>(User.class));
// System.out.println(user);
// } catch(SQLException e) {
// e.printStackTrace();
// }
//--------------------------------------------------------------------------------------------------------
//常用3-----BeanListHandler 查询多行数据(一个对象集合)
QueryRunnerqueryRunner = new QueryRunner(UtilC3P0.getDataSource());
try {
List<User> users = queryRunner.query("select * from userwhere money>?", new BeanListHandler<User>(User.class),50);
System.out.println(users);
} catch(SQLException e) {
e.printStackTrace();
}
}
//更新操作
private voiddoUpdate() {
QueryRunnerqueryRunner = new QueryRunner(UtilC3P0.getDataSource());
try {
queryRunner.update("update user set money=? where username=?", 99999, "dahuang1");
} catch(SQLException e) {
e.printStackTrace();
}
}
//删除操作
private voiddoDelete1() {
QueryRunnerqueryRunner = new QueryRunner(UtilC3P0.getDataSource());
try {
queryRunner.update("delete from user where username=?","hehedada");
} catch(SQLException e) {
e.printStackTrace();
}
}
//插入操作
private voiddoInsert() {
// QueryRunner queryRunner=new QueryRunner(UtilC3P0.getDataSource());
// try {
// queryRunner.update("insert into user values(?,?,?,?)","hehedada","123456","nan",123.45);
// } catch(SQLException e) {
// e.printStackTrace();
// }
QueryRunnerqueryRunner = new QueryRunner();
Connectionconnection = UtilC3P0.openConn();
try {
queryRunner.update(connection, "insert into uservalues(?,?,?,?)", "hehedada22", "123456","nan", 123.45);
} catch(SQLException e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch(SQLException e) {
e.printStackTrace();
}
}
}
1.3 批处理操作---batch
QueryRunner queryRunner=newQueryRunner(UtilC3P0.getDataSource());
Object[][] objects=new Object[100][];
for (int i = 0; i < objects.length;i++) {
objects[i]=new Object[]{"大黄"+i,"123456"+i};
}
try {
queryRunner.batch("INSERT INTO user(username,userpwd) VALUES(?,?)",objects);
} catch (SQLException e) {
e.printStackTrace();
}
四、ResultSetHandler接口
ResultSetHandler下的所有结果处理器
1. ArrayHandler:适合取1条记录。把该条记录的每列值封装到一个数组中Object[]
QueryRunnerqr = new QueryRunner(C3P0Util.getDataSource());
Object[]arr = qr.query("select * fromusers", new ArrayHandler());
for(Object o : arr) {
System.out.println(o);
}
2.ArrayListHandler:适合取多条记录。把每条记录的每列值封装到一个数组中Object[]，把数组封装到一个List中
QueryRunnerqr = new QueryRunner(C3P0Util.getDataSource());
List<Object[]>query = qr.query("select * from users", new ArrayListHandler());
for(Object[] os : query) {
for(Object o : os) {
System.out.println(o);
}
System.out.println("--------------");
}
3.ColumnListHandler:取某一列的数据。封装到List中。
QueryRunnerqr = new QueryRunner(C3P0Util.getDataSource());
List<Object>list = qr.query("select username,password from users", newColumnListHandler(1));
for(Object o : list) {
System.out.println(o);
}
4.KeyedHandler:取多条记录，每一条记录封装到一个Map中，再把这个Map封装到另外一个Map中，key为指定的字段值。
QueryRunnerqr = new QueryRunner(C3P0Util.getDataSource());
//大Map的key是表中的某列数据，小Map中的key是表的列名，所以大Map的key是Object类型，小Map的key是String类型
Map<Object,Map<String,Object>>map = qr.query("select * from users", new KeyedHandler(1));
for(Map.Entry<Object, Map<String,Object>> m : map.entrySet()) {
System.out.println(m.getKey());//大Map中key值就是id列的值
for(Map.Entry<String, Object> mm : m.getValue().entrySet()) {
System.out.println(mm.getKey()+" "+mm.getValue());//取出小Map中的列名和列值
}
System.out.println("---------------------");
}
5.MapHandler:适合取1条记录。把当前记录的列名和列值放到一个Map中
QueryRunner qr = newQueryRunner(C3P0Util.getDataSource());
Map<String,Object>map = qr.query("select * from users where id=?", newMapHandler(),20);
for(Map.Entry<String, Object> m : map.entrySet()) {
System.out.println(m.getKey()+" "+m.getValue());
}
6.MapListHandler:适合取多条记录。把每条记录封装到一个Map中，再把Map封装到List中
QueryRunner qr = newQueryRunner(C3P0Util.getDataSource());
List<Map<String,Object>>list = qr.query("select * from users", new MapListHandler());
for(Map<String, Object> map : list) {
for(Map.Entry<String, Object> m : map.entrySet()) {
System.out.println(m.getKey()+" "+m.getValue());
}
System.out.println("---------------");
}
7.ScalarHandler:适合取单行单列数据（常用）
QueryRunner qr = newQueryRunner(C3P0Util.getDataSource());
Objecto = qr.query("select count(*) from users", new ScalarHandler(1));
System.out.println(o.getClass().getName());
8.BeanHandler 返回我们所需要的实体（常用）
QueryRunnerqr = new QueryRunner(C3P0Util.getDataSource());
Useruser = qr.query("select * from users where id=?", newBeanHandler<User>(User.class),1);
System.out.println(user);
9.BeanListHandler 返回一组实体（常用）
QueryRunnerqr = new QueryRunner(C3P0Util.getDataSource());
List<User>list = qr.query("select * from users where id=?", newBeanListHandler<User>(User.class),1);
System.out.println(list.size());