JAVA WEB基础- DBUtils

来源：互联网发布：手写文字图片制作软件编辑：程序博客网时间：2024/05/22 14:04

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);        }    }

阅读全文

0 0