DBUtils

来源：互联网发布：lbp7100cn 网络设置编辑：程序博客网时间：2024/05/21 21:47

前言

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

阅读全文

1 0