I. demand:
Use poi to read the national economy industry classification table (excel table made by the National Bureau of Statistics), and insert the data into the established MySQL table.

II. Framework:
springboot + MyBatisPlus

III. test:

  1. Use the most basic method to get excel records one by one and put them into the list, insert them one by one in the cycle proportion, and calculate the time
@Test
    public void test01() throws IOException {
        Workbook wb = PoiUtils.readExcel("G:\\industry.xlsx");
        if (wb != null){
            Sheet sheet = wb.getSheetAt(0);
            int maxRowNum = sheet.getPhysicalNumberOfRows();
            System.out.println(maxRowNum);
            Row row = null;
            row = sheet.getRow(3);
            System.out.println(row.getCell(0).getStringCellValue());
            System.out.println(row.getCell(3).getStringCellValue());
            List<String> rowList = new ArrayList<>();
            for (int i = 3;i < maxRowNum;i++){  //Total number of records 2W+
                row = sheet.getRow(i);
                if (row != null && !PoiUtils.isRowEmpty(row)){
                    if (row.getCell(3) != null){
                        //Set cell data type to string type
                        row.getCell(3).setCellType(CellType.STRING);
                        rowList.add(row.getCell(3).getStringCellValue());
                    }
                }
            }
            wb.close();
            //The first and fourth columns of each row in Excel are successfully retrieved here
            long begin = System.currentTimeMillis();
            for (String s:rowList){
                Test001Entity test001Entity = new Test001Entity();
                test001Entity.setContent(s);
                test001Service.save(test001Entity);
            }
            long end = System.currentTimeMillis();
            System.out.println("Total time:" + (end-begin));
        }
    }

Results: the total time was - 714325ms, almost 12 minutes.

2. Use a SQL insert test, that is, use MyBatis's dynamic SQL tag foreach loop traversal condition
Use SQL client to empty (TRUNCATE tableName) table record first




Results: the total time was 1822ms, about 1.8s.