SpringBoot+MybatisPlus+AliEasyExcel实现一级二级导入MySQL+导出
开发此功能，您需要先学习MybatisPlus一些属性，如果已掌握可忽略；
说明：一级分类：商品种类，二级分类：商品名，例如AK-47属于步枪类，步枪：AK-47，每个商品都需要描述，在二级分类加；
参考表设计
DROP TABLE IF EXISTS `shop_subject`;
CREATE TABLE `shop_subject` (
`id` char(19) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '商品类别ID',
`title` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '商品名称',
`parent_id` char(19) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '0' COMMENT '父ID',
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商品描述',
`gmt_create` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建时间',
`gmt_modified` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_parent_id`(`parent_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '武器列表' ROW_FORMAT = Compact;表数据
INSERT INTO `shop_subject` VALUES ('1576136488054226945', '步枪', '0', NULL, '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488091975682', 'M4A1-黑龙', '1576136488054226945', '自带消音器，开火具有红色火焰，射速快，枪械总体暗灰,可容纳弹夹容量:38,有效射程400m,全长750mm,枪重2.68kg', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488154890241', 'AK47-麒麟', '1576136488054226945', '带有尖锐锋利的刺刀，枪头眼睛有红宝打造，枪身由麒麟打造，轻便便携。可能容纳弹量35', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488221999106', '狙击枪', '0', NULL, '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488221999107', 'AWM- 天龙', '1576136488221999106', '龙头含着一颗龙珠，也是一颗瞄准镜，一个弹夹可容纳10发子弹，伤害偏中等,头部可致死', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488289107969', 'M4A1-雷神', '1576136488054226945', '自带消音器，开火以蓝色火焰，射速快，枪身会散发雷电视觉；可容纳子弹数：38', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488289107970', '近战武器', '0', NULL, '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488356216833', '尼泊尔- 屠龙', '1576136488289107970', '尼泊尔军刀进行锋利打造，以龙的雕刻散发红色光效', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488356216834', 'AK-47-A', '1576136488054226945', '常见系列步枪，对性能上进行了改造提升。', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488423325697', 'Barrett-战龙', '1576136488221999106', '具有超大威力的狙击枪，属于偏重，一个弹夹容纳5发子弹；龙图形的喷气制作', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488423325698', '机枪', '0', NULL, '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488490434562', 'RPK-盘龙', '1576136488423325698', '超快射速，大容量弹夹，属于机枪类，重量偏轻；', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488557543425', 'M4A1-黑骑士', '1576136488054226945', '有效射程550m,枪型偏小，消声类步枪武器，方便携带，枪身散发出一股暗红的光效。', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488557543426', '手斧-龙啸', '1576136488289107970', '一把军用斧头，外壳以龙雕刻，龙的眼睛发出红色暗光，手握手感紧致舒适轻便；', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488624652290', 'M4A1-玫瑰精灵', '1576136488054226945', '全长840mm，枪重2.68kg，弹匣容量35，有效射程600m，枪身一部分半透明中间一朵沾满了血液的玫瑰，枪托安装了小玩偶；', '2022-10-01 20:20:40', '2022-10-01 20:20:40');
INSERT INTO `shop_subject` VALUES ('1576136488624652291', '手枪', '0', NULL, '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488687566850', '沙鹰-修罗', '1576136488624652291', '枪 重1.99 kg，全 长270mm，暗黑的光', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488687566851', 'Barrett-极光', '1576136488221999106', '超高伤害的狙击武器，可容纳弹夹15发，偏重，可自由开启激光来提高瞄准度', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488750481410', '冲锋枪', '0', NULL, '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488750481411', 'MP5', '1576136488750481410', '普通款冲锋枪,价格实惠，偏轻，射速一般，容纳弹夹35发', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488817590274', '汤姆逊-烈龙', '1576136488750481410', '有效射程200m,弹匣容量77,枪重4.4 kg,全长808mm,以龙雕刻，龙眼会散发红色光效。伤害偏小,射速800RPM', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488817590275', '投掷武器', '0', NULL, '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488880504833', '高爆手雷', '1576136488817590275', '伤害居高，使用时候按按钮10秒后爆炸', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488880504834', '红色烟雾弹', '1576136488817590275', NULL, '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488943419393', '灰色烟雾弹', '1576136488817590275', NULL, '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136488943419394', '道具', '0', NULL, '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136489010528258', '万圣节V字面具', '1576136488943419394', '用于遮挡自己的真实面貌，有效防止大部分病毒液体', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136489010528259', '防弹衣', '1576136488943419394', '有效防护子弹炸弹降低伤害', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136489077637121', '防弹头盔', '1576136488943419394', '有效防护子弹炸弹降低伤害；', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136489140551682', 'MK23-湛蓝裂痕', '1576136488624652291', '体积小，射速快，伤害高', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136489140551683', 'M4A1-死神', '1576136488054226945', '半透明武器可以查看内部结构，在烟雾中可看清位置', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136489207660545', '金丝匕首', '1576136488289107970', '锋利由金丝打造', '2022-10-01 17:06:37', '2022-10-01 17:06:37');
INSERT INTO `shop_subject` VALUES ('1576136489270575105', '生化病毒防护口罩', '1576136488943419394', NULL, '2022-10-01 17:06:37', '2022-10-01 17:06:37');实体类
package com.withyou.armsMall.entity;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.IdType;
import java.util.Date;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
/**
* @author WithYou
* @since 2022-09-30
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="ShopSubject对象", description="商品对象")
public class ShopSubject implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "商品类别ID")
@TableId(value = "id", type = IdType.ID_WORKER_STR)
private String id;
@ApiModelProperty(value = "商品名称")
private String title;
@ApiModelProperty(value = "父ID")
private String parentId;
@ApiModelProperty(value = "商品描述")
private String description;
@ApiModelProperty(value = "创建时间")
@TableField(fill = FieldFill.INSERT)
private Date gmtCreate;
@ApiModelProperty(value = "更新时间")
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date gmtModified;
}建立一个excel包，方便分类
package com.withyou.armsMall.entity.excel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author WithYou
* @since 2022-09-30
*/
@AllArgsConstructor
@NoArgsConstructor
@Data
public class ShopSubjectVo {
@ExcelProperty(index = 0)
private String oneSubjectName;
@ExcelProperty(index = 1)
private String twoSubjectName;
@ExcelProperty(index = 2)
private String description;
}导出功能
导出Excel,Service层
package com.withyou.armsMall.service;
import com.withyou.armsMall.entity.ShopSubject;
import com.baomidou.mybatisplus.extension.service.IService;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
/**
* @author WithYou
* @since 2022-09-30
*/
public interface ShopSubjectService extends IService<ShopSubject> {
// 导出
void exportData(HttpServletResponse response);
}Controller
package com.withyou.armsMall.controller.Backstage;
import com.withyou.armsMall.service.ShopSubjectService;
import com.withyou.commonutils.R;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
/**
* @author WithYou
* @since 2022-09-30
*/
@RestController
@RequestMapping("/armsMall/shop-subject")
public class ShopSubjectController {
// @Resource
@Autowired
private ShopSubjectService shopSubjectService;
// 导出
@GetMapping("exportData")
public void exportData(HttpServletResponse response){
shopSubjectService.exportData(response);
}
}导出Excel Impl层
package com.withyou.armsMall.service.impl;
import com.alibaba.excel.EasyExcel;
import com.withyou.armsMall.entity.ShopList;
import com.withyou.armsMall.entity.ShopSubject;
import com.withyou.armsMall.entity.excel.ShopSubjectVo;
import com.withyou.armsMall.listener.SubjectExcelListener;
import com.withyou.armsMall.mapper.ShopSubjectMapper;
import com.withyou.armsMall.service.ShopSubjectService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.List;
/**
* @author WithYou
* @since 2022-09-30
*/
@Service
public class ShopSubjectServiceImpl extends ServiceImpl<ShopSubjectMapper, ShopSubject> implements ShopSubjectService {
@Override
public void exportData(HttpServletResponse response) {
// 设置下载信息
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 设置指定名字
String name="ShopPingName";
response.setHeader("Content-disposition","attachment;filename="+name+".xlsx");
// QueryDataBase
List<ShopSubject> list =baseMapper.selectList(null);
try{
EasyExcel.write(response.getOutputStream(),ShopSubject.class).sheet("ShopPingName").doWrite(list);
}catch (Exception exception){
exception.printStackTrace();
}
}
}测试时候，直接通过浏览器地址栏，输入controller给的路径访问回车会自动下载excel文件，里面就是导出的数据（针对小数据量的，几亿的数据后期更新）
Excel导入MySQL功能
创建Listener包，方便分类
package com.withyou.armsMall.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.withyou.armsMall.entity.ShopSubject;
import com.withyou.armsMall.entity.excel.ShopSubjectVo;
import com.withyou.armsMall.service.ShopSubjectService;
import com.withyou.servicebase.WithYouException.WithYouException;
public class SubjectExcelListener extends AnalysisEventListener<ShopSubjectVo> {
//不能自动注入，需要手动，提供有参无参
public ShopSubjectService shopSubjectService;
public SubjectExcelListener() {}
public SubjectExcelListener(ShopSubjectService shopSubjectService){
this.shopSubjectService=shopSubjectService;
}
@Override
public void invoke(ShopSubjectVo shopSubjectVo, AnalysisContext analysisContext) {
if (shopSubjectVo==null){
throw new WithYouException(201,"文件数据为空");
}
//判断一级分类是否重复
ShopSubject existOneShopSubject = this.existOneSubject(shopSubjectService, shopSubjectVo.getOneSubjectName());
if (existOneShopSubject==null){
existOneShopSubject=new ShopSubject();
existOneShopSubject.setTitle(shopSubjectVo.getOneSubjectName());
existOneShopSubject.setParentId("0");
shopSubjectService.save(existOneShopSubject);
}
//拿到一级分类id值绑定二级
String parentId = existOneShopSubject.getId();
//添加二级分类,判断二级分类是否重复
ShopSubject existTwoSubject = this.existTwoSubject(shopSubjectService, shopSubjectVo.getTwoSubjectName(), parentId,shopSubjectVo.getDescription());
if (existTwoSubject==null){
existTwoSubject=new ShopSubject();
existTwoSubject.setParentId(parentId);
existTwoSubject.setTitle(shopSubjectVo.getTwoSubjectName());
existTwoSubject.setDescription(shopSubjectVo.getDescription());
shopSubjectService.save(existTwoSubject);
}
}
//判断一级分类不能重复添加
private ShopSubject existOneSubject(ShopSubjectService shopSubjectService,String OneName){
QueryWrapper<ShopSubject> queryOneWrapper =new QueryWrapper<>();
queryOneWrapper.eq("title",OneName);
queryOneWrapper.eq("parent_id","0");
ShopSubject one = shopSubjectService.getOne(queryOneWrapper);
return one;
}
//判断二级分类不能重复添加
private ShopSubject existTwoSubject(ShopSubjectService shopSubjectService,String OneName,String parentId,String description){
QueryWrapper<ShopSubject> queryTwoWrapper =new QueryWrapper<>();
queryTwoWrapper.eq("title",OneName);
queryTwoWrapper.eq("parent_id",parentId);
queryTwoWrapper.eq("description",description);
ShopSubject two = shopSubjectService.getOne(queryTwoWrapper);
return two;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext){ }
}// 导入 controller
@PostMapping("saveSubject")
public R saveSubject(MultipartFile file){
shopSubjectService.saveSubject(file,shopSubjectService);
return R.ok();
}//导入 interface
void saveSubject(MultipartFile file,ShopSubjectService shopSubjectService);//导入 impl
@Override
public void saveSubject(MultipartFile file,ShopSubjectService shopSubjectService) {
try {
InputStream inputStream =file.getInputStream();
EasyExcel.read(inputStream, ShopSubjectVo.class,new SubjectExcelListener(shopSubjectService)).sheet().doRead();
}catch (Exception exception){
exception.printStackTrace();
}
}该功能包含有：自定义异常、自定义swagger测试文档、lombok；
该功能自己独自摸索，可能与资深程序员写的不一样，逻辑是对的，效果能达到；
浙公网安备 33010602011771号