INSERT INTO T_FOMF_SUPPER_PRICE_METHOD_AD (ID, AUDIT_ID, PRICE_ITEM_CODE, FORMULA, LEFT_RANGE, RIGHT_RANGE, MATHORDER, MIN_FEE, MAX_FEE, OPEN_CLOSE, CREATE_DATE) SELECT #{item.id,jdbcType=VARCHAR} ,#{item.auditId,jdbcType=VARCHAR} ,#{item.priceItemCode,jdbcType=VARCHAR} ,#{item.formula,jdbcType=VARCHAR} ,#{item.leftRange,jdbcType=DOUBLE} ,#{item.rightRange,jdbcType=DOUBLE} ,#{item.mathOrder,jdbcType=NUMERIC} ,#{item.minFee,jdbcType=DOUBLE} ,#{item.maxFee,jdbcType=DOUBLE} ,#{item.openClose,jdbcType=CHAR} ,#{item.createDate,jdbcType=TIMESTAMP} FROM DUAL
业务场景:更绑网点 10000条数据
对两张表做新增,一张表做删除,一张表做修改
处理结果:7s左右
public Boolean insertUpdateSite(MmOrderNumberQueryVo vo,UserEntity userEntity) throws BusinessException{ //1、在散列表中取出 更绑前网点 信息的id集合 ListIdlist = mmSectionOrderQueryDao.getSiteList(vo.getBeforeSiteCode()); if(null == Idlist || Idlist.size() ==0){ throw new BusinessException("更绑前网点绑定的面单数量为0,不允许更绑该网点!!!"); } //需要更绑的面单数量 int valueSize = Idlist.size(); // 批次数 int batchSize = valueSize / totalNum + (valueSize % totalNum == 0 ? 0 : 1); int startNum = 0; int endNum = 0; List singleList = new LinkedList<>(); for(int i = 0;i valueSize){ endNum = valueSize; } singleList = Idlist.subList(startNum, endNum); //2、在网点变更查询表(T_MM_SECTION_SITE_UPDATE)中删除旧的数据 来保证 T_MM_SECTION_SL_ID的唯一 String str = this.GetWhereInValuesSql("T_MM_SECTION_SL_ID",singleList,totalNum); mmSectionOrderQueryDao.deleteBatchQuery(str); //3、在网点变更记录表中批量添加数据 List list = getSiteUpList(vo,userEntity,singleList); mmSectionOrderQueryDao.insertBatchRecord(list); //4、在网点变更查询表中批量添加数据 mmSectionOrderQueryDao.insertBatchQuery(list); //5、修改散列表BIND_CODE字段 mmSectionOrderQueryDao.updateBatchQuery(vo.getBeforeSiteCode(),vo.getAfterSiteCode()); } return true; }
对集合切割处理,每次处理800条,速度较快