关于mybatis批量操作数据的SQL拼接

增：

<insert id="insertList" parameterType="com.tdhc.common.model.PuDelivery">
  insert into pu_delivery
  ( <include refid="Base_Column_List" /> )
  values
  <foreach collection="list" item="item" index="index" separator=",">
    (#{item.deliveryid,jdbcType=VARCHAR}, #{item.supid,jdbcType=VARCHAR}, #{item.supname,jdbcType=VARCHAR}, #{item.connum,jdbcType=VARCHAR},
    #{item.documentcode,jdbcType=VARCHAR}, #{item.matnum,jdbcType=VARCHAR}, #{item.matname,jdbcType=VARCHAR},
    #{item.specification,jdbcType=VARCHAR}, #{item.unit,jdbcType=VARCHAR}, #{item.applyquantity,jdbcType=DOUBLE},
    #{item.declarequantity,jdbcType=DOUBLE}, #{item.deliverynum,jdbcType=VARCHAR}, #{item.deliveryquantity,jdbcType=DOUBLE},
    #{item.deliverydate,jdbcType=DATE}, #{item.deliverygap,jdbcType=DOUBLE}, #{item.saillings,jdbcType=VARCHAR},
    #{item.transportcategory,jdbcType=CHAR}, #{item.creater,jdbcType=VARCHAR}, #{item.createtime,jdbcType=DATE},
    #{item.modifier,jdbcType=VARCHAR}, #{item.modifytime,jdbcType=DATE}, #{item.accountlimit,jdbcType=CHAR},
    #{item.cDr,jdbcType=VARCHAR}, #{item.del01,jdbcType=VARCHAR}, #{item.del02,jdbcType=VARCHAR},
    #{item.del03,jdbcType=VARCHAR}, #{item.del04,jdbcType=VARCHAR}, #{item.del05,jdbcType=VARCHAR},
    #{item.del06,jdbcType=VARCHAR}, #{item.del07,jdbcType=VARCHAR}, #{item.del08,jdbcType=VARCHAR},
    #{item.del09,jdbcType=VARCHAR}, #{item.del10,jdbcType=VARCHAR}, #{item.cTimestamp,jdbcType=TIMESTAMP}
    )
  </foreach>
</insert>

改：

<update id="delete" parameterType="com.tdhc.common.model.PuDelivery">
  <foreach collection="list" open="" separator=";" close=";" item="item">
    update pu_delivery
    <set>
      <if test="item.supid != null">
        SupId = #{item.supid,jdbcType=VARCHAR},
      </if>
      <if test="item.supname != null">
        SupName = #{item.supname,jdbcType=VARCHAR},
      </if>
      <if test="item.connum != null">
        ConNum = #{item.connum,jdbcType=VARCHAR},
      </if>
      <if test="item.documentcode != null">
        DocumentCode = #{item.documentcode,jdbcType=VARCHAR},
      </if>
      <if test="item.matnum != null">
        MatNum = #{item.matnum,jdbcType=VARCHAR},
      </if>
      <if test="item.matname != null">
        MatName = #{item.matname,jdbcType=VARCHAR},
      </if>
      <if test="item.specification != null">
        Specification = #{item.specification,jdbcType=VARCHAR},
      </if>
      <if test="item.unit != null">
        Unit = #{item.unit,jdbcType=VARCHAR},
      </if>
      <if test="item.applyquantity != null">
        ApplyQuantity = #{item.applyquantity,jdbcType=DOUBLE},
      </if>
      <if test="item.declarequantity != null">
        DeclareQuantity = #{item.declarequantity,jdbcType=DOUBLE},
      </if>
      <if test="item.deliverynum != null">
        DeliveryNum = #{item.deliverynum,jdbcType=VARCHAR},
      </if>
      <if test="item.deliveryquantity != null">
        DeliveryQuantity = #{item.deliveryquantity,jdbcType=DOUBLE},
      </if>
      <if test="item.deliverydate != null">
        DeliveryDate = #{item.deliverydate,jdbcType=DATE},
      </if>
      <if test="item.deliverygap != null">
        DeliveryGap = #{item.deliverygap,jdbcType=DOUBLE},
      </if>
      <if test="item.saillings != null">
        Saillings = #{item.saillings,jdbcType=VARCHAR},
      </if>
      <if test="item.transportcategory != null">
        TransportCategory = #{item.transportcategory,jdbcType=CHAR},
      </if>
      <if test="item.creater != null">
        Creater = #{item.creater,jdbcType=VARCHAR},
      </if>
      <if test="item.createtime != null">
        CreateTime = #{item.createtime,jdbcType=DATE},
      </if>
      <if test="item.modifier != null">
        Modifier = #{item.modifier,jdbcType=VARCHAR},
      </if>
      <if test="item.modifytime != null">
        ModifyTime = #{item.modifytime,jdbcType=DATE},
      </if>
      <if test="item.accountlimit != null">
        AccountLimit = #{item.accountlimit,jdbcType=CHAR},
      </if>
      <if test="item.cDr != null">
        c_dr = #{item.cDr,jdbcType=VARCHAR},
      </if>
      <if test="item.del01 != null">
        Del01 = #{item.del01,jdbcType=VARCHAR},
      </if>
      <if test="item.del02 != null">
        Del02 = #{item.del02,jdbcType=VARCHAR},
      </if>
      <if test="item.del03 != null">
        Del03 = #{item.del03,jdbcType=VARCHAR},
      </if>
      <if test="item.del04 != null">
        Del04 = #{item.del04,jdbcType=VARCHAR},
      </if>
      <if test="item.del05 != null">
        Del05 = #{item.del05,jdbcType=VARCHAR},
      </if>
      <if test="item.del06 != null">
        Del06 = #{item.del06,jdbcType=VARCHAR},
      </if>
      <if test="item.del07 != null">
        Del07 = #{item.del07,jdbcType=VARCHAR},
      </if>
      <if test="item.del08 != null">
        Del08 = #{item.del08,jdbcType=VARCHAR},
      </if>
      <if test="item.del09 != null">
        Del09 = #{item.del09,jdbcType=VARCHAR},
      </if>
      <if test="item.del10 != null">
        Del10 = #{item.del10,jdbcType=VARCHAR},
      </if>
      <if test="item.cTimestamp != null">
        c_timestamp = #{item.cTimestamp,jdbcType=TIMESTAMP},
      </if>
    </set>
    where DeliveryId=#{item.deliveryid,jdbcType=VARCHAR}
  </foreach>
</update>

查：

<select id="selectByDeliveryNum" parameterType="java.util.List" resultMap="BaseResultMap">
  select
  <include refid="Base_Column_List"/>
  from pu_delivery
  where c_dr="0"
  and DeliveryNum IN
  <foreach collection="list" index="index" close=")" open="(" item="item" separator=",">
    #{item.deliverynum,jdbcType=VARCHAR}
  </foreach>
</select>

1.删除没有什么技术含量，参照上方即可。
2.比较长，原因是备用字段等，着重语法即可。
3.仅示例，参考用。