项目5：实现数据字典的上传下载
1.什么是数据字典？如何设计？
2.业务流程逻辑
3.数据库表的设计
4.实现上传下载逻辑（前端）
5.实现上传逻辑（后端）
6.实现下载依赖（后端）
项目5：实现数据字典的上传下载
1.什么是数据字典？如何设计？
①什么是数据字典？
数据字典是用来管理系统常用的分类数据和固定设计
②数据字典的设计？
- id
- parent_id
- 即parent_id即为本标题的父标题，没有则为0
③数据字典中数据库对应后端pojo
- 数据库中的数据字典（Dict对象）
需要存储creat_time,update_time,is_deleted - 后端pojo
对应具体的excel即传输对象，不需要上述三个字段的存储，故对象为（ExcelDictDTO）
2.业务流程逻辑
①上传excel表
- 前端单击上传
- 后端接收到excel表解析为ExcelDictDTO对象
- 然后通过mapper保存到数据库中
②下载excel表
- 前端单击下载
- 后端接受到请求后通过mapper查询数据库并且封装为ExcelDictDTO对象
- 然后通过http响应返回文件
③注意
- ajax请求只能解析字符串
- http请求可以解析字符串，解析文件
- 故在上传时可以用ajax请求
- 故在下载时只能用普通的请求
3.数据库表的设计
①数据库表
②数据库表对应的entity
package com.atguigu.srb.core.pojo.entity;import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import java.time.LocalDateTime;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;/*** <p>* 数据字典* </p>** @author Likejin* @since 2023-04-09*/
@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="Dict对象", description="数据字典")
public class Dict implements Serializable {private static final long serialVersionUID = 1L;@ApiModelProperty(value = "id")@TableId(value = "id", type = IdType.AUTO)private Long id;@ApiModelProperty(value = "上级id")private Long parentId;@ApiModelProperty(value = "名称")private String name;@ApiModelProperty(value = "值")private Integer value;@ApiModelProperty(value = "编码")private String dictCode;@ApiModelProperty(value = "创建时间")private LocalDateTime createTime;@ApiModelProperty(value = "更新时间")private LocalDateTime updateTime;@ApiModelProperty(value = "删除标记（0:不可用 1:可用）")@TableField("is_deleted")@TableLogicprivate Boolean deleted;}③数据传输对象dto（与excel对应）
package com.atguigu.srb.core.pojo.dto;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;@Data
public class ExcelDictDTO {@ExcelProperty("id")private Long id;@ExcelProperty("上级id")private Long parentId;@ExcelProperty("名称")private String name;@ExcelProperty("值")private Integer value;@ExcelProperty("编码")private String dictCode;
}
4.实现上传逻辑（前端）
①在路由中加入系统设置/数据字典路由
srb-admin\src\router\index.js
{path: '/core',component: Layout,redirect: '/core/dict/list',name: 'coreDict',meta: { title: '系统设置', icon: 'el-icon-setting' },alwaysShow: true,children: [{path: 'dict/list',name: '数据字典',component: () => import('@/views/core/dict/list'),meta: { title: '数据字典' },},],},
②新建页面组件（配合路由渲染主内容区）
srb-admin\src\views\core\dict\list.vue
<template><div class="app-container"><!--Excel导入按钮--><div style="margin-bottom: 10px"><el-button@click="dialogVisible = true"type="primary"size="mini"icon="el-icon-download">导入Excel</el-button><el-button@click="exportData"type="primary"size="mini"icon="el-icon-upload2">导出Excel</el-button></div><!--显示一个dialog对话框--><el-dialog title="数据字典导入" :visible.sync="dialogVisible" width="30%"><el-form><el-form-item label="请选择Excel文件"><!--accept为只接受xls，xlsx的文件后缀，name名字必须要和后端的名字保持一致,avtion为提交地址发起ajax远程调用（和自己写的request请求无关），自己处理成功失败--><el-upload:auto-upload="true":multiple="false":limit="1":on-exceed="fileUploadExceed":on-success="fileUploadSuccess":on-error="fileUploadError":action="BASE_API + '/admin/core/dict/import'"name="file"accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"><el-button size="small" type="primary">点击上传</el-button></el-upload></el-form-item></el-form><div slot="footer" class="dialog-footer"><el-button @click="dialogVisible = false">取消</el-button></div></el-dialog></div>
</template><script>
export default {data() {return {dialogVisible: false, //对话框是否显式BASE_API: process.env.VUE_APP_BASE_API, //获取后端接口地址}},methods: {// 上传多于一个文件时fileUploadExceed() {this.$message.warning('只能选取一个文件')},//上传成功回调(http通信成功，有可能业务失败或业务失败)fileUploadSuccess(response) {if (response.code === 0) {//业务成功this.$message.success('数据导入成功')this.dialogVisible = false} else {//业务事变this.$message.error(response.message)}},//上传失败回调(http通信不成功)fileUploadError(error) {this.$message.error('数据导入失败')},//Excel数据导出exportData() {//导出excel，此时不能用ajax请求，无法响应文件window.location.href = this.BASE_API + '/admin/core/dict/export'},},
}
</script>5.实现上传逻辑（后端）
①逻辑流程
- 前端单击上传（发起ajax请求）
- 后端controller接收到文件类型数据，调用service层保存
- service层调用easyexcel接口读取excel数据（利用监听器）
- 监听器调用mapper接口存储excel数据到数据库
②创建dto数据传输对象（封装excel表格数据）
package com.atguigu.srb.core.pojo.dto;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;@Data
public class ExcelDictDTO {@ExcelProperty("id")private Long id;@ExcelProperty("上级id")private Long parentId;@ExcelProperty("名称")private String name;@ExcelProperty("值")private Integer value;@ExcelProperty("编码")private String dictCode;
}
③引入easyexcel依赖
<!--excel依赖--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId></dependency><!--excel底层用xml存储，需要xml解析器--><dependency><groupId>org.apache.xmlbeans</groupId><artifactId>xmlbeans</artifactId></dependency>
④编写controller接口（接受文件调用sevice存储数据库）
package com.atguigu.srb.core.controller.admin;import com.alibaba.excel.EasyExcel;
import com.atguigu.common.exception.BusinessException;
import com.atguigu.common.result.R;
import com.atguigu.common.result.ResponseEnum;
import com.atguigu.srb.core.pojo.dto.ExcelDictDTO;
import com.atguigu.srb.core.service.DictService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;/*** <p>* 数据字典 前端控制器* </p>** @author Likejin* @since 2023-04-09*/
@Api(tags = "数据字典管理")
@RestController
@RequestMapping("/admin/core/dict")
@Slf4j
@CrossOrigin
public class AdminDictController {@ResourceDictService dictService;@ApiOperation("Excel数据的批量导入（导入到数据库）")@PostMapping("/import")public R batchImport(@ApiParam(value="Excel数据字典文件")@RequestParam("file") MultipartFile file){try {InputStream inputStream = file.getInputStream();dictService.importData(inputStream);return R.ok().message("数据字典批量导入成功");} catch (Exception e) {//返回R对象，并且打印异常跟踪栈throw new BusinessException(ResponseEnum.UPLOAD_ERROR,e);}}@ApiOperation("Excel数据的导出")//访问页面@GetMapping("/export")public void export(HttpServletResponse response){try {// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman//设置类型为excelresponse.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("mydict", "UTF-8").replaceAll("\\+", "%20");//设置附件的形式下载到浏览器端response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");EasyExcel.write(response.getOutputStream(), ExcelDictDTO.class).sheet("数据字典").doWrite(dictService.listDictData());} catch (IOException e) {//EXPORT_DATA_ERROR(104, "数据导出失败"),throw new BusinessException(ResponseEnum.EXPORT_DATA_ERROR, e);}}}⑤编写service接口和serviceimpl
package com.atguigu.srb.core.service;import com.atguigu.srb.core.pojo.dto.ExcelDictDTO;
import com.atguigu.srb.core.pojo.entity.Dict;
import com.baomidou.mybatisplus.extension.service.IService;import java.io.InputStream;
import java.util.List;/*** <p>* 数据字典 服务类* </p>** @author Likejin* @since 2023-04-09*/
public interface DictService extends IService<Dict> {void importData(InputStream inputStream);List<ExcelDictDTO> listDictData();
}package com.atguigu.srb.core.service.impl;import com.alibaba.excel.EasyExcel;
import com.atguigu.srb.core.listener.ExcelDictDTOListener;
import com.atguigu.srb.core.mapper.DictMapper;
import com.atguigu.srb.core.pojo.dto.ExcelDictDTO;
import com.atguigu.srb.core.pojo.entity.Dict;
import com.atguigu.srb.core.service.DictService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;import javax.annotation.Resource;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;/*** <p>* 数据字典 服务实现类* </p>** @author Likejin* @since 2023-04-09*/
@Slf4j
@Service
public class DictServiceImpl extends ServiceImpl<DictMapper, Dict> implements DictService {@Resourceprivate DictMapper dictMapper;//读取数据//写入数据库，利用事务的操作成功全成功，失败全失败（只要异常出现则回滚）@Transactional(rollbackFor = Exception.class)@Overridepublic void importData(InputStream inputStream) {//传入输入流，EasyExcel.read(inputStream, ExcelDictDTO.class, new ExcelDictDTOListener(dictMapper)).sheet().doRead();log.info("Excel导入成功");}@Overridepublic List<ExcelDictDTO> listDictData() {List<Dict> dictList = dictMapper.selectList(null);//创建ExcelDictDTO列表，将Dict列表转化为ExcelDictDTO列表ArrayList<ExcelDictDTO> excelDictDTOList = new ArrayList<>(dictList.size());dictList.forEach(dict -> {ExcelDictDTO excelDictDTO = new ExcelDictDTO();//对象拷贝，dict拷贝到excelDictDTO（相同列拷贝）BeanUtils.copyProperties(dict, excelDictDTO);excelDictDTOList.add(excelDictDTO);});return excelDictDTOList;}
}⑥编写excel读取监听器并调用mapper接口
package com.atguigu.srb.core.listener;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.srb.core.mapper.DictMapper;
import com.atguigu.srb.core.pojo.dto.ExcelDictDTO;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;import java.util.ArrayList;
import java.util.List;@Slf4j
@NoArgsConstructor
public class ExcelDictDTOListener extends AnalysisEventListener<ExcelDictDTO> {//@Resource注入mapper不行，因为本类并没有被spring管理，需要用参数构造的方式加入mapperprivate DictMapper dictMapper;public ExcelDictDTOListener(DictMapper dictMapper){this.dictMapper = dictMapper;}//数据列表List<ExcelDictDTO> list = new ArrayList<ExcelDictDTO>();//每隔五条记录存储一次数据static final int BATCH_SIZE=5;@Overridepublic void invoke(ExcelDictDTO data, AnalysisContext analysisContext) {log.info("解析到一条数据{}",data);//调用mapper层的save数据//批量解析，列表达到一定数量，然后一次性存储数据//数据存入数据列表list.add(data);if(list.size()>=BATCH_SIZE){saveData();list.clear();}}private void saveData() {log.info("{}条数据被存储到数据库",list.size());//调用mapper层的批量save方法//TODOdictMapper.insertBatch(list);log.info("{}条数据被存储到数据库成功",list.size());}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {//最后剩余的数据不足BATCH_SIZE，最终一次性存储剩余数据saveData();log.info("解析完成");}
}⑦编写mapper接口和具体的sql语句
package com.atguigu.srb.core.mapper;import com.atguigu.srb.core.pojo.dto.ExcelDictDTO;
import com.atguigu.srb.core.pojo.entity.Dict;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;import java.util.List;/*** <p>* 数据字典 Mapper 接口* </p>** @author Likejin* @since 2023-04-09*/
public interface DictMapper extends BaseMapper<Dict> {void insertBatch(List<ExcelDictDTO> 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.atguigu.srb.core.mapper.DictMapper"><insert id="insertBatch">insert into dict (id ,parent_id ,name ,value ,dict_code) values<foreach collection="list" item="item" index="index" separator=",">(#{item.id} ,#{item.parentId} ,#{item.name} ,#{item.value} ,#{item.dictCode})</foreach></insert></mapper>⑧配置打包时打包JAVA文件内的xml
- JAVA目录下的xml不会被打包，只会打包.java
- 代码生成器生成的xml文件都放在java包下
- 两种解决方法：
将xml文件都放入resources下
打包方式改变 - 使用打包方式
<build><!-- 项目打包时会将java目录中的*.xml文件也进行打包 --><resources><resource><directory>src/main/java</directory><includes><include>**/*.xml</include></includes><filtering>false</filtering></resource></resources></build>
6.实现下载依赖（后端）
①业务流程
- 前端单击下载（向后端发起非ajax请求，可以不用设置跨域）
- 后端controller接受请求，controller调用easyexcel接口返回excel文件
- easyexcel接口利用service的获取到的数据封装为excel
- 注意：
调用代码生成器的service接口获得的是Dict类型数据，需要封装为ExcelDictDTO数据返回excel文件
②controller
package com.atguigu.srb.core.controller.admin;import com.alibaba.excel.EasyExcel;
import com.atguigu.common.exception.BusinessException;
import com.atguigu.common.result.R;
import com.atguigu.common.result.ResponseEnum;
import com.atguigu.srb.core.pojo.dto.ExcelDictDTO;
import com.atguigu.srb.core.service.DictService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;/*** <p>* 数据字典 前端控制器* </p>** @author Likejin* @since 2023-04-09*/
@Api(tags = "数据字典管理")
@RestController
@RequestMapping("/admin/core/dict")
@Slf4j
@CrossOrigin
public class AdminDictController {@ResourceDictService dictService;@ApiOperation("Excel数据的批量导入（导入到数据库）")@PostMapping("/import")public R batchImport(@ApiParam(value="Excel数据字典文件")@RequestParam("file") MultipartFile file){try {InputStream inputStream = file.getInputStream();dictService.importData(inputStream);return R.ok().message("数据字典批量导入成功");} catch (Exception e) {//返回R对象，并且打印异常跟踪栈throw new BusinessException(ResponseEnum.UPLOAD_ERROR,e);}}@ApiOperation("Excel数据的导出")//访问页面@GetMapping("/export")public void export(HttpServletResponse response){try {// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman//设置类型为excelresponse.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("mydict", "UTF-8").replaceAll("\\+", "%20");//设置附件的形式下载到浏览器端response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");EasyExcel.write(response.getOutputStream(), ExcelDictDTO.class).sheet("数据字典").doWrite(dictService.listDictData());} catch (IOException e) {//EXPORT_DATA_ERROR(104, "数据导出失败"),throw new BusinessException(ResponseEnum.EXPORT_DATA_ERROR, e);}}}③service层
- 封装代码生成器返回Dict的list为DTO的list
package com.atguigu.srb.core.service;import com.atguigu.srb.core.pojo.dto.ExcelDictDTO;
import com.atguigu.srb.core.pojo.entity.Dict;
import com.baomidou.mybatisplus.extension.service.IService;import java.io.InputStream;
import java.util.List;/*** <p>* 数据字典 服务类* </p>** @author Likejin* @since 2023-04-09*/
public interface DictService extends IService<Dict> {void importData(InputStream inputStream);List<ExcelDictDTO> listDictData();
}package com.atguigu.srb.core.service.impl;import com.alibaba.excel.EasyExcel;
import com.atguigu.srb.core.listener.ExcelDictDTOListener;
import com.atguigu.srb.core.mapper.DictMapper;
import com.atguigu.srb.core.pojo.dto.ExcelDictDTO;
import com.atguigu.srb.core.pojo.entity.Dict;
import com.atguigu.srb.core.service.DictService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;import javax.annotation.Resource;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;/*** <p>* 数据字典 服务实现类* </p>** @author Likejin* @since 2023-04-09*/
@Slf4j
@Service
public class DictServiceImpl extends ServiceImpl<DictMapper, Dict> implements DictService {@Resourceprivate DictMapper dictMapper;//读取数据//写入数据库，利用事务的操作成功全成功，失败全失败（只要异常出现则回滚）@Transactional(rollbackFor = Exception.class)@Overridepublic void importData(InputStream inputStream) {//传入输入流，EasyExcel.read(inputStream, ExcelDictDTO.class, new ExcelDictDTOListener(dictMapper)).sheet().doRead();log.info("Excel导入成功");}@Overridepublic List<ExcelDictDTO> listDictData() {List<Dict> dictList = dictMapper.selectList(null);//创建ExcelDictDTO列表，将Dict列表转化为ExcelDictDTO列表ArrayList<ExcelDictDTO> excelDictDTOList = new ArrayList<>(dictList.size());dictList.forEach(dict -> {ExcelDictDTO excelDictDTO = new ExcelDictDTO();//对象拷贝，dict拷贝到excelDictDTO（相同列拷贝）BeanUtils.copyProperties(dict, excelDictDTO);excelDictDTOList.add(excelDictDTO);});return excelDictDTOList;}
}
