DBUtils

前言

Mybatis、hibernate提供了便捷，快速、高效的交互环境
那为什么要写DBUtils呢？他看起来更像一个简化了JDBC操作的类库

DBUtils

我们都熟悉了Controller → Service → Mapper这种结构，能不能简化？
看段代码：

api

 @RequestMapping(value = "/projectLineList", method = RequestMethod.POST)
    @ResponseBody
    public Object projectLineList(ProjectManager projectManager, HttpServletRequest request)
    {
        return projectManager.projectLineList();
    }

Dao Service xml

public class ProjectManager
{
    private String projectId;

    private String projectName;

    private String projectNumber;

    private String company;

    private String companyId;

    private String principal;

    private String userId;

    private String status;

    private String createTime;

    private String lastmodifyTime;

    private String lineBaseId;

    private String lineName;

    private String lineCode;

    private String tagType;

    private String lineTypeName;

    private String displayName;


public Object projectLineList() {
        StringBuffer sql = new StringBuffer("select lr.projectId, l.lineBaseId,L.lineName,L.lineCode,r.tagType ,t.`name`   as lineTypeName "
                + "  from es_ts_project_line_relation  lr LEFT JOIN line_base_info l on l.lineBaseId = lr.lineBaseId "
                + " LEFT JOIN tb_tag_relation r on  r.subjectId = l.lineBaseId " + " LEFT JOIN tb_tag t on  r.tagid = t.id "
                + " WHERE t.tagstatus = 0 and t.tagType=6 ");
        sql.append(" and 1=1");
        List<Object> paramList = new ArrayList<Object>();
        if (this.projectId != null) {
            sql.append("  and  lr.projectid  like ? ");
            paramList.add("%" + this.projectId + "%");
        }
        if (paramList.size() > 0) {
            return DBUtils.queryList(ProjectManager.class, String.valueOf(sql), paramList.toArray());
        } else {
            return DBUtils.queryList(ProjectManager.class, String.valueOf(sql));
        }
    }
 }

看起来很像JDBC是么？

DBUtils.queryList(ProjectManager.class, String.valueOf(sql), paramList.toArray());

那我们来看看这个DBUtils

    @SuppressWarnings("unchecked")
    public static <T> List<T> queryList(Class<T> beanClass, String sql, Object... params) {
        Connection conn = Conn.get();
        try {
            ResultSetHandler<List<T>> handler = isPrimitive(beanClass) ? columnListHandler
                    : new BeanListHandler<T>(beanClass);
            long time1 = System.currentTimeMillis();
            List<T> list = queryRunner.query(conn, sql, handler, params);
            long time2 = System.currentTimeMillis();
            //logger.info("Time: {} SQL: {}\n List.size:{}\nParams:{}", time2 - time1,sql, list.size(), params);
            if (logger.isDebugEnabled()) {
                String logSql = String.format(sql.replaceAll("\\?", "%s"), params);
                logger.debug("Execute SQL: {}", logSql);
            }
            return list.isEmpty() ? null : list;
        } catch (SQLException e) {
            logger.error("SQL execute failed.", e);
            throw new RuntimeException(e);
        }
    }

Query

List<T> list = queryRunner.query(conn, sql, handler, params);

  public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        return this.<T>query(conn, false, sql, rsh, params);
    }

对于使用，以JDBC的角度去看很好理解，不外乎写好sql后传入参数，执行DBUtils的方法，很简洁的流程

使用

特别提一下 为什么说DBUtils是一个类库，因为它一共也就3个包

1.org.apache.commons.dbutils
2.org.apache.commons.dbutils.handlers
3.org.apache.commons.dbutils.wrappers

使用起来也比较方便
1.建立连接

import java.sql.SQLException;  
import java.sql.Connection;   

public class ConnectDb {  
    private static String driveClassName = "com.mysql.jdbc.Driver";  
    private static String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";   

    private static String user = "root";  
    private static String password = "root";  

    public static Connection Connect(){  
        Connection conn = null;  

        //加载驱动 
        try {  
            Class.forName(driveClassName);  
        } catch (ClassNotFoundException  e) {  
            System.out.println("load driver failed!");  
            e.printStackTrace();  
        }  

        //建立连接 
        try {  
            conn = DriverManager.getConnection(url, user, password);  
        } catch (SQLException e) {  
            System.out.println("connect failed!");  
            e.printStackTrace();  
        }         

        return conn;  
    }  
}  

2.bean

package Beans;  

public class UserBean {  
    private int id;   
    private String name;   
    private int age;  

    public int getId() {  
        return id;  
    }  
    public void setId(int id) {  
        this.id = id;  
    }  
    public String getName() {  
        return name;  
    }  
    public void setName(String name) {  
        this.name = name;  
    }  
    public int getAge() {  
        return age;  
    }  
    public void setAge(int age) {  
        this.age = age;  
    }  
}  

3.sql execution

import java.sql.Connection;  
import java.sql.SQLException;  
import java.util.List;  
import org.apache.commons.dbutils.DbUtils;  
import org.apache.commons.dbutils.QueryRunner;  
import org.apache.commons.dbutils.handlers.BeanListHandler;  
import Beans.UserBean;  

public class main {  

    public static void main(String[] args) throws SQLException {  
        insertTest();
        updateTest();
        queryTest();
        deleteTest();  
    }  

    static void insertTest() throws SQLException{  
        Connection conn = ConnectDb.Connect();  

        //创建SQL执行工具   
        QueryRunner sqlRunner = new QueryRunner();   

        //执行SQL插入   
        int number = sqlRunner.update(conn, "insert into user(name,age) values('testUser',22)");   
        System.out.println("成功插入" + number  + "条数据！");   

        //关闭数据库连接   
        DbUtils.closeQuietly(conn);       
    }   

    static void queryTest() throws SQLException{  
        Connection conn = ConnectDb.Connect();  

        //创建SQL执行工具   
        QueryRunner sqlRunner = new QueryRunner();   

        @SuppressWarnings("unchecked")  
        List<UserBean> list = (List<UserBean>) sqlRunner.query(conn, "select id,name,age from user", new BeanListHandler(UserBean.class));   
        //输出查询结果   
        for (UserBean user : list) {   
                System.out.println(user.getAge());   
        }   

        //关闭数据库连接   
        DbUtils.closeQuietly(conn);   
    }   

    static void updateTest() throws SQLException{  
        Connection conn = ConnectDb.Connect();  

        //创建SQL执行工具   
        QueryRunner sqlRunner = new QueryRunner();   
        //执行SQL插入   
        int n = sqlRunner.update(conn, "update user set name = 'testUser',age=11");   
        System.out.println("成功更新" + n + "条数据！");   

        //关闭数据库连接   
        DbUtils.closeQuietly(conn);   
    }   

    static void deleteTest() throws SQLException{  
        Connection conn = ConnectDb.Connect();  

        //创建SQL执行工具   
        QueryRunner sqlRunner = new QueryRunner();   
        //执行SQL插入   
        int number = sqlRunner.update(conn, "DELETE from user WHERE name='testUser';");   
        System.out.println("成功删除" + number  + "条数据！");   

        //关闭数据库连接   
        DbUtils.closeQuietly(conn);   
    }   
}