mybatis 批量语句写法

ps：本文暂不涉及mybaits批处理操作，只说明语句写法。

参考文章：

最详细的MyBatis批量添加、更新、删除实战篇（日常开发必看）
mybatis 多条件多值批量更新

批量插入

<!--批量插入数据-->
<insert id="insert" parameterType="java.util.List">
    INSERT INTO table_name(id, name, age,update_time)
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (SEQ_USER.NEXTVAL,#{item.name},#{item.age},sysdate)
    </foreach>
</insert>

使用oracle数据库时此处尽量避免如下获取序列值的方法

<selectKey keyProperty="id" order="BEFORE" resultType="java.lang.Long">
  select XXX.nextval from dual
</selectKey>

oracle 批量插入与mysql 的批量插入的方式不同 下面展示Oracle的写法
第一种

<insert id="batchInsert" parameterType="List" >
    insert into T_HEALTH_QUESTIONNAIRE_BODY
    (HEALTH_QUESTIONNAIRE_BODY_ID, HEALTH_QUESTIONNAIRE_ID, SERIAL_NO, QUESTION_MODULE, 
      QUESTION_MODULE_DETAIL, "RESULT", CHECK_DOC_CODE, CHECK_TIME, CREATE_CODE, CREATE_TIME, 
      UPDATE_CODE, UPDATE_TIME, INVALID_FLAG, HOS_CODE)
    select SEQ_HEALTH_QUESTIONNAIRE_BODY.nextval,A.* from (
    <foreach collection="list" item="item" separator="union all">
      select  #{item.healthQuestionnaireId,jdbcType=BIGINT},
        #{item.serialNo,jdbcType=BIGINT}, #{item.questionModule,jdbcType=VARCHAR}, #{item.questionModuleDetail,jdbcType=VARCHAR}, 
        #{item.result,jdbcType=VARCHAR}, #{item.checkDocCode,jdbcType=BIGINT}, #{item.checkTime,jdbcType=VARCHAR}, 
        #{item.createCode,jdbcType=BIGINT}, #{item.createTime,jdbcType=VARCHAR}, #{item.updateCode,jdbcType=BIGINT}, 
        #{item.updateTime,jdbcType=VARCHAR}, #{item.invalidFlag,jdbcType=INTEGER}, #{item.hosCode,jdbcType=VARCHAR}
         from dual
    </foreach>
    ) A
  </insert>

第二种 不使用序列可以采用下面这种方案

<insert id="batchInsertTwo" parameterType="List">
    INSERT ALL
    <foreach collection="list" item="item" index="index">
      into T_HEALTH_QUESTIONNAIRE_BODY
      (HEALTH_QUESTIONNAIRE_BODY_ID, HEALTH_QUESTIONNAIRE_ID, SERIAL_NO, QUESTION_MODULE,
      QUESTION_MODULE_DETAIL, "RESULT", CHECK_DOC_CODE, CHECK_TIME, CREATE_CODE, CREATE_TIME,
      UPDATE_CODE, UPDATE_TIME, INVALID_FLAG, HOS_CODE) VALUES
      (#{item.healthQuestionnaireBodyId,jdbcType=BIGINT},#{item.healthQuestionnaireId,jdbcType=BIGINT},
      #{item.serialNo,jdbcType=BIGINT}, #{item.questionModule,jdbcType=VARCHAR}, #{item.questionModuleDetail,jdbcType=VARCHAR},
      #{item.result,jdbcType=VARCHAR}, #{item.checkDocCode,jdbcType=BIGINT}, #{item.checkTime,jdbcType=VARCHAR},
      #{item.createCode,jdbcType=BIGINT}, #{item.createTime,jdbcType=VARCHAR}, #{item.updateCode,jdbcType=BIGINT},
      #{item.updateTime,jdbcType=VARCHAR}, #{item.invalidFlag,jdbcType=INTEGER}, #{item.hosCode,jdbcType=VARCHAR})
    </foreach>
    select 1 from dual
  </insert>

第三种方法

<insert id="addBatch" parameterType="java.util.List">  
    BEGIN  
   <foreach collection="list" item="item" index="index" separator="">  
        insert into blacklist  
        (id, userid, deviceid, createdate, updatedate, "LEVEL")  
        VALUES  
        ( USER_INFO_SEQ.NEXTVAL,#{item.userId,jdbcType=INTEGER},#{item.deviceId,jdbcType=VARCHAR},  
        #{item.createDate,jdbcType=DATE},sysdate, #{item.level,jdbcType=INTEGER} );  
    </foreach>  
    COMMIT;  
    END; 
 </insert>  

批量修改

单个条件批量更新

<update id="updateBatch" parameterType="java.util.List">
        update mydata_table
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="status =case" suffix="end,">
                 <foreach collection="list" item="item" index="index">
                     <if test="item.status !=null ">
                         when id=#{item.id} then #{item.status}
                     </if>                    
                 </foreach>
            </trim>
        </trim>
        where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id,jdbcType=BIGINT}
        </foreach>
    </update>

多条件多值批量更新

<update id="updateBatch" parameterType="java.util.List">
    update demo_table
    <trim prefix="set" suffixOverrides=",">
        status=
        <foreach collection="list" item="item" open="case " close=" end,">
            when field2=#{item.field2} and company_id=#{item.field3} then #{item.status}
        </foreach>
        create_time =
        <foreach collection="list" item="item" open="case " close=" end,">
          when field2=#{item.field2} and company_id=#{item.field3} then
          <choose>
            <when test="item.createTime!=null">
              #{item.createTime}
            </when>
            <otherwise>now()</otherwise>
          </choose>
        </foreach>
    </trim>
    WHERE
    <foreach collection="list" item="item" open="( " separator=") or (" close=" )">
      device_num=#{item.field2} and company_id=#{item.field3}
    </foreach>
  </update>

批量删除

<delete id="deleteBath" parameterType="java.util.Map">
    DELETE FROM t_employee WHERE id IN
    <foreach collection="ids" item="item" open="(" separator="," close=")">
        #{item.id}
    </foreach>
</delete>

int deleteBath(@Param("ids") Map<String, Integer> ids);

批量查询

<select id="findBath" resultType="com.dt.springbootdemo.entity.Employee" parameterType="com.dt.springbootdemo.entity.Employee">
    SELECT * FROM t_employee WHERE id IN
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item.id}
    </foreach>
</select>