本文共 8266 字，大约阅读时间需要 27 分钟。
前言:[本文属于原创分享文章, 转载请注明出处, 谢谢.]前面已经有文章说了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 selectAllBooks() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource()); return qr.query("select * from books", new BeanListHandler(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 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.class),list.toArray());
}
那么我们来看下源码的实现:
(1)QueryRunner.java
//第一种情况,无参数public T query(String sql, ResultSetHandler rsh) throws SQLException {
Connection conn = this.prepareConnection(); return this.query(conn, true, sql, rsh, (Object[]) null);
}//第二种和第三种使用同一方法: 需要参数public T query(String sql, ResultSetHandler 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 query(Connection conn, boolean closeConn, String sql, ResultSetHandler 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
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[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
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 PreparedStatement
* object's batch of commands.
*
* @exception SQLException if a database access error occurs or
* this method is called on a closed PreparedStatement
* @see Statement#addBatch
* @since 1.2 */
void addBatch() throws SQLException;
一看时间这么晚了, QueryRunner暂时就这么多了, 关于QueryRunner的用法自己挖掘的还不够透彻, 写在这里当做记录和交流. 2016/05/24.
转载地址：https://blog.csdn.net/weixin_30596151/article/details/115040037 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！
发表评论
最新留言
关于作者
