02-dbutils源码之QueryRunner & 一个例子

最新推荐文章于 2021-02-24 08:03:29 发布

weixin_33916256

最新推荐文章于 2021-02-24 08:03:29 发布

阅读量166

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 java python

原文链接：https://my.oschina.net/u/198574/blog/163455

本文详细解析了DBUtils中的QueryRunner类，介绍了其构造方法、主要功能方法及其使用方式，并通过实例展示了如何利用QueryRunner进行数据库查询与更新操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

在01中讲了下AbstractQueryRunner的基本内容，对于JDBC比较熟悉的童鞋都可以很容易明白。接下来会分析下AbstractQueryRunner的子类QueryRunner。这个类是最常用的了。

      下面是类的继承关系：

下面是类的outline

在这outline中，我圈出了要分析的源码。重点在于分析当中的私有方法。因为私有方法是通用的。例如上图中我圈中第一个和第二个公开方法query()内部就是调用第三个私有方法query()。

下面看一下详细的说明

* Licensed to the Apache Software Foundation (ASF) under one or more

* contributor license agreements. See the NOTICE file distributed with

* this work for additional information regarding copyright ownership.

* The ASF licenses this file to You under the Apache License, Version 2.0

* (the "License"); you may not use this file except in compliance with

* the License. You may obtain a copy of the License at

* http://www.apache.org/licenses/LICENSE-2.0

* Unless required by applicable law or agreed to in writing, software

* distributed under the License is distributed on an "AS IS" BASIS,

* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

* See the License for the specific language governing permissions and

* limitations under the License.

package org. apache. commons. dbutils;

import java. sql. Connection;

import java. sql. PreparedStatement;

import java. sql. ResultSet;

import java. sql. SQLException;

import javax. sql. DataSource;

/**

* Executes SQL queries with pluggable strategies for handling

* <code>ResultSet</code>s. This class is thread safe.

* 执行SQL查询语句，使用策略模式对结果进行处理。此类是线程安全的

* 这个类关联到一个重要的接口：ResultSetHandler

* @see ResultSetHandler

public class QueryRunner extends AbstractQueryRunner {

/**

* Constructor for QueryRunner.

public QueryRunner() {

super();

}

/**

* Constructor for QueryRunner that controls the use of <code>ParameterMetaData</code>.

* @param pmdKnownBroken Some drivers don't support { @link java.sql.ParameterMetaData#getParameterType(int) };

* if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,

* and if it breaks, we'll remember not to use it again.

public QueryRunner( boolean pmdKnownBroken) {

super( pmdKnownBroken);

}

/**

* Constructor for QueryRunner that takes a <code>DataSource</code> to use.

* Methods that do not take a <code>Connection</code> parameter will retrieve connections from this

* <code>DataSource</code>.

* @param ds The <code>DataSource</code> to retrieve connections from.

public QueryRunner( DataSource ds) {

super( ds);

}

/**

* Constructor for QueryRunner that takes a <code>DataSource</code> and controls the use of <code>ParameterMetaData</code>.

* Methods that do not take a <code>Connection</code> parameter will retrieve connections from this

* <code>DataSource</code>.

* @param ds The <code>DataSource</code> to retrieve connections from.

* @param pmdKnownBroken Some drivers don't support { @link java.sql.ParameterMetaData#getParameterType(int) };

* if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it,

* and if it breaks, we'll remember not to use it again.

public QueryRunner( DataSource ds, boolean pmdKnownBroken) {

super( ds, pmdKnownBroken);

}

/**

* Execute a batch of SQL INSERT, UPDATE, or DELETE queries.

* 批量处理INSERT、UPDATE、DELETE

* @param conn The Connection to use to run the query. The caller is

* responsible for closing this Connection.

* @param sql The SQL to execute.

* @param params An array of query replacement parameters. Each row in

* this array is one set of batch replacement values.

* @return The number of rows updated per statement.

* @throws SQLException if a database access error occurs

* @since DbUtils 1.1

public int[] batch( Connection conn, String sql, Object[][] params) throws SQLException {

return this. batch( conn, false, sql, params);

}

/**

* Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The

* <code>Connection</code> is retrieved from the <code>DataSource</code>

* set in the constructor. This <code>Connection</code> must be in

* auto-commit mode or the update will not be saved.

* @param sql The SQL to execute.

* @param params An array of query replacement parameters. Each row in

* this array is one set of batch replacement values.

* @return The number of rows updated per statement.

* @throws SQLException if a database access error occurs

* @since DbUtils 1.1

public int[] batch( String sql, Object[][] params) throws SQLException {

Connection conn = this. prepareConnection();

return this. batch( conn, true, sql, params);

}

/**

* Calls update after checking the parameters to ensure nothing is null.

* @param conn The connection to use for the batch call.

* @param closeConn True if the connection should be closed, false otherwise.

* @param sql The SQL statement to execute.

* @param params An array of query replacement parameters. Each row in

* this array is one set of batch replacement values.

* @return The number of rows updated in the batch.

* @throws SQLException If there are database or parameter errors.

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;

}

/**

* Execute an SQL SELECT query with a single replacement parameter. The

* caller is responsible for closing the connection.

* @param <T> The type of object that the handler returns

* @param conn The connection to execute the query in.

* @param sql The query to execute.

* @param param The replacement parameter.

* @param rsh The handler that converts the results into an object.

* @return The object returned by the handler.

* @throws SQLException if a database access error occurs

* @deprecated Use { @link #query(Connection, String, ResultSetHandler, Object...)}

@ Deprecated

public < T > T query( Connection conn, String sql, Object param, ResultSetHandler < T > rsh) throws SQLException {

return this. < T > query( conn, false, sql, rsh, new Object[]{ param});

}

/**

* Execute an SQL SELECT query with replacement parameters. The

* caller is responsible for closing the connection.

* @param <T> The type of object that the handler returns

* @param conn The connection to execute the query in.

* @param sql The query to execute.

* @param params The replacement parameters.

* @param rsh The handler that converts the results into an object.

* @return The object returned by the handler.

* @throws SQLException if a database access error occurs

* @deprecated Use { @link #query(Connection,String,ResultSetHandler,Object...)} instead

@ Deprecated

public < T > T query( Connection conn, String sql, Object[] params, ResultSetHandler < T > rsh) throws SQLException {

return this. < T > query( conn, false, sql, rsh, params);

}

/**

* Execute an SQL SELECT query with replacement parameters. The

* caller is responsible for closing the connection.

* @param <T> The type of object that the handler returns

* @param conn The connection to execute the query in.

* @param sql The query to execute.

* @param rsh The handler that converts the results into an object.

* @param params The replacement parameters.

* @return The object returned by the handler.

* @throws SQLException if a database access error occurs

public < T > T query( Connection conn, String sql, ResultSetHandler < T > rsh, Object... params) throws SQLException {

return this. < T > query( conn, false, sql, rsh, params);

}

/**

* Execute an SQL SELECT query without any replacement parameters. The

* caller is responsible for closing the connection.

* @param <T> The type of object that the handler returns

* @param conn The connection to execute the query in.

* @param sql The query to execute.

* @param rsh The handler that converts the results into an object.

* @return The object returned by the handler.

* @throws SQLException if a database access error occurs

public < T > T query( Connection conn, String sql, ResultSetHandler < T > rsh) throws SQLException {

return this. < T > query( conn, false, sql, rsh, ( Object[]) null);

}

/**

* Executes the given SELECT SQL with a single replacement parameter.

* The <code>Connection</code> is retrieved from the

* <code>DataSource</code> set in the constructor.

* @param <T> The type of object that the handler returns

* @param sql The SQL statement to execute.

* @param param The replacement parameter.

* @param rsh The handler used to create the result object from

* the <code>ResultSet</code>.

* @return An object generated by the handler.

* @throws SQLException if a database access error occurs

* @deprecated Use { @link #query(String, ResultSetHandler, Object...)}

@ Deprecated

public < T > T query( String sql, Object param, ResultSetHandler < T > rsh) throws SQLException {

Connection conn = this. prepareConnection();

return this. < T > query( conn, true, sql, rsh, new Object[]{ param});

}

/**

* Executes the given SELECT SQL query and returns a result object.

* The <code>Connection</code> is retrieved from the

* <code>DataSource</code> set in the constructor.

* 这个方法使用的是数组参数，后来改为使用了可变参数

* @param <T> The type of object that the handler returns

* @param sql The SQL statement to execute.

* @param params Initialize the PreparedStatement's IN parameters with

* this array.

* @param rsh The handler used to create the result object from

* the <code>ResultSet</code>.

* @return An object generated by the handler.

* @throws SQLException if a database access error occurs

* @deprecated Use { @link #query(String, ResultSetHandler, Object...)}

@ Deprecated

public < T > T query( String sql, Object[] params, ResultSetHandler < T > rsh) throws SQLException {

Connection conn = this. prepareConnection();

return this. < T > query( conn, true, sql, rsh, params);

}

/**

* Executes the given SELECT SQL query and returns a result object.

* 执行给定的查询语句，返回一个结果对象

* The <code>Connection</code> is retrieved from the

* <code>DataSource</code> set in the constructor.

* 在方法内部：从数据源中获得链接

* @param <T> The type of object that the handler returns

* @param sql The SQL statement to execute.

* @param rsh The handler used to create the result object from

* the <code>ResultSet</code>.

* 此handler是用来对ResultSet进行处理的，使用了策略模式

* @param params Initialize the PreparedStatement's IN parameters with

* this array.

* @return An object generated by the handler.

* @throws SQLException if a database access error occurs

public < T > T query( String sql, ResultSetHandler < T > rsh, Object... params) throws SQLException {

//获得数据库连接。继承过来的方法。

Connection conn = this. prepareConnection();

return this. < T > query( conn, true, sql, rsh, params);

}

/**

* Executes the given SELECT SQL without any replacement parameters.

* 执行查询语句，这个查询语句没有使用到参数，例如：select * from t_user

* The <code>Connection</code> is retrieved from the

* <code>DataSource</code> set in the constructor.

* @param <T> The type of object that the handler returns

* @param sql The SQL statement to execute.

* @param rsh The handler used to create the result object from

* the <code>ResultSet</code>.

* @return An object generated by the handler.

* @throws SQLException if a database access error occurs

public < T > T query( String sql, ResultSetHandler < T > rsh) throws SQLException {

//获得数据库连接。继承过来的方法。

Connection conn = this. prepareConnection();

return this. < T > query( conn, true, sql, rsh, ( Object[]) null);

}

/**

* Calls query after checking the parameters to ensure nothing is null.

* @param conn The connection to use for the query call.

* @param closeConn True if the connection should be closed, false otherwise.

* @param sql The SQL statement to execute.

* @param params An array of query replacement parameters. Each row in

* this array is one set of batch replacement values.

* @return The results of the query.

* @throws SQLException If there are database or parameter errors.

private < T > T query( Connection conn, boolean closeConn, String sql, ResultSetHandler < T > rsh, Object... params)

throws SQLException {

//数据库连接为null，那么报错

if ( conn == null) {

throw new SQLException( "Null connection");

}

//SQL语句为null，报错。没语句，执行什么呢？

if ( sql == null) {

if ( closeConn) { //SQL语句都为空，要数据库连接干什么

close( conn);

}

throw new SQLException( "Null SQL statement");

}

//结果集为null，报错。

if ( rsh == null) {

if ( closeConn) {

close( conn);

}

throw new SQLException( "Null ResultSetHandler");

}

//定义一个PreparedStatement类型变量

PreparedStatement stmt = null;

//定义一个ResultSet类型变量

ResultSet rs = null;

T result = null; //泛型。用户最终要的结果

try {

//准备PreparedStatement对象，此方法是从父类继承过来的

stmt = this. prepareStatement( conn, sql);

//填充?占位符，此方法是从父类继承过来的

this. fillStatement( stmt, params);

//执行SQL语句。包装结果集，此方法是从父类继承过来的，其实wrap只是把传进去的RS又return了

rs = this. wrap( stmt. executeQuery());

/**

* 对结果集进行处理。策略模式。对于结果进行如何处理，交由用户处理。dbutils已经实现了常用的

* ResultSetHandler。用户可以实现自己的ResultSetHandler，只要实现接口

* ResultSetHandler即可

result = rsh. handle( rs);

} catch ( SQLException e) {

this. rethrow( e, sql, params);

} finally { //释放资源

try {

close( rs);

} finally {

close( stmt);

if ( closeConn) {

close( conn);

}

//返回用户要的结果。此结果就是ResultSetHandler中的handle方法返回的结果

return result;

}

/**

* Execute an SQL INSERT, UPDATE, or DELETE query without replacement

* parameters.

* 这里执行的是不带参数的SQL语句

* @param conn The connection to use to run the query.

* @param sql The SQL to execute.

* @return The number of rows updated.

* @throws SQLException if a database access error occurs

public int update( Connection conn, String sql) throws SQLException {

return this. update( conn, false, sql, ( Object[]) null);

}

/**

* Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement

* parameter.

* @param conn The connection to use to run the query.

* @param sql The SQL to execute.

* @param param The replacement parameter.

* @return The number of rows updated.

* @throws SQLException if a database access error occurs

public int update( Connection conn, String sql, Object param) throws SQLException {

return this. update( conn, false, sql, new Object[]{ param});

}

/**

* Execute an SQL INSERT, UPDATE, or DELETE query.

* @param conn The connection to use to run the query.

* @param sql The SQL to execute.

* @param params The query replacement parameters.

* @return The number of rows updated.

* @throws SQLException if a database access error occurs

public int update( Connection conn, String sql, Object... params) throws SQLException {

return update( conn, false, sql, params);

}

/**

* Executes the given INSERT, UPDATE, or DELETE SQL statement without

* any replacement parameters. The <code>Connection</code> is retrieved

* from the <code>DataSource</code> set in the constructor. This

* <code>Connection</code> must be in auto-commit mode or the update will

* not be saved.

* 执行给定的INSERT,UPDATE,或DELETE语句，语句中没有可替代的参数。Connection从数据源中获得。

* 这个连接必须为自动提交模式。，否则更新操作不会被保存。

* @param sql The SQL statement to execute.

* @throws SQLException if a database access error occurs

* @return The number of rows updated.

public int update( String sql) throws SQLException {

Connection conn = this. prepareConnection();

//执行，因为没使用到参数，所以给个null

return this. update( conn, true, sql, ( Object[]) null);

}

/**

* Executes the given INSERT, UPDATE, or DELETE SQL statement with

* a single replacement parameter. The <code>Connection</code> is

* retrieved from the <code>DataSource</code> set in the constructor.

* This <code>Connection</code> must be in auto-commit mode or the

* update will not be saved.

* 执行给定的INSERT,UPDATE,或DELETE语句，语句中有且仅有一个可替代的参数。Connection从数据源中获得。

* 这个连接必须为自动提交模式。，否则更新操作不会被保存。

* @param sql The SQL statement to execute.

* @param param The replacement parameter.

* @throws SQLException if a database access error occurs

* @return The number of rows updated.

public int update( String sql, Object param) throws SQLException {

Connection conn = this. prepareConnection();

return this. update( conn, true, sql, new Object[]{ param});

}

/**

* Executes the given INSERT, UPDATE, or DELETE SQL statement. The

* <code>Connection</code> is retrieved from the <code>DataSource</code>

* set in the constructor. This <code>Connection</code> must be in

* auto-commit mode or the update will not be saved.

* 执行给定的INSERT,UPDATE,或DELETE语句，语句中有多个可替代的参数。Connection从数据源中获得。

* 这个连接必须为自动提交模式。，否则更新操作不会被保存。

* @param sql The SQL statement to execute.

* @param params Initializes the PreparedStatement's IN (i.e. '?')

* parameters.

* @throws SQLException if a database access error occurs

* @return The number of rows updated.

public int update( String sql, Object... params) throws SQLException {

Connection conn = this. prepareConnection();

return this. update( conn, true, sql, params);

}

/**

* Calls update after checking the parameters to ensure nothing is null.

* 在调用update操作前，检查一下参数，确保没有为null的

* @param conn The connection to use for the update call.

* @param closeConn True if the connection should be closed, false otherwise.

* @param sql The SQL statement to execute.

* @param params An array of update replacement parameters. Each row in

* this array is one set of update replacement values.

* @return The number of rows updated.

* @throws SQLException If there are database or parameter errors.

private int update( Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {

//链接都没，怎么操作，报错得了

if ( conn == null) {

throw new SQLException( "Null connection");

}

//SQL都没，怎么操作，报错得了。closeConn为true，就关闭链接

if ( sql == null) {

if ( closeConn) {

//这里的close是从父类继承过来的

close( conn);

}

throw new SQLException( "Null SQL statement");

}

//定义一个PreparedStatement类型变量

PreparedStatement stmt = null;

//这里记录的是受影响的函数。如update了几行，insert了几行，delete了几行

int rows = 0;

try {

//获得prepareStatement变量，此方法是从父类继承过来的

stmt = this. prepareStatement( conn, sql);

//为prepareStatement中的参数赋值

this. fillStatement( stmt, params);

//执行SQL，返回受影响行数

rows = stmt. executeUpdate();

} catch ( SQLException e) {

this. rethrow( e, sql, params);

} finally { //释放资源

close( stmt);

if ( closeConn) {

close( conn);

}

//返回结果

return rows;

}

以私有的query()进行一下说明：
在JDBC中，我们通过Connection拿到PreparedStatement，就要为PreparedStatement进行设置参数。然后execute，返回了ResultSet，然后就要对ResultSet进行处理。
在DBUtils中，只不过是对各个步骤进行了封装。
获得PreparedStatement，就是this.prepareStatement(conn, sql);
为PreparedStatement进行设置参数：this.fillStatement(stmt, params);
对ResultSet进行处理：rsh.handle(rs);，rsh是ResultSetHandler接口类型的。rs就是ResultSet。

下面会通过一个例子，让大家了解dbutils的基本使用

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
1、数据库，数据库名是testdb

MySQL Data Transfer

Source Host: localhost

Source Database: testdb

Target Host: localhost

Target Database: testdb

Date: 2013/9/12 10:19:21

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for t_user

-- ----------------------------

DROP TABLE IF EXISTS `t_user`;

CREATE TABLE `t_user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(40) DEFAULT NULL,

`birthday` date DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records

-- ----------------------------

INSERT INTO `t_user` VALUES ('1', '黄锦成', '2013-09-11');

INSERT INTO `t_user` VALUES ('2', 'zxx', '2012-09-11');

2、jar包

3、数据源帮助类

/**

* 数据源帮助类。使用的是C3P0。

* @author Administrator

public class DataSourceUtil {

private static ComboPooledDataSource cpds = null;

static{

cpds = new ComboPooledDataSource();

try {

cpds. setDriverClass( "com.mysql.jdbc.Driver" );

cpds. setJdbcUrl( "jdbc:mysql:///testdb" );

cpds. setUser( "root");

cpds. setPassword( "123");

} catch ( PropertyVetoException e) {

throw new RuntimeException( e);

}

public static DataSource getDataSource(){

if( cpds == null){

throw new RuntimeException( "数据源为null");

}

return cpds;

}

4、javabean

import java. util. Date;

public class User {

private Integer id;

private String name;

private Date birthday;

public Integer getId() {

return id;

}

public void setId( Integer id) {

this. id = id;

}

public String getName() {

return name;

}

public void setName( String name) {

this. name = name;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday( Date birthday) {

this. birthday = birthday;

}

@ Override

public String toString() {

return "User [id=" + id + ", name=" + name + ", birthday=" + birthday

+ "]";

}

5、进行测试

这里进行的是update测试

package com. hjc. dbutils. test;

import java. util. Date;

import org. apache. commons. dbutils. QueryRunner;

import org. junit. Before;

import org. junit. Test;

import com. hjc. dbutils. utils. DataSourceUtil;

public class UpdateTest {

* 创建QueryRunner对象

QueryRunner queryRunner = null;

@ Before

public void setUp(){

queryRunner = new QueryRunner( DataSourceUtil. getDataSource());

}

* 调用QueryRunner的update方法

* update、insert、delete操作对于数据库来说，都是更新操作

* 所以QueryRunner中的update方法中的sql语句可以是update、insert、delete语句

@ Test

public void testUpdate() throws Exception{

String sql = "insert into t_user(name,birthday) values(?,?)";

Object[] params = new Object[]{ "hjc", new Date()};

queryRunner. update( sql, params);

}

这里进行的是query测试

package com. hjc. dbutils. test;

import java. util. Date;

import org. apache. commons. dbutils. QueryRunner;

import org. apache. commons. dbutils. ResultSetHandler;

import org. apache. commons. dbutils. handlers. BeanHandler;

import org. junit. Before;

import org. junit. Test;

import com. hjc. dbutils. domain. User;

import com. hjc. dbutils. utils. DataSourceUtil;

public class BeanHandlerTest {

QueryRunner queryRunner = null;

@ Before

public void setUp(){

queryRunner = new QueryRunner( DataSourceUtil. getDataSource());

}

@ Test

public void testBeanHandler() throws Exception{

String sql = "select * from t_user where id=?";

Object[] params = new Object[]{ 1};

ResultSetHandler < User > rsh = new BeanHandler < User >( User. class);

User userResult = queryRunner. query( sql, rsh, params);

System. out. println( userResult);

}

/**

* 当有多行时，使用BeanHandler，只会处理第一行记录

* @throws Exception

@ Test

public void testBeanHandler2() throws Exception{

String sql = "select * from t_user";

ResultSetHandler < User > rsh = new BeanHandler < User >( User. class);

User userResult = queryRunner. query( sql, rsh);

System. out. println( userResult);

}

BeanHandler是用于对一行记录进行处理的，它将一行记录转换成一个javabean对象。转换是如何实现的呢？它使用了反射技术。
javabean的属性和数据库表字段之间又是怎样映射的呢？我们在设计时，会把javabean属性和表字段设置为一样的。例如javabean中是id、name、birthday，那么在数据库表中的字段就是id、name、birthday。

上面使用的 BeanHandler 是 ResultSet 的一个实现类。

可以看到有个BeanListHandler，那么这个与BeanHandler什么关系呢？BeanHandler是将一行记录变成一个javabean对象，那么BeanListHandler就是将多行记录变成多个javabean对象，然后将这些javabean对象放入到一个List集合中。
BeanHandler是用来处理ResultSet中的第一行记录的，除了第一行记录，其他的都不处理。想处理多行的，那么就用BeanListHandler吧!

下面简单说下其他Handler的用法：
ArrayHandler：将一行记录变成一个数组，ArrayListHandler就是一个数组的List集合
MapHandler：将一行记录变成一个Map对象，列名为key，列值为value，MapListHandler就是一个Map对象的List集合

上面总共讲了6个Handler，都是比较常用的，之后的源码分析会重点进行讲解。

转载于:https://my.oschina.net/u/198574/blog/163455