Mybatis 3中方法返回生成的主键：XML、@SelectKey、@Options

桃扇骨 2023-10-05 21:41 9阅读 0赞

### 需求 ###

在很多业务场景中，我们希望插入一条记录时就返回该记录的相关信息，返回主键显得尤为重要。

### 解决方案 ###

##### 1、 `XML` 中配置 #####

在定义xml映射器时设置属性useGeneratedKeys值为true，并分别指定属性keyProperty和keyColumn为对应的数据库记录主键字段与Java对象的主键属性。

<table> 
 <thead> 
  <tr> 
   <th>key</th> 
   <th>释意</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>useGeneratedKeys</td> 
   <td>取值范围true/false(默认值)，设置是否使用JDBC的getGenereatedKeys方法获取主键并赋值到keyProperty设置的领域模型属性中。MySQL和SQLServer执行auto-generated key field，因此当数据库设置好自增长主键后，可通过JDBC的getGeneratedKeys方法获取。但像Oralce等不支持auto-generated key field的数据库就不能用这种方法获取主键了</td> 
  </tr> 
  <tr> 
   <td>keyProperty</td> 
   <td>默认值unset，用于设置getGeneratedKeys方法或selectKey子元素返回值将赋值到领域模型的哪个属性中</td> 
  </tr> 
  <tr> 
   <td>keyColumn</td> 
   <td>设置数据表自动生成的主键名。对特定数据库（如PostgreSQL），若自动生成的主键不是第一个字段则必须设置</td> 
  </tr> 
 </tbody> 
</table>

**自增主键**

<insert id="insert" parameterType="com.xzbd.User" useGeneratedKeys=”true” keyProperty=”id”>
    　　insert into user (username,password,email,bio) values (#{username},#{password},#{email},#{bio})
    </insert>

**使用标签`selectKey`和`select LAST_INSERT_ID()`**

<insert id="inserUser2" parameterType="com.xzbd.User" >
            <selectKey keyProperty="user_id" order="AFTER" resultType="java.lang.Integer">
                select LAST_INSERT_ID()
            </selectKey> 
            insert into t_user(name,age) value(#{name},#{age})
        </insert>

**非自增，UUID**

<insert id="inserUser4" parameterType="com.xzbd.User" >
            <selectKey keyProperty="user_id" order="AFTER" resultType="java.lang.Integer">
                select uuid()
            </selectKey>
            insert into t_user(user_id,name,age) value(#{user_id},#{name},#{age})
        </insert>

##### 2、`Mapper`中使用`@SelectKey`或`@Options`注解配置 #####

`@SelectKey`注解的功能与 `<selectKey>`标签完全一致，用在已经被`@Insert` 、`@InsertProvider` 或 `@Update` 、`@UpdateProvider` 注解了的方法上。在未被上述四个注解的方法上作 `@SelectKey` 注解则视为无效。如果你指定了 `@SelectKey`注解，那么 `MyBatis` 就会忽略掉由 `@Options` 注解所设置的生成主键或设置（configuration）属性,即`@SelectKey`与 `@Options`不应同时使用。

**`@SelectKey`**

<table> 
 <thead> 
  <tr> 
   <th>属性</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>keyProperty</td> 
   <td>selectKey 语句结果应该被设置的目标属性。</td> 
  </tr> 
  <tr> 
   <td>resultType</td> 
   <td>结果的类型。MyBatis 通常可以算出来,但是写上也没有问题。MyBatis 允许任何简单类型用作主键的类型,包括字符串。</td> 
  </tr> 
  <tr> 
   <td>order</td> 
   <td>这可以被设置为 BEFORE 或 AFTER。如果设置为 BEFORE,那么它会首先选择主键,设置 keyProperty 然后执行插入语句。如果设置为 AFTER,那么先执行插入语句,然后是 selectKey 元素-这和如 Oracle 数据库相似,可以在插入语句中嵌入序列调用。</td> 
  </tr> 
  <tr> 
   <td>statementType</td> 
   <td>和前面的相 同,MyBatis 支持 STATEMENT ,PREPARED 和CALLABLE 语句的映射类型,分别代表 PreparedStatement 和CallableStatement 类型。</td> 
  </tr> 
 </tbody> 
</table>

示例如下：

@Insert("insert into user (name) values(#{name})")  
    @SelectKey(statement="select LAST_INSERT_ID()", keyProperty="id", before=false, resultType=int.class)  
    int insert(String name);

**`@Options`**  
该注解能更精细化的控制SQL执行，其代码如下：

@Retention(RetentionPolicy.RUNTIME)
    @Target({
        ElementType.METHOD})
    public @interface Options {
        
        boolean useCache() default true;
        boolean flushCache() default false;
        ResultSetType resultSetType() default ResultSetType.FORWARD_ONLY;
        StatementType statementType() default StatementType.PREPARED;
        int fetchSize() default -1;
        int timeout() default -1;
        boolean useGeneratedKeys() default false;
        String keyProperty() default "id";
        String keyColumn() default "";
    }

示例如下：

@InsertProvider(type = SqlProviderAdapter.class, method = "insert")
        @SelectKey(
                keyProperty = "record.id", before = false,
                resultType = Long.class,statement = {
        "select last_insert_id()"}
        )
        int insert(InsertStatementProvider<User> insertStatement);

##### 3、特别案例 `MyBatis Dynamic SQL` #####

官方文档中[Insert Statements][]描述如下

> MyBatis supports returning generated values from a single row insert, or a batch insert. In either case, it is simply a matter of configuring the insert mapper method appropriately. For example, to retrieve the value of a calculated column configure your mapper method like this:

...
        @InsertProvider(type=SqlProviderAdapter.class, method="insert")
        @Options(useGeneratedKeys=true, keyProperty="row.fullName")
        int insert(InsertStatementProvider<GeneratedAlwaysRecord> insertStatement);
    ...

> The important thing is that the keyProperty is set correctly. It should always be in the form row. where is the attribute of the record class that should be updated with the generated value.

**注意**

*  `keyProperty="row.fullName”` 中的`row`不一定正确，按照这个写后，会报错 `org.apache.ibatis.executor.ExecutorException: No setter found for the keyProperty 'xxx' in …… DefaultInsertStatementProvider`的异常，改为 `keyProperty="fullName“` 也会报异常，通过查看 `DefaultInsertStatementProvider` 源码，**最终通过代码为** `keyProperty="rocord.fullName”`

1.  批量插入  
    批量插入和单条插入差不多， `MyBatis Dynamic SQL`批量插入案例如下。

> MyBatis supports returning generated values from a multiple row insert statement with some limitations. The main limitation is that MyBatis does not support nested lists in parameter objects. Unfortunately, the MultiRowInsertStatementProvider relies on a nested List. It is likely this limitation in MyBatis will be removed at some point in the future, so stay tuned.

> Nevertheless, you can configure a mapper that will work with the MultiRowInsertStatementProvider as created by this library. The main idea is to decompose the statement from the parameter map and send them as separate parameters to the MyBatis mapper. For example:

...
        @InsertProvider(type=SqlProviderAdapter.class, method="insertMultipleWithGeneratedKeys")
        @Options(useGeneratedKeys=true, keyProperty="records.fullName")
        int insertMultipleWithGeneratedKeys(String insertStatement, @Param("records") List<GeneratedAlwaysRecord> records);
    
        default int insertMultipleWithGeneratedKeys(MultiRowInsertStatementProvider<GeneratedAlwaysRecord> multiInsert) {
        
            return insertMultipleWithGeneratedKeys(multiInsert.getInsertStatement(), multiInsert.getRecords());
        }
    ...

> The first method above shows the actual MyBatis mapper method. Note the use of the @Options annotation to specify that we expect generated values. Further, note that the keyProperty is set to records.fullName - in this case, fullName is a property of the objects in the records List. The library supplied adapter method will simply return the insertStatement as supplied in the method call. The adapter method requires that there be one, and only one, String parameter in the method call, and it assumes that this one String parameter is the SQL insert statement. The parameter can have any name and can be specified in any position in the method’s parameter list. The @Param annotation is not required for the insert statement. However, it may be specified if you so desire.

> The second method above decomposes the MultiRowInsertStatementProvider and calls the first method.

### 结论 ###

萝卜白菜各有所爱，选用那种方式凭客观喜好

--------------------

[Mybatis使用经验总结][Mybatis]

[Insert Statements]: https://mybatis.org/mybatis-dynamic-sql/docs/insert.html
[Mybatis]: https://editor.csdn.net/md/?articleId=117435100