mybatis实现批量查询，删除，插入，更新数据

1.批量插入

   <insert id="batchSave">
        insert into (tbl表名) (region_type, prov_id, insert_time)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (
            #{item.regionType},
            #{item.provId},
            #{item.insertTime},
            )
        </foreach>
    </insert>

2.批量删除

       参数说明：

   attend_equipment：表名
   e_id：表字段名
   open:表示语句以什么开始
   separator:表示在每次迭代之间，以什么作为分隔符
   close：表示以什么结束
   collection:表示类型，如果参数是数组，写成array (若为集合的话,写成list)

<!--批量删除信息-->
<delete id="batchdeleteEquipment" parameterType="String">
    DELETE FROM  attend_equipment  WHERE e_id IN
    <foreach collection="array" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

3.批量更新

 <!-- 批量更新第一种方法-->
 <update id="updateBatch" parameterType="java.util.List" >
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update standard_relation
            <set >
                <if test="item.standardFromUuid != null" >
                    standard_from_uuid = #{item.standardFromUuid,jdbcType=VARCHAR},
                </if>
                <if test="item.standardToUuid != null" >
                    standard_to_uuid = #{item.standardToUuid,jdbcType=VARCHAR},
                </if>
                <if test="item.gmtModified != null" >
                    gmt_modified = #{item.gmtModified,jdbcType=TIMESTAMP},
                </if>
            </set>
            where id = #{item.id,jdbcType=BIGINT}
        </foreach>
    </update>
 
    <!-- 批量更新第二种方法，通过 case when语句变相的进行批量更新 -->
    <update id="updateBatch" parameterType="java.util.List" >
        update standard_relation
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="standard_from_uuid =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.standardFromUuid!=null">
                        when id=#{i.id} then #{i.standardFromUuid}
                    </if>
                </foreach>
            </trim>
            <trim prefix="standard_to_uuid =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.standardToUuid!=null">
                        when id=#{i.id} then #{i.standardToUuid}
                    </if>
                </foreach>
            </trim>
            <trim prefix="gmt_modified =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.gmtModified!=null">
                        when id=#{i.id} then #{i.gmtModified}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" separator="or" item="i" index="index" >
            id=#{i.id}
        </foreach>
    </update>

4.批量查询

//实例1
<select id="findMenuName" resultType="java.lang.String" parameterType="java.util.List">
  select menu_name from menu where menu_id in
  <foreach collection="list" item="valueList" open="(" close=")" separator=",">
    #{valueList}
  </foreach>
</select>

//实例2
<select id="selectByIds" parameterType="java.util.List"
            resultType="com.paic.ocss.gateway.model.entity.ReturnCodeEntity">
        SELECT
        id,
        code,
        message
        FROM openapi_return_code WHERE id in
        <trim prefix="(" suffix=")">
            <foreach collection="ids" index="index" item="id" separator=",">
                #{id}
            </foreach>
        </trim>
</select>

//实例3
<select id="findUserListByIdList" parameterType="java.util.ArrayList" resultType="User">    
    select * from user user    
    <where>    
        user.ID in (    
          <foreach collection="list"  item="id" index="index" separator=",">   
             #{id}   
          </foreach>    
        )    
    </where>    
</select>