DBUtils学习----QueryRunner类

QueryRunner

使用可插拔的策略执行SQL查询，并处理 ResultSet结果集

package org.apache.commons.dbutils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

/**
 * 使用可插拔的策略执行SQL查询，并处理 ResultSet
 * 这个类是线程安全的
 */
public class QueryRunner extends AbstractQueryRunner {

    /**
     * QueryRunner 类的构造函数
     */
    public QueryRunner() {
        super();
    }

    /**
     * 用于控制是否使用 ParameterMetaData 的 QueryRunner 的构造函数
     * pmdKnownBroken：有些数据库驱动不支持 java.sql.ParameterMetaData#getParameterType(int)
     * 如果 pmdKnownBroken设置为 true, 我们不去尝试; 如果设置为 false, 我们去尝试,如果测试不能使用，我们就不再去使用它
     */
    public QueryRunner(boolean pmdKnownBroken) {
        super(pmdKnownBroken);
    }

    /**
     * 使用数据源 DataSource 的 QueryRunner 的构造函数
     * 不带有 Connection 参数的方法将会从 DataSource 中获取
     */
    public QueryRunner(DataSource ds) {
        super(ds);
    }

    /**
     * QueryRunner 的构造函数
     */
    public QueryRunner(DataSource ds, boolean pmdKnownBroken) {
        super(ds, pmdKnownBroken);
    }

    /**
     * 批处理（一批INSERT、UPDATE、DELETE操作）
     * conn：用于执行 SQL 操作的 Connection 对象
     * Connection 由调用者负责关闭
     * sql：SQL语句
     * params：一组查询替换参数，该数组中的每一行都是一组批量替换值
     * 返回每个语句更新的行数组成的int数组
     * 数据库访问出错则抛出 SQLException 异常
     */
    public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException {
        return this.batch(conn, false, sql, params);
    }

    /**
     * 批处理操作
     * Connection 从 构造函数中的 DataSource 参数中获取
     * 该 Connection 必须处于自动提交模式，否则更新将不会被保存
     */
    public int[] batch(String sql, Object[][] params) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.batch(conn, true, sql, params);
    }

    /**
     * 同上
     */
    private int[] batch(Connection conn, boolean closeConn, String sql, 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 (params == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
        }

        PreparedStatement stmt = null;
        int[] rows = null;
        try {
            stmt = this.prepareStatement(conn, sql);

            for (int i = 0; i < params.length; i++) {
                this.fillStatement(stmt, params[i]);
                stmt.addBatch();
            }
            rows = stmt.executeBatch();

        } catch (SQLException e) {
            this.rethrow(e, sql, (Object[])params);
        } finally {
            close(stmt);
            if (closeConn) {
                close(conn);
            }
        }

        return rows;
    }

    /**
     * 执行一个带有替换参数的SQL SELECT查询
     * 调用者负责关闭连接
     * T：处理器返回对象的类型
     * conn：执行查询的 Connection 对象
     * sql：执行的查询语句
     * rsh：将ResultSet 转换为其他对象的处理器
     * params：可变参数列表，用于替换sql语句中的占位符
     * 由 ResultSetHandler 负责返回相应对象
     * 数据库访问出错则抛出 SQLException 异常
     */
    public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        return this.<T>query(conn, false, sql, rsh, params);
    }

    /**
     * 同上，只是不带有可变参数列表
     */
    public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
        return this.<T>query(conn, false, sql, rsh, (Object[]) null);
    }

    /**
     * 同上，只是不带有 Connection 参数，它会从构造函数的 DataSource 参数中获取
     */
    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);
    }

    /**
     * 同上
     */
    public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.<T>query(conn, true, sql, rsh, (Object[]) null);
    }

    /**
     * 在检查参数后调用查询，以确保没有任何参数值为空。
     * closeConn：True：该方法替我们关闭；false：调用者负责处理
     */
    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 {
            //获得 PreparedStatement 对象
            stmt = this.prepareStatement(conn, sql);
            //填充参数
            this.fillStatement(stmt, params);
            //获得ResultSet
            rs = this.wrap(stmt.executeQuery());
            //将 ResultSet 转换为指定对象
            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;
    }

    /**
     * 执行 SQL INSERT, UPDATE, 或者 DELETE 操作
     * 返回更新的行数
     */
    public int update(Connection conn, String sql) throws SQLException {
        return this.update(conn, false, sql, (Object[]) null);
    }

    /**
     * 同上，带有一个参数
     */
    public int update(Connection conn, String sql, Object param) throws SQLException {
        return this.update(conn, false, sql, new Object[]{param});
    }

    /**
     * 同上，带有可变参数
     */
    public int update(Connection conn, String sql, Object... params) throws SQLException {
        return update(conn, false, sql, params);
    }

    /**
     * 同上
     */
    public int update(String sql) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.update(conn, true, sql, (Object[]) null);
    }

    /**
     * 同上
     */
    public int update(String sql, Object param) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.update(conn, true, sql, new Object[]{param});
    }

    /**
     * 同上
     */
    public int update(String sql, Object... params) throws SQLException {
        Connection conn = this.prepareConnection();

        return this.update(conn, true, sql, params);
    }

    /**
     * 同上
     */
    private int update(Connection conn, boolean closeConn, String sql, 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");
        }

        PreparedStatement stmt = null;
        int rows = 0;

        try {
            stmt = this.prepareStatement(conn, sql);
            this.fillStatement(stmt, params);
            rows = stmt.executeUpdate();

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

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

        return rows;
    }

    /**
     * 执行给定的SQL插入语句
     */
    public <T> T insert(String sql, ResultSetHandler<T> rsh) throws SQLException {
        return insert(this.prepareConnection(), true, sql, rsh, (Object[]) null);
    }

    /**
     * 执行给定的SQL插入语句
     */
    public <T> T insert(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        return insert(this.prepareConnection(), true, sql, rsh, params);
    }

    /**
     * 执行给定的SQL插入语句
     */
    public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
        return insert(conn, false, sql, rsh, (Object[]) null);
    }

    /**
     * 执行给定的SQL插入语句
     */
    public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        return insert(conn, false, sql, rsh, params);
    }

    /**
     * 执行给定的SQL插入语句
     */
    private <T> T insert(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;
        T generatedKeys = null;

        try {
            stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            this.fillStatement(stmt, params);
            stmt.executeUpdate();
            //获取自增主键值
            ResultSet resultSet = stmt.getGeneratedKeys();
            generatedKeys = rsh.handle(resultSet);
        } catch (SQLException e) {
            this.rethrow(e, sql, params);
        } finally {
            close(stmt);
            if (closeConn) {
                close(conn);
            }
        }

        return generatedKeys;
    }

    /**
     * 执行给定的多个插入SQL语句
     */
    public <T> T insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params) throws SQLException {
        return insertBatch(this.prepareConnection(), true, sql, rsh, params);
    }

    /**
     * 执行给定的多个插入SQL语句
     */
    public <T> T insertBatch(Connection conn, String sql, ResultSetHandler<T> rsh, Object[][] params) throws SQLException {
        return insertBatch(conn, false, sql, rsh, params);
    }

    /**
     * 执行给定的多个插入SQL语句
     */
    private <T> T insertBatch(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 (params == null) {
            if (closeConn) {
                close(conn);
            }
            throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
        }

        PreparedStatement stmt = null;
        T generatedKeys = null;
        try {
            stmt = this.prepareStatement(conn, sql, Statement.RETURN_GENERATED_KEYS);

            for (int i = 0; i < params.length; i++) {
                this.fillStatement(stmt, params[i]);
                stmt.addBatch();
            }
            stmt.executeBatch();
            ResultSet rs = stmt.getGeneratedKeys();
            generatedKeys = rsh.handle(rs);

        } catch (SQLException e) {
            this.rethrow(e, sql, (Object[])params);
        } finally {
            close(stmt);
            if (closeConn) {
                close(conn);
            }
        }

        return generatedKeys;
    }
}

---

AbstractQueryRunner

QueryRunner 和 AsyncQueryRunner 的基类

package org.apache.commons.dbutils;

import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Arrays;

import javax.sql.DataSource;

/**
 * QueryRunner 和 AsyncQueryRunner 的基类
 * 这个类是线程安全的
 */
public abstract class AbstractQueryRunner {
    /**
     * Is {@link ParameterMetaData#getParameterType(int)} broken (have we tried
     * it yet)?
     */
    private volatile boolean pmdKnownBroken = false;

    /**
     * 数据源，用于获取Connection
     */
    @Deprecated
    protected final DataSource ds;

    /**
     * 默认构造函数, 设置 pmdKnownBroken 为 false， ds 为 null
     */
    public AbstractQueryRunner() {
        ds = null;
    }

    /**
     * Constructor to control the use of <code>ParameterMetaData</code>.
     *
     * @param pmdKnownBroken
     *            Some drivers don't support
     *            {@link ParameterMetaData#getParameterType(int) }; if
     *            <code>pmdKnownBroken</code> is set to true, we won't even try
     *            it; if false, we'll try it, and if it breaks, we'll remember
     *            not to use it again.
     */
    public AbstractQueryRunner(boolean pmdKnownBroken) {
        this.pmdKnownBroken = pmdKnownBroken;
        ds = null;
    }

    /**
     * Constructor to provide a <code>DataSource</code>. Methods that do not
     * take a <code>Connection</code> parameter will retrieve connections from
     * this <code>DataSource</code>.
     *
     * @param ds
     *            The <code>DataSource</code> to retrieve connections from.
     */
    public AbstractQueryRunner(DataSource ds) {
        this.ds = ds;
    }

    /**
     * Constructor to provide a <code>DataSource</code> and control the use of
     * <code>ParameterMetaData</code>. Methods that do not take a
     * <code>Connection</code> parameter will retrieve connections from this
     * <code>DataSource</code>.
     *
     * @param ds
     *            The <code>DataSource</code> to retrieve connections from.
     * @param pmdKnownBroken
     *            Some drivers don't support
     *            {@link ParameterMetaData#getParameterType(int) }; if
     *            <code>pmdKnownBroken</code> is set to true, we won't even try
     *            it; if false, we'll try it, and if it breaks, we'll remember
     *            not to use it again.
     */
    public AbstractQueryRunner(DataSource ds, boolean pmdKnownBroken) {
        this.pmdKnownBroken = pmdKnownBroken;
        this.ds = ds;
    }

    /**
     * Returns the <code>DataSource</code> this runner is using.
     * <code>QueryRunner</code> methods always call this method to get the
     * <code>DataSource</code> so subclasses can provide specialized behavior.
     *
     * @return DataSource the runner is using
     */
    public DataSource getDataSource() {
        return this.ds;
    }

    /**
     * Some drivers don't support
     * {@link ParameterMetaData#getParameterType(int) }; if
     * <code>pmdKnownBroken</code> is set to true, we won't even try it; if
     * false, we'll try it, and if it breaks, we'll remember not to use it
     * again.
     *
     * @return the flag to skip (or not)
     *         {@link ParameterMetaData#getParameterType(int) }
     * @since 1.4
     */
    public boolean isPmdKnownBroken() {
        return pmdKnownBroken;
    }

    /**
     * 用给定的SQL语句创建 PreparedStatement 对象的工厂方法
     * 如果需要，子类可以重写此方法以提供特殊的PreparedStatement配置
     * 这个实现简单地调用conn.prepareStatement(sql)
     */
    protected PreparedStatement prepareStatement(Connection conn, String sql)
            throws SQLException {

        return conn.prepareStatement(sql);
    }

    /**
     * returnedKeys：标识是否返回自动生成的键
     */
    protected PreparedStatement prepareStatement(Connection conn, String sql, int returnedKeys)
            throws SQLException {

        return conn.prepareStatement(sql, returnedKeys);
    }

    /**
     * 创建和初始化 Connection 对象的工厂方法
     * QueryRunner里的方法总是调用此方法来从它的数据源 DataSource 中获取连接 Connection
     * 如果需要，子类可以重写此方法以提供特殊的 Connection 配置
     * 这个实现简单地调用ds.getConnection()
     */
    protected Connection prepareConnection() throws SQLException {
        if (this.getDataSource() == null) {
            throw new SQLException(
                    "QueryRunner requires a DataSource to be "
                            + "invoked in this way, or a Connection should be passed in");
        }
        return this.getDataSource().getConnection();
    }

    /**
     * 用给定的对象填充 PreparedStatement 占位符
     */
    public void fillStatement(PreparedStatement stmt, Object... params)
            throws SQLException {

        // 检查参数个数是否和
        ParameterMetaData pmd = null;
        if (!pmdKnownBroken) {
            pmd = stmt.getParameterMetaData();
            int stmtCount = pmd.getParameterCount();
            int paramsCount = params == null ? 0 : params.length;

            if (stmtCount != paramsCount) {
                throw new SQLException("Wrong number of parameters: expected "
                        + stmtCount + ", was given " + paramsCount);
            }
        }

        // nothing to do here
        if (params == null) {
            return;
        }

        for (int i = 0; i < params.length; i++) {
            if (params[i] != null) {
                stmt.setObject(i + 1, params[i]);
            } else {
                // VARCHAR works with many drivers regardless
                // of the actual column type. Oddly, NULL and
                // OTHER don't work with Oracle's drivers.
                int sqlType = Types.VARCHAR;
                if (!pmdKnownBroken) {
                    try {
                        /*
                         * It's not possible for pmdKnownBroken to change from
                         * true to false, (once true, always true) so pmd cannot
                         * be null here.
                         */
                        sqlType = pmd.getParameterType(i + 1);
                    } catch (SQLException e) {
                        pmdKnownBroken = true;
                    }
                }
                stmt.setNull(i + 1, sqlType);
            }
        }
    }

    /**
     * 用给定对象的bean属性值填充 PreparedStatement 的可替换参数
     *
     * stmt：想要填充的 PreparedStatement 对象
     * bean：JavaBean
     * properties：一组有序的 PropertyDescriptor 对象（Java内省机制）
     */
    public void fillStatementWithBean(PreparedStatement stmt, Object bean,
            PropertyDescriptor[] properties) throws SQLException {
        Object[] params = new Object[properties.length];
        for (int i = 0; i < properties.length; i++) {
            PropertyDescriptor property = properties[i];
            Object value = null;
            Method method = property.getReadMethod();
            if (method == null) {
                throw new RuntimeException("No read method for bean property "
                        + bean.getClass() + " " + property.getName());
            }
            try {
                value = method.invoke(bean, new Object[0]);
            } catch (InvocationTargetException e) {
                throw new RuntimeException("Couldn't invoke method: " + method,
                        e);
            } catch (IllegalArgumentException e) {
                throw new RuntimeException(
                        "Couldn't invoke method with 0 arguments: " + method, e);
            } catch (IllegalAccessException e) {
                throw new RuntimeException("Couldn't invoke method: " + method,
                        e);
            }
            params[i] = value;
        }
        fillStatement(stmt, params);
    }

    /**
     * 用给定对象的bean属性值填充 PreparedStatement 的可替换参数
     * propertyNames：一组有序的属性名 (如属性名为name，则需要有setName/getName方法 )
     */
    public void fillStatementWithBean(PreparedStatement stmt, Object bean,
            String... propertyNames) throws SQLException {
        PropertyDescriptor[] descriptors;
        try {
            descriptors = Introspector.getBeanInfo(bean.getClass())
                    .getPropertyDescriptors();
        } catch (IntrospectionException e) {
            throw new RuntimeException("Couldn't introspect bean "
                    + bean.getClass().toString(), e);
        }
        PropertyDescriptor[] sorted = new PropertyDescriptor[propertyNames.length];
        for (int i = 0; i < propertyNames.length; i++) {
            String propertyName = propertyNames[i];
            if (propertyName == null) {
                throw new NullPointerException("propertyName can't be null: "
                        + i);
            }
            boolean found = false;
            for (int j = 0; j < descriptors.length; j++) {
                PropertyDescriptor descriptor = descriptors[j];
                if (propertyName.equals(descriptor.getName())) {
                    sorted[i] = descriptor;
                    found = true;
                    break;
                }
            }
            if (!found) {
                throw new RuntimeException("Couldn't find bean property: "
                        + bean.getClass() + " " + propertyName);
            }
        }
        fillStatementWithBean(stmt, bean, sorted);
    }

    /**
     * 抛出一个具有价值信息的异常
     */
    protected void rethrow(SQLException cause, String sql, Object... params)
            throws SQLException {

        String causeMessage = cause.getMessage();
        if (causeMessage == null) {
            causeMessage = "";
        }
        StringBuffer msg = new StringBuffer(causeMessage);

        msg.append(" Query: ");
        msg.append(sql);
        msg.append(" Parameters: ");

        if (params == null) {
            msg.append("[]");
        } else {
            msg.append(Arrays.deepToString(params));
        }

        SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
                cause.getErrorCode());
        e.setNextException(cause);

        throw e;
    }

    /**
     * 在处理 ResultSet 之前，对它进行包装
     * 通常，这个方法的实现可以通过匿名内部类完成：
     * QueryRunner run = new QueryRunner() {
     *     protected ResultSet wrap(ResultSet rs) {
     *         return StringTrimmedResultSet.wrap(rs);
     *     }

     */
    protected ResultSet wrap(ResultSet rs) {
        return rs;
    }

    /**
     * 关闭 Connection
     * 该实现避免关闭 Connection 为 null 的情况，并且不抑制异常
     * 子类可以重写此方法以提供如日志记录这样的特殊处理
     */
    protected void close(Connection conn) throws SQLException {
        DbUtils.close(conn);
    }

    /**
     * 关闭 Statement
     */
    protected void close(Statement stmt) throws SQLException {
        DbUtils.close(stmt);
    }

    /**
     * 关闭 ResultSet
     */
    protected void close(ResultSet rs) throws SQLException {
        DbUtils.close(rs);
    }

}

---

使用：

    /**
     * 测试 QueryRunner 类的 update 方法
     */
    @Test
    public void testQueryRunnerUpdate() {
        Connection connection = null;

        String sql = "DELETE FROM persons WHERE id in (?, ?)";

        try {
            connection = JDBCTools.getConnection();
            //返回受影响的行数
            int rows = queryRunner.update(connection, sql, 41, 39);
            System.out.println(rows);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 测试 QueryRunner 类的 insert 方法
     */
    @Test
    public void testQueryRunnerInsert() {
        Connection connection = null;
        String sql = "INSERT INTO persons(name, age, birth, email) VALUES(?, ?, ?, ?)";

        try {
            connection = JDBCTools.getConnection();
            Object[] args= {"测试", 99, new java.sql.Date(new Date().getTime()), "tmd@qq.com"};
            //返回插入数据的主键
            Long id = queryRunner.insert(connection, sql, new ScalarHandler<Long>(), args);
            System.out.println(id);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(connection);
        }
    }

    //自定义 ResultSetHandler
    @Test
    public void testQueryRunnerQuery() {
        Connection connection = null;
        try {
            connection = JDBCTools.getConnection();
            String sql = "SELECT * FROM persons";
            List<Person> list = queryRunner.query(connection, sql, rs -> {
                List<Person> list1 = new ArrayList<>();
                while (rs.next()) {
                    Person person = new Person(rs.getInt(1), rs.getString(2),
                            rs.getInt(3), rs.getDate(4), rs.getString(5));
                    list1.add(person);
                }
                return list1;
            });
            list.forEach(System.out::println);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(connection);
        }
    }

需要注意的是insert和update方法都能执行INSERT开头的SQL语句，但是返回值有区别。
insert 执行后返回的是表中的插入行生成的主键值，update 返回的是受语句影响的行数。
所以，如果目标表中有主键且需要返回插入行的主键值就用insert方法，如果表没有主键或者不需要返回主键值可使用update方法。