mybatis SelectKey解析

mysql 文章 2022-07-20 10:07 302  0 全屏看文

小编 VIP3

AI助手支持GPT4.0

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测试及解析

测试代码

#mapperint 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属性进行设置

#mappervoid 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这个类的源码，这里就不细说啦