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

发布时间：2020-12-14 06:16:37 所属栏目：Java 来源：网络整理

导读：前言: [本文属于原创分享文章,转载请注明出处,谢谢.] 前面已经有文章说了DBUtils的一些特性,这里再来详细说下QueryRunner的一些内部实现,写的有错误的地方还恳请大家指出.? QueryRunner QueryRunner 它主要有三个方法 update() batch() 先来看下query的两种

前言:[本文属于原创分享文章,转载请注明出处,谢谢.]前面已经有文章说了DBUtils的一些特性,这里再来详细说下QueryRunner的一些内部实现,写的有错误的地方还恳请大家指出.?

QueryRunner

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

第一种: 不需要params

List selectAllBooks() = qr.query("select * from books", BeanListHandler(Book.

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

Book selectBookById(String id) = qr.query("select * from books where id=?", BeanHandler(Book.

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

List= StringBuilder("select * from books where 1=1"= (!""" and id like ?""%"+id+"%"(!""" and category=?"(!""" and name like ?""%"+name+"%"(!""" and price > ?"(!""" and price < ?"

QueryRunner qr </span>= <span style="color: #0000ff;"&gt;new</span><span style="color: #000000;"&gt; QueryRunner(C3P0Utils.getDataSource());
</span><span style="color: #0000ff;"&gt;return</span> qr.query(sql.toString(),<span style="color: #0000ff;"&gt;new</span> BeanListHandler<Book>(Book.<span style="color: #0000ff;"&gt;class</span><span style="color: #000000;"&gt;),list.toArray());

}

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

T query(String sql,ResultSetHandler rsh) =

</span><span style="color: #0000ff;"&gt;return</span> <span style="color: #0000ff;"&gt;this</span>.query(conn,<span style="color: #0000ff;"&gt;true</span>,rsh,(Object[]) <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;);

}

//第二种和第三种使用同一方法: 需要参数
public T query(String sql,ResultSetHandler rsh,Object... params) throws SQLException {
Connection conn = this.prepareConnection();

</span><span style="color: #0000ff;"&gt;return</span> <span style="color: #0000ff;"&gt;this</span>.query(conn,<span style="color: #0000ff;"&gt;true</span><span style="color: #000000;"&gt;,params);

}

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

Connection prepareConnection() (.getDataSource() == SQLException("QueryRunner requires a DataSource to be " + "invoked in this way,or a Connection should be passed in"

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

T query(Connection conn, closeConn,String sql,ResultSetHandler (conn == SQLException("Null connection"

</span><span style="color: #0000ff;"&gt;if</span> (sql == <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;) {
    </span><span style="color: #0000ff;"&gt;if</span><span style="color: #000000;"&gt; (closeConn) {
        close(conn);
    }
    </span><span style="color: #0000ff;"&gt;throw</span> <span style="color: #0000ff;"&gt;new</span> SQLException("Null SQL statement"<span style="color: #000000;"&gt;);
}

</span><span style="color: #0000ff;"&gt;if</span> (rsh == <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;) {
    </span><span style="color: #0000ff;"&gt;if</span><span style="color: #000000;"&gt; (closeConn) {
        close(conn);
    }
    </span><span style="color: #0000ff;"&gt;throw</span> <span style="color: #0000ff;"&gt;new</span> SQLException("Null ResultSetHandler"<span style="color: #000000;"&gt;);
}

PreparedStatement stmt </span>= <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;;
ResultSet rs </span>= <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;;
T result </span>= <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;;

</span><span style="color: #0000ff;"&gt;try</span><span style="color: #000000;"&gt; {
    stmt </span>= <span style="color: #0000ff;"&gt;this</span><span style="color: #000000;"&gt;.prepareStatement(conn,sql);
    </span><span style="color: #0000ff;"&gt;this</span><span style="color: #000000;"&gt;.fillStatement(stmt,params);
    rs </span>= <span style="color: #0000ff;"&gt;this</span><span style="color: #000000;"&gt;.wrap(stmt.executeQuery());
    result </span>=<span style="color: #000000;"&gt; rsh.handle(rs);

} </span><span style="color: #0000ff;"&gt;catch</span><span style="color: #000000;"&gt; (SQLException e) {
    </span><span style="color: #0000ff;"&gt;this</span><span style="color: #000000;"&gt;.rethrow(e,params);

} </span><span style="color: #0000ff;"&gt;finally</span><span style="color: #000000;"&gt; {
    </span><span style="color: #0000ff;"&gt;try</span><span style="color: #000000;"&gt; {
        close(rs);
    } </span><span style="color: #0000ff;"&gt;finally</span><span style="color: #000000;"&gt; {
        close(stmt);
        </span><span style="color: #0000ff;"&gt;if</span><span style="color: #000000;"&gt; (closeConn) {
            close(conn);
        }
    }
}

</span><span style="color: #0000ff;"&gt;return</span><span style="color: #000000;"&gt; result;

}

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

fillStatement(PreparedStatement stmt,Object... params)

</span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; check the parameter count,if we can</span>
ParameterMetaData pmd = <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;;
</span><span style="color: #0000ff;"&gt;if</span> (!<span style="color: #000000;"&gt;pmdKnownBroken) {
    pmd </span>=<span style="color: #000000;"&gt; stmt.getParameterMetaData();
    </span><span style="color: #0000ff;"&gt;int</span> stmtCount =<span style="color: #000000;"&gt; pmd.getParameterCount();
    </span><span style="color: #0000ff;"&gt;int</span> paramsCount = params == <span style="color: #0000ff;"&gt;null</span> ? 0<span style="color: #000000;"&gt; : params.length;

    </span><span style="color: #0000ff;"&gt;if</span> (stmtCount !=<span style="color: #000000;"&gt; paramsCount) {
        </span><span style="color: #0000ff;"&gt;throw</span> <span style="color: #0000ff;"&gt;new</span> SQLException("Wrong number of parameters: expected "
                + stmtCount + ",was given " +<span style="color: #000000;"&gt; paramsCount);
    }
}

</span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; nothing to do here</span>
<span style="color: #0000ff;"&gt;if</span> (params == <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;) {
    </span><span style="color: #0000ff;"&gt;return</span><span style="color: #000000;"&gt;;
}

</span><span style="color: #0000ff;"&gt;for</span> (<span style="color: #0000ff;"&gt;int</span> i = 0; i < params.length; i++<span style="color: #000000;"&gt;) {
    </span><span style="color: #0000ff;"&gt;if</span> (params[i] != <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;) {
        stmt.setObject(i </span>+ 1<span style="color: #000000;"&gt;,params[i]);
    } </span><span style="color: #0000ff;"&gt;else</span><span style="color: #000000;"&gt; {
        </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; VARCHAR works with many drivers regardless
        </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; of the actual column type.  Oddly,NULL and
        </span><span style="color: #008000;"&gt;//</span><span style="color: #008000;"&gt; OTHER don't work with Oracle's drivers.</span>
        <span style="color: #0000ff;"&gt;int</span> sqlType =<span style="color: #000000;"&gt; Types.VARCHAR;
        </span><span style="color: #0000ff;"&gt;if</span> (!<span style="color: #000000;"&gt;pmdKnownBroken) {
            </span><span style="color: #0000ff;"&gt;try</span><span style="color: #000000;"&gt; {
                sqlType </span>= pmd.getParameterType(i + 1<span style="color: #000000;"&gt;);
            } </span><span style="color: #0000ff;"&gt;catch</span><span style="color: #000000;"&gt; (SQLException e) {
                pmdKnownBroken </span>= <span style="color: #0000ff;"&gt;true</span><span style="color: #000000;"&gt;;
            }
        }
        stmt.setNull(i </span>+ 1<span style="color: #000000;"&gt;,sqlType);
    }
}

}

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

= = = == "update users set name=?,email=? where id=?"=1,"gacl"2,"gacl@sina.com"3,2 num =(num>0"更新成功！！"

}</span><span style="color: #0000ff;"&gt;finally</span><span style="color: #000000;"&gt;{
    JdbcUtils.release(conn,st,rs);
}

}

@Test
public 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) {

}</span><span style="color: #0000ff;"&gt;finally</span><span style="color: #000000;"&gt;{
    JdbcUtils.release(conn,rs);
}

}

2,Update语句

updateBook(Book book) = "UPDATE books SET NAME=?,price=?,bnum=?,category=?,description=? WHERE id=?"

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

update(String sql,Object... params) = return this.update(conn,params);

}

private int update(Connection conn,Object... params) throws SQLException {
if (conn == null) {
throw new SQLException("Null connection");
}

</span><span style="color: #0000ff;"&gt;if</span> (sql == <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;) {
    </span><span style="color: #0000ff;"&gt;if</span><span style="color: #000000;"&gt; (closeConn) {
        close(conn);
    }
    </span><span style="color: #0000ff;"&gt;throw</span> <span style="color: #0000ff;"&gt;new</span> SQLException("Null SQL statement"<span style="color: #000000;"&gt;);
}

PreparedStatement stmt </span>= <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;;
</span><span style="color: #0000ff;"&gt;int</span> rows = 0<span style="color: #000000;"&gt;;

</span><span style="color: #0000ff;"&gt;try</span><span style="color: #000000;"&gt; {
    stmt </span>= <span style="color: #0000ff;"&gt;this</span><span style="color: #000000;"&gt;.prepareStatement(conn,params);
    rows </span>=<span style="color: #000000;"&gt; stmt.executeUpdate();

} </span><span style="color: #0000ff;"&gt;catch</span><span style="color: #000000;"&gt; (SQLException e) {
    </span><span style="color: #0000ff;"&gt;this</span><span style="color: #000000;"&gt;.rethrow(e,params);

} </span><span style="color: #0000ff;"&gt;finally</span><span style="color: #000000;"&gt; {
    close(stmt);
    </span><span style="color: #0000ff;"&gt;if</span><span style="color: #000000;"&gt; (closeConn) {
        close(conn);
    }
}

</span><span style="color: #0000ff;"&gt;return</span><span style="color: #000000;"&gt; rows;

}

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

delBooks(String[] ids) = = Object[ids.length][]; ( i = 0; i < params.length; i++= Object[]{ids[i]}; "delete from books where id=?"

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

[] batch(String sql,Object[][] params) = return this.batch(conn,params);

}

private int[] batch(Connection conn,Object[][] params) throws SQLException {
if (conn == null) {
throw new SQLException("Null connection");
}

</span><span style="color: #0000ff;"&gt;if</span> (sql == <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;) {
    </span><span style="color: #0000ff;"&gt;if</span><span style="color: #000000;"&gt; (closeConn) {
        close(conn);
    }
    </span><span style="color: #0000ff;"&gt;throw</span> <span style="color: #0000ff;"&gt;new</span> SQLException("Null SQL statement"<span style="color: #000000;"&gt;);
}

</span><span style="color: #0000ff;"&gt;if</span> (params == <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;) {
    </span><span style="color: #0000ff;"&gt;if</span><span style="color: #000000;"&gt; (closeConn) {
        close(conn);
    }
    </span><span style="color: #0000ff;"&gt;throw</span> <span style="color: #0000ff;"&gt;new</span> SQLException("Null parameters. If parameters aren't need,pass an empty array."<span style="color: #000000;"&gt;);
}

PreparedStatement stmt </span>= <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;;
</span><span style="color: #0000ff;"&gt;int</span>[] rows = <span style="color: #0000ff;"&gt;null</span><span style="color: #000000;"&gt;;
</span><span style="color: #0000ff;"&gt;try</span><span style="color: #000000;"&gt; {
    stmt </span>= <span style="color: #0000ff;"&gt;this</span><span style="color: #000000;"&gt;.prepareStatement(conn,sql);

    </span><span style="color: #0000ff;"&gt;for</span> (<span style="color: #0000ff;"&gt;int</span> i = 0; i < params.length; i++<span style="color: #000000;"&gt;) {
        </span><span style="color: #0000ff;"&gt;this</span><span style="color: #000000;"&gt;.fillStatement(stmt,params[i]);
        stmt.addBatch();
    }
    rows </span>=<span style="color: #000000;"&gt; stmt.executeBatch();

} </span><span style="color: #0000ff;"&gt;catch</span><span style="color: #000000;"&gt; (SQLException e) {
    </span><span style="color: #0000ff;"&gt;this</span><span style="color: #000000;"&gt;.rethrow(e,(Object[])params);
} </span><span style="color: #0000ff;"&gt;finally</span><span style="color: #000000;"&gt; {
    close(stmt);
    </span><span style="color: #0000ff;"&gt;if</span><span style="color: #000000;"&gt; (closeConn) {
        close(conn);
    }
}

</span><span style="color: #0000ff;"&gt;return</span><span style="color: #000000;"&gt; rows;

}

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

PreparedStatement * object's batch of commands. * * PreparedStatement * addBatch() SQLException;

一看时间这么晚了,QueryRunner暂时就这么多了,关于QueryRunner的用法自己挖掘的还不够透彻,写在这里当做记录和交流. 2016/05/24.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!