使用easyexcel导入导出模板
1.实体类字段 数据库对应的字段
- 使用easyexcel
- @HeadRowHeight(30) @ColumnWidth(25) //设置其表表头列宽行高大小
-
@HeadFontStyle(fontHeightInPoints = 12) 标题字体样式 字大小为12
- HeadStyle 标题样式
相当于写 出excel的实体类
package com.lzy.project.project.domain;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import com.lzy.common.exception.BaseException;
import com.lzy.framework.aspectj.lang.annotation.Excel;
import lombok.Getter;
import java.util.regex.Pattern;
@Getter
@HeadRowHeight(30)
@ColumnWidth(25)
@HeadFontStyle(fontHeightInPoints = 12)
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER,//水平居中
verticalAlignment = VerticalAlignmentEnum.CENTER,//垂直居中
borderLeft = BorderStyleEnum.MEDIUM,//左边框细线
borderRight = BorderStyleEnum.MEDIUM,//右边框细线
borderTop = BorderStyleEnum.MEDIUM,//顶部
borderBottom = BorderStyleEnum.MEDIUM,//下部
fillBackgroundColor = 26)//表头样式
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER,//水平居中
verticalAlignment = VerticalAlignmentEnum.CENTER,//垂直居中
borderLeft = BorderStyleEnum.MEDIUM, //左边框细线
borderRight = BorderStyleEnum.MEDIUM, //右边框细线
borderTop = BorderStyleEnum.MEDIUM, //顶部
borderBottom = BorderStyleEnum.MEDIUM) //下部
//内容样式 index 表示对应生成excel列的索引值
public class ProjectZbfxgzExcel {
/**
* 项目id
*/
@Excel(name = "项目id")
@ExcelIgnore //表示生成的表中没有这个字段列
private Long xmid;
/**
* 标题
*/
@ExcelProperty(value = "风险标题", index = 0)
private String title;
/**
* 风险等级
*/
@ExcelProperty(value = "风险等级", index = 2)
private String fxdj;
/**
* 风险类型
*/
@ExcelProperty(value = "风险类型", index = 3)
private String fxlx;
/**
* 风险问题描述
*/
@ExcelProperty(value = "风险问题描述", index = 4)
private String fxwtms;
/**
* 解决方式
*/
@ExcelProperty(value = "解决方式", index = 5)
private String jjfs;
/**
* 解决方案描述
*/
@ExcelProperty(value = "解决方案描述", index = 7)
private String jjfams;
/**
* 解决日期
*/
@ExcelProperty(value = "解决日期", index = 6)
@DateTimeFormat("yyyy-MM-dd")
private String jjrq;
/**
* 添加人员
*/
@ExcelIgnore
private Long addUser;
/**
* 问题发现日期
*/
@ExcelProperty(value = "问题发现日期", index = 1)
@DateTimeFormat("yyyy-MM-dd") //设置格式
private String problemFoundDate;
/**
* 修改人员
*/
@ExcelIgnore
private Long editUser;
public void setXmid(Long xmid) {
this.xmid = xmid;
}
public void setTitle(String title) {
this.title = title;
}
//判断 excel中的值与数据库的对应匹配 index 数据库中存储为0,1,2,3的数字 excel中存储为文字 进行匹配
public void setFxdj(String fxdj) {
if (fxdj.equals("紧急") || fxdj.equals("一般") || fxdj.equals("缓慢")) {
this.fxdj = (fxdj.equals("紧急") ? "0" : (fxdj.equals("一般") ? "1" : "2"));
} else {
throw new BaseException("风险等级应该填写紧急,一般,缓慢");
}
}
public void setFxlx(String fxlx) {
if (fxlx.equals("内部") || fxlx.equals("外部") || fxlx.equals("不可规避")) {
this.fxlx = (fxlx.equals("内部") ? "0" : (fxlx.equals("外部") ? "1" : "2"));
} else {
throw new BaseException("风险类型应该填写内部,外部,不可规避");
}
}
public void setFxwtms(String fxwtms) {
this.fxwtms = fxwtms;
}
public void setJjfs(String jjfs) {
if (jjfs.equals("转移") || jjfs.equals("接受") || jjfs.equals("回避") || jjfs.equals("减轻")) {
this.jjfs = (jjfs.equals("转移") ? "0" : jjfs.equals("接受") ? "1" : jjfs.equals("回避") ? "2" : "3");
} else {
throw new BaseException("解决方式应该填写转移,接受,回避,减轻");
}
}
public void setJjfams(String jjfams) {
this.jjfams = jjfams;
}
public void setJjrq(String jjrq) {
this.jjrq = jjrq;
}
public void setAddUser(Long addUser) {
this.addUser = addUser;
}
public void setEditUser(Long editUser) {
this.editUser = editUser;
}
//对传入的信息做判断 正则表达式 规定格式为2020-02-02 2022/05/02
public void setProblemFoundDate(String problemFoundDate) {
String rule1 = "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|" +
"((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|" +
"((0[48]|[2468][048]|[3579][26])00))-02-29)$";
String rule2 = "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})\\/(((0[13578]|1[02])\\/(0[1-9]|[12][0-9]|3[01]))|" +
"((0[469]|11)\\/(0[1-9]|[12][0-9]|30))|(02\\/(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|" +
"((0[48]|[2468][048]|[3579][26])00))\\/02\\/29)$";
if (Pattern.matches(rule1, problemFoundDate) || Pattern.matches(rule2, problemFoundDate)) {
this.problemFoundDate = problemFoundDate;
} else {
throw new BaseException("问题发现日期的格式错误,应该为YYYY/MM/DD或者YYYY-MM-DD,例如:2023/01/01");
}
}
}
controller
package com.lzy.project.easyexcel.ExcelController;
import com.alibaba.excel.EasyExcel;
import com.lzy.framework.security.service.TokenService;
import com.lzy.framework.web.domain.AjaxResult;
import com.lzy.project.easyexcel.ExcelAnalysisEventListener.CustomSheetWriteHandler;
import com.lzy.project.easyexcel.ExcelAnalysisEventListener.ProjectZbfxgzListener;
import com.lzy.project.project.domain.ProjectZbfxgzExcel;
import com.lzy.project.project.service.IProjectZbfxgzService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
@Api(tags = "风险管理Excel处理")
@RequestMapping("/projectZbfxgz/excel")
@RestController
@Slf4j
public class ProjectZbfxgzExcelController {
@Autowired
private IProjectZbfxgzService projectZbfxgzService;
@Autowired
private TokenService tokenService;
//导入时候的controller
@PostMapping("/importExcel")
@ApiOperation(notes = "导入数据", value = "导入数据", httpMethod = "POST")
public AjaxResult importExcel(@RequestParam("file") MultipartFile file,
//需要每一个的id 传一个xmid
@RequestParam("xmid")Long xmid) {
try {
// 解析excel 利用easyexcel导入的时候需要一个这样的监听器 就是读
ProjectZbfxgzListener projectZbfxgzListener = new ProjectZbfxgzListener(projectZbfxgzService, xmid, tokenService);
EasyExcel.read(file.getInputStream(),ProjectZbfxgzExcel.class,projectZbfxgzListener).sheet(0).doRead();
} catch (Exception e) {
e.printStackTrace();
return AjaxResult.error(e.getMessage());
}
return AjaxResult.success();
}
//写数据
@GetMapping("/downloadExcelTem")
@ApiOperation(notes = "导入数据", value = "导入数据", httpMethod = "POST")
public AjaxResult downloadExcelTem(HttpServletResponse response) {
try {
// data中的值给一个空值根据实体类创建
ArrayList<Object> objects = new ArrayList<>();
EasyExcel.write(response.getOutputStream(),
//给写出的表格添加样式
ProjectZbfxgzExcel.class).registerWriteHandler(new CustomSheetWriteHandler())
.registerWriteHandler(new CustomSheetWriteHandler()).useDefaultStyle(false)
.sheet("风险问题模板").doWrite(objects);
} catch (Exception e) {
e.printStackTrace();
return AjaxResult.error(e.getMessage());
}
return AjaxResult.success();
}
}
//创建总监听器
package com.lzy.project.easyexcel.ExcelAnalysisEventListener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.google.common.collect.Lists;
import com.lzy.common.exception.BaseException;
import com.lzy.common.utils.DateUtils;
import com.lzy.common.utils.ServletUtils;
import com.lzy.framework.security.LoginUser;
import com.lzy.framework.security.service.TokenService;
import com.lzy.project.easyexcel.mapper.ProjectZbfxgzExcelMapper;
import com.lzy.project.project.domain.ProjectZbfxgz;
import com.lzy.project.project.domain.ProjectZbfxgzExcel;
import com.lzy.project.project.service.IProjectZbfxgzService;
import lombok.extern.slf4j.Slf4j;
import org.mapstruct.factory.Mappers;
import org.springframework.util.StringUtils;
import java.util.List;
//导入easyexcel的jar包 缓存数据
@Slf4j
public class ProjectZbfxgzListener extends HorizontalCellStyleStrategy implements ReadListener<ProjectZbfxgzExcel> {
private final IProjectZbfxgzService projectZbfxgzService;
/**
* 缓存池最大值大小
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存池
*/
private List<ProjectZbfxgzExcel> cachedDataList = Lists.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 实体类转换
*/
private static ProjectZbfxgzExcelMapper INSTANCE = Mappers.getMapper(ProjectZbfxgzExcelMapper.class);
/**
* 保存实体类
*/
private ProjectZbfxgzExcel projectZbfxgz;
/**
* 项目id
*/
private Long xmid;
/**
* 通过tokenService获取信息
*/
private TokenService tokenService;
/**
* 判断excel中的值是否为null 设置一个flag
*/
private Boolean flag = false;
/**
* 监听器构造器
*
* @param projectZbfxgzService
*/
public ProjectZbfxgzListener(IProjectZbfxgzService projectZbfxgzService, Long xmid, TokenService tokenService) {
// excelEntityFieldMap 对应Excel中的key值与实体类中字段的索引保持一致 拿到excel中的值 key value 都拿到了
this.projectZbfxgzService = projectZbfxgzService;
this.projectZbfxgz = new ProjectZbfxgzExcel();
this.xmid = xmid;
this.tokenService = tokenService;
}
@Override
public void invoke(ProjectZbfxgzExcel projectZbfxgzExcel, AnalysisContext analysisContext) {
//进入这个方法进行判断为空抛出异常操作
if (StringUtils.isEmpty(projectZbfxgzExcel.getTitle())){throw new BaseException("风险问题标题不能为空");}
if (StringUtils.isEmpty(projectZbfxgzExcel.getProblemFoundDate())){throw new BaseException("问题发现日期不能为空");}
if (StringUtils.isEmpty(projectZbfxgzExcel.getFxdj())){throw new BaseException("风险等级不能为空");}
if (StringUtils.isEmpty(projectZbfxgzExcel.getFxlx())){throw new BaseException("风险类型不能为空");}
if (StringUtils.isEmpty(projectZbfxgzExcel.getFxwtms())){throw new BaseException("风险问题描述不能为空");}
cachedDataList.add(projectZbfxgzExcel);
if (cachedDataList.size() >= BATCH_COUNT) {
save();
// 存储完成清理 list
cachedDataList = Lists.newArrayListWithExpectedSize(BATCH_COUNT);
}
flag = true;
}
/**
* 解析数据完成
*
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("输出 end ***********************************************");
if (flag){
save();
}else {
throw new BaseException("风险管理表格中不能为空");
}
}
private Boolean save() {
//把拿到的缓存放进去
List<ProjectZbfxgz> projectZbfxgzs = INSTANCE.toTargetlist(cachedDataList);
try {
for (ProjectZbfxgz data : projectZbfxgzs) {
LoginUser loginUser = tokenService.getLoginUser(ServletUtils.getRequest());
data.setAddUser(loginUser.getUser().getUserId());
data.setAddTime(DateUtils.getTime());
data.setXmid(xmid);
projectZbfxgzService.insertProjectZbfxgz(data);
}
} catch (Exception e) {
log.info("数据插入异常!");
return false;
}
return true;
}
}
mapstruct转换器 直接使用这样转换用自己的实体类获取excel然后用原始的传sql语句
package com.lzy.project.easyexcel.mapper;
import com.lzy.project.project.domain.ProjectZbfxgz;
import com.lzy.project.project.domain.ProjectZbfxgzExcel;
import org.mapstruct.Mapper;
import java.util.List;
@Mapper
public interface ProjectZbfxgzExcelMapper {
// @Mapping(source = "sourceName", target = "targetName")
ProjectZbfxgz toTarget(ProjectZbfxgzExcel source);
List<ProjectZbfxgz> toTargetlist(List<ProjectZbfxgzExcel> source);
}
原始的实体类
package com.lzy.project.project.domain;
import com.lzy.framework.aspectj.lang.annotation.Excel;
import com.lzy.framework.web.domain.BaseEntity;
/**
* 周报风险跟踪对象 project_zbfxgz
*
* @author gaochunhu
* @date 2021-09-24
*/
public class ProjectZbfxgz extends BaseEntity {
private static final long serialVersionUID = 1L;
/**
* 主键
*/
private Long id;
/**
* 标题
*/
private String title;
/**
* 问题发现时间
*/
private String problemFoundDate;
/**
* 是否解决
*/
private int isSolve;
/**
* 周报id
*/
@Excel(name = "周报id")
private Long zbid;
/**
* 项目id
*/
@Excel(name = "项目id")
private Long xmid;
/**
* 风险登记
*/
@Excel(name = "风险登记")
private String fxdj;
/**
* 风险类型
*/
@Excel(name = "风险类型")
private String fxlx;
/**
* 风险问题描述
*/
@Excel(name = "风险问题描述")
private String fxwtms;
/**
* 解决方式
*/
@Excel(name = "解决方式")
private String jjfs;
/**
* 解决方案描述
*/
@Excel(name = "解决方案描述")
private String jjfams;
/**
* 解决日期
*/
@Excel(name = "解决日期")
private String jjrq;
/**
* 是否解决
*/
@Excel(name = "是否解决")
private String jjFlag;
/**
* 添加人员
*/
@Excel(name = "添加人员")
private Long addUser;
/**
* 添加时间
*/
@Excel(name = "添加时间")
private String addTime;
/**
* 修改人员
*/
@Excel(name = "修改人员")
private Long editUser;
/**
* 修改时间
*/
@Excel(name = "修改时间")
private String editTime;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getZbid() {
return zbid;
}
public void setZbid(Long zbid) {
this.zbid = zbid;
}
public Long getXmid() {
return xmid;
}
public void setXmid(Long xmid) {
this.xmid = xmid;
}
public String getFxdj() {
return fxdj;
}
public void setFxdj(String fxdj) {
this.fxdj = fxdj;
}
public String getFxlx() {
return fxlx;
}
public void setFxlx(String fxlx) {
this.fxlx = fxlx;
}
public String getFxwtms() {
return fxwtms;
}
public void setFxwtms(String fxwtms) {
this.fxwtms = fxwtms;
}
public String getJjfs() {
return jjfs;
}
public void setJjfs(String jjfs) {
this.jjfs = jjfs;
}
public String getJjfams() {
return jjfams;
}
public void setJjfams(String jjfams) {
this.jjfams = jjfams;
}
public String getJjrq() {
return jjrq;
}
public void setJjrq(String jjrq) {
this.jjrq = jjrq;
}
public String getJjFlag() {
return jjFlag;
}
public void setJjFlag(String jjFlag) {
this.jjFlag = jjFlag;
}
public Long getAddUser() {
return addUser;
}
public void setAddUser(Long addUser) {
this.addUser = addUser;
}
public String getAddTime() {
return addTime;
}
public void setAddTime(String addTime) {
this.addTime = addTime;
}
public Long getEditUser() {
return editUser;
}
public void setEditUser(Long editUser) {
this.editUser = editUser;
}
public String getEditTime() {
return editTime;
}
public void setEditTime(String editTime) {
this.editTime = editTime;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getProblemFoundDate() {
return problemFoundDate;
}
public void setProblemFoundDate(String problemFoundDate) {
this.problemFoundDate = problemFoundDate;
}
public int getIsSolve() {
return isSolve;
}
public void setIsSolve(int isSolve) {
this.isSolve = isSolve;
}
}
创建一个下拉框的监听器
package com.lzy.project.easyexcel.ExcelAnalysisEventListener;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
@Slf4j
public class CustomSheetWriteHandler implements SheetWriteHandler {
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
log.info("第{}个Sheet写入成功。", context.getWriteSheetHolder().getSheetNo());
// 区间设置 第一列第一行和第二行的数据。由于第一行是头,所以第一、二行的数据实际上是第二三行
//CellRangeAddressList(1, 10, 2, 2);创建1到10行的数据下拉框 第三列到第三列
CellRangeAddressList fxdjCellRangeAddressList = new CellRangeAddressList(1, 10, 2, 2);
//创建1到10行的数据下拉框 第4列到第4列
CellRangeAddressList fxlxCellRangeAddressList = new CellRangeAddressList(1, 10, 3, 3);
CellRangeAddressList jjfsCellRangeAddressList = new CellRangeAddressList(1, 10, 5, 5);
//获取他的表格数据
DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
//设置每一个的下拉框值
DataValidationConstraint fxdjConstraint = helper.createExplicitListConstraint(new String[] {"紧急", "一般" ,"缓慢"});
DataValidationConstraint fxlxConstraint = helper.createExplicitListConstraint(new String[] {"内部", "外部" ,"不可规避"});
DataValidationConstraint jjfsConstraint = helper.createExplicitListConstraint(new String[] {"转移", "接受" ,"回避", "减轻"});
//设置每一个列的值
DataValidation fxdjDataValidation = helper.createValidation(fxdjConstraint, fxdjCellRangeAddressList);
DataValidation fxlxDataValidation = helper.createValidation(fxlxConstraint, fxlxCellRangeAddressList);
DataValidation jjfsDataValidation = helper.createValidation(jjfsConstraint, jjfsCellRangeAddressList);
//最后把他放进去
context.getWriteSheetHolder().getSheet().addValidationData(fxdjDataValidation);
context.getWriteSheetHolder().getSheet().addValidationData(fxlxDataValidation);
context.getWriteSheetHolder().getSheet().addValidationData(jjfsDataValidation);
}
}
在实现类中进行判断 是否解决
package com.lzy.project.project.service.impl;
import java.util.List;
import com.lzy.common.utils.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.lzy.project.project.domain.ProjectZbfxgz;
import com.lzy.project.project.mapper.ProjectZbfxgzMapper;
import com.lzy.project.project.service.IProjectZbfxgzService;
/**
* 周报风险跟踪Service业务层处理
*
* @author gaochunhu
* @date 2021-09-24
*/
@Service
public class ProjectZbfxgzServiceImpl implements IProjectZbfxgzService {
@Autowired
private ProjectZbfxgzMapper projectZbfxgzMapper;
/**
* 查询周报风险跟踪
*
* @param id 周报风险跟踪ID
* @return 周报风险跟踪
*/
@Override
public ProjectZbfxgz selectProjectZbfxgzById(Long id) {
return projectZbfxgzMapper.selectProjectZbfxgzById(id);
}
/**
* 查询周报风险跟踪列表
*
* @param projectZbfxgz 周报风险跟踪
* @return 周报风险跟踪
*/
@Override
public List<ProjectZbfxgz> selectProjectZbfxgzList(ProjectZbfxgz projectZbfxgz) {
return projectZbfxgzMapper.selectProjectZbfxgzList(projectZbfxgz);
}
/**
* 新增周报风险跟踪
*
* @param projectZbfxgz 周报风险跟踪
* @return 结果
*/
//判断是否为空是否解决
@Override
public int insertProjectZbfxgz(ProjectZbfxgz projectZbfxgz) {
if(StringUtils.isEmpty(projectZbfxgz.getJjfs()) && StringUtils.isEmpty(projectZbfxgz.getJjrq()) && StringUtils.isEmpty(projectZbfxgz.getJjfams())){
projectZbfxgz.setIsSolve(0);
}else {
projectZbfxgz.setIsSolve(1);
}
return projectZbfxgzMapper.insertProjectZbfxgz(projectZbfxgz);
}
/**
* 修改周报风险跟踪
*
* @param projectZbfxgz 周报风险跟踪
* @return 结果
*/
@Override
public int updateProjectZbfxgz(ProjectZbfxgz projectZbfxgz) {
if(StringUtils.isEmpty(projectZbfxgz.getJjfs()) && StringUtils.isEmpty(projectZbfxgz.getJjrq()) && StringUtils.isEmpty(projectZbfxgz.getJjfams())){
projectZbfxgz.setIsSolve(0);
}else {
projectZbfxgz.setIsSolve(1);
}
return projectZbfxgzMapper.updateProjectZbfxgz(projectZbfxgz);
}
/**
* 批量删除周报风险跟踪
*
* @param ids 需要删除的周报风险跟踪ID
* @return 结果
*/
@Override
public int deleteProjectZbfxgzByIds(Long[] ids) {
return projectZbfxgzMapper.deleteProjectZbfxgzByIds(ids);
}
/**
* 删除周报风险跟踪信息
*
* @param id 周报风险跟踪ID
* @return 结果
*/
@Override
public int deleteProjectZbfxgzById(Long id) {
return projectZbfxgzMapper.deleteProjectZbfxgzById(id);
}
}
mapper
package com.lzy.project.project.mapper;
import java.util.List;
import com.lzy.project.project.domain.ProjectZbfxgz;
/**
* 周报风险跟踪Mapper接口
*
* @author gaochunhu
* @date 2021-09-24
*/
public interface ProjectZbfxgzMapper {
/**
* 查询周报风险跟踪
*
* @param id 周报风险跟踪ID
* @return 周报风险跟踪
*/
ProjectZbfxgz selectProjectZbfxgzById(Long id);
/**
* 查询周报风险跟踪
*
* @param zbid 周报风险跟踪ID
* @return 周报风险跟踪
*/
List<ProjectZbfxgz> selectProjectZbfxgzByZbId(Long zbid);
/**
* 查询周报风险跟踪列表
*
* @param projectZbfxgz 周报风险跟踪
* @return 周报风险跟踪集合
*/
List<ProjectZbfxgz> selectProjectZbfxgzList(ProjectZbfxgz projectZbfxgz);
/**
* 新增周报风险跟踪
*
* @param projectZbfxgz 周报风险跟踪
* @return 结果
*/
int insertProjectZbfxgz(ProjectZbfxgz projectZbfxgz);
/**
* 修改周报风险跟踪
*
* @param projectZbfxgz 周报风险跟踪
* @return 结果
*/
int updateProjectZbfxgz(ProjectZbfxgz projectZbfxgz);
/**
* 删除周报风险跟踪
*
* @param id 周报风险跟踪ID
* @return 结果
*/
int deleteProjectZbfxgzById(Long id);
/**
* 批量删除周报风险跟踪
*
* @param ids 需要删除的数据ID
* @return 结果
*/
int deleteProjectZbfxgzByIds(Long[] ids);
}
mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lzy.project.project.mapper.ProjectZbfxgzMapper">
<resultMap type="ProjectZbfxgz" id="ProjectZbfxgzResult">
<result property="id" column="id"/>
<result property="zbid" column="zbid"/>
<result property="xmid" column="xmid"/>
<result property="title" column="title"/>
<result property="isSolve" column="is_solve"/>
<result property="fxdj" column="fxdj"/>
<result property="fxlx" column="fxlx"/>
<result property="fxwtms" column="fxwtms"/>
<result property="jjfs" column="jjfs"/>
<result property="jjfams" column="jjfams"/>
<result property="jjrq" column="jjrq"/>
<result property="jjFlag" column="jj_flag"/>
<result property="addUser" column="add_user"/>
<result property="addTime" column="add_time"/>
<result property="problemFoundDate" column="problem_found_date"/>
<result property="editUser" column="edit_user"/>
<result property="editTime" column="edit_time"/>
<result property="title" column="title"/>
</resultMap>
<sql id="selectProjectZbfxgzVo">
select id, zbid, xmid, fxdj, fxlx, fxwtms, jjfs, jjfams, jjrq, jj_flag, add_user, add_time, edit_user, edit_time, title,problem_found_date,
is_solve
from project_zbfxgz
</sql>
<select id="selectProjectZbfxgzList" parameterType="ProjectZbfxgz" resultMap="ProjectZbfxgzResult">
<include refid="selectProjectZbfxgzVo"/>
<where>
<if test="zbid != null ">and zbid = #{zbid}</if>
<if test="xmid != null ">and xmid = #{xmid}</if>
<if test="title != null ">and title = #{title}</if>
<if test="fxdj != null and fxdj != ''">and fxdj = #{fxdj}</if>
<if test="fxlx != null and fxlx != ''">and fxlx = #{fxlx}</if>
<if test="fxwtms != null and fxwtms != ''">and fxwtms = #{fxwtms}</if>
<if test="jjfs != null and jjfs != ''">and jjfs = #{jjfs}</if>
<if test="jjfams != null and jjfams != ''">and jjfams = #{jjfams}</if>
<if test="jjrq != null and jjrq != ''">and jjrq = #{jjrq}</if>
<if test="jjFlag != null and jjFlag != ''">and jj_flag = #{jjFlag}</if>
<if test="addUser != null ">and add_user = #{addUser}</if>
<if test="addTime != null and addTime != ''">and add_time = #{addTime}</if>
<if test="problemFoundDate != null and problemFoundDate != ''">and problem_found_date = #{problemFoundDate}</if>
<if test="editUser != null ">and edit_user = #{editUser}</if>
<if test="editTime != null and editTime != ''">and edit_time = #{editTime}</if>
</where>
order by add_time desc
</select>
<select id="selectProjectZbfxgzById" parameterType="Long" resultMap="ProjectZbfxgzResult">
<include refid="selectProjectZbfxgzVo"/>
where id = #{id}
</select>
<select id="selectProjectZbfxgzByZbId" parameterType="Long" resultMap="ProjectZbfxgzResult">
<include refid="selectProjectZbfxgzVo"/>
where zbid = #{zbid}
</select>
<insert id="insertProjectZbfxgz" parameterType="ProjectZbfxgz" useGeneratedKeys="true" keyProperty="id">
insert into project_zbfxgz
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="zbid != null ">zbid,</if>
<if test="xmid != null ">xmid,</if>
<if test="title != null ">title,</if>
<if test="isSolve != null ">is_solve,</if>
<if test="fxdj != null and fxdj != ''">fxdj,</if>
<if test="fxlx != null and fxlx != ''">fxlx,</if>
<if test="fxwtms != null and fxwtms != ''">fxwtms,</if>
<if test="jjfs != null and jjfs != ''">jjfs,</if>
<if test="jjfams != null and jjfams != ''">jjfams,</if>
<if test="jjrq != null and jjrq != ''">jjrq,</if>
<if test="jjFlag != null and jjFlag != ''">jj_flag,</if>
<if test="addUser != null ">add_user,</if>
<if test="addTime != null and addTime != ''">add_time,</if>
<if test="problemFoundDate != null and problemFoundDate != ''">problem_found_date,</if>
<if test="editUser != null ">edit_user,</if>
<if test="editTime != null and editTime != ''">edit_time,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="zbid != null ">#{zbid},</if>
<if test="xmid != null ">#{xmid},</if>
<if test="title != null ">#{title},</if>
<if test="isSolve != null ">#{isSolve},</if>
<if test="fxdj != null and fxdj != ''">#{fxdj},</if>
<if test="fxlx != null and fxlx != ''">#{fxlx},</if>
<if test="fxwtms != null and fxwtms != ''">#{fxwtms},</if>
<if test="jjfs != null and jjfs != ''">#{jjfs},</if>
<if test="jjfams != null and jjfams != ''">#{jjfams},</if>
<if test="jjrq != null and jjrq != ''">#{jjrq},</if>
<if test="jjFlag != null and jjFlag != ''">#{jjFlag},</if>
<if test="addUser != null ">#{addUser},</if>
<if test="addTime != null and addTime != ''">#{addTime},</if>
<if test="problemFoundDate != null and problemFoundDate != ''">#{problemFoundDate},</if>
<if test="editUser != null ">#{editUser},</if>
<if test="editTime != null and editTime != ''">#{editTime},</if>
</trim>
</insert>
<update id="updateProjectZbfxgz" parameterType="ProjectZbfxgz">
update project_zbfxgz
<trim prefix="SET" suffixOverrides=",">
<if test="zbid != null ">zbid = #{zbid},</if>
<if test="xmid != null ">xmid = #{xmid},</if>
<if test="title != null ">title = #{title},</if>
<if test="isSolve != null ">is_solve = #{isSolve},</if>
<if test="fxdj != null and fxdj != ''">fxdj = #{fxdj},</if>
<if test="fxlx != null and fxlx != ''">fxlx = #{fxlx},</if>
<if test="fxwtms != null and fxwtms != ''">fxwtms = #{fxwtms},</if>
<if test="jjfs != null and jjfs != ''">jjfs = #{jjfs},</if>
<if test="jjfams != null and jjfams != ''">jjfams = #{jjfams},</if>
<if test="jjrq != null and jjrq != ''">jjrq = #{jjrq},</if>
<if test="jjFlag != null and jjFlag != ''">jj_flag = #{jjFlag},</if>
<if test="addUser != null ">add_user = #{addUser},</if>
<if test="addTime != null and addTime != ''">add_time = #{addTime},</if>
<if test="problemFoundDate != null and problemFoundDate != ''">problem_found_date = #{problemFoundDate},</if>
<if test="editUser != null ">edit_user = #{editUser},</if>
<if test="editTime != null and editTime != ''">edit_time = #{editTime},</if>
</trim>
where id = #{id}
</update>
<delete id="deleteProjectZbfxgzById" parameterType="Long">
delete from project_zbfxgz where id = #{id}
</delete>
<delete id="deleteProjectZbfxgzByIds" parameterType="String">
delete from project_zbfxgz where id in
<foreach item="id" collection="array" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
<delete id="deleteZbfxgzByZbid" parameterType="Long">
delete from project_zbfxgz where zbid =#{zbid}
</delete>
</mapper>
service
package com.lzy.project.project.service;
import java.util.List;
import com.lzy.project.project.domain.ProjectZbfxgz;
/**
* 周报风险跟踪Service接口
*
* @author gaochunhu
* @date 2021-09-24
*/
public interface IProjectZbfxgzService {
/**
* 查询周报风险跟踪
*
* @param id 周报风险跟踪ID
* @return 周报风险跟踪
*/
ProjectZbfxgz selectProjectZbfxgzById(Long id);
/**
* 查询周报风险跟踪列表
*
* @param projectZbfxgz 周报风险跟踪
* @return 周报风险跟踪集合
*/
List<ProjectZbfxgz> selectProjectZbfxgzList(ProjectZbfxgz projectZbfxgz);
/**
* 新增周报风险跟踪
*
* @param projectZbfxgz 周报风险跟踪
* @return 结果
*/
int insertProjectZbfxgz(ProjectZbfxgz projectZbfxgz);
/**
* 修改周报风险跟踪
*
* @param projectZbfxgz 周报风险跟踪
* @return 结果
*/
int updateProjectZbfxgz(ProjectZbfxgz projectZbfxgz);
/**
* 批量删除周报风险跟踪
*
* @param ids 需要删除的周报风险跟踪ID
* @return 结果
*/
int deleteProjectZbfxgzByIds(Long[] ids);
/**
* 删除周报风险跟踪信息
*
* @param id 周报风险跟踪ID
* @return 结果
*/
int deleteProjectZbfxgzById(Long id);
}
给数据库添加字段
ALTER TABLE `project_zbfxgz` ADD title varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '风险问题标题';
ALTER TABLE `project_zbfxgz` ADD problem_found_date varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '问题发现日期';
ALTER TABLE `project_zbfxgz` ADD is_solve char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '是否解决 0:未解决 1:解决';