Mybatis 批量插入修改时返回自增主键

Question

我的开发环境

pom 文件中 springboot 的版本号是

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.4.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

mybatis 版本号是

 <dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
 </dependency>

Mybatis 的xml 文件中的SQL

传入一个List类型，可以批量插入新数据或者修改（根据自增主键 iid 判断，如果是NULL则新增，如果非空则修改）的代码如下，下面使用了 useGeneratedkeys="true" keyProperty="iid" keyColumn="iid"

<insert id="saveBatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="iid" keyColumn="iid">
 <foreach collection ="list" item="ele" index= "index" separator =";">
 insert into order_detail
    <trim prefix="(" suffix=")" suffixOverrides=",">
 <if test="ele.iid != null"> iid, </if>
 <if test="ele.foreign_iid != null"> foreign_iid, </if>
 <if test="ele.goods_sid != null"> goods_sid, </if>
 <if test="ele.goods_name != null"> goods_name, </if>
 <if test="ele.specification != null"> specification, </if>
 <if test="ele.unit != null"> unit, </if>
 <if test="ele.qty != null"> qty, </if>
 <if test="ele.quoted_price != null"> quoted_price, </if>
 <if test="ele.tax_price != null"> tax_price, </if>
 <if test="ele.pricetax_money != null"> pricetax_money, </if>
 <if test="ele.tax_ratio != null"> tax_ratio, </if>
 <if test="ele.tax_money != null"> tax_money, </if>
 <if test="ele.notax_price != null"> notax_price, </if>
 <if test="ele.notax_money != null"> notax_money, </if>
 <if test="ele.discount_ratio != null"> discount_ratio, </if>
 <if test="ele.discount_money != null"> discount_money, </if>
 <if test="ele.preship_date != null"> preship_date, </if>
 <if test="ele.remark != null"> remark, </if>
 <if test="ele.last_date != null"> last_date, </if>
 <if test="ele.last_operatorsid != null"> last_operatorsid, </if>
 <if test="ele.last_operatorname != null"> last_operatorname, </if>
 <if test="ele.create_time != null"> create_time, </if>
 </trim> values
    <trim prefix="(" suffix=")" suffixOverrides=",">
 <if test="ele.iid != null"> #{ele.iid},</if>
 <if test="ele.foreign_iid != null"> #{ele.foreign_iid},</if>
 <if test="ele.goods_sid != null"> #{ele.goods_sid},</if>
 <if test="ele.goods_name != null"> #{ele.goods_name},</if>
 <if test="ele.specification != null"> #{ele.specification},</if>
 <if test="ele.unit != null"> #{ele.unit},</if>
 <if test="ele.qty != null"> #{ele.qty},</if>
 <if test="ele.quoted_price != null"> #{ele.quoted_price},</if>
 <if test="ele.tax_price != null"> #{ele.tax_price},</if>
 <if test="ele.pricetax_money != null"> #{ele.pricetax_money},</if>
 <if test="ele.tax_ratio != null"> #{ele.tax_ratio},</if>
 <if test="ele.tax_money != null"> #{ele.tax_money},</if>
 <if test="ele.notax_price != null"> #{ele.notax_price},</if>
 <if test="ele.notax_money != null"> #{ele.notax_money},</if>
 <if test="ele.discount_ratio != null"> #{ele.discount_ratio},</if>
 <if test="ele.discount_money != null"> #{ele.discount_money},</if>
 <if test="ele.preship_date != null"> #{ele.preship_date},</if>
 <if test="ele.remark != null"> #{ele.remark},</if>
 <if test="ele.last_date != null"> #{ele.last_date},</if>
 <if test="ele.last_operatorsid != null"> #{ele.last_operatorsid},</if>
 <if test="ele.last_operatorname != null"> #{ele.last_operatorname},</if>
 <if test="ele.create_time != null"> #{ele.create_time},</if>
 </trim> ON DUPLICATE KEY UPDATE
    <trim suffixOverrides=",">
 <if test="ele.foreign_iid != null"> foreign_iid = #{ele.foreign_iid}, </if>
 <if test="ele.goods_sid != null"> goods_sid = #{ele.goods_sid}, </if>
 <if test="ele.goods_name != null"> goods_name = #{ele.goods_name}, </if>
 <if test="ele.specification != null"> specification = #{ele.specification}, </if>
 <if test="ele.unit != null"> unit = #{ele.unit}, </if>
 <if test="ele.qty != null"> qty = #{ele.qty}, </if>
 <if test="ele.quoted_price != null"> quoted_price = #{ele.quoted_price}, </if>
 <if test="ele.tax_price != null"> tax_price = #{ele.tax_price}, </if>
 <if test="ele.pricetax_money != null"> pricetax_money = #{ele.pricetax_money}, </if>
 <if test="ele.tax_ratio != null"> tax_ratio = #{ele.tax_ratio}, </if>
 <if test="ele.tax_money != null"> tax_money = #{ele.tax_money}, </if>
 <if test="ele.notax_price != null"> notax_price = #{ele.notax_price}, </if>
 <if test="ele.notax_money != null"> notax_money = #{ele.notax_money}, </if>
 <if test="ele.discount_ratio != null"> discount_ratio = #{ele.discount_ratio}, </if>
 <if test="ele.discount_money != null"> discount_money = #{ele.discount_money}, </if>
 <if test="ele.preship_date != null"> preship_date = #{ele.preship_date}, </if>
 <if test="ele.remark != null"> remark = #{ele.remark}, </if>
 <if test="ele.last_date != null"> last_date = #{ele.last_date}, </if>
 <if test="ele.last_operatorsid != null"> last_operatorsid = #{ele.last_operatorsid}, </if>
 <if test="ele.last_operatorname != null"> last_operatorname = #{ele.last_operatorname}, </if>
 <if test="ele.create_time != null"> create_time = #{ele.create_time}, </if>
 </trim> </foreach></insert>

我的测试结果

创建有2个实例构成的 list（iid字段上是空，那么表示会新增两行数据到表中并且会为这两个实例自动填充iid） 调用放 saveBatch() 结果只有第一条的 iid 被填充了自增后的数值，第二个对象的 iid 是空
是哪里的问题导致只有第一条可以获得自增后的返回值？
我想要 list 中的多个对象实例都能获取 iid 自增后的返回值要怎么做

Answer 1

没做实验，但是从batchExcutor的源码中可以知道，他是会循环执行的，如果你的数据是在插入之后获取的id 也就是mysql这一类的数据库
默认使用的是SimpleExecutor，自己去指定executor应该就行了