[JavaWeb]关于DBUtils中QueryRunner的一些解读.

QueryRunner类

QueryRunner中提供对sql语句操作的API
它主要有三个方法
　　query() 用于执行select
　　update() 用于执行insert/update/delete
　　batch() 批处理

1,Query语句
先来看下query的两种形式, 我们这里主要讲第一个方法, 因为我们用C3P0来统一管理connection.(QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()))
query(sql,ResultSetHandler,Object...params);
query(conn,sql,ResultSetHandler,Object...params);

第一种: 不需要params

//查询所有图书public List<Book> selectAllBooks() throws SQLException {    QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());    return qr.query("select * from books", new BeanListHandler<Book>(Book.class));}

 

第二种: 需要一个参数查询

//根据id查询指定的书public Book selectBookById(String id) throws SQLException {    QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());    return qr.query("select * from books where id=?", new BeanHandler(Book.class),id);}

第三种:需要多个参数查询

//多条件查询图书信息public List<Book> findBookByManyCondition(String id, String category,        String name, String minprice, String maxprice) throws SQLException {    StringBuilder sql = new StringBuilder("select * from books where 1=1");    List list = new ArrayList();    if(!"".equals(id)){        sql.append(" and id like ?");        list.add("%"+id+"%");    }    if(!"".equals(category)){        sql.append(" and category=?");        list.add(category);    }    if(!"".equals(name)){        sql.append(" and name like ?");        list.add("%"+name+"%");    }    if(!"".equals(minprice)){        sql.append(" and price > ?");        list.add(minprice);    }    if(!"".equals(maxprice)){        sql.append(" and price < ?");        list.add(maxprice);    }        QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());    return qr.query(sql.toString(),new BeanListHandler<Book>(Book.class),list.toArray());}

那么我们来看下源码的实现: 
(1)QueryRunner.java

//第一种情况,无参数public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {    Connection conn = this.prepareConnection();    return this.query(conn, true, sql, rsh, (Object[]) null);}//第二种和第三种使用同一方法: 需要参数public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {    Connection conn = this.prepareConnection();    return this.query(conn, true, sql, rsh, params);}

解读: 这里先是获取connection, 利用this.preparaConnection() 获取. 然后调用query()方法去执行查询语句. 接下来看源码是如何获取到当前传输过来的connection以及query()方法的内部实现.

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

这里很简单, 因为我们用的C3P0数据库连接池获取的DataSource, 所以这里直就可以过去到当前的Connection.接下来就看下query()方法的内部实现. 

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 {        stmt = this.prepareStatement(conn, sql);        this.fillStatement(stmt, params);        rs = this.wrap(stmt.executeQuery());        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;}

解读: 在这里可以看出, 无论是否有传递参数params, 都调用的是同一个query方法, 接着来看this.fillStatement(stmt, params);是如何将参数赋予preparedStatement中的. 

public void fillStatement(PreparedStatement stmt, Object... params) throws SQLException {    // check the parameter count, if we can    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 {                    sqlType = pmd.getParameterType(i + 1);                } catch (SQLException e) {                    pmdKnownBroken = true;                }            }            stmt.setNull(i + 1, sqlType);        }    }}

这个方法就是核心所在. 
第一种情况: 当params为null的时候, 直接return然后执行sql语句.
第二种第三种情况: 当params不为null时, 循环遍历传入的params, 然后将params赋值到preparedStatement中, 然后填充占位符进行sql查询. 这里我们也来回顾下直接使用preparedStatement来进行查询的方式:

@Testpublic void update(){    Connection conn = null;    PreparedStatement st = null;    ResultSet rs = null;    try{        conn = JdbcUtils.getConnection();        String sql = "update users set name=?,email=? where id=?";        st = conn.prepareStatement(sql);        st.setString(1, "gacl");        st.setString(2, "gacl@sina.com");        st.setInt(3, 2);        int num = st.executeUpdate();        if(num>0){            System.out.println("更新成功！！");        }    }catch (Exception e) {        e.printStackTrace();            }finally{        JdbcUtils.release(conn, st, rs);    }}@Testpublic void find(){    Connection conn = null;    PreparedStatement st = null;    ResultSet rs = null;    try{        conn = JdbcUtils.getConnection();        String sql = "select * from users where id=?";        st = conn.prepareStatement(sql);        st.setInt(1, 1);        rs = st.executeQuery();        if(rs.next()){            System.out.println(rs.getString("name"));        }    }catch (Exception e) {            }finally{        JdbcUtils.release(conn, st, rs);    }}

2, Update语句
查看update语句:

//修改图书 public void updateBook(Book book) throws SQLException {    QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());    qr.update(            "UPDATE books SET NAME=? ,price=?,bnum=?,category=?,description=? WHERE id=?",            book.getName(), book.getPrice(), book.getBnum(),            book.getCategory(), book.getDescription(), book.getId())}

接着是QueryRunner.java中的update 方法:

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

到了参数赋值的时候又调用了上面的fillStatement方法, 这里就不再阐述了. 


3, Batch语句
这里直接看batch方法的实例, 然后结合源码的实现.

//批量删除public void delBooks(String[] ids) throws SQLException {    QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());    Object[][] params = new Object[ids.length][];//高维确定执行sql语句的次数，低维是给？赋值    for (int i = 0; i < params.length; i++) {        params[i] = new Object[]{ids[i]};//给“？”赋值    }    qr.batch("delete from books where id=?", params);}

然后看QueryRunner中的batch()方法:

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

解读: 因为params是一个二维数组, 所以往preparedStatement中赋值的时候使用了for循环, 然后通过preparedstatement.addBatch() 进行批量添加, 然后执行executeBatch()进行操作.

　　 /**     * Adds a set of parameters to this <code>PreparedStatement</code>     * object's batch of commands.     *     * @exception SQLException if a database access error occurs or     * this method is called on a closed <code>PreparedStatement</code>     * @see Statement#addBatch     * @since 1.2     */    void addBatch() throws SQLException;