[JavaWeb]关于DBUtils中QueryRunner的一些解读.
前言:
[本文属于原创分享文章, 转载请注明出处, 谢谢.]
前面已经有文章说了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来进行查询的方式:
@Test public 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); } } @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) { }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;
一看时间这么晚了, QueryRunner暂时就这么多了, 关于QueryRunner的用法自己挖掘的还不够透彻, 写在这里当做记录和交流. 2016/05/24.
