excel导入导出 easyexcel+poi样式操作

使用easyexcel导入导出模板
1.实体类字段 数据库对应的字段

  1. 使用easyexcel
  2. @HeadRowHeight(30) @ColumnWidth(25) //设置其表表头列宽行高大小
  3. @HeadFontStyle(fontHeightInPoints = 12) 标题字体样式	字大小为12
    
  4. 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:解决';
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值