莫大人

mybatis 批量新增-批量修改-批量删除操作

mapper.xml

<!-- 批量新增 -->
    <insert id="saveBatch" parameterType="java.util.List">
	 insert into t_gceb_login_log(
	  id ,
	  user_id ,
	  user_name ,
	  mip ,
	  jsession_id_4a ,
	  create_by ,
	  create_by_name ,
	  update_by ,
	  update_by_name ,
	  create_time ,
	  update_time ,
	  data_version ,
	  attribute1 ,
	  attribute2 ,
	  attribute3 ,
	  attribute4 ,
	  attribute5 ,
	  attribute6 
	 )
	 values
	 <foreach collection="list" item="item" index="index" separator="," >
	 (
	   #{item.id},
	   #{item.userId},
	   #{item.userName},
	   #{item.mip},
	   #{item.jsessionId4a},
	   #{item.createBy},
	   #{item.createByName},
	   #{item.updateBy},
	   #{item.updateByName},
	   #{item.createTime},
	   #{item.updateTime},
	   #{item.dataVersion},
	   #{item.attribute1},
	   #{item.attribute2},
	   #{item.attribute3},
	   #{item.attribute4},
	   #{item.attribute5},
	   #{item.attribute6}
	 ) 
	 </foreach>
</insert>

	<!-- 批量修改 -->
    <update id="updateBatch" parameterType="java.util.List">
        update t_gceb_login_log
        <trim prefix="set" suffixOverrides=",">
        	<trim prefix="userId =case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.userId!=null">
                        when id=#{item.id} then #{item.userId}
                    </if>
                </foreach>
            </trim>
        	<trim prefix="attribute1 =case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.attribute1!=null">
                        when id=#{item.id} then #{item.attribute1}
                    </if>
                </foreach>
            </trim>
	    </trim>
	    <where>
	    	<foreach collection="list" separator="or" item="item">
	    		id=#{item.id}
	    	</foreach>
	    </where>
    </update>
    
    <delete id="logBatchRemove"  parameterType = "java.util.List">
     delete from t_gceb_login_log 
     <where>
     	id in
     	<foreach collection="list"  item="item" open="(" separator="," close=")"  >
	   	#{item}
	   	</foreach>
	   </where>
	</delete> 

 

注意点:批量操作也是有性能问题的,一个批次建议200条

List<List<LoginLog>> batchList = ListUtils.partition(loginLogList, 200);
		for (List<LoginLog> array: batchList) {
			int sum = this.sqlSessionTemplate.update("loginLog.updateBatch", array);
			LOGGER.debug("批量更新记录数:{}", sum);
		}

  

 

posted on 2019-05-28 17:19  莫大人  阅读(1786)  评论(0编辑  收藏  举报

导航