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

Z时代
2024-01-10
分类：技术分享

我的开发环境

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 自增后的返回值要怎么做