我们在保存数据操作时,可能会遇到批量插入的操作,有3中方法来操作该保存:
一、Mybatis插入数据,返回id
在sql语句上添加下面代码
useGeneratedKeys="true" keyProperty="id"
例如:
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
INSERT INTO t_user
(userName,sex)
values ("小明","男")
</insert>
二、批量插入
1、在 for循环中批量调用单次保存操作,sql语句简单,操作方便,但是效率比较低,因为要调用多次,mysql要执行多次insert操作。
2、利用Mybatis批量保存sql语句,service层只需要调用一次,就能完成多条数据插入操作,sql语句如下:
<insert id="batchInsertSelective" parameterType="java.util.List">
INSERT INTO 表名 (reason,shop_id,create_user) values
<foreach collection="list" item="record" index="index" separator=",">
<trim prefix=" (" suffix=")" suffixOverrides="," >
#{record.reason,jdbcType=VARCHAR},
#{record.shopId,jdbcType=BIGINT},
#{record.createUser,jdbcType=VARCHAR},
</trim>
</foreach>
</insert>
3、如果是有条件的批量操作呢,只保存有值的字段呢,又该如何写,sql语句如下:
<insert id="batchInsertSelective" parameterType="java.util.List">
INSERT INTO 表名
<foreach collection="list" item="record" index="index" separator=",">
<if test="index == 0">
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="record.reason != null"> reason,</if>
<if test="record.shopId != null"> shop_id,</if>
<if test="record.createUser != null"> create_user,</if>
</trim>
values
</if>
<trim prefix=" (" suffix=")" suffixOverrides="," >
<if test="record.reason!=null">#{record.reason,jdbcType=VARCHAR},</if>
<if test="record.shopId!=null">#{record.shopId,jdbcType=BIGINT},</if>
<if test="record.createUser!=null">#{record.createUser,jdbcType=VARCHAR},</if>
</trim>
</foreach>
</insert>