dbutils源码阅读

在查询或者更新数据的时候我们都容易犯忘记关闭JDBC的错误,spring之类的框架又非常笨重，DbUtils 是一个非常轻量的操作JDBC的工具。
除了操作简便之外，dbutils还可以将ResultSet转换成不同的对象。

使用示例

/*
* @param dataNode 相应的数据节点的信息
* @return 返回QueryRunner实例
* @throws java.lang.NullPointerException 如果连接池并没有初始化，则抛出此异常
* */
public static QueryRunner getQueryRunner(final String dataNode) {
    return new QueryRunner(MySQLHelper._DATASOURCEMAP.get(dataNode));
}

其中_DATASOURCEMAP数组存放的是线程池,初始化_DATASOURCEMAP:

private static final Map<String, DataSource> _DATASOURCEMAP = new ConcurrentHashMap<>();
...
for (final Map<String, Object> row : (List<Map<String, Object>>) config.get("node")) {
    //System.out.println(row.get("aliasName"));
    _CONFIG.put(row.get("aliasName").toString(), row);  /*把节点缓存*/
    _DATASOURCEMAP.put(row.get("aliasName").toString(), DruidDataSourceFactory.createDataSource(row));
}

这部分有个druid的线程池的部分，关于druid的可以参考博客中的druid源码阅读。

其中调用queryrunner实例的部分:

final QueryRunner qr = MySQLHelper.getQueryRunner();
final List<Map<String, Object>> result = qr.query(sql, new MapListHandler());

进入正题

重要的几个类:

• DbUtils 工具类

  定义了close、加载驱动等静态方法。

• QueryRunner

  查询的入口类，对应的AsyncQueryRunner是异步的

• query,执行查询sql

  private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
          throws SQLException {
         ...
      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;
  }

• update 方法执行更新操作SQL:

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

• insert方法，执行插入操作SQL:

   
  try {
      stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
      this.fillStatement(stmt, params);
      stmt.executeUpdate();
      ResultSet resultSet = stmt.getGeneratedKeys();
      generatedKeys = rsh.handle(resultSet);
  } catch (SQLException e) {
      this.rethrow(e, sql, params);
  } finally {
      close(stmt);
      if (closeConn) {
          close(conn);
      }
  }

• insertBatch，批量插入：

  try {
      stmt = this.prepareStatement(conn, sql, Statement.RETURN_GENERATED_KEYS);
   
      for (int i = 0; i < params.length; i++) {
          this.fillStatement(stmt, params[i]);
          stmt.addBatch();
      }
      stmt.executeBatch();
      ResultSet rs = stmt.getGeneratedKeys();
      generatedKeys = rsh.handle(rs);
   
  } catch (SQLException e) {
      this.rethrow(e, sql, (Object[])params);
  } finally {
      close(stmt);
      if (closeConn) {
          close(conn);
      }
  }

• batch，批量更新：

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

• ResultSetHandler

  接口，负责把ResultSet的结果转换成指定的对象

/**
* Implementations of this interface convert ResultSets into other objects.
*
* @param <T> the target type the input ResultSet will be converted to.
*/
public interface ResultSetHandler<T> {
 
    /**
     * Turn the <code>ResultSet</code> into an Object.
     *
     * @param rs The <code>ResultSet</code> to handle.  It has not been touched
     * before being passed to this method.
     *
     * @return An Object initialized with <code>ResultSet</code> data. It is
     * legal for implementations to return <code>null</code> if the
     * <code>ResultSet</code> contained 0 rows.
     *
     * @throws SQLException if a database access error occurs
     */
    T handle(ResultSet rs) throws SQLException; //转换操作
}

比如比较常用的

• MapHandler是将结果集的数据封装到了Map里，其中key是列名，Value是对应的值。

public class MapHandler implements ResultSetHandler<Map<String, Object>> {
    @Override
    public Map<String, Object> handle(ResultSet rs) throws SQLException {
        return rs.next() ? this.convert.toMap(rs) : null;
    }
}

• MapListHandler 将结果集的每一行都存到map里，然后再存放到List中。

  public class MapListHandler extends AbstractListHandler<Map<String, Object>> {
      @Override
      protected Map<String, Object> handleRow(ResultSet rs) throws SQLException {
          return this.convert.toMap(rs);
      }
  }

两者的转换行数据的方法都是toMap，关键在于，MapHandler扩展的是ResultSetHandler,

MapListHanddler扩展的是AbstractListHandler:

@Override
public List<T> handle(ResultSet rs) throws SQLException {
    List<T> rows = new ArrayList<T>();
    while (rs.next()) {
        rows.add(this.handleRow(rs));
    }
    return rows;
}

• RowProcessor

  为ResultSetHandler转换行数据。比如
  toArray:

  @Override
  public Object[] toArray(ResultSet rs) throws SQLException {
      ResultSetMetaData meta = rs.getMetaData();
      int cols = meta.getColumnCount();
      Object[] result = new Object[cols];
   
      for (int i = 0; i < cols; i++) {
          result[i] = rs.getObject(i + 1);
      }
   
      return result;
  }

toMap:

@Override
public Map<String, Object> toMap(ResultSet rs) throws SQLException {
    Map<String, Object> result = new CaseInsensitiveHashMap();
    ResultSetMetaData rsmd = rs.getMetaData();
    int cols = rsmd.getColumnCount();
 
    for (int i = 1; i <= cols; i++) {
        String columnName = rsmd.getColumnLabel(i);
        if (null == columnName || 0 == columnName.length()) {
          columnName = rsmd.getColumnName(i);
        }
        result.put(columnName, rs.getObject(i));
    }
 
    return result;
}

参考资料

PreparedStatement详解
Apache Commons DbUtils 源码分析 | Apache Commons DbUtils Source Code Analysis