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

 账单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,后来才看到