需求

在很多業(yè)務(wù)場景中，我們希望插入一條記錄時就返回該記錄的相關(guān)信息，返回主鍵顯得尤為重要。

解決方案

1、XML中配置

在定義xml映射器時設(shè)置屬性useGeneratedKeys值為true，并分別指定屬性keyProperty和keyColumn為對應(yīng)的數(shù)據(jù)庫記錄主鍵字段與Java對象的主鍵屬性。

自增主鍵

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

使用標(biāo)簽selectKey和select LAST_INSERT_ID()

　　 <!-- mysql的自增ID ：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>標(biāo)簽完全一致，用在已經(jīng)被@Insert 、@InsertProvider 或 @Update 、@UpdateProvider 注解了的方法上。

在未被上述四個注解的方法上作 @SelectKey 注解則視為無效。

如果你指定了 @SelectKey注解，那么 MyBatis 就會忽略掉由 @Options 注解所設(shè)置的生成主鍵或設(shè)置（configuration）屬性,即@SelectKey與 @Options不應(yīng)同時使用。

• @SelectKey

示例如下：

@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執(zhí)行，其代碼如下：

@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” 

4、批量插入

批量插入和單條插入差不多， 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.

結(jié)論

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論