通过复合唯一索引及redis实现抢单业务模式

通过复合唯一索引及redis实现抢单业务模式

具体的应用场景：投递到某个区域的订单，该区域的所有负责该区域的经纪人可以通过抢单来处理相关的订单。

1.mysql中主要通过两张表：一张可抢单表，一张抢单日志表。

创建可抢单表

CREATE TABLE `zcb_rush_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `apply_id` bigint(20) DEFAULT NULL COMMENT '订单ID',
  `resume_id` bigint(20) DEFAULT NULL,
  `apply_create_time` datetime DEFAULT NULL COMMENT '订单创建时间',
  `name` varchar(128) DEFAULT NULL COMMENT '姓名',
  `gender` tinyint(4) DEFAULT NULL COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `education` tinyint(4) DEFAULT NULL COMMENT '学历',
  `corp_abbr` varchar(100) DEFAULT NULL COMMENT '企业简称',
  `demand_name` varchar(50) DEFAULT NULL COMMENT '需求名称',
  `work_city_code` varchar(10) DEFAULT NULL COMMENT '意向工作所在城市',
  `work_dist_code` varchar(10) DEFAULT NULL COMMENT '意向工作所在区县',
  `time_out` tinyint(1) DEFAULT '1' COMMENT '是否超时(1.未超时,2.超时)',
  `service_type` tinyint(4) DEFAULT '4' COMMENT '服务模式(1:优蓝快招;2:优蓝新城;3:APP专项;4:招财宝;5:呼叫中心)',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `status_no` tinyint(4) DEFAULT '0' COMMENT '状态（1：可以抢单；2：已经被抢；）',
  `remark` text COMMENT '备注',
  `resume_id1` bigint(20) DEFAULT NULL COMMENT '简历ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `APPLY_ID` (`apply_id`),
  KEY `EORK_CITY_CODE` (`work_city_code`),
  KEY `IDX_APPLY_ID` (`apply_id`),
  KEY `IDX_STATUS_NO_ID` (`status_no`),
  KEY `IDX_TIME_OUT_ID` (`time_out`),
  KEY `IDX_SERVICE_TYPE_ID` (`service_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1279 DEFAULT CHARSET=utf8 COMMENT='招财宝抢单表';

创建抢单日志表

CREATE TABLE `zcb_apply_change_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `apply_id` bigint(20) DEFAULT NULL COMMENT '订单ID',
  `resume_id` bigint(20) DEFAULT NULL COMMENT '简历ID',
  `operate_change_code` tinyint(4) DEFAULT NULL COMMENT '操作动作(1：订单释放公海 2：订单流转某人3.抢单成功4.取消抢单)',
  `change_time` datetime DEFAULT NULL COMMENT '操作动作发生时间',
  `before_change_phase` tinyint(4) DEFAULT NULL COMMENT '操作前阶段(1：确认；2：面试；3: 入职；4：在职；5：流失)',
  `before_change_node` tinyint(4) DEFAULT NULL COMMENT '流程节点(待沟通:11;已推荐:12;待面试:21;面试取消:22;面试未到场:23;已到访:24;到场未参面:25;面试待定:26;面试成功:27(废弃);面试失败:28;已Offer:31;拒绝Offer:32;面试通过:33;未报到:34;已报到:41;已离职:51;体检成功:35;体检失败:36;入职成功:45;入职失败:46;接站成功:16;接站失败:15;订单关闭:13;审核超时，转客服处理:18;审核失败:19;待企业审核:17;)',
  `operate_by` bigint(20) DEFAULT NULL COMMENT '转单人(zcb_sys_user表中的用户)',
  `operate_accept` bigint(20) DEFAULT NULL COMMENT '接单人(zcb_sys_user表中的用户)',
  `after_chang_phase` tinyint(4) DEFAULT NULL COMMENT '操作后阶段(1：确认；2：面试；3: 入职；4：在职；5：流失)',
  `after_change_node` tinyint(4) DEFAULT NULL COMMENT '流程节点(待沟通:11;已推荐:12;待面试:21;面试取消:22;面试未到场:23;已到访:24;到场未参面:25;面试待定:26;面试成功:27(废弃);面试失败:28;已Offer:31;拒绝Offer:32;面试通过:33;未报到:34;已报到:41;已离职:51;体检成功:35;体检失败:36;入职成功:45;入职失败:46;接站成功:16;接站失败:15;订单关闭:13;审核超时，转客服处理:18;审核失败:19;待企业审核:17;)',
  `update_time` datetime DEFAULT NULL COMMENT '订单流传更改时间',
  `create_time` datetime DEFAULT NULL COMMENT '订单流传创建时间',
  `status_no` tinyint(4) DEFAULT '1' COMMENT '1 .(可用)2，不可用',
  `operate_rush` tinyint(1) DEFAULT NULL COMMENT '为了防止重复抢单做唯一复合索引用',
  `remark` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `apply_operate_rush` (`apply_id`,`operate_rush`),
  KEY `IDX_ZCB_APPLY_CHANGE_LOG_APPLY_ID` (`apply_id`),
  KEY `IDX_ZCB_APPLY_CHANGE_LOG_RESUME_ID` (`resume_id`),
  KEY `idx_updatetime` (`update_time`),
  KEY `idx_operateby_operatechangecode_updatetime` (`operate_by`,`operate_change_code`,`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=11676 DEFAULT CHARSET=utf8 COMMENT='订单-简历释放-流转记录表';

2.抢单及取消抢单逻辑代码：

抢单逻辑：

    /**
     * 抢单
     * @param applyId 订单id
     */
    @ResponseBody
    @RequestMapping(value = "/changeResponse", method = RequestMethod.POST)
    public ResultMessage changeResponse(HttpServletRequest request,Long applyId) {
    	 try {
			 Long userId = (Long) request.getSession().getAttribute(ZcbConstants.SESSION_KEY_ZCB_LOGIN_USER);
			 if(userId==null){
				 return new ResultMessage(false,"1", "抢单失败！");
			 }
			 Map<String, Object> search1 = new HashMap<String, Object>();
			 search1.put("code","agentMaxApplyLevel");
			 search1.put("statusNo",1);
			 List<CodeConfig> codeConfigs = codeConfigService.find(search1);
			 if(codeConfigs!=null&&codeConfigs.size()>0){
			 	if(StringUtils.isNotBlank(codeConfigs.get(0).getValue())){
					Integer maxApplyNum =Integer.valueOf(codeConfigs.get(0).getValue());
					search1.clear();
					search1.put("flowNode",11);
					search1.put("responseBy",userId);
					Long count = applyFlowService.countByResponse(search1);
					if(count!=null&&count.intValue()>=maxApplyNum){
						logger.info("你已经达到超过最大抢单数:redis已经存在该key值"+applyId);
						return new ResultMessage(true, "4","你已经达到超过最大抢单数");

					}
				}
			 }
			 Boolean exists = redisPoolUtil.isExists("rushApplyId"+applyId);
			 
    	    if(exists){
    	    	logger.info("来晚了！该订单已经被人抢走了:redis已经存在该key值"+applyId);
    	    	return new ResultMessage(true, "1","来晚了！该订单已经被人抢走了！");
    	    }
    	    redisPoolUtil.ItemSetEx("rushApplyId"+applyId,300, "1");

      	    ApplyBase applyBase = applyBaseService.getById(applyId);
      	    ApplyFlow applyFlow = applyFlowService.getById(applyId);
      	    if(applyBase.getFlowNode()!=11||(applyFlow.getTimeOut()!=null&&applyFlow.getTimeOut()==2)){
      	    	logger.info("来晚了！该订单已经转客服处理了"+applyId);
      	    	return new ResultMessage(true, "3","来晚了！该订单已经转客服处理了！");
      	    }
      	    if(applyFlow.getResponseBy()!=null&&(!String.valueOf(applyFlow.getResponseBy()).equals("0"))){
      	    	logger.info("来晚了！该订单已经被人抢走了！已经存在订单负责人"+applyId);
      	    	return new ResultMessage(true, "1","来晚了！该订单已经被人抢走了！");
      	    }
      	      ZcbSysUser byEhrUserId = zcbSysUserService.getByEhrUserId(userId);
      		//记录订单负责人变更变记录表，日志表中有唯一索引。利用唯一索引避免订单被重复抢的问题
      	    ZcbApplyChangeLog zcbApplyChangeLog = new ZcbApplyChangeLog();
      	    zcbApplyChangeLog.setApplyId(applyId);
      	    zcbApplyChangeLog.setOperateChangeCode(3);
      	    zcbApplyChangeLog.setOperateRush(1);
      	    zcbApplyChangeLog.setChangeTime(new Date());
      	    zcbApplyChangeLog.setBeforeChangeNode(applyBase.getFlowNode());
      	    if(byEhrUserId!=null){
      	    	 zcbApplyChangeLog.setOperateAccept(byEhrUserId.getId());
      	    }
      	    zcbApplyChangeLog.setAfterChangeNode(applyBase.getFlowNode());
      	    zcbApplyChangeLog.setCreateTime(new Date());
      	    zcbApplyChangeLog.setStatusNo(1);
      	    ResultEntity<ZcbApplyChangeLog> insertZcbApplyChangeLog = zcbApplyChangeLogService.insertZcbApplyChangeLog(zcbApplyChangeLog);
      	    if(!insertZcbApplyChangeLog.isSuccess()){
      	    	logger.error("唯一索引冲突！："+applyId);
      	    	return new ResultMessage(true, "1","来晚了！该订单已经被人抢走了！");
      	    }
      	    
      	    //更新订单flow表开始
      	    applyFlow.setResponseBy(userId==null?Long.parseLong("0"):userId);
      	    applyFlow.setIsPush(1);
      	    applyFlow.setIsDeal(2);
      	    applyFlowService.updateApplyFlow(applyFlow);
      	    ZcbRushOrder zcbRushOrder = zcbRushOrderService.getApplyId(applyId);
      	    if(zcbRushOrder!=null){
      	    	zcbRushOrder.setStatusNo(2);
      	    	zcbRushOrderService.update(zcbRushOrder);
      	    }
      	    //把所有用户关于该订单的取消抢单记录置为不可用（为了让抢单列表不卡顿）
      	/*    final  Map<String, Object> searchParams = new HashMap<String, Object>();
	  	    searchParams.put("operateChangeCode", "4");
	  	    searchParams.put("statusNo", "1");
	  	    searchParams.put("applyId", applyId);
	  	    try {
	  	    	 Thread t = new Thread(new Runnable(){
	  	              public void run(){
	  	      		     zcbApplyChangeLogService.updateByMap(searchParams);

	  	              }});
	  	          t.start();
			} catch (Exception e) {
				// TODO: handle exception
				logger.error("更新订单取消抢单状态失败：不影响核心功能报错不关键"+e);
			}*/
      	   
  			logger.info("保存订单负责人日志表成功"+applyId);
  			logger.error("抢单成功！"+applyId);
            return new ResultMessage(true,"2", "抢单成功！");
		} catch (Exception e) {
			// TODO: handle exception
			logger.error("抢单异常：订单id为："+applyId+e.toString());
			return new ResultMessage(false,"1", "抢单失败！");
		}
    }

取消抢单逻辑：

  /**
     * 取消抢改订单
     * @param applyId 订单id
     */
    @ResponseBody
    @RequestMapping(value = "/affirmCancel", method = RequestMethod.POST)
    public ResultMessage affirmCancel(HttpServletRequest request,Long applyId) {
    	 try {
    	    Long userId = (Long) request.getSession().getAttribute(ZcbConstants.SESSION_KEY_ZCB_LOGIN_USER);
      	    ApplyBase applyBase = applyBaseService.getById(applyId);
      	    ApplyFlow applyFlow = applyFlowService.getById(applyId);
      	    ZcbSysUser byEhrUserId = zcbSysUserService.getByEhrUserId(userId);
      		//记录订单负责人变更变记录表，日志表中有唯一索引。利用唯一索引避免订单被重复抢的问题
      	    ZcbApplyChangeLog zcbApplyChangeLog = new ZcbApplyChangeLog();
      	    zcbApplyChangeLog.setApplyId(applyId);
      	    zcbApplyChangeLog.setOperateChangeCode(4);
      	    zcbApplyChangeLog.setChangeTime(new Date());
      	    zcbApplyChangeLog.setBeforeChangeNode(applyBase.getFlowNode());
      	    if(byEhrUserId!=null){
      	    	 zcbApplyChangeLog.setOperateBy(byEhrUserId.getId());
      	    }
      	    zcbApplyChangeLog.setAfterChangeNode(applyBase.getFlowNode());
      	    zcbApplyChangeLog.setCreateTime(new Date());
      	    zcbApplyChangeLog.setStatusNo(1);
      	    zcbApplyChangeLogService.insertZcbApplyChangeLog(zcbApplyChangeLog);
  			logger.info("取消抢单成功");
            return new ResultMessage(true,"1", "取消抢单成功！");
		} catch (Exception e) {
			// TODO: handle exception
			logger.error(e.toString());
			return new ResultMessage(false,"1", "取消抢单失败！");
		}
    }

3.抢单列表逻辑：

       @ResponseBody
			@RequestMapping(value = "/rushOrdersList", method = RequestMethod.POST)
			public Page<ResultAttribute> rushOrdersList(HttpServletRequest request,@RequestParam(value = "current",defaultValue="1") int pageNumber,
					@RequestParam(value = "rowCount",defaultValue="10") int pageSize,@RequestParam(value = "isTimeOut")String isTimeOut,@RequestParam(value = "searchString")String searchString
					,Model model) {
				Long userId = (Long) request.getSession().getAttribute(ZcbConstants.SESSION_KEY_ZCB_LOGIN_USER);
				if (userId == null || userId.equals("")) {
		            return null;
		        }
				Map<String, Object> searchParams = new HashMap<String, Object>();
				ZcbSysUser byEhrUserId2 = zcbSysUserService.getByEhrUserId(userId);
				if(byEhrUserId2==null){
					return null;
				}
				searchParams.put("zcbUserId", byEhrUserId2.getId());
				
				//查找用户负责的区域
				if(userId!=null){
					searchParams.put("statusNo",1);
					List<Long> areaCodeList = zcbUserAreaService.findIdList(searchParams);
				    if(areaCodeList!=null&&areaCodeList.size()>0){
				    	//searchParams.put("areaCodeList", areaCodeList);
				    	//新增
				    	ArrayList arraylist = new ArrayList();
				    	//int i = 0;
				    	for (Long  areaCode: areaCodeList) {
				    		//arraylist.a(i, areaCode.toString());
				    		arraylist.add(areaCode.toString());
				    		//i++;
						}
				    	searchParams.put("areaCodeList", arraylist);
				    }else{
				    	return null;
				    }
				}
			    //过滤掉用户放弃的订单
			    Map<String, Object> param = new HashMap<String, Object>();
			    ZcbSysUser byEhrUserId = zcbSysUserService.getByEhrUserId(userId);
			    if(byEhrUserId!=null){
			    	 param.put("operateChangeCode", 4);
					 param.put("operateBy", byEhrUserId.getId());
					 param.put("statusNo", 1);
				     List<Long> abandonedList = zcbApplyChangeLogService.findIdList(param);
				     if(abandonedList!=null&&abandonedList.size()>0){
						   searchParams.put("abandonedList", abandonedList);
						 }
			    }
				//只查找服务模式为招财宝的订单
			    if("1".equals(isTimeOut)){
			    	searchParams.put("isTimeOut", 1);
			    }else if("2".equals(isTimeOut)){
			    	searchParams.put("isTimeOut", 2);
			    }
				
				searchParams.put("userId", userId);
				searchParams.put("serviceType", 4);
				searchParams.put("searchString", searchString);
			    Page<ResultAttribute> ApplyList = applyBaseService.rushOrdersList(searchParams,new PageBounds(pageNumber, pageSize));
			    return ApplyList;
			}

  /**
     * 抢单列表逻辑
     * @param searchParams
     * @param pageBounds
     * @return
     */
		@Override
		public Page<ResultAttribute> rushOrdersList(
				Map<String, Object> searchParams, PageBounds pageBounds) {
			Integer timeOut = (Integer) searchParams.get("isTimeOut");
			PageList<ResultAttribute> findResumeApplyList =null;
			if(timeOut==1){
				findResumeApplyList =  applyBaseDao.rushOrdersList(searchParams, pageBounds);
			}else{
				findResumeApplyList =applyBaseDao.rushOrdersListTimeOut(searchParams, pageBounds);
			}
			return new PageConvertPageList<ResultAttribute>(findResumeApplyList).getPage();
		}

抢单具体的sql：

<!-- 抢单列表 -->
 <select id="rushOrdersList" resultMap="RM_ResultAttribute">
		SELECT
			zro.apply_id dataId,
			zro.apply_create_time date1,
			zro.name attribute1,
			zro.gender attribute2,
            TIMESTAMPDIFF(YEAR,zro.birthday ,now())  attribute10,
			zro.education attribute3,
			zro.corp_abbr attribute4,
			zro.demand_name attribute5,
			zro.work_city_code attribute8,
			zro.work_dist_code attribute9,
			zro.time_out attribute11
		From
			zcb_rush_order zro
			LEFT JOIN apply_base ab on zro.apply_id=ab.id
		<where>
		<if test="serviceType != null and serviceType  != ''">
          AND zro.service_type = #{serviceType}
        </if>
          AND zro.status_no = 1
          AND ab.flow_node =11
          AND zro.time_out=1
        <if test="areaCodeList != null and areaCodeList != ''">
			AND zro.work_city_code in
			<foreach collection="areaCodeList" item="areaCode" open="(" separator="," close=")">      
				#{areaCode}
			</foreach>
		</if>     
		<if test="abandonedList != null and abandonedList != ''">
			AND zro.apply_id not in
			<foreach collection="abandonedList" item="abandone" open="(" separator="," close=")">
				#{abandone}
			</foreach>
		</if>
		<if test="searchString != null and searchString != ''">
               AND (zro.name like "%"#{searchString}"%"  or zro.corp_abbr like "%"#{searchString}"%" or zro.demand_name like "%"#{searchString}"%" )
        </if>
		</where>
		ORDER BY zro.apply_id DESC 
    </select>

抢单超时的具体sql：

 <!-- 抢单超时   -->  
	 <select id="rushOrdersListTimeOut" resultMap="RM_ResultAttribute">
		<!-- SELECT
			ab.id dataId,
			ab.create_time date1,
			asn.NAME attribute1,
			asn.gender attribute2,
            TIMESTAMPDIFF(YEAR,asn.birthday ,now())  attribute10,
			asn.education attribute3,
			cb.abbr attribute4,
			cd. NAME attribute5,
			ab.work_city_code attribute8,
			ab.work_dist_code attribute9,
			af.time_out attribute11
		From
			apply_base ab
		LEFT JOIN apply_flow af ON ab.id = af.id
		LEFT JOIN apply_snapshot asn ON ab.id = asn.id
		LEFT JOIN corp_demand cd ON ab.demand_id = cd.id
		LEFT JOIN corp_base cb ON cd.corp_id = cb.id
		<where>
		<if test="serviceType != null and serviceType  != ''">
           af.service_type = #{serviceType}
        </if>
        AND ab.create_time >'2018-12-15 00:00:00'
		AND af.response_by = 0
		AND ab.flow_node = 11
		<if test="isTimeOut !=null and isTimeOut !=''">
		 AND  af.time_out = #{isTimeOut}
		</if>
        <if test="areaCodeList != null and areaCodeList != ''">
			AND ab.work_city_code in
			<foreach collection="areaCodeList" item="areaCode" open="(" separator="," close=")">      
				 #{areaCode} &apos;
		CONCAT(CONCAT("%",#{areaCode}),"%"))  
			</foreach>
		</if>     
		<if test="abandonedList != null and abandonedList != ''">
			AND ab.id not in
			<foreach collection="abandonedList" item="abandone" open="(" separator="," close=")">
				#{abandone}
			</foreach>
		</if>
		<if test="searchString != null and searchString != ''">
               AND (asn.name like "%"#{searchString}"%"  or cb.name like "%"#{searchString}"%" or cd.name like "%"#{searchString}"%" )
        </if>
		</where>
		ORDER BY ab.id DESC  -->
		
		SELECT
			zro.apply_id dataId,
			zro.apply_create_time date1,
			zro.name attribute1,
			zro.gender attribute2,
            TIMESTAMPDIFF(YEAR,zro.birthday ,now())  attribute10,
			zro.education attribute3,
			zro.corp_abbr attribute4,
			zro.demand_name attribute5,
			zro.work_city_code attribute8,
			zro.work_dist_code attribute9,
			zro.time_out attribute11
		From
			zcb_rush_order zro
		<where>
          AND zro.time_out=2
        <if test="areaCodeList != null and areaCodeList != ''">
			AND zro.work_city_code in
			<foreach collection="areaCodeList" item="areaCode" open="(" separator="," close=")">      
				#{areaCode}
			</foreach>
		</if>     
		<if test="abandonedList != null and abandonedList != ''">
			AND zro.apply_id not in
			<foreach collection="abandonedList" item="abandone" open="(" separator="," close=")">
				#{abandone}
			</foreach>
		</if>
		<if test="searchString != null and searchString != ''">
               AND (zro.name like "%"#{searchString}"%"  or zro.corp_abbr like "%"#{searchString}"%" or zro.demand_name like "%"#{searchString}"%" )
        </if>
		</where>
		ORDER BY zro.apply_id DESC 
    </select>