1.背景
DBUtils是JDBC的简化开发工具包。对应的包：commons-dbutils-1.X.jar.
DBUtils三个核心功能：
*1.QueryRunner中提供对SQL语句操作的API。
*2.ResultSetHandler接口，用于定义select操作后，怎样封装结果集。
*3.DButils类，是一个工具类，定义了关闭资源与事务处理的方法。
2.Queryrunner核心类
2.1提供数据源
构造方法
*QueryRunner（DataSource）创建核心类，并提供数据源，内部自己维护connection。
普通方法
*Update（String sql，Object。。。Parameters）执行DML（增删改）语句。
*query(String sql,ResultSetHandle,Object …)执行DQL语句，并将查询结果封装到对象中。
2.2提供链接
*QueryRunner（）创建核心类，没有提供数据源，在进行具体操作的时，需要手动提供connection。
*update
*Query
3.QueryRunner实现增删改操作
public class DBUtilsDemo_01 {
@Test
public void demo01() throws SQLException{
// 1.核心类
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
// 2.执行Update方法
int i = queryRunner.update("insert into product(pid,name,price,category_id) values(?,?,?,?)","k001","johnson",1000,"k001");
System.out.println(i);
}
@Test
public void demo02() throws SQLException{
// DML- Add
// 将SQL和实际参数进行抽取
// 1.核心类
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
// 2.执行Update方法
String sql = "insert into product(pid,name,price,category_id) values(?,?,?,?)";
Object[] params = {"k003","tommylily",33000,"k003"};
int i = queryRunner.update(sql,params);
System.out.println(i);
}
@Test
public void demo03() throws SQLException{
// DML- update
// 将SQL和实际参数进行抽取
// 1.核心类
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
// 2.执行Update方法
String sql = "update product set name = ?,price =?,category_id = ? where pid = ?";
Object[] params = {"lilytommy",90,"k002","k001"};
int i = queryRunner.update(sql,params);
System.out.println(i);
}
@Test
public void demo04() throws SQLException{
// DML- delete
// 将SQL和实际参数进行抽取
// 1.核心类
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
// 2.执行Update方法
String sql = "delete from product where pid = ?";
Object[] params = {"k001"};
int i = queryRunner.update(sql,params);
System.out.println(i);
}
}4.QueryRunner查询方法
*4.1 结构
*query（String sql,ResultSetHandler rsh, Object … parameters）,
用来完成表数据的查询操作。
*4.2 ResultSetHandler
BeanHandler，BeanListHandler
* 4.3 JavaBean
常用于封装数据。
/*
* JavaBean 规范
* 1.必须提供私有字段
* private String pname;
* 2.必须为私有字段提供setter和getter方法
* 3.提供无参数构造
* 4.实现序列化接口
* implement Serializable
*
* */
public class Product implements Serializable{
/**
*
*/
private static final long serialVersionUID = 1L;
private String pid;
private String name;
private String price;
private String category_id;
public String getPid() {
return pid;
}
public void setPid(String pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPrice() {
return price;
}
public void setPrice(String price) {
this.price = price;
}
public String getCategory_id() {
return category_id;
}
public void setCategory_id(String category_id) {
this.category_id = category_id;
}
public static long getSerialversionuid() {
return serialVersionUID;
}
@Override
public String toString() {
return "Product [pid=" + pid + ", name=" + name + ", price=" + price + ", category_id=" + category_id + "]";
}
public Product() {
super();
// TODO Auto-generated constructor stub
}
public Product(String pid, String name, String price, String category_id) {
super();
this.pid = pid;
this.name = name;
this.price = price;
this.category_id = category_id;
}
}*4.4 BeanHandler 处理结果集
@Test
public void demo01() throws Exception{
// 通过id查询详情，将查询结果封装到JavaBean product
// 1.核心类
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());// 2.SQL语句
String sql = “select * from product where pid = ?”;
// 3、实现参数
Object[] paras = {“k002”};
// 4.查询并封装
Product product = queryRunner.query(sql, new BeanHandler(Product.class), paras);
System.out.println(product);
}*4.5 queryRunner 查询操作
@Test
public void demo01() throws Exception{
// 通过id查询详情，将查询结果封装到JavaBean product
// 1.核心类
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
// 2.SQL语句
String sql = "select * from product where pid = ?";
// 3、实现参数
Object[] paras = {"k002"};
// 4.查询并封装
Product product = queryRunner.query(sql, new BeanHandler<Product>(Product.class), paras);
System.out.println(product);
}
@Test
public void demo02() throws Exception{
// 查询所有，将每一条记录封装到一个JavaBean，然后将JavaBean添加到List中，最后返回List，BeanListHandler.
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product";
Object[] params = {};
List<Product> list = queryRunner.query(sql, new BeanListHandler<Product>(Product.class), params);
for (Product product : list) {
System.out.println(product);
}
}
@Test
// scalarHandler : 用于处理聚合函数执行结果（一行一列）
// *查询总记录数
public void demo03() throws Exception{
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select count(*) from product";
long object = queryRunner.query(sql, new ScalarHandler<Long>());
System.out.println(object);
}
@Test
// MapHandler : 将查询到的一条记录，封装到Map中，map.key/,ap.value
// *多表操作。将数据转换成JSON。
public void demo04() throws Exception{
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product where pid = ?";
Object[] objects = {"k002"};
Map<String, Object> map = queryRunner.query(sql, new MapHandler(), objects);
System.out.println(map);
}
@Test
// MapListHandler : 查询所有数据，将每一条记录封装到Map中，然后将Map添加到List中，最后返回List。
// *多表操作，将数据转换成Json。
public void demo05() throws SQLException{
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product ";
List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler());
for (Map<String, Object> map : list) {
System.out.println(map);
}
}
@Test
// ArrayHandler : 查询每一条记录，将数据封装到数组中。
public void demo06() throws Exception{
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product where pid = ?";
Object[] objects = {"k002"};
Object[] arr = queryRunner.query(sql,new ArrayHandler(), objects);
System.out.println(arr);
System.out.println(Arrays.toString(arr));
}
@Test
// ArrayListHandler : 查询所有记录，将数据封装到数组中。
public void demo07() throws Exception{
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product";
List<Object[]> list= queryRunner.query(sql,new ArrayListHandler());
for (Object[] objects : list) {
System.out.println(Arrays.toString(objects));
}
}
@Test
// KeyedHandler : 查询所有记录，将查询结果封装到Map中。
// *map.key为指定字段名称对应的值。
// *map.value为当前整条记录所有的值，数据为Map<key,value>。
// *类型Map<String,Map<String,Object>>
public void demo08() throws Exception{
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product";
Map<String, Map<String, Object>> map = queryRunner.query(sql, new KeyedHandler<String>("name"));
for (Map.Entry<String, Map<String, Object>> entry : map.entrySet() ) {
System.out.println(entry.getKey());
System.out.println(entry.getValue());
}
}
@Test
// ColumListHandler : 查询指定一列数据。
public void demo09() throws Exception{
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product";
List<String> list = queryRunner.query(sql, new ColumnListHandler<>("name"));
for (String string : list) {
System.out.println(string);
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
