mybatis批量操作

1.批量增加

<insert id="bathAddFileData" parameterType="java.util.List">
    insert into info_staticresource (id,fileName, mobileFileName,
    filePath, mobileFilePath, extensionName,
    lastCreateTime,httpLink) values
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.id}, #{item.fileName},#{item.mobileFileName},#{item.filePath},#{item.mobileFilePath},#{item.extensionName},#{item.lastCreateTime},#{item.httpLink} )
    </foreach>
</insert>

2.批量修改

<update id="updateStockByDateAndProductId" parameterType="java.util.List">
  <foreach collection="list" item="item" index="index" open="" close="" separator=";">
    update inf_productdatabase
    <set>
      <if test="item.dataBaseTotalCount != null" >
        dataBaseTotalCount = dataBaseTotalCount + #{item.dataBaseTotalCount},
      </if>
      <if test="item.alreadyUseCount != null" >
        alreadyUseCount = alreadyUseCount + #{item.alreadyUseCount},
      </if>
      <if test="item.occupationCount != null" >
        occupationCount = occupationCount + #{item.occupationCount},
      </if>
      <if test="item.saleCount != null" >
        saleCount = saleCount + #{item.saleCount},
      </if>
    </set>
    where productId=#{item.productId} and dataBaseDate=#{item.dataBaseDate}
  </foreach>
</update>

转载：https://blog.csdn.net/lishaojun0115/article/details/50395669
第一种方式

<update id="updateThreadreturnList"  parameterType="java.util.List">  
        update tb_thread set isDelete=0
        where threadId in ( 
    <foreach collection="list" item="item" index="index" open="" close="" separator=",">
        #{item.threadId}
    </foreach>      
        )     
  </update>
1
2
3
4
5
6
7
8
第二种方式

注意 需要在数据库添加 &allowMultiQueries=true 
jdbc:mysql://192.168.1.109:3306/healthmanage?characterEncoding=utf-8&allowMultiQueries=true

    <update id="updateQuestionseleteTempalteList"  parameterType="java.util.List">  
    <foreach collection="list" item="item" index="index">
        update tb_question_template_seleteitem_detail set selectedName=#{item.selectedName}
        where 1=1 and  selectedId =#{item.selectedId  };

    </foreach>      
  </update>
 

 

3.批量查询

<select id="selectFileDatas" resultMap="BaseResultMap" parameterType="list">
    select   <include refid="Base_Column_List"/>  from info_staticresource
    <where>
        id in
        <foreach collection="list" item="item" index="index"
                 open="(" separator="," close=")">#{item}
        </foreach>
    </where>
</select>

第一种方式

<update id="updateThreadreturnList"  parameterType="java.util.List">  
        update tb_thread set isDelete=0
        where threadId in ( 
    <foreach collection="list" item="item" index="index" open="" close="" separator=",">
        #{item.threadId}
    </foreach>      
        )     
  </update>

第二种方式

注意 需要在数据库添加 &allowMultiQueries=true 
jdbc:mysql://192.168.1.109:3306/healthmanage?characterEncoding=utf-8&allowMultiQueries=true

    <update id="updateQuestionseleteTempalteList"  parameterType="java.util.List">  
    <foreach collection="list" item="item" index="index">
        update tb_question_template_seleteitem_detail set selectedName=#{item.selectedName}
        where 1=1 and  selectedId =#{item.selectedId  };

    </foreach>      
  </update>