mybatis SelectKey解析

1.selectKey介绍及作用

resultType：sql返回的java类型

statementType：STATEMENT|PREPARED|CALLABLE三种默认PREPARED

keyProperty：列名对应的java属性名，可逗号分隔

keyColumn：列名，可逗号分隔

order：BEFORE|AFTER，BEFORE表示

databaseId：数据库Id一般不需要填

mybatis的

2.selectKey测试及解析

测试代码

#mapper
int insert(UserDO userDO);

#mapper.xml
  <insert id="insert">
     <selectkey keyproperty="userId" keycolumn="user_id" order="AFTER" resulttype="integer">
       select last_insert_id()
     </selectkey>
     insert into user(username, password, nickname)
     values(#{username}, #{password}, #{nickname})
  </insert>

#java&#x6D4B;&#x8BD5;&#x4EE3;&#x7801;
public class Test {

  public static void main(String[] args) throws IOException {

    try (InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml")) {
      // &#x6784;&#x5EFA;session&#x5DE5;&#x5382; DefaultSqlSessionFactory
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
      SqlSession sqlSession = sqlSessionFactory.openSession();
      UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
      UserDO userDO = new UserDO();
      userDO.setUsername("monian");
      userDO.setPassword("123");
      userDO.setNickname("monianx");
      userMapper.insert(userDO);
      System.out.println("&#x81EA;&#x589E;&#x4E3B;&#x952E;userId:" + userDO.getUserId());
    }
  }

}

从输出可以看到成功获取到自增主键userId并已经设置到userDO参数对象中了，下面来看看

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

当为

public class SelectKeyGenerator implements KeyGenerator {

  public static final String SELECT_KEY_SUFFIX = "!selectKey";
  private final boolean executeBefore;
  private final MappedStatement keyStatement;

  public SelectKeyGenerator(MappedStatement keyStatement, boolean executeBefore) {
    // 主sql前面执行还是后面执行
    this.executeBefore = executeBefore;
    this.keyStatement = keyStatement;
  }

  @Override
  public void processBefore(Executor executor, MappedStatement ms, Statement stmt, Object parameter) {
    if (executeBefore) {
      processGeneratedKeys(executor, ms, parameter);
    }
  }

  @Override
  public void processAfter(Executor executor, MappedStatement ms, Statement stmt, Object parameter) {
    if (!executeBefore) {
      processGeneratedKeys(executor, ms, parameter);
    }
  }

  // 处理生成的键
  private void processGeneratedKeys(Executor executor, MappedStatement ms, Object parameter) {
    try {
      if (parameter != null && keyStatement != null && keyStatement.getKeyProperties() != null) {
        // 获取需要设置的属性值 如id
        String[] keyProperties = keyStatement.getKeyProperties();
        final Configuration configuration = ms.getConfiguration();
        final MetaObject metaParam = configuration.newMetaObject(parameter);
        // Do not close keyExecutor.

        // The transaction will be closed by parent executor.

        Executor keyExecutor = configuration.newExecutor(executor.getTransaction(), ExecutorType.SIMPLE);
        // 查询sql如 select last_insert_id()获取主键id
        List<object> values = keyExecutor.query(keyStatement, parameter, RowBounds.DEFAULT, Executor.NO_RESULT_HANDLER);
        if (values.size() == 0) {
          throw new ExecutorException("SelectKey returned no data.");
        } else if (values.size() > 1) {
          throw new ExecutorException("SelectKey returned more than one value.");
        } else {
          MetaObject metaResult = configuration.newMetaObject(values.get(0));
          // &#x5C06;&#x4E3B;&#x952E;id&#x7684;&#x503C;&#x8BBE;&#x7F6E;&#x5230;parameter&#x53C2;&#x6570;&#x4E2D;
          if (keyProperties.length == 1) {
            if (metaResult.hasGetter(keyProperties[0])) {
              setValue(metaParam, keyProperties[0], metaResult.getValue(keyProperties[0]));
            } else {
              // no getter for the property - maybe just a single value object
              // so try that
              setValue(metaParam, keyProperties[0], values.get(0));
            }
          } else {
            // &#x82E5;&#x67E5;&#x8BE2;&#x7684;&#x5C5E;&#x6027;&#x6709;&#x591A;&#x4E2A;&#x5219;&#x5206;&#x522B;&#x8BBE;&#x7F6E;
            handleMultipleProperties(keyProperties, metaParam, metaResult);
          }
        }
      }
    } catch (ExecutorException e) {
      throw e;
    } catch (Exception e) {
      throw new ExecutorException("Error selecting key or setting result to parameter object. Cause: " + e, e);
    }
  }

  private void handleMultipleProperties(String[] keyProperties,
      MetaObject metaParam, MetaObject metaResult) {
    String[] keyColumns = keyStatement.getKeyColumns();

    if (keyColumns == null || keyColumns.length == 0) {
      // no key columns specified, just use the property names
      for (String keyProperty : keyProperties) {
        setValue(metaParam, keyProperty, metaResult.getValue(keyProperty));
      }
    } else {
      if (keyColumns.length != keyProperties.length) {
        throw new ExecutorException("If SelectKey has key columns, the number must match the number of key properties.");
      }
      for (int i = 0; i < keyProperties.length; i++) {
        setValue(metaParam, keyProperties[i], metaResult.getValue(keyColumns[i]));
      }
    }
  }

  private void setValue(MetaObject metaParam, String property, Object value) {
    if (metaParam.hasSetter(property)) {
      metaParam.setValue(property, value);
    } else {
      throw new ExecutorException("No setter found for the keyProperty '" + property + "' in " + metaParam.getOriginalObject().getClass().getName() + ".");
    }
  }
}</object>

可以看到上述代码会先查询sql获取返回结果之后再把值设置到参数对象中，但可以看到当查询结果value.size() > 1的时候就会抛出异常，因此

3. useGeneratedKeys

那么有什么办法可以获取到批量插入的主键id呢，答案是有的可以使用

#mapper
void batchInsert(@Param("userDOList") List<userdo> userDOList);

#mapper.xml
    insert into user(username, password, nickname)
    values
    <foreach collection="userDOList" item="userDO" separator=",">
      (#{userDO.username}, #{userDO.password}, #{userDO.nickname})
    </foreach>

#java&#x6D4B;&#x8BD5;&#x4EE3;&#x7801;
public class Test {

  public static void main(String[] args) throws IOException {

    try (InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml")) {
      // &#x6784;&#x5EFA;session&#x5DE5;&#x5382; DefaultSqlSessionFactory
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
      SqlSession sqlSession = sqlSessionFactory.openSession();
      UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
      UserDO userDO = new UserDO();
      userDO.setUsername("monian");
      userDO.setPassword("123");
      userDO.setNickname("monianx");
      UserDO userDO1 = new UserDO();
      userDO1.setUsername("monian");
      userDO1.setPassword("123");
      userDO1.setNickname("monianx");
      userMapper.batchInsert(Arrays.asList(userDO, userDO1));
      System.out.println("&#x81EA;&#x589E;&#x4E3B;&#x952E;userId:" + Arrays.asList(userDO.getUserId(), userDO1.getUserId()));
    }
  }

}</userdo>

输出结果可以看到批量插入成功获取到主键userId的值了，原理的话感兴趣的同学可以去阅读下Jdbc3KeyGenerator这个类的源码，这里就不细说啦

4.selectKey和useGeneratedKeys

最后谈谈笔者对这两个的理解，selectKey可以自定义查询的sql更加的灵活不单单只是获取自增主键但查询结果行数不能有多行否则会抛出异常，而useGeneratorKeys主要是获取自动生成主键且能支持多行支持批量插入获取主键值，至于在实际开发中使用哪一种就看业务需求是怎样的了。

Original: https://www.cnblogs.com/monianxd/p/16481802.html
Author: 默念x
Title: mybatis SelectKey解析