DBUtils使用详解 #yyds干货盘点#

精选原创

梁云亮 2022-02-13 23:52:43 博主文章分类：JavaSE ©著作权

文章标签 sql 结果集 java 文章分类 Java 后端开发 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者梁云亮的原创作品，请联系作者获取转载授权，否则将追究法律责任

DBUtils

简介

DBUtils是Java编程中的数据库操作实用工具，小巧简单实用。DBUtils封装了对JDBC的操作，简化了JDBC操作，可以减少60%以上的代码。

DBUtils三个核心功能介绍

QueryRunner：核心类提供对sql语句操作的API
- QueryRunner(DataSourcr ds)：提供数据源（连接池），DbUtils底层自动维护连接connection
- update(String sql，Obj...params)：执行更新操作
- query(String sql，ResultSetHandler<T>rsh,Object...panrams)：执行查询操作
ResultSetHandler：结果集处理类，用于定义select操作后，怎样封装结果集
- ArrayHandler：把查询结果集中的第一行数据放到一个数组中。适合取1条记录。
- ArrayListHandler：将查询的结果的每一行数据都放到一个数组中，然后再将数组放到集合List中。适合取多条记录。
- BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中
- BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，然后再将这些对象存放到List里。
- MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
- MapListHandler：将查询的结果的每一行存入到一个map中，键为列名，值为各列值；然后再将map存入list中
- ColumnListHandler：将结果集中某一列的数据存放到List中。
- KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里(List<Map>)，再把这些map再存到一个map里，其key为指定的列。
- ScalarHandler:将结果集第一行的某一列放到某个对象中。//重点
DBUtils：工具类，定义了关闭资源与事务处理的方法。

示例：采用Druid和dbutils实现对Dept的CRUD

第一步：Dept.java：<a rel="nofollow" href="https://blog.csdn.net/lianghecai52171314/article/details/101469279">Dept源代码</a>

第二步：新建项目，加入Maven依赖：

<dependency>
  <groupId>commons-dbutils</groupId>
  <artifactId>commons-dbutils</artifactId>
  <version>1.7</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.17</version>
</dependency>

第二步：在resources目录下创建mysql.properties，代码如下：

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&user=root&password=&useUnicode=true&characterEncoding=UTF8&autoReconnect=true&failOverReadOnly=false
username=root
password=root


filters=stat
initialSize=2
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
maxPoolPreparedStatementPerConnectionSize=200

第三步：创建数据库连接工具类：

public class DBUtil {
    private static final ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    private static DataSource dataSource = null;

    private DBUtil() {
    }

    static {    //配置文件加载，只执行一次
        try (InputStream is = DBUtil.class.getResourceAsStream("/mysql.properties");) {
            Properties properties = new Properties();
            properties.load(is);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e1) {
            throw new RuntimeException("读取配置文件异常", e1);
        }
    }

    public static Connection getConnection() {    //获取连接
        Connection conn = null;
        try {
            conn = threadLocal.get();   //从当前线程获得 conn
            if (conn == null || conn.isClosed()) {
                conn = dataSource.getConnection();
                threadLocal.set(conn);
            }
        } catch (Exception e) {
            throw new RuntimeException("连接数据库异常", e);
        }
        return conn;
    }

    public static QueryRunner getQueryRunner() {
        return new QueryRunner(dataSource);
    }

    public static void release(Connection conn) {
        try { // 建议采用这种形式来释放资源，因为finally里面的一定会被释放
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

第四步：测试事务

public static void main(String[] args) {
    Connection conn = getConnection();
    try {
        conn.setAutoCommit(false); // 设置事务提交为手动
        String sql = "insert into tb_dept (dname,loc) values(?,?)";
        QueryRunner queryRunner = getQueryRunner();
        queryRunner.update(conn, sql, "aa", "aaaaa");
        System.out.println(3 / 0);
        queryRunner.update(sql, "bb", "bbbbbbbbbb");
        conn.commit();
    } catch (SQLException e) {
        e.printStackTrace();
        try {
            conn.rollback();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    } finally {
        release(conn);
    }
}

第五步：DeptDaoImpl.java

public class DeptDaoImpl {
    private QueryRunner queryRunner = DBUtil.getQueryRunner();

    public int insertDept(Dept dept) throws SQLException {
        String sql = "insert into tb_dept (deptno, dname, loc) values (?, ?, ?)";
        return queryRunner.update(sql, dept.getDeptno(), dept.getDname(), dept.getLoc());
    }

    public int batchInsertDept(List<Dept> deptList) throws SQLException {
        Object[][] params = new Object[deptList.size()][3];

        for (int i = 0; i < params.length; i++) {
            Dept dept = deptList.get(i);
            params[i][0] = dept.getDeptno();
            params[i][1] = dept.getDname();
            params[i][2] = dept.getLoc();
        }

        StringBuilder wenHao = new StringBuilder();
        for (int i = 0; i < params[0].length; i++) {
            wenHao.append("?,");
        }
        String sql = "insert into tb_dept values(" + wenHao.deleteCharAt(wenHao.length() - 1) + ")";

        queryRunner.batch(sql, params);
        return 1;  // 如果不抛出异常，就返回1，表示删除成功
    }

    public int deleteDeptByDeptno(Byte deptno) throws SQLException {
        String sql = "delete from tb_dept where deptno = ?";
        return queryRunner.update(sql, deptno);
    }

    public int deleteDeptByCondition(Dept dept) throws SQLException {
        List<Object> paramValueList = new ArrayList<>();
        StringBuffer paramBuf = new StringBuffer("1=1 ");

        if (dept.getDeptno() != null) {
            paramBuf.append("and deptno= ? ");
            paramValueList.add(dept.getDeptno());
        }
        if (dept.getDname() != null) {
            paramBuf.append("and dname= ? ");
            paramValueList.add(dept.getDname());
        }
        if (dept.getLoc() != null) {
            paramBuf.append("and loc= ? ");
            paramValueList.add(dept.getLoc());
        }

        String sql = "delete from tb_dept where " + paramBuf.substring(0, paramBuf.length() - 3);
        return queryRunner.update(sql, paramValueList.toArray());
    }

    public int batchDeleteDeptByDeptnos(String deptnos) throws SQLException {
        String[] split = deptnos.split(",");
        Object[][] params = new Object[1][];

        StringBuilder wenHao = new StringBuilder();
        for (int i = 0; i < split.length; i++) {
            wenHao.append("?,");
        }
        params[0] = split;

        String sql = "delete from tb_dept where deptno in (" + wenHao.deleteCharAt(wenHao.length() - 1) + ")";

        queryRunner.batch(sql, params);
        return 1;  // 如果不抛出异常，就返回1，表示删除成功
    }

    public int updateDept(Dept dept) throws SQLException {
        String sql = "update tb_dept set dname= ? ,loc= ? where deptno = ?";
        return queryRunner.update(sql, dept.getDname(), dept.getLoc(), dept.getDeptno());
    }

    public long selectCount() throws SQLException {
        String sql = "select count(*) from tb_dept";
        // ScalarHandler：将查询的结果的第一行的某一列放到一个对象中；精确定位到某个值
        Long query = queryRunner.query(sql, new ScalarHandler<Long>());
        return query.intValue();
    }

    public long selectCountByCondition(Dept dept) throws SQLException {
        List<Object> paramValueList = new ArrayList<>();
        StringBuffer paramBuf = new StringBuffer("1=1 ");

        if (dept.getDeptno() != null) {
            paramBuf.append("and deptno= ? ");
            paramValueList.add(dept.getDeptno());
        }
        if (dept.getDname() != null) {
            paramBuf.append("and dname= ? ");
            paramValueList.add(dept.getDname());
        }
        if (dept.getLoc() != null) {
            paramBuf.append("and loc= ? ");
            paramValueList.add(dept.getLoc());
        }

        String sql = "select count(*) from tb_dept where " + paramBuf.substring(0, paramBuf.length() - 3);
        Long query = queryRunner.query(sql, new ScalarHandler<Long>(), paramValueList.toArray());
        return query.intValue();
    }

    public Dept selectDeptByDeptno(Byte deptno) throws SQLException {
        String sql = "select deptno as deptno, dname as dname, loc as loc from tb_dept where  deptno = ?";
        return queryRunner.query(sql, new BeanHandler<>(Dept.class), deptno);
    }

    public List<Dept> selectAllDept() throws SQLException {
        String sql = "select deptno as deptno, dname as dname, loc as loc from tb_dept";
        return queryRunner.query(sql, new BeanListHandler<>(Dept.class));
    }

    public List<Dept> selectDeptByCondition(Dept dept) throws SQLException {
        List<Object> paramValueList = new ArrayList<>();
        StringBuffer paramBuf = new StringBuffer("1=1 ");

        if (dept.getDeptno() != null) {
            paramBuf.append("and deptno= ? ");
            paramValueList.add(dept.getDeptno());
        }
        if (dept.getDname() != null) {
            paramBuf.append("and dname= ? ");
            paramValueList.add(dept.getDname());
        }
        if (dept.getLoc() != null) {
            paramBuf.append("and loc= ? ");
            paramValueList.add(dept.getLoc());
        }

        String sql = "select deptno as deptno, dname as dname, loc as loc  from tb_dept where " + paramBuf.substring(0, paramBuf.length() - 3);
        return queryRunner.query(sql, new BeanListHandler<>(Dept.class), paramValueList.toArray());
    }

    public List<Dept> selectDeptWithPagination(int page, int rows) throws SQLException {
        String sql = "select deptno as deptno, dname as dname, loc as loc from tb_dept limit ?, ?";
        return queryRunner.query(sql, new BeanListHandler<>(Dept.class), (page - 1) * rows, rows);
    }

    public List<Dept> selectDeptWithPaginationByCondition(int page, int rows, Dept dept) throws SQLException {
        List<Object> paramValueList = new ArrayList<>();
        StringBuffer paramBuf = new StringBuffer("1=1 ");

        if (dept.getDeptno() != null) {
            paramBuf.append("and deptno= ? ");
            paramValueList.add(dept.getDeptno());
        }
        if (dept.getDname() != null) {
            paramBuf.append("and dname= ? ");
            paramValueList.add(dept.getDname());
        }
        if (dept.getLoc() != null) {
            paramBuf.append("and loc= ? ");
            paramValueList.add(dept.getLoc());
        }

        String sql = "select deptno as deptno, dname as dname, loc as loc  from tb_dept where " + paramBuf.substring(0, paramBuf.length() - 3) + " limit ?, ?";

        paramValueList.add((page - 1) * rows);
        paramValueList.add(rows);
        return queryRunner.query(sql, new BeanListHandler<>(Dept.class), paramValueList.toArray());
    }
}

动态SQL与静态SQL

SQL 语句从编译和运行的角度可以分为静态 SQL和动态 SQL，所谓SQL的动态和静态，是指SQL语句在何时被编译和执行。

这两种 SQL 在使用方式、运行机制和性能表现等方面各有特点 :

静态 SQL：静态 SQL 语句一般用于嵌入式 SQL 应用中，在程序运行前，SQL 语句在编程时候必须是确定好的，例如 SQL 语句中涉及的列名和表名必须是存在的。静态 SQL 语句的编译是在应用程序运行前进行的，编译的结果会存储在数据库内部。而后程序运行时，数据库将直接执行编译好的 SQL 语句，降低运行时的开销。
动态 SQL：动态 SQL 语句是在应用程序运行时被编译和执行的，例如，使用 DB2 的交互式工具 CLP 访问数据库时，用户输入的 SQL 语句是不确定的，因此 SQL 语句只能被动态地编译。动态 SQL 的应用较多，常见的 CLI 和 JDBC 应用程序都使用动态 SQL。

静态sql的存取路径是在运行前就确定好的，而动态sql的存取路径是在运行时动态生成的。

注意：在SQL中如果某些参数没有确定，如"select * from t1 where c1>? and c2<?"，这种语句是静态SQL，不是动态SQL，虽然个别参数的值不知道，但整个SQL的结构已经确定，数据库是可以将它编译的，在执行阶段只需将个别参数的值补充进来即可。