利用视图实现复杂查询

在这里插入图片描述

💗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());//查询符合要求的UniqueIdList<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");}