springmvc+mybatis下批量插入和批量更新

// 查询出没有的需要插入的
// 1:将查询出的list转为map
Map<Long, ImCategoryBean> imCategoryMap = imCategoryBeanList.stream().collect(Collectors.toMap(ImCategoryBean::getfCategoryId, imCategoryBean -> imCategoryBean, (k1, k2) -> k1));

// 2: 查询出传入有的和没有的
// 2.1: 有的list需要更新
List<ImCategoryBean> updateImCateList = new ArrayList<>();
// 2.2: 没有的list
List<ImCategoryBean> insertImCateList = new ArrayList<>();
categorys.stream().forEach(cate -> {
    //能够获取到说明有
    ImCategoryBean categoryBean = imCategoryMap.get(cate.getfCategoryId());
    if (null != categoryBean) {
        // 获取当前状态是否一致，不一致更新，一致则不更新
        if (!(cate.getfCategoryType().equals(categoryBean.getfCategoryType())) || categoryBean.getfIsDelete().equals(ImConstant.DB_DELETE)) {
            ImCategoryBean imCategoryBean = new ImCategoryBean();
            imCategoryBean.setfId(categoryBean.getfId());
            imCategoryBean.setfIsDelete(ImConstant.DB_CORRECT);
            imCategoryBean.setfCategoryType(cate.getfCategoryType());
            updateImCateList.add(imCategoryBean);
        }
    } else {
        // 插入的话缺当前人
        cate.setfSysUserId(fSysUserId);
        insertImCateList.add(cate);
    }
});

//将传入的数据转换为map
Map<Long, ImCategoryBean> categorysMap = categorys.stream().collect(Collectors.toMap(ImCategoryBean::getfCategoryId, imCategoryBean -> imCategoryBean, (k1, k2) -> k1));
imCategoryBeanList.stream().forEach(cate -> {
    ImCategoryBean categoryBean = categorysMap.get(cate.getfCategoryId());
    if (null == categoryBean) {//如果没有，说明已经被移除  被移除但是数据库之中还是有数据的，则需要进行逻辑删除
        ImCategoryBean imCategoryBean = new ImCategoryBean();
        imCategoryBean.setfId(cate.getfId());
        imCategoryBean.setfIsDelete(ImConstant.DB_DELETE);
        updateImCateList.add(imCategoryBean);
    }
});

//全部完成之后进行批量插入和批量更新
if (updateImCateList.size() > 0) {
    imCategoryService.batchUpdateList(updateImCateList);
}
if (insertImCateList.size() > 0) {
    imCategoryService.batchInsertList(insertImCateList);
}

在mybatis中

批量插入：

<insert id="batchInsertList" parameterType="java.util.List">
  insert into zm_im_category (f_sys_user_id, f_category_id, f_category_type)
  values
  <foreach collection="list" item="cate" index="index" separator="," >
    (
    #{cate.fSysUserId,jdbcType=BIGINT},
    #{cate.fCategoryId,jdbcType=BIGINT},
    #{cate.fCategoryType,jdbcType=VARCHAR})
  </foreach>
</insert>

批量更新

<update id="batchUpdateList" parameterType="java.util.List">
  update zm_im_category
  <trim prefix="set" suffixOverrides=",">
    <trim prefix="f_is_delete =case" suffix="end,">
      <foreach item="item" collection="list">
        when zm_im_category.f_id = (#{item.fId})
        then (#{item.fIsDelete})
      </foreach>
    </trim>
      <trim prefix="f_category_type =case" suffix="end,">
          <foreach item="item" collection="list">
              when zm_im_category.f_id = (#{item.fId})
              then (#{item.fCategoryType})
          </foreach>
      </trim>
  </trim>
  where
  <foreach item="item" collection="list" separator="or">
    (zm_im_category.f_id = (#{item.fId}))
  </foreach>
</update>