springboot接收excel数据文件去重

1.创建测试表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL COMMENT '学号',
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `year` int(11) NOT NULL COMMENT '年份',
  `level` tinytext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '等级',
  PRIMARY KEY (`id`, `name`, `year`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2.生产测试文档

3.springboot接收文件并入库

接收到上传的模板文件后，首先读取首行进行字段名称判断，然后一行行的读取excel表格中的数据，生成entity对象。如果入库的数组大小超过1000，那就入库一次，然后入库使用的类似oracle的merge into语句的ON DUPLICATE KEY UPDATE

注意事项：

1. 因为测试样例数据量小，上传文件大小限制在springboot中没有设置，。
2. 生成的excel字段学号和年份单元格格式应该设置为数值

    @Autowired
    private OperateDao operateDao;

    @PostMapping(value = "/add/tax")
    public String insertTaxData(@RequestParam("file") MultipartFile file) {
        try {
            String filename = file.getOriginalFilename();
            System.out.println(filename);
            InputStream tps = (file.getInputStream());
            final XSSFWorkbook tpWorkbook = new XSSFWorkbook(tps);

            Sheet sheet = tpWorkbook.getSheetAt(0);
            Row headerRow = sheet.getRow(0);
            if (headerRow.getCell(0).getStringCellValue().equals("学号")
                    && headerRow.getCell(1).getStringCellValue().equals("姓名")
                    && headerRow.getCell(2).getStringCellValue().equals("年份")
                    && headerRow.getCell(3).getStringCellValue().equals("等级")
            ) {
                int i = 1;
                boolean flag = true;
                List<StudentEntity> entityList = new ArrayList<>();
                StudentEntity studentEntity;
                while (flag) {
                    Row row = sheet.getRow(i++);
                    if (row != null) {
                        studentEntity = new StudentEntity();
                        Cell id = row.getCell(0);
                        Cell name = row.getCell(1);
                        Cell year = row.getCell(2);
                        Cell level = row.getCell(3);
                        studentEntity.setId((int) id.getNumericCellValue());
                        studentEntity.setName(name.getStringCellValue());
                        studentEntity.setYear((int) year.getNumericCellValue());
                        studentEntity.setLevel( level.getStringCellValue());
                        entityList.add(studentEntity);
                    } else {
                        flag = false;
                    }
                    if (entityList.size() > 1000) {
                        int rs = operateDao.addOrUpdateBatchTax(entityList);
                        System.out.println(rs);
                        entityList.clear();
                    }
                }
                if (entityList.size() > 0) {
                    operateDao.addOrUpdateBatchTax(entityList);
                }
                tps.close();
            } else {
                return "检查模板是否正确";
            }
        } catch (IOException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return "success";
    }

@Mapper
public interface OperateDao {
    Integer addOrUpdateBatchTax(List<StudentEntity> list);
}

<?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.test.springboot.dao.OperateDao">
    <insert id="addOrUpdateBatchTax" parameterType="java.util.List">
        INSERT INTO `student`(`id`, `name`, `year`, `level`)
        VALUES
        <foreach collection="list" item="tax" index="index" separator=",">
            (#{tax.id},#{tax.name},#{tax.year},#{tax.level})
        </foreach>
        ON DUPLICATE KEY UPDATE
        level =VALUES ( `level` )
    </insert>

</mapper>