commons-dbutils的使用

1.官網

​​https://commons.apache.org/proper/commons-dbutils/​​

2.概述

1.coomons-dbutils是apache旗下的一個資料庫連接配接的工具
2.DbUtils 解決的最核心的問題就是結果集的映射，可以把 ResultSet 封裝成JavaBean。      

3.結果集映射

3.1.結果集映射概述

1.首先 DbUtils 提供了一個 QueryRunner 類，它對資料庫的增删改查的方法進行了封裝，那麼我們操作資料庫就可以直接使用它提供的方法。
2.在 QueryRunner 的構造函數裡面，我們又可以傳入一個資料源，比如在這裡我們Hikari，這樣我們就不需要再去寫各種建立和釋放連接配接的代碼了。
  queryRunner = new QueryRunner(dataSource);
3.那我們怎麼把結果集轉換成對象呢？比如實體類 Bean 或者 List 或者 Map？
  在DbUtils 裡面提供了一系列的支援泛型的 ResultSetHandler      

3.2.結果集映射樣例

​​https://gitee.com/gaoxinfu_admin/open-source/tree/master/commons-dbutils-master/commons-dbutils-demo​​

建立資料庫

略

CommonsDbutilsDemo測試類

public class CommonsDbutilsDemo {

    public static void main(String[] args) throws Exception {
        //初始化資料庫的連接配接池的操作
        HikariUtil.init();
        BlogDao.selectBlog(1);
//        BlogDao.selectList();
    }
}      

HikariUtil 使用Hikari進行資料庫連接配接池的統一管理

package com.gaoxinfu.demo.commons.dbutils;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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


public class HikariUtil {
    private static final String PROPERTY_PATH = "/hikari.properties";
    private static final Logger LOGGER = LoggerFactory.getLogger(HikariUtil.class);
    private static HikariDataSource dataSource;
    private static QueryRunner queryRunner;

    public static void init() {
        HikariConfig config = new HikariConfig(PROPERTY_PATH);
        dataSource = new HikariDataSource(config);
        queryRunner = new QueryRunner(dataSource);
    }

    public static QueryRunner getQueryRunner() {
        check();
        return queryRunner;
    }

    public static Connection getConnection() {
        check();
        try {
            Connection connection = dataSource.getConnection();
            return connection;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void close(Connection connection) {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static void check() {
        if (dataSource == null || queryRunner == null) {
            throw new RuntimeException("DataSource has not been init");
        }
    }
}      

具體表操作

package com.gaoxinfu.demo.commons.dbutils.dao;

import com.gaoxinfu.demo.commons.dbutils.HikariUtil;
import com.gaoxinfu.demo.commons.dbutils.dto.BlogDto;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

public class BlogDao {

    private static QueryRunner queryRunner;
    static {
        queryRunner = HikariUtil.getQueryRunner();
    }

    // 傳回單個對象，通過new BeanHandler<>(Class<?> clazz)來設定封裝
    public static void selectBlog(Integer bid) throws SQLException {
        String sql = "select * from blog where bid = ? ";
        Object[] params = new Object[]{bid};
        BlogDto blogDto = queryRunner.query(sql, new BeanHandler<>(BlogDto.class), params);
        System.out.println(blogDto);
    }

    //傳回清單，通過new BeanListHandler<>(Class<?> clazz)來設定List的泛型
    public static void selectList() throws SQLException {
        String sql = "select * from blog";
        List<BlogDto> list = queryRunner.query(sql, new BeanListHandler<>(BlogDto.class));
        //list.forEach(System.out::println);
    }
}      

QueryRunner.query 源碼分析

public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        Connection conn = this.prepareConnection();
        //見下面的方法
        return this.<T>query(conn, true, sql, rsh, params);
 }
 /**
     * Calls query after checking the parameters to ensure nothing is null.
     * @param conn The connection to use for the query call.
     * @param closeConn True if the connection should be closed, false otherwise.
     * @param sql The SQL statement to execute.
     * @param params An array of query replacement parameters.  Each row in
     * this array is one set of batch replacement values.
     * @return The results of the query.
     * @throws SQLException If there are database or parameter errors.
     */
    private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
            throws SQLException {
        if (conn == null) {
            throw new SQLException("Null connection");
        }

        if (sql == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null SQL statement");
        }

        if (rsh == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null ResultSetHandler");
        }

        PreparedStatement stmt = null;
        ResultSet rs = null;
        T result = null;

        try {
            //建立 根據給定的連接配接和SQL建立PreparedStatement，實際上是組裝SQL
            stmt = this.prepareStatement(conn, sql);
            //将Params填充上面組裝的SQL中預留的參數變量，進行變量替換
            this.fillStatement(stmt, params);
            //stmt.executeQuery() 執行SQL傳回查詢結果
            //this.wrap 使用包裝方法，對傳回結果進行包裝(當然這裡沒有做任何操作)
            rs = this.wrap(stmt.executeQuery());
            //對放回結果進行映射，将資料映射到DTO(entity)對象中，見下面的代碼
           result = rsh.handle(rs);

        } catch (SQLException e) {
            this.rethrow(e, sql, params);

        } finally {
            try {
                close(rs);
            } finally {
                close(stmt);
                if (closeConn) {
                    close(conn);
                }
            }
        }

        return result;
    }      

BeanHandler.handle(ResultSet rs)

@Override
    public T handle(ResultSet rs) throws SQLException {
        return rs.next() ? this.convert.toBean(rs, this.type) : null;
    }      

@Override
    public <T> T toBean(ResultSet rs, Class<? extends T> type) throws SQLException {
        return this.convert.toBean(rs, type);
    }      

public <T> T toBean(ResultSet rs, Class<? extends T> type) throws SQLException {
        T bean = this.newInstance(type);
        return this.populateBean(rs, bean);
    }
public <T> T populateBean(ResultSet rs, T bean) throws SQLException {
        PropertyDescriptor[] props = this.propertyDescriptors(bean.getClass());
        ResultSetMetaData rsmd = rs.getMetaData();
        int[] columnToProperty = this.mapColumnsToProperties(rsmd, props);

        return populateBean(rs, bean, props, columnToProperty);
    }
/**
     * This method populates a bean from the ResultSet based upon the underlying meta-data.
     * populateBean 渲染構造Bean對象
     * @param <T> The type of bean
     * @param rs The result set.
     * @param bean The bean to be populated.
     * @param props The property descriptors.
     * @param columnToProperty The column indices in the result set.
     * @return An initialized object.
     * @throws SQLException if a database error occurs.
     */
    private <T> T populateBean(ResultSet rs, T bean,
            PropertyDescriptor[] props, int[] columnToProperty)
            throws SQLException {

        for (int i = 1; i < columnToProperty.length; i++) {

            if (columnToProperty[i] == PROPERTY_NOT_FOUND) {
                continue;
            }

            PropertyDescriptor prop = props[columnToProperty[i]];
            Class<?> propType = prop.getPropertyType();

            Object value = null;
            if(propType != null) {

                //擷取某一列字段的值
                value = this.processColumn(rs, i, propType);

                if (value == null && propType.isPrimitive()) {
                    value = primitiveDefaults.get(propType);
                }
            }
            //将值設定到對象的某個字段上
            this.callSetter(bean, prop, value);
        }

        return bean;
    }