JDBC連接配接資料庫第三步：利用開源架構Dbutils查詢資料庫原理及實作方案

commons-dbutils-1.9.3.jar 是 Apache 組織提供的一個開源 JDBC工具類庫，它是對JDBC的簡單封裝，學習成本極低，并且使用dbutils能極大簡化jdbc編碼的工作量，同時也不會影響程式的性能。

0、Dbutils的query()的實作原理：

1、萬變不離其宗，查詢方法隻不過是封裝了結果集ResultSet轉換為map或list的過程。

2、具體實作方法：

（1）以BeanListHandler為例，我們期望“将結果集中的每一行資料都封裝到一個對應的JavaBean執行個體中，存放到List裡”。代碼如下：

Connection connection = JdbcUtils.getConnection();
 List<User> userList = new ArrayList<>();
 QueryRunner runner = new QueryRunner();
 ResultSetHandler<List<User>> rsh = new BeanListHandler<User>(User.class);
 userList = runner.query(connection,sql,rsh);

在上一篇博文說道，使用Statement或PreparedStatement執行查詢，得到結果集ResultSet，然後對結果集周遊，再進行封裝處理：

public static <T> List<T> statementExecuteQuery(Class<T> clazz, final String sql){
        T entity = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<T> queryResult = new ArrayList<>();
        Connection connection= getConnection();
        if (null == connection){
            //log.error("Connection is null.");
            return null;
        }
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);//過程一：得到結果集
            final List<Map<String, Object>> data = converResultSetToList(resultSet);//過程二：将結果集toList
            for (Map<String, Object> map : data){//過程三：将結果集中的每一行資料都封裝到一個對應的JavaBean執行個體中
                entity = clazz.newInstance();
                for (Map.Entry<String, Object> entry : map.entrySet()){
                    String columnName = entry.getKey();
                    Object columnValue = entry.getValue();
                    BeanUtils.setProperty(entity,columnName,columnValue);
                }
                queryResult.add(entity);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResource(connection,null,null,resultSet);
        }
        return queryResult;
    }

private static List<Map<String, Object>> converResultSetToList(ResultSet resultSet) throws SQLException {
        if (null == resultSet){
            return null;
        }
        List<Map<String, Object>> data = new ArrayList<>();
        ResultSetMetaData rsmd = resultSet.getMetaData();
        while (resultSet.next()){
            Map<String, Object> rowData = new HashMap<String, Object>();
            for(int i = 0,columnCount = rsmd.getColumnCount();i < columnCount; i++){
                rowData.put(rsmd.getColumnName(i + 1),resultSet.getObject(i + 1));
            }
            data.add(rowData);
        }
        return data;
    }

問：Dbutils是怎麼做到封裝的呢？先看看查詢實作代碼：

List<T> entityList = new ArrayList<>();
Connection connection = JdbcUtils.getConnection();
QueryRunner runner = new QueryRunner();
ResultSetHandler<List<User>> rsh = new BeanListHandler<User>(User.class);
entityList = (List<T>)runner.query(connection,sql,rsh);

其實很簡單，就是把上面三個過程做了封裝而已，封裝到 runner.query(connection,sql,rsh) 方法中。

仔細一點就是，queryRunner.query(con,sql,rsh)方法有三個參數，第一個是資料庫連接配接對象，第二個是執行資料庫查詢的sql語句，第三個很重主要了，就是結果集處理ResultSetHandler的實作類。也就是你要将查詢出來的一什麼形式傳回，是傳回一個數組，還是JavaBean執行個體，還是Map結果，還是一個具體的結果ScalarHandler。

ResultSetHandler接口，有一個 handler 方法，queryRunner.query(con,sql,rsh) 方法将查詢出來的結果集交給handler 方法來進行處理，按照 rsh 具體實作類的類型來封裝處理結果集。

看看源代碼：

ResultSetHandler接口：

public interface ResultSetHandler<T> {
    T handle(ResultSet var1) throws SQLException;
}

queryRunner.query(con,sql,rsh)方法：

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

private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
        //略去如參判斷code
            PreparedStatement stmt = null;
            ResultSet rs = null;
            Object result = null;
            try {
                stmt = this.prepareStatement(conn, sql);
                this.fillStatement(stmt, params);
                rs = this.wrap(stmt.executeQuery());
                result = rsh.handle(rs);//調用handler方法，處理結果集。
            } catch (SQLException var33) {
                this.rethrow(var33, sql, params);
            } finally {
                try {
                    this.close(rs);
                } finally {
                    this.close(stmt);
                    if (closeConn) {
                        this.close(conn);
                    }
                }
            }
            return result;
    }

BeanListHandler實作類的handler方法如何處理結果集：

public class BeanListHandler<T> implements ResultSetHandler<List<T>> {
    private final Class<? extends T> type;
    private final RowProcessor convert;

    public BeanListHandler(Class<? extends T> type) {
        this(type, ArrayHandler.ROW_PROCESSOR);
    }

    public BeanListHandler(Class<? extends T> type, RowProcessor convert) {
        this.type = type;
        this.convert = convert;
    }
    //調用RowProcessor對象的toBeanLit方法處理結果集方法
    public List<T> handle(ResultSet rs) throws SQLException {
        return this.convert.toBeanList(rs, this.type);
    }
}

RowProcessor類的toBeanList()方法：

public <T> List<T> toBeanList(ResultSet rs, Class<? extends T> type) throws SQLException {
        List<T> results = new ArrayList();
        if (!rs.next()) {
            return results;
        } else {
            PropertyDescriptor[] props = this.propertyDescriptors(type);
            ResultSetMetaData rsmd = rs.getMetaData();
            int[] columnToProperty = this.mapColumnsToProperties(rsmd, props);
            do {
                results.add(this.createBean(rs, type, props, columnToProperty));
            } while(rs.next());

            return results;
        }
    }

不信，我們可以測試一波：

public static void main(String[] args){
        String selectSql = "SELECT * FROM userinfos WHERE userName = 'aerfa';";
        Object object = dbutilsExecuteQueryUserOne(selectSql);
        System.out.println(object);   
    }

    static class MyResultSetHandler implements ResultSetHandler{
        @Override
        public Object handle(ResultSet var1) throws SQLException{
            System.out.println("This my ResultSetHandler.");
           return "myResultSetHandler";
        }
    }

    public static Object dbutilsExecuteQueryUserOne(final String sql){
        Object user = null;
        Connection connection = JdbcUtils.getConnection();
        if(null == connection){
            return null;
        }
        try {
            user = new User();
            QueryRunner runner = new QueryRunner();
            ResultSetHandler rsh = new MyResultSetHandler();
            user = runner.query(connection,sql,rsh);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return user;
    }

測試結果：

JDBC連接配接資料庫第三步：利用開源架構Dbutils查詢資料庫原理及實作方案

ResultSetHandler的各個實作類：

ArrayHandler：把結果集中的第一行資料轉成對象數組。

ArrayListHandler：把結果集中的每一行資料都轉成一個對象數組，再存放到List中。

BeanHandler：将結果集中的第一行資料封裝到一個對應的JavaBean執行個體中。

BeanListHandler：将結果集中的每一行資料都封裝到一個對應的JavaBean執行個體中，存放到List裡。

MapHandler：将結果集中的第一行資料封裝到一個Map裡，key是列名，value就是對應的值。

MapListHandler：将結果集中的每一行資料都封裝到一個Map裡，然後再存放到List。

ColumnListHandler：将結果集中某一列的資料存放到List中。

KeyedHandler(name)：将結果集中的每一行資料都封裝到一個Map裡(List<Map>)，再把這些map再存到一個map裡，其key為指定的列。

ScalarHandler：将結果集第一行的某一列放到某個對象。

使用遵從以下步驟：

1．加載JDBC驅動程式類，并用DriverManager來得到一個資料庫連接配接conn。

2．執行個體化 QueryRunner,得到執行個體化對象runner。

3. runner.update()方法，執行增改删的sql指令，runner.query()方法，得到結果集。

1、使用MapHandler查詢單個結果

public static User dbutilsExecuteQueryUserOne(final String sql){
        User user = null;
        Connection connection = JdbcUtils.getConnection();
        if(null == connection){
            return null;
        }
        try {
            user = new User();
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<Map<String, Object>> rsh = new MapHandler();
            Map<String, Object> data = runner.query(connection,sql,rsh);
            for(Map.Entry<String, Object> entry : data.entrySet()){
                String colunName = entry.getKey();
                Object colunValue = entry.getValue();
                BeanUtils.setProperty(user,colunName,colunValue);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return user;
    }

2、使用MapListHandler查詢多個結果

public static List<User> dbutilsExecuteQueryUsersOne(final String sql){
        List<User> userList = new ArrayList<>();
        Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return null;
        }
        try {
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<List<Map<String,Object>>> rsh = new MapListHandler();
            List<Map<String,Object>> data = runner.query(connection,sql,rsh);
            for (Map<String, Object> map : data){
                User user = new User();
                for (Map.Entry<String, Object> entry : map.entrySet()){
                    String columnName = entry.getKey();
                    Object columnValue = entry.getValue();
                    BeanUtils.setProperty(user,columnName,columnValue);
                }
                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return userList;
    }

3、使用BeanHandler查詢單個結果

public static User dbutilsExecuteQueryUserTwo(final String sql){
        User user = null;
        Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return null;
        }
        try {
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<User> rsh = new BeanHandler<User>(User.class);
            user = runner.query(connection,sql,rsh);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return user;
    }

4、使用BeanListHandler查詢多個結果

public static List<User> dbutilsExecuteQueryUsersTwo(final String sql){
        List<User> userList = new ArrayList<>();
        Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return null;
        }
        try {
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<List<User>> rsh = new BeanListHandler<User>(User.class);
            userList = runner.query(connection,sql,rsh);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return userList;
    }

5、利用反射機制，查詢方法通用化

public static <T> T dbutilsExecuteQueryUserOne(Class<T> clazz, final String sql){
        T entity = null;
        Connection connection = JdbcUtils.getConnection();
        if(null == connection){
            return null;
        }
        try {
            entity = clazz.newInstance();
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<Map<String, Object>> rsh = new MapHandler();
            Map<String, Object> data = runner.query(connection,sql,rsh);
            for(Map.Entry<String, Object> entry : data.entrySet()){
                String colunName = entry.getKey();
                Object colunValue = entry.getValue();
                BeanUtils.setProperty(entity,colunName,colunValue);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return entity;

    }

    public static <T> List<T> dbutilsExecuteQueryUsersOne(Class<T> clazz, final String sql){
        List<T> entityList = new ArrayList<>();
        Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return null;
        }
        try {
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<List<Map<String,Object>>> rsh = new MapListHandler();
            List<Map<String,Object>> data = runner.query(connection,sql,rsh);
            for (Map<String, Object> map : data){
                T entity = clazz.newInstance();
                for (Map.Entry<String, Object> entry : map.entrySet()){
                    String columnName = entry.getKey();
                    Object columnValue = entry.getValue();
                    BeanUtils.setProperty(entity,columnName,columnValue);
                }
                entityList.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return entityList;
    }

    public static <T> T dbutilsExecuteQueryUserTwo(Class<T> clazz, final String sql){
        T entity = null;
        Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return null;
        }
        try {
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<User> rsh = new BeanHandler<User>(User.class);
            entity = (T) runner.query(connection,sql,rsh);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return entity;
    }

    public static <T> List<T> dbutilsExecuteQueryUsersTwo(Class<T> clazz, final String sql){
        List<T> entityList = new ArrayList<>();
        Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return null;
        }
        try {
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<List<User>> rsh = new BeanListHandler<User>(User.class);
            entityList = (List<T>)runner.query(connection,sql,rsh);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return entityList;
    }

查詢：可接受帶有填充字元的sql語句，可防止sql注入。

public static <T> T queryByMapHandler(Class<T> clazz, final String sql,Object...args){
        T entity = null;
        Connection connection = JdbcUtils.getConnection();
        if(null == connection){
            return null;
        }
        try {
            entity = clazz.newInstance();
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<Map<String, Object>> rsh = new MapHandler();
            Map<String, Object> data = runner.query(connection,sql,rsh,args);
            for(Map.Entry<String, Object> entry : data.entrySet()){
                String colunName = entry.getKey();
                Object colunValue = entry.getValue();
                BeanUtils.setProperty(entity,colunName,colunValue);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return entity;
    }
    
    public static <T> List<T> queryByMapListHandler(Class<T> clazz, final String sql, Object...args){
        List<T> entityList = new ArrayList<>();
        Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return null;
        }
        try {
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<List<Map<String,Object>>> rsh = new MapListHandler();
            List<Map<String,Object>> data = runner.query(connection,sql,rsh,args);
            for (Map<String, Object> map : data){
                T entity = clazz.newInstance();
                for (Map.Entry<String, Object> entry : map.entrySet()){
                    String columnName = entry.getKey();
                    Object columnValue = entry.getValue();
                    BeanUtils.setProperty(entity,columnName,columnValue);
                }
                entityList.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return entityList;
    }

	public static <T> T queryByBeanHandler(Class<T> clazz, final String sql, Object...args){
        T entity = null;
        Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return null;
        }
        try {
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<User> rsh = new BeanHandler<User>(User.class);
            entity = (T) runner.query(connection,sql,rsh,args);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return entity;
    }
    
    
	public static <T> List<T> queryByBeanListHandler(Class<T> clazz, final String sql,Object...args){
        List<T> entityList = new ArrayList<>();
        Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return null;
        }
        try {
            QueryRunner runner = new QueryRunner();
            ResultSetHandler<List<User>> rsh = new BeanListHandler<User>(User.class);
            entityList = (List<T>)runner.query(connection,sql,rsh,args);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(connection,null,null,null);
        }
        return entityList;
    }

6、執行插入、删除、更新操作

/**
     * 可執行插入、删除、更新操作
     * @param sql:一條完整的sql語句
     */
    public static void executeByUpdate(final String sql){
    	Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return;
        }
        try {
           QueryRunner runner = new QueryRunner();
	   int num = runner.update(connection, sql);
	   if (0 == num) {
	      //log.error("execute update failed.");
	   }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    JdbcUtils.closeResource(connection,null,null,null);
	}
    }
    
    /**
     * 可執行插入、删除、更新操作
     * @param sql:帶有可填充的sql語句
     */
    public static void executeByUpdate(final String sql, Object... args){
    	Connection connection = JdbcUtils.getConnection();
        if (null == connection){
            return;
        }
        try {
           QueryRunner runner = new QueryRunner();
	   int num = runner.update(connection, sql, args);
	   if (0 == num) {
	     //log.error("execute update failed.");
	   }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	   JdbcUtils.closeResource(connection,null,null,null);
	}
    }

參考：

使用DbUtils實作增删改查——ResultSetHandler 接口的實作類

利用ResultSetHandler各實作類來處理查詢結果

開源工具DbUtils的使用（資料庫的增删改查）

DbUtils

開源架構：DBUtils使用詳解

JDBC連接配接資料庫第三步：利用開源架構Dbutils查詢資料庫原理及實作方案

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述