设为首页收藏本站
开启辅助访问

【业务功能篇27】Springboot+mybatisPlus实现一个月报报表中在线添加/删除问题单需求

 您所在的位置：网站首页 › hbase删除表功能的接口 › 【业务功能篇27】Springboot+mybatisPlus实现一个月报报表中在线添加/删除问题单需求

【业务功能篇27】Springboot+mybatisPlus实现一个月报报表中在线添加/删除问题单需求

2023-06-17 19:56| 来源: 网络整理| 查看: 265

业务场景： 在线自定义编辑报表，通过拖拉组件，形成相应的图形数据，表格数据，富文本等。这里介绍一个接口功能，业务通过一张告警表单，从这个表中搜索单号，然后把对应的记录插入到另外一个表中，前台回显表格就是这个新表插入的记录。

为什么需要插入到新表，而不是直接查原告警表，是因为业务需要对这个数据做修改操作，并且不影响原先的告警表，所以我们需要把查询出来的记录，插入到另外一个表。

交互操作：插入top问题进展数据：前端通过拖拉一个表格组件，然后组件选择数据接口，接着就是给用户传参，可以通过 表单单号，将数据给回显到前端的表单中，多个单号用逗号分割，如果需要去掉已经添加进来的单号，删除后，前端会直接回显新的结果展示， 也就是说，单号的输入框，改变就会直接请求接口，然后实时返回当前单号对应的数据出来，需要我们实现一个接口，通过入参的实时变化，判断哪些是新增数据，就插入表格，哪些是删除数据，就插入表格： 

比如： 一开始输入单号 A,B,C 三个单号，插入三个数据同步到新表中，接着，将此时单号C去掉，那么接口就会判断，把C单号从表中删除，如果把A,B都一起删除，那么就会把A,B单号从表中删除，接着再放入A,B,C,同理 ，插入A,B,C单号记录；

接着加入D 变成A,B,C,D 数据库识别当前A,B,C已存，不会重复插入，D单号记录插入

响应实体类：ResponseVo  package com.xxx.model; import java.util.Objects; import java.io.Serializable; import com.fasterxml.jackson.annotation.JsonIgnoreProperties; import com.fasterxml.jackson.annotation.JsonProperty; @JsonIgnoreProperties(ignoreUnknown = true) public class ResponseVo implements Serializable { private static final long serialVersionUID = 1L; @JsonProperty("code") private Integer code = null; @JsonProperty("data") private Object data = null; @JsonProperty("message") private String message = null; public ResponseVo() { super(); } /** **/ public Integer getCode() { return code; } public void setCode(Integer code) { this.code = code; } /** **/ public Object getData() { return data; } public void setData(Object data) { this.data = data; } /** **/ public String getMessage() { return message; } public void setMessage(String message) { this.message = message; } @Override public boolean equals(Object o) { if (this == o) { return true; } if (o == null || getClass() != o.getClass()) { return false; } ResponseVo responseVo = (ResponseVo) o; return Objects.equals(this.code, responseVo.code) && Objects.equals(this.data, responseVo.data) && Objects.equals(this.message, responseVo.message); } @Override public int hashCode() { return Objects.hash(code , data , message); } @Override public String toString() { StringBuilder sb = new StringBuilder(); sb.append("class ResponseVo { "); sb.append(" code: ").append(code).append(", "); sb.append(" data: ").append(data).append(", "); sb.append(" message: ").append(message).append(", "); sb.append("} "); return sb.toString(); } }

controller层 getRepTopProblem 方法：修改 时间年月，单号信息，进行的增删操作，把最终对应的单号数据返回前端saveRepTopProblem方法：插入单号对应数据记录之后，修改其他字段内容，保存修改数据，数据会返回前端刷新 @RestController @RequestMapping(value = "/ProdProblem", produces = {"application/json;charset=UTF-8"}) @Validated public class ProdProblemWarnController { @Autowired(required=false) private ProdProblemWarnService delegate; @RequestMapping( value = "/getProblem", produces = { "application/json" }, method = RequestMethod.POST) public ResponseVo getRepTopProblem(@RequestBody ProdProblemWarnQueryParam queryParam) throws ServiceException { return delegate.getRepTopProblem(queryParam); } @RequestMapping( value = "/saveRepTopProblem", produces = { "application/json" }, method = RequestMethod.POST) public ResponseVo saveRepTopProblem(@RequestBody ProdProblemWarnQueryParam queryParam) throws ServiceException { return delegate.saveRepTopProblem(queryParam); } }

service层 接口 public interface ProdProblemWarnService { ResponseVo getRepTopProblem(ProdProblemWarnQueryParam queryParam); ResponseVo saveRepTopProblem(ProdProblemWarnQueryParam queryParam); } service层 接口实现类 @Slf4j @Component("prodProblemWarnService") public class ProdProblemWarnServiceImpl implements ProdProblemWarnService { @Resource private QbdReportTopProblemService qbdReportTopProblemService; /** * getRepTopProblem 返回查询的表单数据 * * @param queryParam queryParam 参数类 包括 年year 月month 单号warnno * 整条问题记录 List topProblem * @return ResponseVo */ @Override public ResponseVo getRepTopProblem(ProdProblemWarnQueryParam queryParam) { //取出前端参数类中传递的单号，逗号分割 List warnNo = Arrays.asList(queryParam.getWarnNo().split(",")); //参数类中传递年，月 通过注入接口方法查询 这个问题表单中对应年 月时间 存在的问题单 List existList = qbdReportTopProblemService.selectDataList(queryParam.getYear(), queryParam.getMonth()); //取出该年月的问题单后，再提炼出对应的告警单号 List existWarnNo = existList.stream().map(QbdReportTopProblem::getWarnNo).collect(Collectors.toList()); //表中存在的单号集合，不在前端传参的单号集合内的，过滤出来 比如 existWarnNo：[01,02,03] warnNo:[01,02] 那么03单号就是过滤后的单号集合，表示需要删除 因为前端传参当前年月的 单号集合 只有2个 //数据库中当前年月的也只能有这2个 List delWarnNo = existWarnNo.stream().filter(f -> !warnNo.contains(f)).collect(Collectors.toList()); //同理，在前端传参单号中过滤出不在 数据库内的单号集合 那么就说明 过滤出来的单号数据 是新加入的 需要插入数据库 List addWarnNo = warnNo.stream().filter(f -> !existWarnNo.contains(f)).collect(Collectors.toList()); if (!delWarnNo.isEmpty()) { // 删除 qbdReportTopProblemService.delByWarnNo(delWarnNo, queryParam.getYear(), queryParam.getMonth()); } if (!addWarnNo.isEmpty()) { // 新增 queryParam.setWarnNo(String.join(",", addWarnNo)); //传参类把单号修改成 需要新增的单号字符串 然后调用接口方法 获取在原告警表单中对应需要同步的字段数据 List list = qbdReportTopProblemService.getRepTopProblem(queryParam); //取出需要插入的集合后，需要再设置对应的年 月时间，这两个字段在原告警单中不存在的 是在新表中设计的 前端传参的年月 list.forEach(item -> { item.setYear(queryParam.getYear()); item.setMonth(queryParam.getMonth()); }); //执行插入 qbdReportTopProblemService.batchInsert(list); } //插入完成后 重新查询 新问题表单中对应 年月时间的数据 返回 List result = qbdReportTopProblemService.selectDataList(queryParam.getYear(), queryParam.getMonth()); return ResponseUtils.successResponse(result, "获取数据成功！"); } /** * 修改问题单后进行保存 * 修改记录问题都放到参数类的list属性内 List topProblem */ @Override public ResponseVo saveRepTopProblem(ProdProblemWarnQueryParam queryParam) { //定义一个新表单类集合 用来传递给mapper接口去指定修改记录 List list = new ArrayList(); //请求参数中的TopProblem 集合 存放的就是修改的问题单记录 一般前端就是一条条的修改 所以这个list大小就是1 具体还得跟进前端的修改表单设计 queryParam.getTopProblem().forEach(item -> { QbdReportTopProblem target = new QbdReportTopProblem(); //调用类对象浅拷贝，将item前端传递的需要修改的问题单记录 拷贝给 QbdReportTopProblem对应的表实体类 MyBeanUtils.shallowCopy(item, target); //然后拷贝后之后，再加入list结果集合 list.add(target); }); //调用mapper接口 更新list这个修改问题单记录，对应表中 年月时间 以及该单单号 进行相关字段的修改 qbdReportTopProblemService.batchUpdate(list, queryParam.getYear(), queryParam.getMonth()); //最后方法成功信息即可 return ResponseUtils.successResponse("数据更新成功！"); } }

dao层 接口 package com.xxx.service.dao.iservice; import com.xxx.domain.model.QbdReportTopProblem; import com.baomidou.mybatisplus.extension.service.IService; import java.util.List; public interface QbdReportTopProblemService extends IService { void batchInsert(List list); void batchUpdate(List list, int year, int month); void delByWarnNo(List warnNo, int year, int month); List selectDataList(int year, int month); List getRepTopProblem(ProdProblemWarnQueryParam queryParam); } dao层 接口实现类 package com.xxx.service.impl.dao.iservice; import com.xxx.domain.model.QbdReportTopProblem; import com.xxx.service.dao.iservice.QbdReportTopProblemService; import com.xxx.service.dao.mapper.QbdReportTopProblemMapper; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import org.springframework.stereotype.Service; import java.util.Date; import java.util.List; @Service public class QbdReportTopProblemServiceImpl extends ServiceImpl implements QbdReportTopProblemService { @Override public void batchInsert(List list) { list.forEach(item -> { item.setCreationDate(new Date()); baseMapper.insert(item); }); } @Override public void batchUpdate(List list, int year, int month) { for (QbdReportTopProblem item:list) { baseMapper.update(null, new LambdaUpdateWrapper() .eq(QbdReportTopProblem::getYear, year) .eq(QbdReportTopProblem::getMonth, month) .eq(QbdReportTopProblem::getWarnNo, item.getWarnNo()) .set(QbdReportTopProblem::getWarnType, item.getWarnType()) .set(QbdReportTopProblem::getWarnStatus, item.getWarnStatus()) .set(QbdReportTopProblem::getWarnDate, item.getWarnDate()) .set(QbdReportTopProblem::getProduct, item.getProduct()) .set(QbdReportTopProblem::getProject, item.getProject()) .set(QbdReportTopProblem::getCategory, item.getCategory()) .set(QbdReportTopProblem::getSubCategory, item.getSubCategory()) .set(QbdReportTopProblem::getOccurCause, item.getOccurCause()) .set(QbdReportTopProblem::getEncloseMeasure, item.getEncloseMeasure()) .set(QbdReportTopProblem::getProblemDesc, item.getProblemDesc()) .set(QbdReportTopProblem::getLastUpdateDate, new Date()) ); }; } @Override public void delByWarnNo(List warnNo, int year, int month) { baseMapper.delete(new LambdaQueryWrapper() .in(QbdReportTopProblem::getWarnNo, warnNo) .eq(QbdReportTopProblem::getYear, year) .eq(QbdReportTopProblem::getMonth, month) ); } @Override public List selectDataList(int year, int month) { return baseMapper.selectDataList(year, month); } } mapper接口 package com.xxx.service.dao.mapper; import com.xxx.domain.model.QbdReportTopProblem; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; @Mapper public interface QbdReportTopProblemMapper extends BaseMapper { List selectDataList(@Param("year") int year, @Param("month") int month); List getRepTopProblem(@Param("queryParam") ProdProblemWarnQueryParam queryParam); }

XML映射

getRepTopProblem方法:

用到两个函数 

ROW_NUMBER(): 排名函数 目的就是给结果集增加一列序号 find_in_set(str，strlist): 查询 在strlist字符串中，是否包含str 比如find_in_set('a','a,b,c') 返回true

解析：

     (select issure_desc from issure_feed_f where issure_desc is not null and issure_desc != '' and find_in_set(issure_no, mes_no) limit 1) problemDesc

这里issure_desc 这个字段，是从另外一张生产问题单表同步的问题描述字段，需要怎么关联，

这里生产问题单中有生产问题单号issure_no,

而告警表单中有预警单号，还有对应告警的关联的生产问题单号mes_no,可能对应有多个生产问题单号，所以这个mes_no可能是 01,02,03 逗号拼接多个单号的内容，那么我们告警单要同步出一个生产问题单的问题描述，这里有多个，我们只取其一非空内容的即可

所以：issure_desc is not null and issure_desc != ''   非空判断， 

find_in_set(issure_no, mes_no) limit 1  生产问题单表的issure_no单号 包含在mes_no多个单号中 返回其一条

ID, YEAR, MONTH, WARN_NO, WARN_TYPE, WARN_STATUS, WARN_DATE, PRODUCT, PROJECT, CATEGORY, SUB_CATEGORY, OCCUR_CAUSE, ENCLOSE_MEASURE, PROBLEM_DESC, CREATION_DATE, LAST_UPDATE_DATE select from top_problem where year = #{year} and month = #{month} select ROW_NUMBER() over(order by warn_date) rowNum, warn_no warnNo, warn_type warnType, warn_status warnStatus, date_format(warn_date,'%Y-%m-%d %H:%i:%s') warnDate, product, project, category, sub_category subCategory, occur_Cause occurCause, enclose_Measure encloseMeasure, (select issure_desc from issure_feed_f where issure_desc is not null and issure_desc != '' and find_in_set(issure_no, mes_no) limit 1) problemDesc from dwr_mt_production_problem_warn_f where find_in_set(warn_no, #{queryParam.warnNo});

设计响应实体类 package com.xxx.qualitybigdata.model; import java.util.Objects; import java.io.Serializable; import com.fasterxml.jackson.annotation.JsonIgnoreProperties; import com.fasterxml.jackson.annotation.JsonProperty; @JsonIgnoreProperties(ignoreUnknown = true) public class ResponseVo implements Serializable { private static final long serialVersionUID = 1L; @JsonProperty("code") private Integer code = null; @JsonProperty("data") private Object data = null; @JsonProperty("message") private String message = null; public ResponseVo() { super(); } /** **/ public Integer getCode() { return code; } public void setCode(Integer code) { this.code = code; } /** **/ public Object getData() { return data; } public void setData(Object data) { this.data = data; } /** **/ public String getMessage() { return message; } public void setMessage(String message) { this.message = message; } @Override public boolean equals(Object o) { if (this == o) { return true; } if (o == null || getClass() != o.getClass()) { return false; } ResponseVo responseVo = (ResponseVo) o; return Objects.equals(this.code, responseVo.code) && Objects.equals(this.data, responseVo.data) && Objects.equals(this.message, responseVo.message); } @Override public int hashCode() { return Objects.hash(code , data , message); } @Override public String toString() { StringBuilder sb = new StringBuilder(); sb.append("class ResponseVo { "); sb.append(" code: ").append(code).append(", "); sb.append(" data: ").append(data).append(", "); sb.append(" message: ").append(message).append(", "); sb.append("} "); return sb.toString(); } }

package com.xxx.utils; import com.xxx.qualitybigdata.model.ResponseVo; import com.xxx.domain.entity.VerifyInfo; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import org.springframework.stereotype.Component; import java.util.HashMap; import java.util.List; import java.util.Map; @Component public class ResponseUtils { /** * 成功响应 */ public static ResponseVo parseVerifyResponse(VerifyInfo verifyInfo) { ResponseVo resp = new ResponseVo(); resp.setCode(verifyInfo.isPass() ? 200 : 500); resp.setData(verifyInfo.getMessage()); resp.setMessage(verifyInfo.getMessage()); return resp; } /** * 成功响应 */ public static ResponseVo successResponse(Object data, String message) { ResponseVo resp = new ResponseVo(); resp.setCode(200); resp.setData(data); resp.setMessage(message); return resp; } public static ResponseVo successResponse(String message) { ResponseVo resp = new ResponseVo(); resp.setCode(200); resp.setData(message); return resp; } /** * 错误响应 */ public static ResponseVo errorResponse(Object data, String message) { ResponseVo resp = new ResponseVo(); resp.setCode(500); resp.setData(data); resp.setMessage(message); return resp; } public static ResponseVo errorResponse(String message) { ResponseVo resp = new ResponseVo(); resp.setCode(500); resp.setMessage(message); return resp; } /** * 错误响应 */ public static ResponseVo unAuthResponse(Object data, String message) { ResponseVo resp = new ResponseVo(); resp.setCode(500); resp.setData(data); resp.setMessage(message); return resp; } /** * 分页数据返回，返回时会带有total * * @param list 需要返回的数据，只能为List类型 * @param page 分页对象 * @param * @return */ public static ResponseVo successPageResponse(List list, Page page) { Map result = new HashMap(8); result.put("page", page.getTotal()); result.put("list", list); ResponseVo resp = new ResponseVo(); resp.setCode(200); resp.setData(result); return resp; } public static ResponseVo successPageResponse(List list, long page) { Map result = new HashMap(8); result.put("page", page); result.put("list", list); ResponseVo resp = new ResponseVo(); resp.setCode(200); resp.setData(result); return resp; } } package com.XXX.domain.entity; import lombok.Data; @Data public class VerifyInfo { private boolean pass; private String message; public VerifyInfo() { this.pass = true; } /** * 验证不通过 */ public VerifyInfo notPassed(String message) { this.setPass(false); this.setMessage(message); return this; } /** * 验证通过 */ public VerifyInfo pass(String message) { this.setPass(true); this.setMessage(message); return this; } }

转换对象工具类 MyBeanUtils package com.xxx.utils; import com.alibaba.fastjson.JSONObject; import com.alibaba.fastjson.TypeReference; import org.springframework.beans.BeanUtils; import java.util.List; import java.util.Map; /** * MyBeanUtils * * @author: q30031797 * @since: 1.0 */ /** * 对象之间的转换工具类 * * @see JsonUtils */ public class MyBeanUtils { // 浅拷贝 public static void shallowCopy(Object source, Object target) { BeanUtils.copyProperties(source, target); } /** * 强转为List(常用于强转前端参数) * * @param obj 实际类型为List的Object */ public static List castList(Object obj) { String json = JsonUtils.objectToJson(obj); return JSONObject.parseObject(json, new TypeReference(){}); } /** * 强转为Map(常用于强转前端参数) * * @param obj 实际类型为Map的Object */ public static Map castMap(Object obj) { String json = JsonUtils.objectToJson(obj); return JSONObject.parseObject(json, new TypeReference(){}); } }

这里提到的json工具类的转换，可以看这一篇文章 【业务功能篇01】Springboot+mybatis-plus+POI实现表单数据导出Excel    JsonUtils类



【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3