谷粒学院项目

项目后台设计---课程分类管理

表如何存储二级分类

一：EasyExcel简介

1.EasyExcel特点

2.使用EasyExcel进行写操作

（1）引入EasyExcel的依赖

<!--xls-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
</dependency>
<dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
</dependency>

<dependencies>
    <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.1</version>
    </dependency>
</dependencies>

（2）创建实体类--与excel表对应

package com.atguigu.utils.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.models.auth.In;
import lombok.Data;

@Data
public class DemoData {
    // 设置excel表头名称
    @ExcelProperty("学生编号")
    private Integer sno;

    @ExcelProperty("学生姓名")
    private String sname;
}

（3）实现最终写操作

package com.atguigu.utils.excel;

import com.alibaba.excel.EasyExcel;

import java.util.ArrayList;
import java.util.List;

public class TestEasyExcel {
    
    public static void main(String[] args) {
        // 实现excel写的操作
        // 1.设置写入文件夹地址和excel文件名称
        String filename = "D:\guli_1010\write.xlsx";

        // 2.调用easyexcel里面的方法实现写操作
        // 参数一：文件路径名称  参数二：实体类的Class
        EasyExcel.write(filename, DemoData.class).sheet("学生列表").doWrite(getData());
    }

    private static List<DemoData> getData() {
        List<DemoData> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            DemoData demoData = new DemoData();
            demoData.setSno(i);
            demoData.setSname("jack" + i);
            list.add(demoData);
        }
        return list;
    }
}

3.使用EasyExcel进行读操作

（1）创建实体类

package com.atguigu.utils.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.models.auth.In;
import lombok.Data;

@Data
public class DemoData {
    // 设置excel表头名称
    @ExcelProperty(value = "学生编号", index = 0)
    private Integer sno;

    @ExcelProperty(value = "学生姓名", index = 1)
    private String sname;
}

（2）创建读取操作的监听器

package com.atguigu.utils.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.Map;

public class ExcelListener extends AnalysisEventListener<DemoData> {

    // 一行一行的读取excel内容
    @Override
    public void invoke(DemoData demoData, AnalysisContext analysisContext) {
        System.out.println("******" + demoData);
    }

    // 读取表头的内容
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头: " + headMap);
    }

    // 读取完成以后做的事情
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

（3）调用实现最终的读取

 public static void main(String[] args) {
        // 实现excel写的操作
//        // 1.设置写入文件夹地址和excel文件名称
//        String filename = "D:\guli_1010\write.xlsx";
//
//        // 2.调用easyexcel里面的方法实现写操作
//        // 参数一：文件路径名称  参数二：实体类的Class
//        EasyExcel.write(filename, DemoData.class).sheet("学生列表").doWrite(getData());


        // 实现excel操作
        String filename = "D:\guli_1010\write.xlsx";
        EasyExcel.read(filename, DemoData.class, new ExcelListener()).sheet().doRead();

    }

二：课程分类添加功能--后端实现

1.引入easyexcel依赖

2.使用代码生成器把课程分类的代码生成出来

package com.atguigu.utils;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.GlobalConfig;
import com.baomidou.mybatisplus.generator.config.PackageConfig;
import com.baomidou.mybatisplus.generator.config.StrategyConfig;
import com.baomidou.mybatisplus.generator.config.rules.DateType;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
import org.junit.Test;

/**
 * @author
 * @since 2018/12/13
 */
public class CodeGenerator {

    @Test
    public void run() {

        // 1、创建代码生成器
        AutoGenerator mpg = new AutoGenerator();

        // 2、全局配置
        GlobalConfig gc = new GlobalConfig();
        String projectPath = System.getProperty("user.dir");
        gc.setOutputDir("D:\JavaProject\guli_parent\service\service_edu" + "/src/main/java");


        gc.setAuthor("testjava");
        gc.setOpen(false); //生成后是否打开资源管理器
        gc.setFileOverride(false); //重新生成时文件是否覆盖
        gc.setServiceName("%sService");    //去掉Service接口的首字母I
        gc.setIdType(IdType.ID_WORKER_STR); //主键策略
        gc.setDateType(DateType.ONLY_DATE);//定义生成的实体类中日期类型
        gc.setSwagger2(true);//开启Swagger2模式
        mpg.setGlobalConfig(gc);

        // 3、数据源配置
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setUrl("jdbc:mysql://localhost:3306/guli?serverTimezone=GMT%2B8");
        dsc.setDriverName("com.mysql.cj.jdbc.Driver");
        dsc.setUsername("root");
        dsc.setPassword("l1140223988");
        dsc.setDbType(DbType.MYSQL);
        mpg.setDataSource(dsc);

        // 4、包配置
        PackageConfig pc = new PackageConfig();

        pc.setParent("com.atguigu");
        pc.setModuleName("eduservice"); //模块名

        pc.setController("controller");
        pc.setEntity("entity");
        pc.setService("service");
        pc.setMapper("mapper");
        mpg.setPackageInfo(pc);

        // 5、策略配置
        StrategyConfig strategy = new StrategyConfig();
        strategy.setInclude("edu_subject");
        strategy.setNaming(NamingStrategy.underline_to_camel);//数据库表映射到实体的命名策略
        strategy.setTablePrefix(pc.getModuleName() + "_"); //生成实体时去掉表前缀

        strategy.setColumnNaming(NamingStrategy.underline_to_camel);//数据库表字段映射到实体的命名策略
        strategy.setEntityLombokModel(true); // lombok 模型 @Accessors(chain = true) setter链式操作

        strategy.setRestControllerStyle(true); //restful api风格控制器
        strategy.setControllerMappingHyphenStyle(true); //url中驼峰转连字符

        mpg.setStrategy(strategy);


        // 6、执行
        mpg.execute();
    }
}

3.创建实体类和excel对应关系

package com.atguigu.eduservice.entity.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class SubjectData {

    @ExcelProperty(index = 0)
    private int oneSubjectName;
    @ExcelProperty(index = 1)
    private String twoSubjectName;

}

4.在监听器实现类中实现代码

package com.atguigu.eduservice.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.eduservice.entity.EduSubject;
import com.atguigu.eduservice.entity.excel.SubjectData;
import com.atguigu.eduservice.service.EduSubjectService;
import com.atguigu.servicebase.exceptionhandler.GuliException;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {

    // 因为SubjectExcelListener不能交给spring进行管理，需要自己new，不能注入其他对象
    public EduSubjectService subjectService;

    // 无参构造
    public SubjectExcelListener() {

    }

    // 有参构造
    public SubjectExcelListener(EduSubjectService subjectService) {
        this.subjectService = subjectService;
    }

    // 读取excel内容，一行一行进行读取
    @Override
    public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
        if (subjectData == null) {
            throw new GuliException(20001, "文件数据为空");
        }

        // 一行一行读取，每次读取有两个值，第一个值一级分类，第二个值二级分类
        // 判断一级分类是否重复
        EduSubject existOneSubject = this.existOneSubject(subjectService, subjectData.getOneSubjectName());
        if (existOneSubject == null) {
            // 说明表里面没有相同的一级分类，说明可以添加
            // 设置值
            existOneSubject = new EduSubject();
            existOneSubject.setParentId("0");
            existOneSubject.setTitle(subjectData.getOneSubjectName());
            // 调用service里面内置的方法，实现往数据库中添加分类的功能
            subjectService.save(existOneSubject);
        }

        // 添加二级分类
        // 判断二级分类是否重复
        String pid = existOneSubject.getId();  //获取当前二级分类所对应一级分类的id值
        EduSubject existTwoSubject = this.existTwoSubject(subjectService, subjectData.getTwoSubjectName(), pid);
        if (existTwoSubject == null) {
            // 说明表里面没有相同的二级分类，说明可以添加
            // 设置值
            existTwoSubject = new EduSubject();
            existTwoSubject.setParentId(pid);
            existTwoSubject.setTitle(subjectData.getTwoSubjectName());
            // 调用service里面内置的方法，实现往数据库中添加分类的功能
            subjectService.save(existTwoSubject);
        }
    }

    // 判断一级分类不能重复添加
    private EduSubject existOneSubject(EduSubjectService subjectService, String name) {
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        // 加判断条件
        // select * from edu_subject where title = name and parent_id = 0;
        wrapper.eq("title", name);
        wrapper.eq("parent_id", 0);
        // 调用service进行数据库查询
        EduSubject one = subjectService.getOne(wrapper);
        return one;
    }

    // 判断二级分类不能重复添加
    private EduSubject existTwoSubject(EduSubjectService subjectService, String name, String pid) {
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        // 加判断条件
        // select * from edu_subject where title = name and parent_id = pid;
        wrapper.eq("title", name);
        wrapper.eq("parent_id", pid);
        // 调用service进行数据库查询
        EduSubject one = subjectService.getOne(wrapper);
        return one;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

}

三：课程分类添加功能--前端实现

1.添加课程分类路由

2.编写路由跳转的页面

3.初步实现添加课程分类前端页面

<template>
    <div class="app-container">
        <el-form label-width="120px">
            <!-- 提示信息 -->
            <el-form-item label="信息描述">
                <el-tag type="info">excel模版说明</el-tag>
                <el-tag>
                    <i class="el-icon-download"/>
                    <a :href="'/static/01.xlsx'">点击下载模版</a>
                </el-tag>
            </el-form-item>
           
            <el-form-item label="选择Excel">
                <!-- 上传组件 -->
                <el-upload
                    ref="upload"  
                    :auto-upload="false"
                    :on-success="fileUploadSuccess"
                    :on-error="fileUploadError"
                    :disabled="importBtnDisabled"
                    :limit="1"
                    :action="BASE_API+'/eduservice/subject/addSubject'"
                    name="file"
                    accept="application/vnd.ms-excel">
                    <el-button slot="trigger" size="small" type="primary">选取文件</el-button>
                    <el-button
                        :loading="loading"
                        style="margin-left: 10px;"
                        size="small"
                        type="success"
                        @click="submitUpload">上传到服务器</el-button>
                </el-upload>
            </el-form-item>
        </el-form>
    </div>
</template>

<script>
export default {
    data(){
        return{
            BASE_API: process.env.BASE_API, // 接口API地址
            // OSS_PATH: process.env.OSS_PATH, // 阿里云OSS地址
            importBtnDisabled: false, // 按钮是否禁用,
            loading: false
        }
    },
    created(){

    },
    methods:{
        // 点击按钮上传文件到接口里面
        submitUpload(){
            // 我们提交的是表单，不是ajax请求，所以不能用封装好的request工具来往后端发送请求
            // vue表单提交写法
            this.importBtnDisabled = true
            this.loading = true
            // js:document.getElementById("upload").submit()
            this.$refs.upload.submit()
        },
        // 上传成功
        fileUploadSuccess(response){
            // response代表添加成功以后的返回数据
            // 提示信息
            this.loading = false
            this.$message({
                type: 'success',
                message: '添加课程分类成功'
            })
            // 跳转到课程分类列表页面
            
        },
        // 上传失败
        fileUploadError(){
            this.loading = false
            this.$message({
                type: 'error',
                message: '添加课程分类失败'
            })
        }
    }
}
</script>

四：课程分类列表功能--后端实现

后端接口返回数据格式:

data2: [
            {
                id: 1,
                label: '一级分类1',
                children: [
                    {
                        id: 3,
                        label: '二级分类1',
                    }
                 ]
            },
            
            {
                id: 2,
                label: '一级分类2',
                children: [
                    {
                        id: 4,
                        label: '二级分类1'
                    }, 
                    {
                        id: 5,
                        label: '二级分类2'
                    }
                 ]
             }
      ]

后端返回这种数据格式的做法:

(1):针对返回数据创建对应的实体类

(2):在两个实体类之间表示关系（一个一级分类中有多个二级分类）

(3):编写具体封装代码

1.创建controller

package com.atguigu.eduservice.controller;


import com.atguigu.commonutils.R;
import com.atguigu.eduservice.entity.subject.OneSubject;
import com.atguigu.eduservice.service.EduSubjectService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import org.springframework.web.multipart.MultipartFile;

import java.util.List;

/**
 * <p>
 * 课程科目 前端控制器
 * </p>
 *
 * @author testjava
 * @since 2022-10-03
 */
@RestController
@RequestMapping("/eduservice/subject")
@CrossOrigin
public class EduSubjectController {
    @Autowired
    private EduSubjectService eduSubjectService;

    // 添加课程分类
    // 获取上传过来文件，把文件内容读取出来
    @PostMapping("addSubject")
    public R addSubject(MultipartFile file) {
        // 上传过来excel文件
        eduSubjectService.saveSubject(file, eduSubjectService);
        return R.ok();
    }

    // 课程分类列表(树形)
    @GetMapping("getAllSubject")
    public R getAllSubject() {
        // 获取所有一级分类
        List<OneSubject> list = eduSubjectService.getAllOneTwoSubject();
        return R.ok().data("list", list);
    }
}

2.编写service代码

package com.atguigu.eduservice.service.impl;

import com.alibaba.excel.EasyExcel;
import com.atguigu.eduservice.entity.EduSubject;
import com.atguigu.eduservice.entity.excel.SubjectData;
import com.atguigu.eduservice.entity.subject.OneSubject;
import com.atguigu.eduservice.entity.subject.TwoSubject;
import com.atguigu.eduservice.listener.SubjectExcelListener;
import com.atguigu.eduservice.mapper.EduSubjectMapper;
import com.atguigu.eduservice.service.EduSubjectService;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>
 * 课程科目 服务实现类
 * </p>
 *
 * @author testjava
 * @since 2022-10-03
 */
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {


    // 添加课程分类
    @Override
    public void saveSubject(MultipartFile file, EduSubjectService eduSubjectService) {
        try {
            // 文件输入流
            InputStream in = file.getInputStream();
            // 调用方法进行读取
            EasyExcel.read(in, SubjectData.class, new SubjectExcelListener(eduSubjectService)).sheet().doRead();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 课程分类列表（树形）
    @Override
    public List<OneSubject> getAllOneTwoSubject() {
        // 1.查询出所有一级分类   parent_id = 0
        // Select * from edu_subject Where parent_id = '0';
        QueryWrapper<EduSubject> wrapperOne = new QueryWrapper<>();
        wrapperOne.eq("parent_id", 0);
        List<EduSubject> oneSubjectList = baseMapper.selectList(wrapperOne);

        // 2.查询出所有二级分类   parent_id != 0
        // Select * from edu_subject Where parent_id != '0';
        QueryWrapper<EduSubject> wrapperTwo = new QueryWrapper<>();
        wrapperOne.ne("parent_id", 0);
        List<EduSubject> TwoSubjectList = baseMapper.selectList(wrapperTwo);

        // 创建list集合，用于存储最终封装数据
        List<OneSubject> finalSubjectList = new ArrayList<>();
        // 3.封装一级分类
        // 遍历oneSubjectList集合
        for (EduSubject eduSubject : oneSubjectList) {
            // 得到oneSubjectList每个eduSubject对象
            // 把eduSubject转化为oneSubject
            OneSubject oneSubject = new OneSubject();
//            oneSubject.setId(eduSubject.getId());
//            oneSubject.setTitle(eduSubject.getTitle());
            // 简写
            BeanUtils.copyProperties(eduSubject, oneSubject);
            finalSubjectList.add(oneSubject);

            // 4.封装二级分类
            for (EduSubject subject : TwoSubjectList) {
                if (subject.getParentId().equals(eduSubject.getId())) {
                    // 当前二级分类的父id等于当前一级分类的id
                    TwoSubject twoSubject = new TwoSubject();
                    BeanUtils.copyProperties(subject, twoSubject);
                    oneSubject.getChildren().add(twoSubject);
                }
            }
        }
        return finalSubjectList;
    }
}

五：课程分类列表功能--前端实现

1.定义调用后端api的函数

import request from '@/utils/request'


export default{
    // 前端调用后端api的方法 

    // 1.课程分类列表
    getSubjectList(){
        return request({
            url: `/eduservice/subject/getAllSubject`,
            method: 'get'
          })
    }
}

2.定义显示页面

<template>
  <div class="app-container">
    <el-input v-model="filterText" placeholder="Filter keyword" style="margin-bottom:30px;" /> 

    <el-tree
      ref="tree2"
      :data="data2"
      :props="defaultProps"
      :filter-node-method="filterNode"
      class="filter-tree"
      default-expand-all
    />

  </div>
</template>

<script>
import subject from '@/api/edu/subject'
export default {

  data() { 
    return {
      filterText: '',
      data2: [], // 返回所有分类数据
      defaultProps: {
        children: 'children',
        label: 'title'
      }
    }
  },
  watch: {
    filterText(val) {
      this.$refs.tree2.filter(val)
    }
  },
  methods: {
    getAllSubjectList(){
        subject.getSubjectList()
            .then(response => {
                this.data2 = response.data.list
            })
    },
    filterNode(value, data) {
      if (!value) return true
      return data.title.toLowerCase().indexOf(value.toLowerCase()) !== -1
    }
  },
  created(){
    this.getAllSubjectList()
  }
}
</script>

3.完善添加课程分类前端页面

<template>
    <div class="app-container">
        <el-form label-width="120px">
            <!-- 提示信息 -->
            <el-form-item label="信息描述">
                <el-tag type="info">excel模版说明</el-tag>
                <el-tag>
                    <i class="el-icon-download"/>
                    <a :href="'/static/01.xlsx'">点击下载模版</a>
                </el-tag>
            </el-form-item>
           
            <el-form-item label="选择Excel">
                <!-- 上传组件 -->
                <el-upload
                    ref="upload"  
                    :auto-upload="false"
                    :on-success="fileUploadSuccess"
                    :on-error="fileUploadError"
                    :disabled="importBtnDisabled"
                    :limit="1"
                    :action="BASE_API+'/eduservice/subject/addSubject'"
                    name="file"
                    accept="application/vnd.ms-excel">
                    <el-button slot="trigger" size="small" type="primary">选取文件</el-button>
                    <el-button
                        :loading="loading"
                        style="margin-left: 10px;"
                        size="small"
                        type="success"
                        @click="submitUpload">上传到服务器</el-button>
                </el-upload>
            </el-form-item>
        </el-form>
    </div>
</template>

<script>
export default {
    data(){
        return{
            BASE_API: process.env.BASE_API, // 接口API地址
            // OSS_PATH: process.env.OSS_PATH, // 阿里云OSS地址
            importBtnDisabled: false, // 按钮是否禁用,
            loading: false
        }
    },
    created(){

    },
    methods:{
        // 点击按钮上传文件到接口里面
        submitUpload(){
            // 我们提交的是表单，不是ajax请求，所以不能用封装好的request工具来往后端发送请求
            // vue表单提交写法
            this.importBtnDisabled = true
            this.loading = true
            // js:document.getElementById("upload").submit()
            this.$refs.upload.submit()
        },
        // 上传成功
        fileUploadSuccess(response){
            // response代表添加成功以后的返回数据
            // 提示信息
            this.loading = false
            this.$message({
                type: 'success',
                message: '添加课程分类成功'
            })
            // 跳转到课程分类列表页面
            this.$router.push({path:'/subject/list'})
        },
        // 上传失败
        fileUploadError(){
            this.loading = false
            this.$message({
                type: 'error',
                message: '添加课程分类失败'
            })
        }
    }
}
</script>