我人生中的第一个需求——Excel数据批量上传到数据库

受光于隙见一床,受光于牗见室央,受光于庭户见一堂,受光于天下照四方。——魏源

一、写在前面

（由于考虑到公司业务和代码安全性问题，所有内容已脱敏）

二、问题场景

最近，在实习中遇到如下场景：（某个项目的一个分支，我也是刚接触，所以架构不是很懂）

2.1 功能简述

一个地区的核酸预约系统，现在有PC端和客户端。

• PC端：超级管理员，也就是可以理解为每个小区的物业或者政府人员登录的端口。登录方式为 Session 。

  PC端只有查询数据功能，即根据某个字段筛选。

• 客户端：客户端的使用群体只有普通用户和做核酸检查的医护人员。登录方式为 Token 。

  客户端有两个功能：

  1. 普通用户可以预约做核酸，医务人员根据用户提交的时间判断自己能不能去给他做核酸（可以通过也可以拒绝）。
  2. 医务人员可以根据你的行程码判断你需要做核酸，然后直接邀约一个时间段去给你做核酸（系统会短信通知你，为了自己的安全，你只能接收）

  上门做核酸，所以是一对一预约关系。

2.2 基本表信息

用户预约表：

字段信息	字段类型	其他约束
用户电话	VARCHAR	主键，不能为空
用户姓名	VARCHAR	不能为空
开始预约时间	VARCHAR	不能为空
结束预约时间	VARCHAR	不能为空
用户健康码	VARCHAR	不能为空
用户行程码截图	VARCHAR	不能为空
审批状态	INT	不能为空

医务人员预约表：

字段信息	字段类型	其他约束
医生电话	VARCHAR	主键，不能为空
医生姓名	VARCHAR	不能为空
所属医院	VARCHAR	不能为空
用户健康码	VARCHAR	不能为空
用户行程码	VARCHAR	不能为空

PC端展示效果：

用户电话	用户姓名	医生姓名	医生电话	医生所属医院	开始预约时间	结束预约时间	审批状态	用户健康码	用户行程码
123456	张三	大白1	1201	长安医院	2020-06-09 10:00	2020-06-09 11:00	待审批	/check_img/001.jpg	/tour_img/001.jpg
654321	李四	大白2	1202	和平医院	2020-06-09 08:00	2020-06-09 09:00	已通过	/check_img/002.jpg	/tour_img/002.jpg

2.3 需求简述

现在需求是给PC端增加一个核酸批量预约功能，可以通过上传 Excel 文件批量预约核酸。

例如一个小区变为封控区后，小区物业会将所有用户提交到数据库，即给所有人预约核酸检测。

Excel 表模板如下：（前面的一列是）

1	用户电话	用户姓名	开始预约时间	结束预约时间	用户健康码	用户行程码
2	123456	张三	2020-06-09 10:00	2020-06-09 11:00	/check_img/001.jpg	/tour_img/001.jpg
3	654321	李四	2020-06-09 08:00	2020-06-09 09:00	/check_img/002.jpg	/tour_img/002.jpg

2.4 初步方案

因为考虑到后端解析 Excel 用的是工具类（工具类不可能调用 Service 层上传文件的方法），然后如果图片都放到 Excel 中这个数据可能有很多行，所以可能会有很多图片，一次性占用的带宽较大。

所以我们做了如下调整：

前端将用户上传的 Excel 传给后端，后端做图片以外数据的解析，解析后返回 json 给前端。前端将数据展示给PC端管理员，管理员给前端提供一个上传图片的按键，每次对应上传一个图片，将这个图片调用后端接口上传，将url填入 json 返回给后端。

然后我就开始快乐的写我的 ExcelUtil 了。

2.5 问题分析

好景不长，当我写完工具类，写好 Excel 校验以及转 json 的方法后，我该写解析 json 了，这时候才发现不对:

如果按照样表的数据插入，不知道他预约的医生是谁。

所以就查看了医生预约用户的方法，方法中有医生的参数都是从 request 中的 Token 拿取的，而 Token 中的信息是拦截器添加到 Token 中的。

于是乎我也就傻傻的从 PC 端的 request 中取医生信息。结果取不到信息，每次都插入失败。

后来才发现 PC 端和客户端的登录方式不一样，根本就不再一个系统。而且我这时候才意识到这个是管理员，和客户端可能的两张表的。

2.6 解决方案

于是乎，和产品开会，最终决议给 Excel 中增加一条字段，即医护人员的电话。

因为电话是主键，所以就可以查询到医护人员的信息了。

新 Excel 表模板如下：

序号	医生电话	用户电话	用户姓名	用户健康码	用户行程码
1	666666	123456	张三	/check_img/001.jpg	/tour_img/001.jpg
2	888888	654321	李四	/check_img/002.jpg	/tour_img/002.jpg

2.7 其他问题

但是到最后，我发现这个 json 的格式不好解析。

我之前是打算让前端传个 Excel 文件，然后继续解析 Excel 文件为 set 集合，然后遍历插入数据库。

{
    "code": 200,
    "message": null,
    "data": {
        “1”： {
        	“医生电话”: "66666666",
        	“用户电话”: "张三",
        	“用户健康码”: "1234567989",
        	“用户行程码”: "/abc.jpg"
    	},
        “2”： {
        	“医生电话”: "8888888",
        	“用户电话”: "张三",
        	“用户健康码”: "1234567989",
        	“用户行程码”: "/abc.jpg"
    	},
		....
        “50”： {
        	“医生电话”: "...",
        	“用户电话”: "张三",
        	“用户健康码”: "1234567989",
        	“用户行程码”: "/abc.jpg"
    	}
    }
}

因为封装太严重，导致直接解析需要Map<String,<Map<String,String>>>，也就是Map<“1”,<Map<“字段”,“字符串”>>>的形式，然后一层层解析。

昨天在咨询 背影 好（da）友（lao）这个业务场景，他给出了他的建议：封装一个新的实体类，继承以前的实体类多加一个字段即可。然后封装为 Data 集合，最后 Controller 层进行接收。

三、原本代码逻辑

3.1 controller层

/**
 * 社区预约
 */
@RestController
@RequestMapping("/community")
@Validated
public class CommunityReserveController {

    @Resource
    private CommunityService communityService;
    
    @RequestMapping(value = "/analyzeExcel")
    public BaseResponse analyzeExcel(MultipartHttpServletRequest multiparthttpservletrequest) {
        return communityService.analyzeExcel(multiparthttpservletrequest);
    }
    
    @RequestMapping(value = "/createReserve")
    public BaseResponse createReserve(MultipartHttpServletRequest multiparthttpservletrequest) {
        return communityService.createReserve(multiparthttpservletrequest);
    }
    
}

3.2 service 层

接口

/**
 * 社区预约接口
 */
public interface CommunityService {

    /**
    * 解析并校验 Excel 到 Json
    */
    BaseResponse communityService(MultipartHttpServletRequest multiparthttpservletrequest);
    
    /**
    * 将 Excel 信息插入到数据库
    */
    BaseResponse createReserve(MultipartHttpServletRequest multiparthttpservletrequest);
    
    
}

接口实现类

/**
 * 社区预约实现类
 */
@Service
public class CommunityServiceImpl implements CommunityService {

    @Override
    JSONObject communityService(MultipartHttpServletRequest multiparthttpservletrequest) {
        JSONObject infos = new JSONObject();
        // 解析文件逻辑略
        return ResultUtils.success(infos);
    }
    
    @Override
    JSONObject communityService(MultipartHttpServletRequest multiparthttpservletrequest) {
        JSONObject infos = new JSONObject();
        // 解析文件逻辑略
        // 插入数据逻辑略
        return ResultUtils.success(infos);
    }
    
}

3.3 dao层

实体类代码如下：

import lombok.Data;

import javax.validation.constraints.NotNull;


/**
 * 医生创建用户预约 
 */
@Data
public class DoctoReserveReq implements Serializable {

    /**
     * 用户电话
     */
    @NotNull(message = "用户电话 不能为空")
    private String phone;
    
    /**
     * 用户姓名
     */
    @NotNull(message = "用户姓名 不能为空")
    private String username;
    
    /**
     * 开始预约时间
     */
    @NotNull(message = "开始预约时间 不能为空")
    private String start_time;
    
    /**
     * 结束预约时间
     */
    @NotNull(message = "结束预约时间 不能为空")
    private String end_time;
    
    /**
     * 用户健康码截图
     */
    @NotNull(message = "用户健康码截图 不能为空")
    private String health_img;
    
    /**
     * 用户行程码截图
     */
    @NotNull(message = "用户行程码截图 不能为空")
    private String travel_img;

}

3.4 common层

通用返回类：

import lombok.Data;

import java.io.Serializable;

/**
 * 通用返回类
 *
 * @param <T>
 */
@Data
public class BaseResponse<T> implements Serializable {

    private int code;

    private T data;

    private String message;

    private String description;

    public BaseResponse(int code, T data, String message, String description) {
        this.code = code;
        this.data = data;
        this.message = message;
        this.description = description;
    }

    public BaseResponse(int code, T data, String message) {
        this(code, data, message, "");
    }

    public BaseResponse(int code, String message, String description) {
        this(code, null, message, description);
    }

    public BaseResponse(int code, T data) {
        this(code, data, "", "");
    }

    public BaseResponse(ErrorCode errorCode) {
        this(errorCode.getCode(), null, errorCode.getMessage(), errorCode.getDescription());
    }


}

返回工具类：

/**
 * 返回工具类
 */
public class ResultUtils {

    /**
     * 成功
     *
     * @param data
     * @param <T>
     * @return
     */
    public static <T> BaseResponse<T> success(T data) {
        return new BaseResponse<>(0, data, "ok");
    }

    /**
     * 失败
     *
     * @param errorCode
     * @param <T>
     * @return
     */
    public static <T> BaseResponse<T> error(ErrorCode errorCode) {
        return new BaseResponse<>(errorCode);
    }

    /**
     * 失败
     *
     * @param code
     * @param message
     * @param description
     * @param <T>
     * @return
     */
    public static <T> BaseResponse<T> error(int code, String message, String description) {
        return new BaseResponse<T>(code, message, description);
    }

    /**
     * 失败
     *
     * @param errorCode
     * @param <T>
     * @return
     */
    public static <T> BaseResponse<T> error(ErrorCode errorCode, String message, String description) {
        return new BaseResponse<T>(errorCode.getCode(), message, description);
    }

    /**
     * 失败
     *
     * @param errorCode
     * @param <T>
     * @return
     */
    public static <T> BaseResponse<T> error(ErrorCode errorCode, String description) {
        return new BaseResponse<T>(errorCode.getCode(), errorCode.getMessage(), description);
    }


}

错误码枚举类：

/**
 * 错误码
 */
public enum ErrorCode {

    SUCCESS(0, "ok", ""),
    PARAM_ERROR(40000, "请求参数错误", ""),
    NULL_ERROR(40001, "请求数据为空", ""),
    NO_LOGIN(40100, "未登录", ""),
    NO_AUTH(40101, "无权限", ""),
    SYSTEM_ERROR(50000, "系统内部异常", "");

    private final int code;
    /**
     * 状态码信息
     */
    private final String message;
    /**
     * 状态码描述（）详细
     */
    private final String description;

    ErrorCode(int code, String message, String description) {
        this.code = code;
        this.message = message;
        this.description = description;
    }

    public int getCode() {
        return code;
    }

    public String getMessage() {
        return message;
    }

    public String getDescription() {
        return description;
    }
}

四、新增代码逻辑

和前端沟通传过来的 Json 格式 ：

[
    {
        “医生电话”: "66666666",
        “用户电话”: "张三",
        “用户健康码”: "1234567989",
        “用户行程码”: "/abc.jpg"
    },
    {
        “医生电话”: "8888888",
        “用户电话”: "张三",
        “用户健康码”: "1234567989",
        “用户行程码”: "/abc.jpg"
    },
    ...
    {
        “医生电话”: "...",
        “用户电话”: "张三",
        “用户健康码”: "1234567989",
        “用户行程码”: "/abc.jpg"
    }
]

4.1 controller层

/**
 * 社区预约
 */
@RestController
@RequestMapping("/community")
@Validated
public class CommunityReserveController {

    @Resource
    private CommunityService communityService;
    
    @RequestMapping(value = "/analyzeExcel")
    public BaseResponse analyzeExcel(MultipartHttpServletRequest multiparthttpservletrequest) {
        return communityService.analyzeExcel(multiparthttpservletrequest);
    }
    
    @RequestMapping(value = "/createReserve")
    public BaseResponse createReserve(@Validated @RequestBody ArrayList<DoctoReserveReq> list) {
        return communityService.createReserve(list);
    }
   
}

4.2 service 层

接口

/**
 * 社区预约接口
 */
public interface CommunityService {

    /**
    * 解析并校验 Excel 到 Json
    */
    BaseResponse communityService(MultipartHttpServletRequest multiparthttpservletrequest);
    
    /**
    * 将 Excel 信息插入到数据库
    */
    BaseResponse createReserve(ArrayList<DoctoReserveReq> list);
    
    
}

接口实现类

/**
 * 社区预约实现类
 */
@Service
public class CommunityServiceImpl implements CommunityService {

    @Override
    JSONObject communityService(MultipartHttpServletRequest multiparthttpservletrequest) {
        JSONObject infos = new JSONObject();
        // 解析文件逻辑略
        return ResultUtils.success(infos);
    }
    
    @Override
    JSONObject communityService(ArrayList<DoctoReserveReq> list) {
        JSONObject infos = new JSONObject();
        // 遍历 list 逻辑 校验数据逻辑略
        // 遍历 list 逻辑 插入数据逻辑略
        return ResultUtils.success(infos);
    }
    
}

4.3 dao层

根据需求新建实体类：

import lombok.Data;

import javax.validation.constraints.NotNull;

/**
 * 社区预约
 */
@Data
public class CommunityReserveReq extends DoctoReserveReq {
    /**
     * 医生电话
     */
    @NotNull(message = "医生电话 不能为空")
    private String doctor_phone;

}

五、总结反思

1. 自己对 Json 的不够熟悉，只是停留在简单的了解和写入，对解析过程不够熟练
2. 对接收前端传过来的信息的接收不够了解，对 @Validated 和 @RequestBody 注解的理解和应用较少
3. 在 coding 前对整个框架的运行逻辑不够熟悉，导致 coding 过程出现了发现PC端没Session等问题而影响效率
4. 沉迷于造轮子，没有和前端沟通好传过来的数据的类型，导致浪费了部分时间

六、写在后面

欢迎关注，实现期间会经常发一些工作中遇到的问题。

欢迎随时留言讨论，知无不答！