java import and parse excel content and store it in the database springboot+mybatis

java import and parse excel content and store it in the database springboot+mybatis

maven depends on jar

<!--Import the package needed for the form-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.3.1</version>
    </dependency>
    <dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>2.4</version>
    </dependency>

Entity class

@Entity
@Table(name = "subject")
public class Subject implements Serializable {
    public Subject() {

    }

    public Subject(String curriculum, String content, String type, String degree, String name, String analysis, String parameter, String correct, String error) {
        this.curriculum = curriculum;
        this.content = content;
        this.type = type;
        this.degree = degree;
        this.name = name;
        this.analysis = analysis;
        this.parameter = parameter;
        this.correct = correct;
        this.error = error;
    }

    @GeneratedValue(strategy = GenerationType.IDENTITY)//JPA automatically selects the appropriate generation strategy
    @Column(name="id")//Column name, the default is the attribute name, you can specify the column name through the name attribute
    @Id
    private Integer id;

   //Belonging to the course
    @Column(name = "curriculum")
    private String curriculum;

   //Subject content
    @Column(name = "content")
    private String content;

   //Question type
    @Column(name = "type")
    private String type;

   //Degree of difficulty
    @Column(name = "degree")
    private String degree;

   //Knowledge point name
    @Column(name = "name")
    private String name;

   //parse
    @Column(name = "analysis")
    private String analysis;

   //Formula question parameters
    @Column(name = "parameter")
    private String parameter;

   //correct answer
    @Column(name = "correct")
    private String correct;

   //Wrong answer
    @Column(name = "error")
    private String error;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCurriculum() {
        return curriculum;
    }

    public void setCurriculum(String curriculum) {
        this.curriculum = curriculum;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getDegree() {
        return degree;
    }

    public void setDegree(String degree) {
        this.degree = degree;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAnalysis() {
        return analysis;
    }

    public void setAnalysis(String analysis) {
        this.analysis = analysis;
    }

    public String getParameter() {
        return parameter;
    }

    public void setParameter(String parameter) {
        this.parameter = parameter;
    }

    public String getCorrect() {
        return correct;
    }

    public void setCorrect(String correct) {
        this.correct = correct;
    }

    public String getError() {
        return error;
    }

    public void setError(String error) {
        this.error = error;
    }

    @Override
    public boolean equals(Object that) {
        if (this == that) {
            return true;
        }
        if (that == null) {
            return false;
        }
        if (getClass() != that.getClass()) {
            return false;
        }
        Subject other = (Subject) that;
        return (this.getId() == null? other.getId() == null: this.getId().equals(other.getId()))
                && (this.getName() == null? other.getName() == null: this.getName().equals(other.getName()))
                && (this.getAnalysis() == null? other.getAnalysis() == null: this.getAnalysis().equals(other.getAnalysis()))
                && (this.getContent() == null? other.getContent() == null: this.getContent().equals(other.getContent()))
                && (this.getCorrect() == null? other.getCorrect() == null: this.getCorrect().equals(other.getCorrect()))
                && (this.getCurriculum() == null? other.getCurriculum() == null: this.getCurriculum().equals(other.getCurriculum()))
                && (this.getDegree()== null? other.getDegree() == null: this.getDegree().equals(other.getDegree()))
                && (this.getError() == null? other.getError() == null: this.getError().equals(other.getError()))
                && (this.getParameter() == null? other.getParameter() == null: this.getParameter().equals(other.getParameter()))
                && (this.getType() == null? other.getType() == null: this.getType().equals(other.getType()));
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((getId() == null)? 0: getId().hashCode());
        result = prime * result + ((getName() == null)? 0: getName().hashCode());
        result = prime * result + ((getAnalysis() == null)? 0: getAnalysis().hashCode());
        result = prime * result + ((getContent() == null)? 0: getContent().hashCode());
        result = prime * result + ((getCorrect() == null)? 0: getCorrect().hashCode());
        result = prime * result + ((getDegree() == null)? 0: getDegree().hashCode());
        result = prime * result + ((getError() == null)? 0: getError().hashCode());
        result = prime * result + ((getParameter() == null)? 0: getParameter().hashCode());
        result = prime * result + ((getType() == null)? 0: getType().hashCode());
        result = prime * result + ((getCurriculum() == null)? 0: getCurriculum().hashCode());
        return result;
    }

}

DAO layer

@Repository
public interface SubjectDao {

    @Insert("insert into subject(name,content,curriculum,type,degree,analysis,parameter,correct,error) values(#{name},#{content},#{curriculum},#{type},#{ degree},#{analysis},#{parameter},#{correct},#{error})")
    void addUser(Subject subject);

    @Update("update subject set name=#{name},content=#{content},curriculum=#{curriculum},type=#{type},degree=#{degree},analysis=#{analysis},parameter =#{parameter},correct=#{correct},error=#{error} where id=#{id}")
    int updateUserByName(Subject subject);

    @Select("select * from subject where content=#{content}")
    Subject selectByName(@Param("content") String content);


}

service layer

   @Override
    public String batchImport(String fileName, MultipartFile file) throws Exception {

        String notNull = "0";
        List<Subject> userList = new ArrayList<Subject>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return "The format of the uploaded file is incorrect";
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null){
            notNull = "1";
        }
        Subject subject;
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }

          /* if( row.getCell(0).getCellType() !=1){
                throw new Exception("Import failed (line "+(r+1)+", please set the name in text format)");
            }*/
            String curriculum = row.getCell(0).getStringCellValue();
            if(curriculum == null || curriculum.isEmpty()){
                return "Import failed (the "+(r+1)+" column, the course did not fill in)";
            }

            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
            String content = row.getCell(1).getStringCellValue();
            if(content==null || content.isEmpty()){
                return "Import failed (the "+(r+1)+" column, the title is not filled in)";
            }
            String type = row.getCell(2).getStringCellValue();
            if(type==null|| type.isEmpty()){
                return "Import failed (Column "+(r+1)+", title type)";
            }

            String degree = row.getCell(3).getStringCellValue();
            if(degree==null|| degree.isEmpty()){
                return "Import failed (the "+(r+1)+" column, the degree of difficulty is not filled in)";
            }
            String name = row.getCell(4).getStringCellValue();
            if(name==null|| name.isEmpty()){
                return "Import failed (the "+(r+1)+" column, the knowledge point name is not filled in)";
            }
            String analysis = row.getCell(5).getStringCellValue();
            if(analysis==null|| analysis.isEmpty()){
                return "Import failed (the "+(r+1)+" column, the analysis did not fill in)";
            }
            String parameter = row.getCell(6).getStringCellValue();
            if(parameter==null|| parameter.isEmpty()){
                return "Import failed (the "+(r+1)+" column, the formula question parameter is not filled in)";
            }
            String correct = row.getCell(7).getStringCellValue();
            if(correct==null|| correct.isEmpty()){
                return "Import failed (the "+(r+1)+" column, the correct answer is not filled in)";
            }
            String error = row.getCell(8).getStringCellValue();
            if(error==null|| error.isEmpty()){
                return "Import failed (the "+(r+1)+" column, the wrong answer is not filled in)";
            }


            subject=new Subject();
            subject.setAnalysis(analysis);
            subject.setDegree(degree);
            subject.setContent(content);
            subject.setCorrect(correct);
            subject.setCurriculum(curriculum);
            subject.setError(error);
            subject.setName(name);
            subject.setParameter(parameter);
            subject.setType(type);
            userList.add(subject);
        }




        for (Subject subject1: userList) {
            String content = subject1.getContent();
            Subject cnt = subjectDao.selectByName(content);
            if (cnt == null ||null == cnt.getContent()) {
                subjectDao.addUser(subject1);
            } else {
                subject1.setId(cnt.getId());
                subjectDao.updateUserByName(subject1);
            }
        }
       

        return notNull;
    }

controller layer

   @RequestMapping(value = "import")
    @ResponseBody
    public String addUser(@RequestParam(value = "file",required=false) MultipartFile file) {
        if(file.isEmpty()){
            return null;
        }
        String result = null;
        String fileName = file.getOriginalFilename();
        try {
             result = subjectService.batchImport(fileName, file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }