Mybatis批量新增、更新、ON DUPLICATE

批量新增

  <insert id="insertBankAccountInfoBatch" parameterType="java.util.List">
    INSERT INTO `bank_account_info`
        (
        `company_id`,
        `account_no`,
        `account_name`
        )
    VALUES
    <foreach collection="list" item="item" separator=",">
        (
        #{item.companyId,jdbcType=INTEGER},
        #{item.accountNo,jdbcType=VARCHAR},
        #{item.accountName,jdbcType=VARCHAR}
        )
    </foreach>
  </insert>

批量更新

  <update id="updateBankAccountInfoBatch" parameterType="java.util.List">
    update bank_account_info
    <trim prefix="set" suffixOverrides=",">

      <trim prefix="company_id=case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          <if test="item.companyId !=null">
            when id = #{item.id,jdbcType=INTEGER} then #{item.companyId}
          </if>
          <if test="item.companyId == null">
            when id=#{item.id} then bank_account_info.company_id
          </if>
        </foreach>
      </trim>

      <trim prefix="account_name=case" suffix="end,">
        <foreach collection="list" item="item" index="index">
          <if test="item.accountName !=null and item.accountName != '' ">
            when id = #{item.id,jdbcType=INTEGER} then #{item.accountName}
          </if>
          <if test="item.accountName == null or item.accountName == ''">
            when id=#{item.id} then bank_account_info.accountName
          </if>
        </foreach>
      </trim>

    </trim>
    where id in
    <foreach collection="list" index="index" item="item"
             separator="," open="(" close=")">
      #{item.id}
    </foreach>
  </update>

ON DUPLICATE

  <insert id="insertBranchCompanyBatch" keyColumn="branch_name" keyProperty="description" parameterType="java.util.List" useGeneratedKeys="true">
    insert into branch_company
      (
      branch_name,
      bx_company_id
      )
    values
    <foreach collection="list" item="item" separator=",">
      (
      #{item.description,jdbcType=VARCHAR},
      #{item.id,jdbcType=INTEGER}
      )
    </foreach>
    ON DUPLICATE KEY UPDATE
    <foreach collection="list" item="item" separator=",">
      bx_company_id = VALUES(bx_company_id)
    </foreach>
  </insert>