MyBatis 之 StatementHandler 来执行 SQL 语句

简介

使用原生的 JDBC API 来执行 SQL，需要经历加、连、语、执、释步骤。如下：

private static void query() throws Exception {
     // 加载驱动（可以不显示加载，因为引入 MySQL 驱动之后会通过 SPI自动加载）
     Class.forName("com.mysql.jdbc.Driver");
     // 获取连接
     Connection connection = DriverManager.getConnection("jdbc:mysql:///test", "root", "admin");
     // 预编译语句
     PreparedStatement statement = connection.prepareStatement("SELECT * FROM user WHERE id = ?");
     // 设置参数
     statement.setLong(1, 1);
     // 执行 SQL
     ResultSet resultSet = statement.executeQuery();
     ResultSetMetaData metaData = resultSet.getMetaData();
     int columnCount = metaData.getColumnCount();
     while (resultSet.next()) {
         for (int i = 1; i <= columnCount; i++) {
             String columnName = metaData.getColumnName(i);
             String columnValue = resultSet.getString(columnName);
             System.out.println(columnName + " : " + columnValue);
         }
     }
     // 关闭
     statement.close();
     connection.close();
 }

在使用 MyBatis 框架的时候我们无感知，并没有如上一系列操作。只需要编写 Mapper.xml 中的 SQL 语句即可。在前面我们已经学习了 SQL 语句抽象 和 根据实参获取 SQL 语句 ，现在我们学习在获取 SQL 之后用 StatementHandler 来处理 。

类图

• RoutingStatementHandler：如其类名具有路由功能，MyBatis 框架内部根据 MappedStatement 中的 StatementType 类型来获取对应的 PreparedStatementHandler、CallableStatementHandler 和 SimpleStatementHandler 实例。
• BaseStatementHandler：实现了 prepare 方法通用逻辑，通过定义 instantiateStatement 模板方法以获得不同的 Statement 实例并设置超时时间和从数据库获取数据数量。
• SimpleStatementHandler：对应 JDBC Statement（静态 SQL 语句）
• PreparedStatementHandler：对应 JDBC PreparedStatement（预编译 SQL 语句）
• CallableStatementHandler：对应 JDBC CallableStatement（存储过程）

方法解读

• Statement prepare(Connection connection, Integer transactionTimeout)：通过 Connection 实例获取 Statement 对象
• void parameterize(Statement statement)：为 Statement 对象设置参数，特别是 PreparedStatement 设置 ? 占位符对应的实参
• void batch(Statement statement)：添加批量数据
• int update(Statement statement)：通过 Statement 实例来执行修改（增、删、改）操作
• <E> List<E> query(Statement statement, ResultHandler resultHandler)：查询并处理结果集
• <E> List<E> queryCursor(Statement statement)：游标
• BoundSql getBoundSql()：获取底层关联的 SQL与参数元信息对象
• ParameterHandler getParameterHandler()：获取底层关联的参数处理器

RountingStatementHandler

MyBatis 框架内部使用此类来处理 Statement， 内部通过创建 SimpleStatementHandler、PreparedStatementHandler 和 CallableStatementHandler 对象进行正真的处理

	// 通过构造 RoutingStatementHandler 时候根据 StatementType 来创建对应的 StatementHandler
	// 在 Mapper.xml 中 insert/update/delete/select 标签中有 statementType 属性进行设置，默认是 PREPARED
	public RoutingStatementHandler(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
	
	  switch (ms.getStatementType()) {
	    case STATEMENT:
	      delegate = new SimpleStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
	      break;
	    case PREPARED:
	      delegate = new PreparedStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
	      break;
	    case CALLABLE:
	      delegate = new CallableStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
	      break;
	    default:
	      throw new ExecutorException("Unknown statement type: " + ms.getStatementType());
	  }
	
	}

BaseStatementHandler

StatementHandler 通用实现，如 getBoundSql、getParameterHandler 以及 prepare 方法，通过定义 instantiateStatement(Connection connection) 模板方法来获取不同类型的 Statment 实例。

public abstract class BaseStatementHandler implements StatementHandler {
  // MyBatis 全局配置对象
  protected final Configuration configuration;
  // 对象工厂
  protected final ObjectFactory objectFactory;
  // 类型处理器注册表
  protected final TypeHandlerRegistry typeHandlerRegistry;
  // 结果集处理器
  protected final ResultSetHandler resultSetHandler;
  // 参数处理器
  protected final ParameterHandler parameterHandler;
  // 执行器 
  protected final Executor executor;
  // XML insert/update/select/delete 标签所有元信息对象
  protected final MappedStatement mappedStatement;
  // 分页设置
  protected final RowBounds rowBounds;
  // SQL 语句和参数映射等元信息
  protected BoundSql boundSql;

  protected BaseStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
    this.configuration = mappedStatement.getConfiguration();
    this.executor = executor;
    this.mappedStatement = mappedStatement;
    this.rowBounds = rowBounds;

    this.typeHandlerRegistry = configuration.getTypeHandlerRegistry();
    this.objectFactory = configuration.getObjectFactory();

    if (boundSql == null) { 
      generateKeys(parameterObject);
      boundSql = mappedStatement.getBoundSql(parameterObject);
    }

    this.boundSql = boundSql;

    this.parameterHandler = configuration.newParameterHandler(mappedStatement, parameterObject, boundSql);
    this.resultSetHandler = configuration.newResultSetHandler(executor, mappedStatement, rowBounds, parameterHandler, resultHandler, boundSql);
  }
}

SimpleStatementHandler

简单语句处理，对应 JDBC Statement（静态 SQL），所有涉及数据库操作则通过调用 Statement API

public class SimpleStatementHandler extends BaseStatementHandler {

  public SimpleStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
    super(executor, mappedStatement, parameter, rowBounds, resultHandler, boundSql);
  }

  @Override
  public int update(Statement statement) throws SQLException {
    // 通过 BoundSql 获取 SQL 语句，然后调用 JDBC Statement API 执行操作
    // 根据主键生成策略不同回填主键值
    String sql = boundSql.getSql();
    Object parameterObject = boundSql.getParameterObject();
    KeyGenerator keyGenerator = mappedStatement.getKeyGenerator();
    int rows;
    if (keyGenerator instanceof Jdbc3KeyGenerator) {
      statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
      rows = statement.getUpdateCount();
      keyGenerator.processAfter(executor, mappedStatement, statement, parameterObject);
    } else if (keyGenerator instanceof SelectKeyGenerator) {
      statement.execute(sql);
      rows = statement.getUpdateCount();
      keyGenerator.processAfter(executor, mappedStatement, statement, parameterObject);
    } else {
      statement.execute(sql);
      rows = statement.getUpdateCount();
    }
    return rows;
  }

  @Override
  public void batch(Statement statement) throws SQLException {
    String sql = boundSql.getSql();
    statement.addBatch(sql);
  }

  @Override
  public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
  	// 通过 BoundSql 获取 SQL 语句，然后调用 JDBC Statement API 执行操作并对结果集进行处理
    String sql = boundSql.getSql();
    statement.execute(sql);
    return resultSetHandler.handleResultSets(statement);
  }

  @Override
  public <E> Cursor<E> queryCursor(Statement statement) throws SQLException {
  // 通过 BoundSql 获取 SQL 语句，然后调用 JDBC Statement API 执行操作并对结果集进行处理
    String sql = boundSql.getSql();
    statement.execute(sql);
    return resultSetHandler.handleCursorResultSets(statement);
  }

  @Override
  protected Statement instantiateStatement(Connection connection) throws SQLException {
  	// 通过原生 JDBC Connectio API 创建 Statement 对象设置相应的结果集类型
  	// select 标签可以设置 resultSetType 属性为 FORWARD_ONLY、SCROLL_INSENSITIVE 、SCROLL_SENSITIVE
    if (mappedStatement.getResultSetType() == ResultSetType.DEFAULT) {
      return connection.createStatement();
    } else {
      // 设置 select 标签里面的 resultSetType，并把结果集的并发模型设置为只读
      return connection.createStatement(mappedStatement.getResultSetType().getValue(), ResultSet.CONCUR_READ_ONLY);
    }
  }
 
  
  @Override
  public void parameterize(Statement statement) {
    // N/A
    // 静态 SQL 不存在设置参数空实现
  }

}

PreparedStatementHandler

public class PreparedStatementHandler extends BaseStatementHandler {

  public PreparedStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
    super(executor, mappedStatement, parameter, rowBounds, resultHandler, boundSql);
  }

  @Override
  public int update(Statement statement) throws SQLException {
  	// 调用 JDBC PreparedStatement  API 执行操作
    // 根据主键生成策略不同回填主键值
    PreparedStatement ps = (PreparedStatement) statement;
    ps.execute();
    int rows = ps.getUpdateCount();
    Object parameterObject = boundSql.getParameterObject();
    KeyGenerator keyGenerator = mappedStatement.getKeyGenerator();
    keyGenerator.processAfter(executor, mappedStatement, ps, parameterObject);
    return rows;
  }

  @Override
  public void batch(Statement statement) throws SQLException {
    PreparedStatement ps = (PreparedStatement) statement;
    ps.addBatch();
  }

  @Override
  public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
  	// 调用 JDBC PreparedStatement  API 执行操作
  	// 使用结果处理器对结果集进行处理
    PreparedStatement ps = (PreparedStatement) statement;
    ps.execute();
    return resultSetHandler.handleResultSets(ps);
  }

  @Override
  public <E> Cursor<E> queryCursor(Statement statement) throws SQLException {
  	// 调用 JDBC PreparedStatement  API 执行操作
  	// 使用结果处理器对结果集进行处理
    PreparedStatement ps = (PreparedStatement) statement;
    ps.execute();
    return resultSetHandler.handleCursorResultSets(ps);
  }

  @Override
  protected Statement instantiateStatement(Connection connection) throws SQLException {
    // 通过 BoundSql 获取 SQL 语句，通过 MappedStatement 对象获取主键生成策略以及主键列，
    // 然后调用 JDBC Connection API 获得 PreparedStatement 实例
    String sql = boundSql.getSql();
    if (mappedStatement.getKeyGenerator() instanceof Jdbc3KeyGenerator) {
      String[] keyColumnNames = mappedStatement.getKeyColumns();
      if (keyColumnNames == null) {
        return connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
      } else {
        return connection.prepareStatement(sql, keyColumnNames);
      }
    } else if (mappedStatement.getResultSetType() == ResultSetType.DEFAULT) {
      return connection.prepareStatement(sql);
    } else {
      return connection.prepareStatement(sql, mappedStatement.getResultSetType().getValue(), ResultSet.CONCUR_READ_ONLY);
    }
  }

  @Override
  public void parameterize(Statement statement) throws SQLException {
  	// 通过参数处理器对预编译中的 ? 占位符进行值设置
    parameterHandler.setParameters((PreparedStatement) statement);
  }

}

CallableStatementHandler

与上面的 PreparedStatementHandler 源码差不多，只是要设置存储过程输出参数。通过 JDBC 原生 Connection API 获取 CallableStatement 实例，然后通过该实例操作数据库。