【MySQL × SpringBoot 小点子】全面实现流程 · xlsx文件，Excel表格导入数据库的接口
最近由于学校压力，心情不太好，没咋写博客；
但最近做数据库实验的时候，数据是xlsx文件展示的，要求将这些导入数据库，我懒得去下载MySQL WorkBench等等可视化的工具，就想着写个程序来实现这个功能~
只要访问这个接口，输入xlsx表格文件的路径，就会向程序设置好的数据库的表中导入这个表格的所有数据~
借此也讲解一下数据库的表自动生成实体类和实现的方法，还有接口测试api的swagger2
1. 分析问题
主要面临几个问题：
- xlsx文件如何解析成Java中一个个的对象？
- 我会将xlsx转化为json格式，再转化为对象
- Java的对象集合如何存入数据库？
- 利用MyBatis框架
2. 基本配置
jdk：1.8
idea：2022.1.3
SpringBoot：2.7.6
项目创建，SpringMVC、MyBatis、lombok等框架导入和配置文件基本配置…不必多说~
- 参考我写的文章哦！
- 搜一下就有了
3. 数据库与表
-- 创建数据库
drop database if exists school;
CREATE database school character set utf8mb4 collate utf8mb4_general_ci;
use school;
-- 专业设置
drop TABLE if exists major;
CREATE TABLE major (
major_no char(4) NOT NULL primary key COMMENT '专业代码',
GB_major_no char(6) NOT NULL COMMENT '国家专业编号',
major_name VARCHAR(60) NOT NULL COMMENT '专业名称',
en_major_name VARCHAR(250) NOT NULL COMMENT '英文名称',
length_school int NOT NULL DEFAULT 4 COMMENT '学制',
edu_level char(6) NOT NULL DEFAULT '本科' COMMENT '培养层次',
ddegree CHAR(12) NOT NULL COMMENT '授予学位',
department_no CHAR(2) NOT NULL COMMENT '院系代码',
department VARCHAR(40) NOT NULL COMMENT '院系名称'
);
-- 学生
drop TABLE if exists student;
CREATE TABLE student (
sno char(12) NOT NULL primary key COMMENT '学生学号',
sname char(16) NOT NULL COMMENT '学生姓名',
sex CHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
birthday DATE NOT NULL COMMENT '出生日期',
nationality char(16) DEFAULT '汉族' COMMENT '民族',
native VARCHAR(16) DEFAULT '东莞市' COMMENT '籍贯',
political CHAR(12) DEFAULT '共青团员' COMMENT '政治面貌',
district CHAR(12) NOT NULL DEFAULT '松山湖校区' COMMENT '院系代码',
student_source VARCHAR(24) COMMENT '生源地',
enter_year DATE NOT NULL COMMENT '入学日期',
school_year int NOT NULL COMMENT '年级',
class char(24) NOT NULL COMMENT '班级',
major_no CHAR(4) NOT NULL COMMENT '专业代码',
FOREIGN key (major_no) REFERENCES major(major_no) -- 设置副键
);
-- 课程设置
drop TABLE if EXISTS course;
CREATE TABLE course (
school_year INT NOT NULL COMMENT '学年',
semester INT NOT NULL COMMENT '学期',
course_no CHAR(8) NOT NULL COMMENT '课程代码',
course_name VARCHAR(40) NOT NULL COMMENT '课程名称',
credit NUMERIC(3,1) NOT NULL COMMENT '学分',
credit_hourse INT NOT NULL COMMENT '学时',
course_type_1 CHAR(16) NOT NULL COMMENT '课程类别',
course_type_2 CHAR(16) COMMENT '课程性质',
cegment_type CHAR(16) COMMENT '环节类别',
examine_way CHAR(6) COMMENT '考核方式',
primary key (school_year, semester, course_no)
);
-- 学生选课
drop TABLE if exists select_course;
CREATE TABLE select_course (
sno CHAR(12) NOT NULL COMMENT '学号',
school_year int NOT NULL COMMENT '学年',
semester int NOT NULL COMMENT '学期',
course_no CHAR(8) NOT NULL COMMENT '课程代码',
score NUMERIC(6,2) COMMENT '综合成绩',
primary key (sno, school_year, semester, course_no),
FOREIGN key (sno) REFERENCES student(sno), -- 设置副键
FOREIGN key (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 编写工具类
package com.example.demo.utils;
import lombok.extern.slf4j.Slf4j;
import net.minidev.json.JSONArray;
import net.minidev.json.JSONObject;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
/**
* 表格处理工具类
*/
@Slf4j
public class ExcelUtils {
public static String xlsxTransferJson(String path) {
try {
FileInputStream inp = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(inp);
//获取sheet数
int sheetNum = workbook.getNumberOfSheets();
JSONObject jsonObject = new JSONObject();
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 = new JSONArray();
for (int i = 1; i < rownum; i++) {
Row row = sheet.getRow(i);
if (row != null) {
// List<Object> list = new ArrayList<>();
JSONObject rowObj = new JSONObject();
//循环列
for (int j = 0; j < colnum; j++) {
Cell cellData = row.getCell(j);
if (cellData != null) {
//判断cell类型
switch (cellData.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
rowObj.put(row1.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
break;
}
case Cell.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;
}
case Cell.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();
}
return null;
}
}
4.3 测试
右击：
编写测试代码，右击运行：
检查结果是否正确：
无错误提示代表格式正确，解析成功
4.4 注意事项
由于json是要反序列化为java对象的，所以要将表头的列名改为 对象的属性名 一致！
5. 表实体类和实现类自动生成
5.1 创建目录
5.2 配置文件xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!-- 驱动包路径，location中路径替换成⾃⼰本地路径 -->
<classPathEntry location="C:/Users/马拉圈/.m2/repository/mysql/mysql-connector-java/5.1.49/mysql-connector-java-5.1.49.jar"/>
<context id="DB2Tables" targetRuntime="MyBatis3">
<!-- 禁⽤⾃动⽣成的注释 -->
<commentGenerator>
<property name="suppressAllComments" value="true"/>
<property name="suppressDate" value="true"/>
</commentGenerator>
<!-- 连接配置 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://127.0.0.1:3306/school?characterEncoding=utf8&useSSL=false"
userId="root"
password="mmsszsd666">
</jdbcConnection>
<javaTypeResolver>
<!-- ⼩数统⼀转为BigDecimal -->
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!-- 实体类⽣成位置 -->
<javaModelGenerator targetPackage="com.example.demo.model"
targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!-- mapper.xml⽣成位置 -->
<sqlMapGenerator targetPackage="mapper"
targetProject="src/main/resources">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!-- DAO类⽣成位置 -->
<javaClientGenerator type="XMLMAPPER"
targetPackage="com.example.demo.dao" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!-- 配置⽣成表与实例, 只需要修改表名tableName, 与对应类名domainObjectName 即
可-->
<table tableName="course" domainObjectName="Course"
enableSelectByExample="false"
enableDeleteByExample="false" enableDeleteByPrimaryKey="false"
enableCountByExample="false"
enableUpdateByExample="false">
<!-- 类的属性⽤数据库中的真实字段名做为属性名, 不指定这个属性会⾃动转换 _ 为
驼峰命名规则-->
<property name="useActualColumnNames" value="true"/>
</table>
<table tableName="major" domainObjectName="Major"
enableSelectByExample="false"
enableDeleteByExample="false" enableDeleteByPrimaryKey="false"
enableCountByExample="false"
enableUpdateByExample="false">
<property name="useActualColumnNames" value="true"/>
</table>
<table tableName="select_course" domainObjectName="SelectCourse"
enableSelectByExample="false" enableDeleteByExample="false"
enableDeleteByPrimaryKey="false" enableCountByExample="false"
enableUpdateByExample="false">
<property name="useActualColumnNames" value="true"/>
</table>
<table tableName="student" domainObjectName="Student"
enableSelectByExample="false"
enableDeleteByExample="false" enableDeleteByPrimaryKey="false"
enableCountByExample="false"
enableUpdateByExample="false">
<property name="useActualColumnNames" value="true"/>
</table>
</context>
</generatorConfiguration>
几个需要自定义的点：
5.3 启动 · 自动生成
成功后：
每次生成，会覆盖同目录下之前生成的东西，要谨慎！
删除所有实体类的getter和setter，加入注解@Data，每个接口类加入注解@Mapper
示例：
5.3 常见问题
5.3.1 表的列名为java的关键字或者列名不符合json反序列化规则
如果是java关键字：
- 首先肯定是要将属性名改为正常属性名
- 这里是序列化时改变映射，由于本项目不涉及序列化，所以暂时不用在意，不加也行
- 后面可能会将数据库导出表格文件可能会用到，敬请期待~
- 这里是序列化时改变映射，由于本项目不涉及序列化，所以暂时不用在意，不加也行
- 改变接口实现的xml的部分代码（resultmap的列名属性映射关系）
其他地方同理~
如果是列名不符合json反序列化规则：
- 俺也不知道为啥不行，后续发现的，反正这种sb命名方式估计只有这些老毕登会想的出来，反正列名正常就不会出现这些问题！
- 修改属性名
- 修改配置（同理，省略~）
6. 编写后端代码
6.1 xml
动态插入方法已自动生成
- 由于我们插入的是集合，可以用动态sql构造一整条语句，我这里不这样，感兴趣可以试试
- 可以参考博客：【JavaEE】懒人的福音-MyBatis框架—复杂的操作-动态SQL_s:103的博客-CSDN博客
- 这边我想插入的时候知道对应哪条数据不符合要求，我就不一次性写再一个sql语句了
示例：
6.2 dao
方法已声明
示例：
6.3 service
接口声明：
6.4 service.impl
接口实现：
@Service
@Slf4j
public class CourseServiceImpl implements ICourseService {
@Resource
private CourseMapper courseMapper;
@Override
public void insert(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")
public class CourseController {
@Resource
private ICourseService courseService;
@Resource
private ObjectMapper objectMapper;
@PostMapping("/insert")
@ApiOperation("插入课程表格")
public void insert(@NonNull @RequestParam("path") @ApiParam("表格路径") String path) throws JsonProcessingException {
String json = ExcelUtils.xlsxTransferJson(path);
List<Course> list = objectMapper.readValue(json, new TypeReference<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 配置类
需要自定义的点：
package com.example.demo.config;
import org.springframework.boot.actuate.autoconfigure.endpoint.web.CorsEndpointProperties;
import org.springframework.boot.actuate.autoconfigure.endpoint.web.WebEndpointProperties;
import org.springframework.boot.actuate.autoconfigure.web.server.ManagementPortType;
import org.springframework.boot.actuate.endpoint.ExposableEndpoint;
import org.springframework.boot.actuate.endpoint.web.*;
import org.springframework.boot.actuate.endpoint.web.annotation.ControllerEndpointsSupplier;
import org.springframework.boot.actuate.endpoint.web.annotation.ServletEndpointsSupplier;
import org.springframework.boot.actuate.endpoint.web.servlet.WebMvcEndpointHandlerMapping;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.util.StringUtils;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.oas.annotations.EnableOpenApi;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
// 配置类
@Configuration
// 开启Springfox-Swagger
@EnableOpenApi
public class SwaggerConfig {
/**
* Springfox-Swagger基本配置
* @return
*/
@Bean
public Docket createApi() {
Docket docket = new Docket(DocumentationType.OAS_30)
.apiInfo(apiInfo())
.select()
.apis(RequestHandlerSelectors.basePackage("com.example.demo.controller"))//一定要设置对的controller包路径
.paths(PathSelectors.any())
.build();
return docket;
}
// 配置API基本信息
private ApiInfo apiInfo() {
ApiInfo apiInfo = new ApiInfoBuilder()
.title("转换器")
.description("转换器API测试")
.contact(new Contact("马大帅", "https://blog.csdn.net/Carefree_State?type=blog", "2040484356@qq.com"))
.version("1.0")
.build();
return apiInfo;
}
/**
* 解决SpringBoot 6.0以上与Swagger 3.0.0 不兼容的问题
* 复制即可
**/
@Bean
public WebMvcEndpointHandlerMapping webEndpointServletHandlerMapping(WebEndpointsSupplier webEndpointsSupplier,
ServletEndpointsSupplier servletEndpointsSupplier,
ControllerEndpointsSupplier controllerEndpointsSupplier,
EndpointMediaTypes endpointMediaTypes, CorsEndpointProperties corsProperties,
WebEndpointProperties webEndpointProperties, Environment environment) {
List<ExposableEndpoint<?>> allEndpoints = new ArrayList();
Collection<ExposableWebEndpoint> webEndpoints = webEndpointsSupplier.getEndpoints();
allEndpoints.addAll(webEndpoints);
allEndpoints.addAll(servletEndpointsSupplier.getEndpoints());
allEndpoints.addAll(controllerEndpointsSupplier.getEndpoints());
String basePath = webEndpointProperties.getBasePath();
EndpointMapping endpointMapping = new EndpointMapping(basePath);
boolean shouldRegisterLinksMapping = this.shouldRegisterLinksMapping(webEndpointProperties, environment,
basePath);
return new WebMvcEndpointHandlerMapping(endpointMapping, webEndpoints, endpointMediaTypes,
corsProperties.toCorsConfiguration(), new EndpointLinksResolver(allEndpoints, basePath),
shouldRegisterLinksMapping, null);
}
private boolean shouldRegisterLinksMapping(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了
数据库检查：
我们的开发就结束啦~
文章到此结束！谢谢观看
可以叫我 小马，我可能写的不好或者有错误，但是一起加油鸭🦆！
- 后续可能会出导出表格文件的教程，敬请期待！
642
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
