💗wei_shuo的个人主页

💫wei_shuo的学习社区

🌐Hello World ！

---

利用视图实现复杂查询

需求：需要对Excel表中导入的四列进行，精准查询（搜索符合这四列的数据），并提供预览后下载功能

实现：创建视图表将四个字段合并存储在一个字段中，搜索的时候搜索合并后视图字段，通过主键查询原表中的数据

创建一个名为 AuthCodeView 的视图。视图是一个虚拟表，它基于现有的表（在这里是 transaction_order 表）的查询结果而创建；视图可以简化复杂的查询操作，并提供一个方便的方式来访问和处理数据

将 transaction_order 表中的数据进行处理和格式化，生成一个新的列 authcode_splice，并将其与 unique_id 列一起包含在视图中：authcode_splice 列是通过连接 auth_code、trade_currency、trade_amount 和 create_at 列的值，并使用连字符 - 进行分隔而得到

CREATE VIEW AuthCodeView AS 
SELECT CONCAT(auth_code,'-',trade_currency,'-',ROUND(trade_amount,2),'-',TO_CHAR(create_at,'YYYY/MM/DD')) AS authcode_splice, unique_id
FROM transaction_order;

Preview

• Controller

    @PostMapping("authCodePreview")
    @ApiOperation("Auth Code 预览")
    public Result<List<AuthCodeResultPreview>> authCodePreview(@RequestParam(name = "file", required = false) MultipartFile file) {
        return Result.succ(chargebackWarnService.authCodeUploadFile(file).getPreviewList());
    }

• Service

 public AuthCodeUpload authCodeUploadFile(MultipartFile file) {
        if (file == null) {
            throw new ManageException("上传文件不能为空");
        }
        StringJoiner joiner = new StringJoiner("<br/>");
        AuthCodeUpload res = new AuthCodeUpload();
        List<AuthCodeResultPreview> previewSuccess = new ArrayList<>();
        List<AuthCodeResultPreview> previewError = new ArrayList<>();
        List<AuthCodeFile> list;

        try {
            @Cleanup InputStream inputStream = file.getInputStream();
            String filename = file.getOriginalFilename().toLowerCase();
            if (!(filename.contains("auth") && filename.contains("code"))) {
                throw new ManageException("请传入正确的Auth Code文件上传模板");
            }
            list = analysisAuthCodeFile(AuthCodeFile.class, inputStream);
            for (AuthCodeFile bean : list) {
                AuthCodeResultPreview preview;
                if (bean.isError()) {
                    joiner.add(bean.getIndex() + ": " + bean.getErrorMessage());
                    preview = new AuthCodeResultPreview(bean.getIndex(), bean.getErrorMessage());
                    previewError.add(preview);
                } else {
                    preview = new AuthCodeResultPreview(bean.getIndex(), bean.getOrder(), bean.getAuthCode());
                    previewSuccess.add(preview);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        //文件内包含错误信息时只展示错误条数, 全部正确时展示所有,并返回总数据条数
        if (previewError.isEmpty()) {
            res.setPreviewList(previewSuccess);
            res.setTotal(previewSuccess.size());
        } else {
            res.setPreviewList(previewError);
            res.setTotal(previewError.size());
        }
        res.setMessage(joiner.toString());
        return res;
    }

    public <T extends AuthCodeFile> List<AuthCodeFile> analysisAuthCodeFile(Class<T> clazz, InputStream is) {
        List<T> list = ExcelTools.excelToList(clazz, is);
        if (list.isEmpty()) {
            throw new ManageException("文件内不包含Auth Code信息, 请传入正确文件进行导入");
        }

        List<AuthCodeFile> files = new ArrayList<>();

        //拼接四个字段进行视图查询
        List<String> searchCondition = list.stream().map(bean -> (bean.getAuthCode() == null ? "" : bean.getAuthCode()) + "-" + (bean.getTradeCurrency() == null ? "" : bean.getTradeCurrency()) + "-" + (bean.getTradeAmount() == null ? "" : bean.getTradeAmount()) + "-" + (bean.getCreateAt() == null ? "" : bean.getCreateAt())).distinct().collect(Collectors.toList());
        //查询符合要求的UniqueId
        List<String> uniqueIdList = orderRepo.listByAuthCodeSplice(searchCondition);
        //根据符合要求的UniqueId获取列表
        List<TransactionOrder> orderList = uniqueIdList.isEmpty() ? new ArrayList<>() : orderRepo.listByUniqueIds(uniqueIdList);

        for (int i = 0; i < searchCondition.size(); i++) {
            String currentAuthCode = searchCondition.get(i).split("-")[0];
            T bean = list.get(i);
            try {
                List<TransactionOrder> collectOrders = orderList.stream().filter(order -> order.getAuthCode().equals(currentAuthCode)).collect(Collectors.toList());
                //如果当前AuthCode没有订单
                if (collectOrders.isEmpty()) {
                    bean.setAuthCode(currentAuthCode);
                    bean.setOrder(null);
                    bean.setIndex(i + 1);
                    files.add(bean);
                }
                //如果当前AuthCode有订单显示到预览界面
                for (TransactionOrder order : collectOrders) {
                    AuthCodeFile authCodeFile = new AuthCodeFile(bean.getAuthCode(), bean.getTradeCurrency(), bean.getTradeAmount(), bean.getCreateAt(), bean.isError(), bean.getErrorMessage(), i + 1, order);
                    files.add(authCodeFile);
                }
            } catch (Exception e) {
                files.add(new AuthCodeFile(i + 1, e.getMessage()));
            }
        }
        return files;
    }

Download

• Controller

@PostMapping("authCodeDownload")
@ApiOperation("Auth Code 下载")
public void authCodeUpload(HttpServletResponse response, @RequestBody AuthCodeDownloadRequest request) throws IOException {
    chargebackWarnService.authCodeDownload(response, request.getList());
}

• Service

  public void authCodeDownload(HttpServletResponse response, List<String> list) throws IOException {
        HashSet<String> stringSet = new LinkedHashSet<>(list);
        List<String> codeList = new ArrayList<>(stringSet);

        List<AuthCodeResultDownload> downloadList =  orderRepo.getListByAuthCode(codeList);
        List<AuthCodeResultDownload> downloadSortList = new ArrayList<>();
        for (String code : codeList){
            for (AuthCodeResultDownload download : downloadList){
                if (download.getAuthCode().equals(code)) {
                    downloadSortList.add(download);
                }
            }
        }

        ExcelTools.download(response, AuthCodeResultDownload.class, downloadSortList, "AuthCodeDownload");
    }

---

🌼 结语：创作不易，如果觉得博主的文章赏心悦目，还请——点赞👍收藏⭐️评论📝

---