记一次Jpa 账单excel批量导出问题

谁践踏了优雅 2021-12-10 09:34 111阅读 0赞

账单excel导出|获取的账单数orderModerns=173

执行时间time=178140ms

执行时间time=61212ms

执行时间time=1527ms

一、同事写的常见处理方法

由于是动态多条件查询，jpa很麻烦，先根据条件查出用户Id List, 再根据用户Id List 查出账单 List ,遍历账单List，关联用户id查询用户信息一个个set进去，=====慢慢慢

log.info("根据查询条件拿到人员Id列表");
    
                System.out.println("+++++++++++++++++++++++++++++++++++++++++++++"+req.getOrganization_id());
    
                String name=req.getUserName();
                if (StringUtils.isNotBlank(name))name="%"+name+"%";
                orderUserList = crmUserDao.findByCondition(name, req.getOrganization_id(),req.getBranch_id());
                log.info("orderUserList："+orderUserList.size());
    
                log.info("根据人员id列表和其余条件拿到订单列表");
                orderList = orderOrderDao.findPagePcByUserCondition(orderUserList,
                        req.getPayStatus(),
                        req.getOrderDate(),
                        req.getPayTimeStart(),
                        req.getPayTimeEnd(),
                        pageable);
                count = orderOrderDao.countByUserCondition(orderUserList,
                        req.getPayStatus(),
                        req.getOrderDate(),
                        req.getPayTimeStart(),
                        req.getPayTimeEnd());
            }
    
    
                for (int i= 0; i<orderList.size(); i++) {
                    //根据订单查询组织名称
                    ApiPcOrderResp.order  orderModern = new ApiPcOrderResp.order();
                    SysOrganization sysOrganization = sysOrganizationDao.getOne(orderList.get(i).getOrganizationId());
                    //根据人员id查询人员信息
                    CrmUser user = crmUserDao.getOne(orderList.get(i).getOrderUser());
                    //根据人员id查询部门名称
                    SysCoBranch sysCoBranch = sysCoBranchDao.getOne(user.getSysCoBranchId());
    
                    orderModern.setOrderId(orderList.get(i).getOrderId());
                    orderModern.setOrderNum(orderList.get(i).getOrderNum());
                    orderModern.setWaterNum(orderList.get(i).getWaterNum());
                    orderModern.setAmount(orderList.get(i).getAmount());
                    orderModern.setDescription(orderList.get(i).getDescription());
                    orderModern.setPayStatus(orderList.get(i).getPayStatus());
                    orderModern.setOrderDate(orderList.get(i).getOrderDate());
                    orderModern.setOrderUser(orderList.get(i).getOrderUser());
                    orderModern.setOrganizationId(orderList.get(i).getOrganizationId());
                    orderModern.setRemark(orderList.get(i).getRemark());
                    orderModern.setModifyUser(orderList.get(i).getModifyUser());
                    orderModern.setModifyTime(orderList.get(i).getModifyTime());
                    orderModern.setSystemTime(orderList.get(i).getSystemTime());
                    orderModern.setPayUserName(user.getName());
                    orderModern.setOrganizationName(sysOrganization.getName());
                    orderModern.setBranchName(sysCoBranch.getName());
                    respList.add(orderModern);
    
                }
                resp.setContactItems(respList);
                resp.setCount(count.intValue());

二、我将用户关联信息查询set处启线程执行，还是慢

/**
         * 账单excel导出
         */
        @ResponseBody
        @RequestMapping(value = "/excelOrder", method = RequestMethod.POST)
        public SystemResult excelOrder(long companyId,ApiPcOrderReq req, HttpServletRequest request, HttpServletResponse response) {
            log.info("账单excel导出入参companyId={}，ApiPcOrderReq={}", companyId,JSONObject.toJSONString(req));
            long startTime=System.currentTimeMillis();   //获取开始时间
            //根据多条件查询userList
            String name=req.getUserName();
            if (StringUtils.isNotBlank(name))name="%"+name+"%";
           List<Integer> orderUserIds = crmUserDao.findByCondition(name, req.getOrganization_id(),req.getBranch_id());
            log.info("账单excel导出|orderUserIds={}",orderUserIds.size());
    
            log.info("根据人员id列表和其余条件拿到订单列表");
            List<OrderOrder>orders = orderOrderDao.findExcelOrders(orderUserIds,
                    req.getPayStatus(),
                    req.getOrderDate(),
                    req.getPayTimeStart(),
                    req.getPayTimeEnd());
    
            //遍历查询部门支部名称信息
            List<ApiPcOrderResp.order> orderModerns=new ArrayList<ApiPcOrderResp.order>();
    
            ExecutorService es = Executors.newCachedThreadPool();
            for (OrderOrder order:
            orders) {
                Thread thread=new Thread(()->{
                    getAllNames(orderModerns, order);
                });
                es.execute(thread);
            }
            /*
             * es.shutdown(); 阻止新任务的提交，但是原本已经提交的，不会受到影响，当已提交的任务全部完成后，中断闲置的线程
             * es.shutdownNow(); 阻止新任务的提交，且已提交的任务也会受到影响，不等已提交的任务完成，就会中断所有的线程。
             */
            es.shutdown();
            log.info("账单excel导出|获取的账单数orderModerns={}", orderModerns.size());
    
            try {
                myLock(es,orderModerns,request,response,companyId);
            } catch (Exception e) {
                e.printStackTrace();
                result=new SystemResult(Dict_transaction_result_code.SYSTEM_EXCEPTION,"账单excel导出|excel系统异常",companyId);
                log.info("账单excel导出result={}", JSONObject.toJSONString(result));
                return result;
            }
    
            result=new SystemResult(Dict_transaction_result_code.SUCCESS,"账单excel导出",companyId);
            log.info("账单excel导出result={}", JSONObject.toJSONString(result));
            long endTime=System.currentTimeMillis();   //获取开始时间
            log.info("=============执行时间time={}ms",endTime-startTime);
            return result;
        }
    
        private void myLock(ExecutorService es,List<ApiPcOrderResp.order> orderModerns, HttpServletRequest request, HttpServletResponse response,long companyId) throws IOException, InterruptedException {
            if (es.isTerminated()) {
                excelService.excelOrder(orderModerns,request,response,companyId);
            } else {
                Thread.sleep(1000 * 60); // 1000 毫秒，也就是1秒.1分
                myLock(es,orderModerns,request,response,companyId);
            }
        }
    
        private void getAllNames(List<ApiPcOrderResp.order> orderModerns, OrderOrder order) {
            ApiPcOrderResp.order orderModern = new ApiPcOrderResp.order();
    
            SysOrganization sysOrganization = sysOrganizationDao.getOne(order.getOrganizationId());
            //根据人员id查询人员信息
            CrmUser user = crmUserDao.getOne(order.getOrderUser());
            //根据人员id查询部门名称
            SysCoBranch sysCoBranch = sysCoBranchDao.getOne(user.getSysCoBranchId());
    
            orderModern.setOrderId(order.getOrderId());
            orderModern.setOrderNum(order.getOrderNum());
            orderModern.setWaterNum(order.getWaterNum());
            orderModern.setAmount(order.getAmount());
            orderModern.setDescription(order.getDescription());
            orderModern.setPayStatus(order.getPayStatus());
            orderModern.setOrderDate(order.getOrderDate());
            orderModern.setOrderUser(order.getOrderUser());
            orderModern.setOrganizationId(order.getOrganizationId());
            orderModern.setRemark(order.getRemark());
            orderModern.setModifyUser(order.getModifyUser());
            orderModern.setModifyTime(order.getModifyTime());
            orderModern.setSystemTime(order.getSystemTime());
            orderModern.setPayUserName(user.getName());
            orderModern.setOrganizationName(sysOrganization.getName());
            orderModern.setBranchName(sysCoBranch.getName());
            orderModerns.add(orderModern);
        }

三、改用批量

/**
         * 账单excel导出
         */
        @RequestMapping(value = "/excelOrder")
        public void excelOrder(long companyId,ApiPcOrderReq req, HttpServletRequest request, HttpServletResponse response) {
            log.info("账单excel导出入参companyId={}，ApiPcOrderReq={}", companyId,JSONObject.toJSONString(req));
            long startTime=System.currentTimeMillis();   //获取开始时间
            //根据多条件查询userList
            String name=req.getUserName();
            if (StringUtils.isNotBlank(name))name="%"+name+"%";
           List<Integer> orderUserIds = crmUserDao.findByCondition(name, req.getOrganization_id(),req.getBranch_id());
            log.info("账单excel导出|orderUserIds={}",orderUserIds.size());
    
    
            ArrayList<BigInteger>orderIds = orderOrderDao.findOrderIds(orderUserIds,
                    req.getPayStatus(),
                    req.getOrderDate(),
                    req.getPayTimeStart(),
                    req.getPayTimeEnd());
    
            if (orderIds==null) return ;
            log.info("根据人员id列表拿到订单列表orderIds={}",orderIds.size());
            //根据orderIds联查获取用户信息，部门等
            //Long[] orderIdsArray = orderIds.toArray(new Long[orderIds.size()]);
            //Long[] orderIdsArray = orderIds.stream().toArray(Long[]::new);
            //long [] array = orderIds.stream().mapToLong(t->t.longValue()).toArray();
            Long[] orderIdsArray = new Long[orderIds.size()];
            for (int i = 0; i <orderIds.size() ; i++) {
                orderIdsArray[i]=orderIds.get(i).longValue();
            }
            List<UserOrderInfo> userOrderInfoAll = orderOrderDao.findUserOrderInfoAll(orderIdsArray);
    
            try {
                excelService.excelOrder(userOrderInfoAll,request,response,companyId);
            } catch (IOException e) {
                e.printStackTrace();
            }
    
            result=new SystemResult(Dict_transaction_result_code.SUCCESS,"账单excel导出成功",companyId);
            log.info("账单excel导出result={}", JSONObject.toJSONString(result));
            long endTime=System.currentTimeMillis();   //获取开始时间
            log.info("=============执行时间time={}ms",endTime-startTime);
        }

其实线程应该还是可以的，只是sleep了6000,后来才看到