mybatis Oracle 批量操作

最新推荐文章于 2021-10-25 21:17:14 发布

stay hungry,stay you

最新推荐文章于 2021-10-25 21:17:14 发布

阅读量488

点赞数

CC 4.0 BY-SA版权

分类专栏： java 文章标签： mybatis Oracle 批量操作

本文链接：https://blog.youkuaiyun.com/weixin_41987908/article/details/89210504

java 专栏收录该内容

211 篇文章

订阅专栏

本文介绍了一种批量操作的实现方式，包括批量新增和批量删除，使用UUID作为主键，通过Java和MyBatis进行数据库操作。批量新增部分展示了如何通过流迭代和循环创建对象，将数据插入数据库；批量删除部分介绍了如何处理多个ID的删除操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前提：主键不是自增的是插入UUID的。若@Parm 中是a 则foreach collection 就是a。若不写默认list
`批量新增：
void insertProjectDemonstrationMany(@Param(“proposalItemList”) List proposalItemList);

@ApiOperation(value = "项目论证概览批量新增", notes = "需要包装多个对象的集合", code = 200, produces = "application/json")
@RequestMapping(value = "insertProjectDemonstrationMany", method = RequestMethod.POST)
public ApiResult insertProjectDemonstrationMany(@ApiParam(name = "itemOrgSiteManyDto", value = "集合对象") @RequestBody List<ItemOrgSiteDto> itemOrgSiteDtoList) {
    //定义一个空集合
    List<ProposalItem>proposalItemList=new ArrayList<ProposalItem>();
    Stream.iterate(0,i->i+1).limit(itemOrgSiteDtoList.size()).forEach(i->{
        //每一个空对象(如果把空对象放到外面会有覆盖问题需循环创建多个对象而不是一个)
        ProposalItem proposalItem = new ProposalItem();
        ItemOrgSiteDto itemOrgSiteDto = itemOrgSiteDtoList.get(i);
        String orgId = queryItem_ORG(itemOrgSiteDto.getOrgName()).getOrgId();
        proposalItem.setItemId(UUID.randomUUID().toString().replaceAll("-", "").toUpperCase());
        proposalItem.setItemOrg(orgId);
        proposalItem.setItemYear(itemOrgSiteDto.getItemYear());
        proposalItem.setItemName(itemOrgSiteDto.getItemName());
        proposalItem.setIsInvestment(itemOrgSiteDto.getIsInvestment());
        proposalItem.setItemContent(itemOrgSiteDto.getItemContent());
        proposalItem.setItemSite(itemOrgSiteDto.getItemSite());
        proposalItem.setItemClass("2001");
        proposalItem.setParentItemId(null);
        proposalItem.setItemLevel(itemOrgSiteDto.getItemLevel());
        proposalItem.setRemarks(itemOrgSiteDto.getRemarks());
        proposalItem.setCreateUser(SpringManager.getCurrentUser().getUserId());
        proposalItemList.add(proposalItem);
    });
    annualDeploymentService.insertProjectDemonstrationMany(proposalItemList);
    return ApiResult.ofSuccess();
}
*sql：*
<insert id="insertProjectDemonstrationMany" parameterType="java.util.List">
    INSERT ALL
    <foreach collection="proposalItemList" item="p" index="index">
        INTO     PRODUCTION.CD_ITEM(ITEM_ID,PARENT_ITEM_ID,ITEM_NAME,ITEM_CLASS,ITEM_YEAR,ITEM_LEVEL,IS_INVESTMENT,ITEM_CONTENT,ITEM_ORG,ITEM_SITE,REMARKS,CREATE_USER,CREATE_DATE)
        VALUES
        (
        #{p.itemId,jdbcType=VARCHAR},
        #{p.parentItemId,jdbcType=VARCHAR},
        #{p.itemName,jdbcType=VARCHAR},
        #{p.itemClass,jdbcType=VARCHAR},
        #{p.itemYear,jdbcType=VARCHAR},
        #{p.itemLevel,jdbcType=VARCHAR},
        #{p.isInvestment,jdbcType=VARCHAR},
        #{p.itemContent,jdbcType=VARCHAR},
        #{p.itemOrg,jdbcType=VARCHAR},
        #{p.itemSite,jdbcType=VARCHAR},
        #{p.remarks,jdbcType=VARCHAR},
        #{p.createUser,jdbcType=VARCHAR},
        SYSDATE
        )
    </foreach>
    SELECT 1 FROM DUAL
</insert>`

批量删除（和mysql 一样）
void deleteProjectDemonstrationMany(List list)

@ApiOperation(value = "批量删除生产框架计划指标", notes = "需要fmPlanId", code = 200, produces = "application/json")
   @RequestMapping(value = "deleteLtdFramePlanMany", method = RequestMethod.POST)
   @ResponseBody
   public ApiResult deleteLtdFramePlanMany(@ApiParam(name = "ids", value = "多个fmPlanId", required = true) @RequestParam(value = "ids", required = true) String ids) {
       List<String> list = new ArrayList<String>();
       String[] stIds = ids.split(",");
       for (String value : stIds) {
           //判断fmPlanId是否存在
           List<ProposalLtdFramePlan>listLtdFramePlan = (List<ProposalLtdFramePlan>) queryLtdFramePlanById(value).getResult();
           if (listLtdFramePlan != null && listLtdFramePlan.size() > 0) {
               list.add(value);
           } else {
               return ApiResult.ofFailureResultMsg(null, "批量删除中有数据不存在，请重新选择！", false);
           }
       }
       annualDeploymentService.deleteLtdFramePlanMany(list);
       return ApiResult.ofSuccess();
   }
   *sql:*
<delete id="deleteProjectDemonstrationMany" parameterType="java.util.List">
        delete from PRODUCTION.CD_ITEM where ITEM_ID in
        <foreach collection="list" item="ids" open="(" close=")" separator=",">
            #{ids,jdbcType=VARCHAR}
        </foreach>
    </delete>