mysql之批量操作foreach用法

1、批量查询

<select id="selectAllItemsByIds" parameterType="java.util.List" resultMap="BaseResultMap">
    select
    id, field1, field2
    from item_info
    <where>
        id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </where>
</select>

2、批量删除

<delete id="deleteUsersByNames" parameterType="java.util.List">
   delete from item_info
   where username in
   <foreach collection="list" item="item" open="(" separator="," close=")">
       #{item}
   </foreach>
</delete>

3、批量更新

<update id="updateItemsById">
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        UPDATE item_info
        <set>
            field1 = #{item.field1}
        </set>
        WHERE id = #{id}
    </foreach>
</update>

4、批量插入

<insert id="insertItemsByBatch" parameterType="java.util.List">
  insert into item_info (id,field1,field2)
  values
  <foreach collection="list" item="item" index="index" separator=",">
    (
	    #{item.id,jdbcType=BIGINT},
	    #{item.field1,jdbcType=VARCHAR},
	    #{item.field2,jdbcType=VARCHAR}
    )
  </foreach>
</insert>