【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件，Excel表格导入数据库的接口

文章目录

【MySQL × SpringBoot 小点子】全面实现流程 · xlsx文件，Excel表格导入数据库的接口

最近由于学校压力，心情不太好，没咋写博客；

但最近做数据库实验的时候，数据是xlsx文件展示的，要求将这些导入数据库，我懒得去下载MySQL WorkBench等等可视化的工具，就想着写个程序来实现这个功能~

只要访问这个接口，输入xlsx表格文件的路径，就会向程序设置好的数据库的表中导入这个表格的所有数据~

借此也讲解一下数据库的表自动生成实体类和实现的方法，还有接口测试api的swagger2

1. 分析问题

主要面临几个问题：

1. xlsx文件如何解析成Java中一个个的对象？ - 我会将xlsx转化为json格式，再转化为对象
2. Java的对象集合如何存入数据库？ - 利用MyBatis框架

2. 基本配置

jdk：1.8

idea：2022.1.3

SpringBoot：2.7.6

项目创建，SpringMVC、MyBatis、lombok等框架导入和配置文件基本配置…不必多说~

• 参考我写的文章哦！
• 搜一下就有了

3. 数据库与表

-- 创建数据库dropdatabaseifexists school;CREATEdatabase school  characterset utf8mb4 collate utf8mb4_general_ci;use school;-- 专业设置dropTABLEifexists major;CREATETABLE major (
    major_no char(4)NOTNULLprimarykeyCOMMENT'专业代码',
    GB_major_no char(6)NOTNULLCOMMENT'国家专业编号',
    major_name VARCHAR(60)NOTNULLCOMMENT'专业名称',
    en_major_name  VARCHAR(250)NOTNULLCOMMENT'英文名称',
    length_school intNOTNULLDEFAULT4COMMENT'学制',
    edu_level char(6)NOTNULLDEFAULT'本科'COMMENT'培养层次',
    ddegree CHAR(12)NOTNULLCOMMENT'授予学位',
    department_no CHAR(2)NOTNULLCOMMENT'院系代码',
    department VARCHAR(40)NOTNULLCOMMENT'院系名称');-- 学生dropTABLEifexists student;CREATETABLE student (
    sno char(12)NOTNULLprimarykeyCOMMENT'学生学号',
    sname char(16)NOTNULLCOMMENT'学生姓名',
    sex  CHAR(2)NOTNULLDEFAULT'男'COMMENT'性别',
    birthday DATENOTNULLCOMMENT'出生日期',
    nationality  char(16)DEFAULT'汉族'COMMENT'民族',
    native VARCHAR(16)DEFAULT'东莞市'COMMENT'籍贯',
    political CHAR(12)DEFAULT'共青团员'COMMENT'政治面貌',
    district CHAR(12)NOTNULLDEFAULT'松山湖校区'COMMENT'院系代码',
    student_source VARCHAR(24)COMMENT'生源地',
    enter_year DATENOTNULLCOMMENT'入学日期',
    school_year intNOTNULLCOMMENT'年级',
    class char(24)NOTNULLCOMMENT'班级',
    major_no CHAR(4)NOTNULLCOMMENT'专业代码',FOREIGNkey(major_no)REFERENCES major(major_no)-- 设置副键);-- 课程设置dropTABLEifEXISTS course;CREATETABLE course (
    school_year INTNOTNULLCOMMENT'学年',
    semester  INTNOTNULLCOMMENT'学期',
    course_no CHAR(8)NOTNULLCOMMENT'课程代码',
    course_name VARCHAR(40)NOTNULLCOMMENT'课程名称',
    credit NUMERIC(3,1)NOTNULLCOMMENT'学分',
    credit_hourse  INTNOTNULLCOMMENT'学时',
    course_type_1  CHAR(16)NOTNULLCOMMENT'课程类别',
    course_type_2  CHAR(16)COMMENT'课程性质',
    cegment_type   CHAR(16)COMMENT'环节类别',
    examine_way   CHAR(6)COMMENT'考核方式',primarykey(school_year, semester, course_no));-- 学生选课dropTABLEifexists select_course;CREATETABLE select_course (
    sno CHAR(12)NOTNULLCOMMENT'学号',
    school_year intNOTNULLCOMMENT'学年',
    semester  intNOTNULLCOMMENT'学期',
    course_no CHAR(8)NOTNULLCOMMENT'课程代码',
    score NUMERIC(6,2)COMMENT'综合成绩',primarykey(sno, school_year, semester, course_no),FOREIGNkey(sno)REFERENCES student(sno),-- 设置副键FOREIGNkey(school_year, semester, course_no)REFERENCES course(school_year, semester, course_no)-- 设置副键);

注意：由于表与表的联系，所以不能独立删除父表，如果要删除父表要先删除其子表

• 我的习惯是直接sql再走一遍😀

不得不吐槽的是，学校的老毕登，对变量的起名，真的是有够傻的！

而且居然用常见编程语言的关键字命名！

• 这个问题后面会讲！

4. xlsx文件的解析

4.1 导入相关第三方库的依赖

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>net.minidev</groupId><artifactId>json-smart</artifactId></dependency>

不要忘记reload

4.2 编写工具类

packagecom.example.demo.utils;importlombok.extern.slf4j.Slf4j;importnet.minidev.json.JSONArray;importnet.minidev.json.JSONObject;importorg.apache.poi.ss.usermodel.*;importjava.io.FileInputStream;/**
 * 表格处理工具类
 */@Slf4jpublicclassExcelUtils{publicstaticStringxlsxTransferJson(String path){try{FileInputStream inp =newFileInputStream(path);Workbook workbook =WorkbookFactory.create(inp);//获取sheet数int sheetNum = workbook.getNumberOfSheets();JSONObject jsonObject =newJSONObject();for(int s =0; s < sheetNum; s++){// Get the Sheet of s.Sheet sheet = workbook.getSheetAt(s);//获取最大行数int rownum = sheet.getPhysicalNumberOfRows();if(rownum <=1){continue;}//获取第一行Row row1 = sheet.getRow(0);//获取最大列数int colnum = row1.getPhysicalNumberOfCells();JSONArray jsonArray =newJSONArray();for(int i =1; i < rownum; i++){Row row = sheet.getRow(i);if(row !=null){//                    List<Object> list = new ArrayList<>();JSONObject rowObj =newJSONObject();//循环列for(int j =0; j < colnum; j++){Cell cellData = row.getCell(j);if(cellData !=null){//判断cell类型switch(cellData.getCellType()){caseCell.CELL_TYPE_NUMERIC:{
                                        rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getNumericCellValue());break;}caseCell.CELL_TYPE_FORMULA:{//判断cell是否为日期格式if(DateUtil.isCellDateFormatted(cellData)){//转换为日期格式YYYY-mm-dd
                                            rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getDateCellValue());}else{//数字
                                            rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getNumericCellValue());}break;}caseCell.CELL_TYPE_STRING:{
                                        rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getStringCellValue());break;}default:
                                        rowObj.put(row1.getCell(j).getStringCellValue(),"");}}else{
                                rowObj.put(row1.getCell(j).getStringCellValue(),"");}}
                        jsonArray.add(rowObj);}}return jsonArray.toJSONString();//                jsonObject.put(sheet.getSheetName(), jsonArray);}//            System.out.println(jsonObject.toJSONString());}catch(Exception e){
            e.printStackTrace();}returnnull;}}

4.3 测试

右击：

编写测试代码，右击运行：

检查结果是否正确：

• 在线 JSON 解析 | 菜鸟工具 (runoob.com)

无错误提示代表格式正确，解析成功

4.4 注意事项

由于json是要反序列化为java对象的，所以要将表头的列名改为 对象的属性名 一致！

5. 表实体类和实现类自动生成

5.1 创建目录

5.2 配置文件xml

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPEgeneratorConfigurationPUBLIC"-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN""http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd"><generatorConfiguration><!-- 驱动包路径，location中路径替换成⾃⼰本地路径 --><classPathEntrylocation="C:/Users/马拉圈/.m2/repository/mysql/mysql-connector-java/5.1.49/mysql-connector-java-5.1.49.jar"/><contextid="DB2Tables"targetRuntime="MyBatis3"><!-- 禁⽤⾃动⽣成的注释 --><commentGenerator><propertyname="suppressAllComments"value="true"/><propertyname="suppressDate"value="true"/></commentGenerator><!-- 连接配置 --><jdbcConnectiondriverClass="com.mysql.jdbc.Driver"connectionURL="jdbc:mysql://127.0.0.1:3306/school?characterEncoding=utf8&amp;useSSL=false"userId="root"password="mmsszsd666"></jdbcConnection><javaTypeResolver><!-- ⼩数统⼀转为BigDecimal --><propertyname="forceBigDecimals"value="false"/></javaTypeResolver><!-- 实体类⽣成位置 --><javaModelGeneratortargetPackage="com.example.demo.model"targetProject="src/main/java"><propertyname="enableSubPackages"value="true"/><propertyname="trimStrings"value="true"/></javaModelGenerator><!-- mapper.xml⽣成位置 --><sqlMapGeneratortargetPackage="mapper"targetProject="src/main/resources"><propertyname="enableSubPackages"value="true"/></sqlMapGenerator><!-- DAO类⽣成位置 --><javaClientGeneratortype="XMLMAPPER"targetPackage="com.example.demo.dao"targetProject="src/main/java"><propertyname="enableSubPackages"value="true"/></javaClientGenerator><!-- 配置⽣成表与实例, 只需要修改表名tableName, 与对应类名domainObjectName 即
       可--><tabletableName="course"domainObjectName="Course"enableSelectByExample="false"enableDeleteByExample="false"enableDeleteByPrimaryKey="false"enableCountByExample="false"enableUpdateByExample="false"><!-- 类的属性⽤数据库中的真实字段名做为属性名, 不指定这个属性会⾃动转换 _ 为
           驼峰命名规则--><propertyname="useActualColumnNames"value="true"/></table><tabletableName="major"domainObjectName="Major"enableSelectByExample="false"enableDeleteByExample="false"enableDeleteByPrimaryKey="false"enableCountByExample="false"enableUpdateByExample="false"><propertyname="useActualColumnNames"value="true"/></table><tabletableName="select_course"domainObjectName="SelectCourse"enableSelectByExample="false"enableDeleteByExample="false"enableDeleteByPrimaryKey="false"enableCountByExample="false"enableUpdateByExample="false"><propertyname="useActualColumnNames"value="true"/></table><tabletableName="student"domainObjectName="Student"enableSelectByExample="false"enableDeleteByExample="false"enableDeleteByPrimaryKey="false"enableCountByExample="false"enableUpdateByExample="false"><propertyname="useActualColumnNames"value="true"/></table></context></generatorConfiguration>

几个需要自定义的点：

5.3 启动 · 自动生成

成功后：

每次生成，会覆盖同目录下之前生成的东西，要谨慎！

删除所有实体类的getter和setter，加入注解@Data，每个接口类加入注解@Mapper

示例：

5.3 常见问题

5.3.1 表的列名为java的关键字或者列名不符合json反序列化规则

如果是java关键字：

1. 首先肯定是要将属性名改为正常属性名 - 这里是序列化时改变映射，由于本项目不涉及序列化，所以暂时不用在意，不加也行 - 后面可能会将数据库导出表格文件可能会用到，敬请期待~

1. 改变接口实现的xml的部分代码（resultmap的列名属性映射关系）

其他地方同理~

如果是列名不符合json反序列化规则：

• 俺也不知道为啥不行，后续发现的，反正这种sb命名方式估计只有这些老毕登会想的出来，反正列名正常就不会出现这些问题！

1. 修改属性名

1. 修改配置（同理，省略~）

6. 编写后端代码

6.1 xml

动态插入方法已自动生成

• 由于我们插入的是集合，可以用动态sql构造一整条语句，我这里不这样，感兴趣可以试试 - 可以参考博客：【JavaEE】懒人的福音-MyBatis框架—复杂的操作-动态SQL_s:103的博客-CSDN博客- 这边我想插入的时候知道对应哪条数据不符合要求，我就不一次性写再一个sql语句了

示例：

6.2 dao

方法已声明

示例：

6.3 service

接口声明：

6.4 service.impl

接口实现：

@Service@Slf4jpublicclassCourseServiceImplimplementsICourseService{@ResourceprivateCourseMapper courseMapper;@Overridepublicvoidinsert(List<Course> list){for(int i =0; i < list.size(); i++){try{
                courseMapper.insertSelective(list.get(i));}catch(RuntimeException e){System.out.println("-----------------------------------------");
                log.error(i +"插入失败");System.out.println("-----------------------------------------");}}
        log.info("插入完毕");}}

补充：

在service层进行一些自定义的约束，减少数据库负担，我想这也是mysql忽略check语句的初心！

测试代码我就不写了，没啥问题，如果你要写一定记得加@Transactional，防止测试代码对数据库的污染

6.5 controller

@Api(tags ="课程相关接口")@Slf4j@RestController@RequestMapping("/course")publicclassCourseController{@ResourceprivateICourseService courseService;@ResourceprivateObjectMapper objectMapper;@PostMapping("/insert")@ApiOperation("插入课程表格")publicvoidinsert(@NonNull@RequestParam("path")@ApiParam("表格路径")String path)throwsJsonProcessingException{String json =ExcelUtils.xlsxTransferJson(path);List<Course> list = objectMapper.readValue(json,newTypeReference<List<Course>>(){});
        courseService.insert(list);}}

那些api是啥，随后讲解

7. 前端页面（接口测试swagger）

这里你只需要在static写前端代码，可以用按钮触发事件啥的…

这里我偷懒，用接口测试的swagger页面

7.1 依赖

<!-- API⽂档⽣成，基于swagger2 --><dependency><groupId>io.springfox</groupId><artifactId>springfox-boot-starter</artifactId><version>3.0.0</version></dependency><!-- SpringBoot健康监控 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-actuator</artifactId></dependency>

7.2 配置类

需要自定义的点：

packagecom.example.demo.config;importorg.springframework.boot.actuate.autoconfigure.endpoint.web.CorsEndpointProperties;importorg.springframework.boot.actuate.autoconfigure.endpoint.web.WebEndpointProperties;importorg.springframework.boot.actuate.autoconfigure.web.server.ManagementPortType;importorg.springframework.boot.actuate.endpoint.ExposableEndpoint;importorg.springframework.boot.actuate.endpoint.web.*;importorg.springframework.boot.actuate.endpoint.web.annotation.ControllerEndpointsSupplier;importorg.springframework.boot.actuate.endpoint.web.annotation.ServletEndpointsSupplier;importorg.springframework.boot.actuate.endpoint.web.servlet.WebMvcEndpointHandlerMapping;importorg.springframework.context.annotation.Bean;importorg.springframework.context.annotation.Configuration;importorg.springframework.core.env.Environment;importorg.springframework.util.StringUtils;importspringfox.documentation.builders.ApiInfoBuilder;importspringfox.documentation.builders.PathSelectors;importspringfox.documentation.builders.RequestHandlerSelectors;importspringfox.documentation.oas.annotations.EnableOpenApi;importspringfox.documentation.service.ApiInfo;importspringfox.documentation.service.Contact;importspringfox.documentation.spi.DocumentationType;importspringfox.documentation.spring.web.plugins.Docket;importjava.util.ArrayList;importjava.util.Collection;importjava.util.List;// 配置类@Configuration// 开启Springfox-Swagger@EnableOpenApipublicclassSwaggerConfig{/**
         * Springfox-Swagger基本配置
         * @return
         */@BeanpublicDocketcreateApi(){Docket docket =newDocket(DocumentationType.OAS_30).apiInfo(apiInfo()).select().apis(RequestHandlerSelectors.basePackage("com.example.demo.controller"))//一定要设置对的controller包路径.paths(PathSelectors.any()).build();return docket;}// 配置API基本信息privateApiInfoapiInfo(){ApiInfo apiInfo =newApiInfoBuilder().title("转换器").description("转换器API测试").contact(newContact("马大帅","https://blog.csdn.net/Carefree_State?type=blog","[email protected]")).version("1.0").build();return apiInfo;}/**
         * 解决SpringBoot 6.0以上与Swagger 3.0.0 不兼容的问题
         * 复制即可
         **/@BeanpublicWebMvcEndpointHandlerMappingwebEndpointServletHandlerMapping(WebEndpointsSupplier webEndpointsSupplier,ServletEndpointsSupplier servletEndpointsSupplier,ControllerEndpointsSupplier controllerEndpointsSupplier,EndpointMediaTypes endpointMediaTypes,CorsEndpointProperties corsProperties,WebEndpointProperties webEndpointProperties,Environment environment){List<ExposableEndpoint<?>> allEndpoints =newArrayList();Collection<ExposableWebEndpoint> webEndpoints = webEndpointsSupplier.getEndpoints();
                allEndpoints.addAll(webEndpoints);
                allEndpoints.addAll(servletEndpointsSupplier.getEndpoints());
                allEndpoints.addAll(controllerEndpointsSupplier.getEndpoints());String basePath = webEndpointProperties.getBasePath();EndpointMapping endpointMapping =newEndpointMapping(basePath);boolean shouldRegisterLinksMapping =this.shouldRegisterLinksMapping(webEndpointProperties, environment,
                        basePath);returnnewWebMvcEndpointHandlerMapping(endpointMapping, webEndpoints, endpointMediaTypes,
                        corsProperties.toCorsConfiguration(),newEndpointLinksResolver(allEndpoints, basePath),
                        shouldRegisterLinksMapping,null);}privatebooleanshouldRegisterLinksMapping(WebEndpointProperties webEndpointProperties,Environment environment,String basePath){return webEndpointProperties.getDiscovery().isEnabled()&&(StringUtils.hasText(basePath)||ManagementPortType.get(environment).equals(ManagementPortType.DIFFERENT));}}

7.3 使用

在controller层的接口里：

7.4 访问，展示与使用

http://127.0.0.1:8080/swagger-ui/index.html#/

访问链接格式：ip:port/swagger-ui/index.html#/

• 是不是很神奇！
• 底层的技术就不需要知道

展示：

点击一级分类显示接口：

使用（示例）：

• 如果表与表之间有联系，注意导入表的顺序！

点击接口

try it out

这样就ok了

数据库检查：

我们的开发就结束啦~

文章到此结束！谢谢观看
可以叫我 小马，我可能写的不好或者有错误，但是一起加油鸭🦆！

• 后续可能会出导出表格文件的教程，敬请期待！

代码：memory · 游离态/马拉圈2023年10月 - 码云 - 开源中国 (gitee.com)