dbutils

dbutils

  是apache组织的一个工具类,jdbc框架,更方便使用

  使用步骤:

    1.导入jar包(commons-dbutils-1.4.jar)

    2.创建一个queryrunner类

      queryrunner作用:操作sql语句

        构造方法:

          new QueryRunner(DataSource ds)

    3.编写sql

    4.执行sql

      query(...) :执行r操作

      update(...):执行cud操作

dbutils
package com.company.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DataSourceUtils {
    private static ComboPooledDataSource ds = new ComboPooledDataSource();

    /**
     * 获取数据源
     * @return 连接池
     */
    public static DataSource getDataSource(){
        return ds;
    }

    /**
     * 获取连接
     * @return 连接
     * @throws SQLException
     */
    public static Connection getCon() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 释放资源
     *
     * @param conn
     *            连接
     * @param st
     *            语句执行者
     * @param rs
     *            结果集
     */
    public static void closeResource(Connection conn, Statement st, ResultSet rs) {
        closeResultSet(rs);
        closeStatement(st);
        closeConn(conn);
    }

    /**
     * 释放连接
     *
     * @param conn
     *            连接
     */
    public static void closeConn(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }

    }

    /**
     * 释放语句执行者
     *
     * @param st
     *            语句执行者
     */
    public static void closeStatement(Statement st) {
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            st = null;
        }

    }

    /**
     * 释放结果集
     *
     * @param rs
     *            结果集
     */
    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }

    }
}
工具类
dbutils
public class CURDDemo {
    @Test
    public void insert() throws SQLException {
        //1.创建queryrunner类
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        //编写sql
        String sql = "insert into category values(?,?)";
        //执行sql
        qr.update(sql,"c030","电器");
    }
}
测试

  核心类或接口:

    QueryRunner类:

      作用:操作sql语句

      构造器:

        new QueryRunner(DataSource ds)

      注意:

        底层帮我们创建连接,创建语句执行者,释放资源

      常用方法:

        query(...)

        update(...)

  

    Dbutils:释放资源,控制事务

      closeQuietly(conn):内部处理了异常

      commitAndClose(Connection conn)提交事务并释放连接

      ...

 

    ResultSetHandler:封装结果集,接口

      9个实现类:

 

(了解)ArrayHandler: 将查询结果的第一条记录封装成数组,返回
(了解)ArrayListHandler:将查询结果的每一条记录封装成数组,将每一个数组放入list中返回
★★BeanHandler:将查询结果的第一条记录封装成指定的bean对象,返回
★★BeanListHandler:将查询结果的每一条记录封装成指定的bean对象,将每一个bean对象放入list中 返回.
(了解)ColumnListHandler:将查询结果的指定一列放入list中返回
(了解)MapHandler:将查询结果的第一条记录封装成map,字段名作为key,值为value 返回
★MapListHandler:将查询结果的每一条记录封装map集合,将每一个map集合放入list中返回
★ScalarHandler:针对于聚合函数 例如:count(*) 返回的是一个Long值

dbutils
public class Category {
    private String cid;
    private String cname;
    public String getCid() {
        return cid;
    }
    public void setCid(String cid) {
        this.cid = cid;
    }
    public String getCname() {
        return cname;
    }
    public void setCname(String cname) {
        this.cname = cname;
    }
    @Override
    public String toString() {
        return "Category [cid=" + cid + ", cname=" + cname + "]";
    }
}
Category类
dbutils
package com.company.utils;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import com.company.domain.Category;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

public class ResultHandleDemo {
    @Test
    public void arrayHandler() throws SQLException{
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());

        String sql="select * from category";

        Object[] query = qr.query(sql, new ArrayHandler());
        System.out.println(Arrays.toString(query));
    }

    @Test
    public void arrayListHandler() throws SQLException{
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());

        String sql="select * from category";
        List<Object[]> list = qr.query(sql, new ArrayListHandler());
        for (Object[] obj : list) {
            System.out.println(Arrays.toString(obj));
        }
    }


    @Test
    public void beanHandler() throws SQLException{
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());

        String sql="select * from category";

        Category bean = qr.query(sql, new BeanHandler<>(Category.class));

        System.out.println(bean);
    }

    @Test
    public void beanListHandler() throws SQLException{
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());

        String sql="select * from category";

        List<Category> list = qr.query(sql, new BeanListHandler<>(Category.class));

        for (Category bean : list) {

            System.out.println(bean);
        }
    }

    @Test
    public void mapHandler() throws SQLException{
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());

        String sql="select * from category";

        Map<String, Object> map = qr.query(sql, new MapHandler());
        System.out.println(map);
    }

    @Test
    public void mapListHandler() throws SQLException{
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());

        String sql="select * from category";

        List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
        for (Map<String, Object> map : list) {
            System.out.println(map);
        }
    }

    @Test
    public void scalarHandler() throws SQLException{
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());

        String sql="select count(*) from category";

        Object obj = qr.query(sql, new ScalarHandler());
        System.out.println(obj.getClass().getName());
    }

}
测试类

dbutils

上一篇:【*狂魔】抛弃IIS,打造个性的Web Server - WebAPI/Lua/MVC(附带源码)


下一篇:[C#参考]细说进程、应用程序域与上下文之间的关系