mybatis批量插入

1、mybatis使用foreach批量插入list集合中的对象

【这里有个小坑，坑了我两个小时，批量插入一个list，insert into xxx() values()，values后面的括号()要写到foreach标签里面，直接写values后面（）或在foreach语法上加open="(" colse=")"都没用，会报一个错 Column count doesn't match value count at row 1】

 

批量插入的代码：

 insert into t_task
        (
        id, title, positionId,
          province, city, area,
          comeDate, isComeDate, startDate,
          endDate, isWorkDate, uid,
          isSex, terminate, needNum,
          number, price, timeType,
          trusteeship, isHot, address,
          createDate, content
        )
        VALUES
        <foreach collection="taskList" item="t" index="index" separator=",">
            (
            #{t.id,jdbcType=INTEGER}, #{t.title,jdbcType=VARCHAR}, #{t.positionId,jdbcType=INTEGER},
            #{t.province,jdbcType=INTEGER}, #{t.city,jdbcType=INTEGER}, #{t.area,jdbcType=INTEGER},
            #{t.comeDate,jdbcType=TIMESTAMP}, #{t.isComeDate,jdbcType=INTEGER}, #{t.startDate,jdbcType=TIMESTAMP},
            #{t.endDate,jdbcType=TIMESTAMP}, #{t.isWorkDate,jdbcType=INTEGER}, #{t.uid,jdbcType=VARCHAR},
            #{t.isSex,jdbcType=INTEGER}, #{t.terminate,jdbcType=INTEGER}, #{t.needNum,jdbcType=INTEGER},
            #{t.number,jdbcType=INTEGER}, #{t.price,jdbcType=DECIMAL}, #{t.timeType,jdbcType=VARCHAR},
            #{t.trusteeship,jdbcType=INTEGER}, #{t.isHot,jdbcType=INTEGER}, #{t.address,jdbcType=VARCHAR},
            #{t.createDate,jdbcType=TIMESTAMP}, #{t.content,jdbcType=LONGVARCHAR}
            )
        </foreach>

 

 

2、在做查询时，要批量查询一个数组中的元素则，可以写open="(", colse=")"，亲测

<if test="terminates != null and terminates.length > 0">
    AND tt.terminate IN
    <foreach collection="terminates" item="terminate" index="index" open="(" close=")" separator=",">
        #{terminate}
    </foreach>
</if>

 

 

 

3、批量修改：dao层和对应的mapper.xml

void deleteBatchIdsStockLog(@Param("stockLogIds") List<Long> stockLogIds);

<update id="deleteBatchIdsStockLog" >
        update ic_stock_log_v1 set dr = 1
        where id in
        <foreach collection="stockLogIds" item="item" index="index" open="(" separator="," close=")">
            #{item, jdbcType=BIGINT}
        </foreach>

    </update>