mybatis SelectKey解析_ZenDei技術網路在線

1.selectKey介紹及作用 <selectKey>標簽有如下屬性 resultType：sql返回的java類型 statementType：STATEMENT|PREPARED|CALLABLE三種預設PREPARED keyProperty：列名對應的java屬性名，可逗號分隔 keyCo ...

1.selectKey介紹及作用

<selectKey>標簽有如下屬性

resultType：sql返回的java類型

statementType：STATEMENT|PREPARED|CALLABLE三種預設PREPARED

keyProperty：列名對應的java屬性名，可逗號分隔

keyColumn：列名，可逗號分隔

order：BEFORE|AFTER，BEFORE表示<selectKey>里的sql先執行然後再把獲取到的值進行設置，AFTER則表示後執行，獲取自增主鍵並設置肯定是需要用AFTER的，畢竟先等主sql插入才能獲取到自增Id~

databaseId：資料庫Id一般不需要填

mybatis的<selectKey>標簽主要可以用來獲取自增主鍵id的值併進行設置，SELECT LAST_INSERT_ID() 該sql的作用返回最近一次插入的id通常用來配合<selectKey>標簽來使用 ,但要註意假如用insert同時插入多條sql，其只能返回插入的第一條記錄的自增主鍵id因此<selectKey>是不支持批量插入獲取主鍵值的

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測試代碼
public class Test {

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

    try (InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml")) {
      // 構建session工廠 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("自增主鍵userId:" + userDO.getUserId());
    }
  }

}

從輸出可以看到成功獲取到自增主鍵userId並已經設置到userDO參數對象中了，下麵來看看<selectKey>具體解析

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

當為<insert><delete><update>標簽時會調用此update方法，執行完sql後調用 keyGenerator.processAfter(executor, mappedStatement, ps, parameterObject); 而根據<selectKey>標簽解析出來的keyGenerator為SelectKeyGenerator，下麵具體分析下這個類它是怎麼獲取主鍵值並設置的。

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));
          // 將主鍵id的值設置到parameter參數中
          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 {
            // 若查詢的屬性有多個則分別設置
            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() + ".");
    }
  }
}

可以看到上述代碼會先查詢sql獲取返回結果之後再把值設置到參數對象中，但可以看到當查詢結果value.size() > 1的時候就會拋出異常，因此<selectKey>標簽中的sql返回行數不能大於1。從這邊也能看出<selectKey>不支持批量獲取主鍵值

3. useGeneratedKeys

那麼有什麼辦法可以獲取到批量插入的主鍵id呢，答案是有的可以使用<insert>標簽中的useGeneratedKeys、keyProperty、keyColumn屬性進行設置

#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>
  </insert>
  
#java測試代碼 
public class Test {

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

    try (InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml")) {
      // 構建session工廠 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("自增主鍵userId:" + Arrays.asList(userDO.getUserId(), userDO1.getUserId()));
    }
  }

}

輸出結果可以看到批量插入成功獲取到主鍵userId的值了，原理的話感興趣的同學可以去閱讀下Jdbc3KeyGenerator這個類的源碼，這裡就不細說啦