vue-element-admin-master 在线教育 - 【4】你用 POI，我已经用 EasyExcel 了+课程科目管理

哈喽，大家好！我是话里人，欢迎大家来到本期Blog，本期将对EasyExcel和POI作为前置知识快速入门，为项目的继续运行做好铺垫~

在线教育

1. EasyExcel 入门

1.1 EasyExcel概述

​ EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel工具。

​ 官网: https://easyexcel.opensource.alibaba.com/docs/current/

​ Github地址：https://github.com/alibaba/easyexcel

1.2 EasyExcel 特点

• Java解析、生成Excel比较有名的框架有Apache poi、jxl，但他们都存在一个严重的问题就是非常的耗内存。

• EasyExcel 重写了poi，使一个3M的excel只需要几M内存，并且再大的excel不会出现内存溢出。

  • 64M内存1分钟内读取75M(46W行25列)的Excel。

1.3 环境搭建

1.3.1 测试父项目

• 项目名：zx-test-parent

  

• 修改pom文件

      <dependencies>
          <!-- 测试 -->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-test</artifactId>
          </dependency>
      </dependencies>
  

1.3.2 测试excel项目

• 项目名：zx-test-excel

  

  

• 修改pom，添加依赖

      <dependencies>
          <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>easyexcel</artifactId>
              <version>3.1.1</version>
          </dependency>
          <dependency>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
          </dependency>
      </dependencies>
  

1.4 基本操作

1.4.1 测试JavaBean

package com.czxy.zx.demo01;

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

import java.util.Date;

/**
 * Created by tanxintong.
 */
@Data
public class Student {
    @ExcelProperty("编号")
    private String id;

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

    @ExcelProperty("电话")
    private String telephone;

    @ExcelProperty("邮箱")
    private String email;

    @ExcelProperty("生日")
    private Date brithday;
}

1.4.2 测试文件路径

package com.czxy.zx.demo01;

import org.junit.jupiter.api.Test;

/**
 * @author txt
 * @email tanxintong9968@163.com
 */
public class TestExcel {
    /**
     * 获得根路径
     * @return
     */
    public String getPath() {
        return this.getClass().getResource("/").getPath();
    }

    @Test
    public void testPath() {
        // 测试文件路径
        String path = getPath() + "student_demo.xls";
        System.out.println(path);
    }


}

1.4.3 写操作

• excel 属于 office组件一个软件

• 存在若干版本，大体上划分2种情况，2007前的，2007年后的

  • 2003版：扩展名 xls，内容比较少，最大单元格 IV65536 ，256列(IV)
  • 2007版：扩展名 xlsx，内容较多，最大单元格 XFD1048576，16384列(XFD)

   /**
     * 准备数据
     * @return
     */
    private List<Student> getData(){
        List<Student> list = new ArrayList<Student>();
        for(int i = 0 ; i < 10 ; i ++){
            Student student = new Student();
            student.setId("stu" + i);
            student.setName("wang" + i);
            student.setAge( 18 + i );
            student.setTelephone("1361234" + i);
            student.setEmail("wang" + i + "@czxy.com");
            student.setBrithday(new Date());
            list.add(student);
        }
        return list;
    }

    @Test
    public void testWrite(){
        String file = getPath() + "student_demo.xls";
        //EasyExcel.write(位置,对象).sheet("表名").doWrite(数据);
        EasyExcel.write(file,Student.class).sheet("班级").doWrite(getData());
    }

1.4.3 读操作

• 处理类：

  • 处理类需要实现 AnalysisEventListener 接口

  package com.czxy.zx.demo01;
  
  import com.alibaba.excel.context.AnalysisContext;
  import com.alibaba.excel.event.AnalysisEventListener;
  import com.czxy.zx.domain.Student;
  
  /**
   * @author txt
   * @email tanxintong9968@163.com
   */
  public class StudentListener extends AnalysisEventListener<Student> {
      @Override
      public void invoke(Student student, AnalysisContext analysisContext) {
          System.out.println(student);
      }
  
      @Override
      public void doAfterAllAnalysed(AnalysisContext analysisContext) {
          System.out.println("解析完成");
      }
  }
  
  

• 测试

      @Test
      public void testRead(){
          String file = getPath() + "student_demo.xls";
          //EasyExcel.read(文件, 封装对象, 处理类).sheet("表").doRead();
          EasyExcel.read(file, Student.class, new StudentListener()).sheet("班级").doRead();
      }
  

1.5 复杂操作

1.5.1 复合表头

package com.czxy.zx.demo02;

        import com.alibaba.excel.annotation.ExcelProperty;
        import com.alibaba.excel.annotation.format.DateTimeFormat;
        import com.alibaba.excel.annotation.write.style.ColumnWidth;
        import com.alibaba.excel.annotation.write.style.ContentRowHeight;
        import com.alibaba.excel.annotation.write.style.HeadRowHeight;
        import lombok.Data;

        import java.util.Date;
        
/**
 * @author txt
 * @email tanxintong9968@163.com
 */
@Data
@ContentRowHeight(20)
@HeadRowHeight(20)  //行高
@ColumnWidth(25)    //列宽
public class Student2 {
    @ExcelProperty("编号")
    private String id;

    @ExcelProperty({"基本信息","姓名"})   //复制表头
    private String name;

    @ExcelProperty({"基本信息","年龄"})
    private Integer age;

    @ExcelProperty("电话")
    private String telephone;

    @ExcelProperty("邮箱")
    private String email;

    @ExcelProperty("生日")
    @DateTimeFormat("yyyy年MM月dd日")
    private Date brithday;
}

1.5.2 写操作：多表

package com.czxy.zx.demo02;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.czxy.zx.demo01.Student;
import org.junit.Test;

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

/**
 * @author txt
 * @email tanxintong9968@163.com
 */
public class TestExcel2 {
    /**
     * 获得根路径
     * @return
     */
    public String getPath() {
        return this.getClass().getResource("/").getPath();
    }

    /**
     * 准备数据
     * @return
     */
    private List<Student2> getData(Integer flag){
        List<Student2> list = new ArrayList<Student2>();
        for(int m = 0 ; m < 10 ; m ++){
            String i = "" + flag + m ;
            Student2 student = new Student2();
            student.setId("stu" + i);
            student.setName("wang" + i);
            student.setAge( 18 );
            student.setTelephone("1361234" + i);
            student.setEmail("wang" + i + "@czxy.com");
            student.setBrithday(new Date());
            list.add(student);
        }
        return list;
    }

    @Test
    public void testMoreSheetWrite(){
        String file = getPath() + "student_demo2.xls";

        ExcelWriter excelWriter = EasyExcel.write(file).build();

        for (int i = 0; i < 5; i++) {
            WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(Student2.class).build();
            // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
            List<Student2> data = getData(i);
            excelWriter.write(data, writeSheet);
        }

        excelWriter.finish();
    }


}

1.5.3 读操作：多表

• 具有缓存处理类

  package com.czxy.zx.demo02;
  
  import com.alibaba.excel.context.AnalysisContext;
  import com.alibaba.excel.event.AnalysisEventListener;
  
  import java.util.ArrayList;
  import java.util.List;
  
  /**
   * Created by tanxintong.
   */
  public class Student2Listener extends AnalysisEventListener<Student2> {
  
      // 批量操作数
      private static final int BATCH_COUNT = 10;
      // 用于缓存信息
      private List<Student2> cache = new ArrayList<Student2>();
  
      public void invoke(Student2 student, AnalysisContext analysisContext) {
          //保存学生信息
          cache.add(student);
          if(cache.size() >= BATCH_COUNT){
              // 保存数据
              saveData();
          }
      }
  
      public void doAfterAllAnalysed(AnalysisContext analysisContext) {
          //最后的不够 BATCH_COUNT 倍数
          saveData();
      }
  
      private void saveData() {
          // 集合不为空
          if(! cache.isEmpty()) {
              // 处理缓存数据
              System.out.println(cache);
              // 清空缓存
              cache.clear();
          }
      }
  }
  
  

• 读操作

  @Test
      public void testMoreRead(){
          String file = getPath() + "student_demo2.xls";
          //EasyExcel.read(文件, 封装对象, 处理类).sheet("表").doRead();
  
          ExcelReader excelReader = EasyExcel.read(file, Student2.class, new Student2Listener()).build();
          // 确定需要解析的sheet
          for (int i = 0; i < 5; i++) {
              ReadSheet readSheet = EasyExcel.readSheet("模板" + i).build();
              excelReader.read(readSheet);
          }
  
          excelReader.finish();
      }
  

1.6.4 写操作：多对象

• Student

  @Data
  @NoArgsConstructor
  @AllArgsConstructor
  public class Student {
      @ExcelProperty("姓名")
      private String name;
      @ExcelProperty("年龄")
      private Integer age;
  }
  
  

• Book

  @Data
  @NoArgsConstructor
  @AllArgsConstructor
  @HeadRowHeight(50)
  @HeadFontStyle(fontName = "黑体",italic =  BooleanEnum.TRUE, color = Font.COLOR_RED, underline = 2)
  public class Book {
      @ExcelProperty("编号")
      private String id;
  
      @ExcelProperty({"作者信息","姓名"})
      private String authorName;
  
      @ExcelProperty({"作者信息","年龄"})
      private Integer authorAge;
  
      @ExcelProperty({"书籍基本信息","标题"})
      private String title;
  
      @ContentFontStyle(fontName = "楷书",italic =  BooleanEnum.TRUE, color = Font.COLOR_RED, underline = -1)
      @ExcelProperty({"书籍基本信息","价格"})
      private Double price;
      @ExcelProperty({"书籍基本信息","出版日期"})
      @DateTimeFormat("yyyy年MM月dd日")
      private Date publishDate;
  }
  

• 实现

  package com.czxy.zx.demo03;
  
  import com.alibaba.excel.EasyExcel;
  import com.alibaba.excel.ExcelWriter;
  import com.alibaba.excel.write.metadata.WriteSheet;
  import com.czxy.zx.demo01.Student;
  import com.czxy.zx.demo02.Book;
  import org.junit.Test;
  
  import java.util.*;
  
  
  
    /**
     * @author txt
     * @email tanxintong9968@163.com
     */
  public class TestManyObject {
  
      // 获得当前项目的运行时的根目录
      public String getPath() {
          return this.getClass().getResource("/").getPath();
      }
  
      // 模拟数据
      public List<Student> getStudentData() {
          List<Student> list = new ArrayList<>();
          for (int i = 0; i < 20; i++) {
              list.add(new Student("张三" + i, 18 + i));
          }
          return list;
      }
  
      public List<Book> getBookData() {
          List<Book> list = new ArrayList<>();
          for (int i = 0; i < 20; i++) {
              list.add(new Book(i+"" , "张三" + i , 18 +i, "坏蛋是怎么"+i, 998d+i, new Date()));
          }
          return list;
      }
  
      // 遍历map即可
      private Map<Class<?>, List<?>> getData() {
          Map<Class<?>, List<?>> map = new HashMap<>();
          map.put(Student.class, getStudentData());
          map.put(Book.class, getBookData());
          return map;
      }
  
      @Test
      public void testManyObject() {
          String file = getPath() + "many_object.xlsx";
          //1 开始写
          ExcelWriter excelWriter = EasyExcel.write(file).build();
          //2 依次写每一个对象
          for(Map.Entry<Class<?>, List<?>> entry : getData().entrySet()) {
              Class<?> clazz = entry.getKey();            //类型
              List<?> data = entry.getValue();            //数据
              WriteSheet writeSheet = EasyExcel.writerSheet(clazz.getSimpleName()).head(clazz).build();
              excelWriter.write(data, writeSheet);
          }
  
          //3 写完成
          excelWriter.finish();
      }
  }
  
  

1.6 扩展：excel备份数据库

• 步骤
  • 步骤1：添加坐标
  • 步骤2：编写封装类
  • 步骤3：编写核心类

• 步骤1：添加坐标

      <dependencies>
          <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>easyexcel</artifactId>
              <version>3.0.5</version>
          </dependency>
          <dependency>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
          </dependency>
          <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
          </dependency>
          <dependency>
              <groupId>commons-dbutils</groupId>
              <artifactId>commons-dbutils</artifactId>
              <version>1.7</version>
          </dependency>
      </dependencies>
  

• 步骤2：编写封装类

  package com.czxy.zx.demo03;
  
  import com.alibaba.excel.annotation.ExcelProperty;
  import com.alibaba.excel.annotation.write.style.ColumnWidth;
  import com.alibaba.excel.annotation.write.style.ContentRowHeight;
  import com.alibaba.excel.annotation.write.style.HeadRowHeight;
  import lombok.Data;
  
  import java.util.Date;
  import java.util.List;
  
  /**
   * 课程
   *
   */
  @Data
  @ContentRowHeight(20)
  @HeadRowHeight(20)  //行高
  @ColumnWidth(25)    //列宽
  public class Chapter {
  
      @ExcelProperty("章节ID")
      private String id;
  
      @ExcelProperty("课程ID")
      private String courseId;
  
      @ExcelProperty("章节名称")
      private String title;
  
      @ExcelProperty("显示排序")
      private Integer sort;
  
      @ExcelProperty("创建时间")
      private Date gmtCreate;
  
      @ExcelProperty("更新时间")
      private Date gmtModified;
  
  }
  
  

  package com.czxy.zx.demo03;
  
  import com.alibaba.excel.annotation.ExcelProperty;
  import com.alibaba.excel.annotation.format.DateTimeFormat;
  import com.alibaba.excel.annotation.write.style.ColumnWidth;
  import com.alibaba.excel.annotation.write.style.ContentRowHeight;
  import com.alibaba.excel.annotation.write.style.HeadRowHeight;
  import lombok.Data;
  
  import java.util.Date;
  
  /**
   * 课程
   *
   */
  @Data
  @ContentRowHeight(20)
  @HeadRowHeight(20)  //行高
  @ColumnWidth(25)    //列宽
  public class Course {
  
      @ExcelProperty("课程ID")
      private String id;
  
      @ExcelProperty("课程讲师ID")
      private String teacherId;
  
      @ExcelProperty("课程专业ID二级分类ID")
      private String subjectId;
  
      @ExcelProperty("一级分类ID")
      private String subjectParentId;
  
      @ExcelProperty("课程标题")
      private String title;
  
      @ExcelProperty("课程销售价格，设置为0则可免费观看")
      private Double price;
  
      @ExcelProperty("总课时")
      private Integer lessonNum;
  
      @ExcelProperty("课程封面图片路径")
      private String cover;
  
      @ExcelProperty("销售数量")
      private Long buyCount;
  
      @ExcelProperty("浏览数量")
      private Long viewCount;
  
      @ExcelProperty("乐观锁")
      private Long version;
  
      @ExcelProperty("视频状态 Draft未发布  Normal已发布")
      private String status;
  
      @ExcelProperty("创建时间")
      @DateTimeFormat("yyyy年MM月dd日")
      private Date gmtCreate;
  
      @ExcelProperty("更新时间")
      @DateTimeFormat("yyyy年MM月dd日")
      private Date gmtModified;
  
  
  }
  
  

• 步骤3：编写核心类

  package com.czxy.zx.demo03;
  
  import com.alibaba.excel.EasyExcel;
  import com.alibaba.excel.ExcelWriter;
  import com.alibaba.excel.write.metadata.WriteSheet;
  import com.czxy.zx.demo02.Student2;
  import org.apache.commons.dbutils.BasicRowProcessor;
  import org.apache.commons.dbutils.DbUtils;
  import org.apache.commons.dbutils.GenerousBeanProcessor;
  import org.apache.commons.dbutils.QueryRunner;
  import org.apache.commons.dbutils.handlers.BeanListHandler;
  import org.junit.Test;
  
  import java.sql.*;
  import java.util.*;
  
    /**
     * @author txt
     * @email tanxintong9968@163.com
     */
  public class TestBackdb {
  
      public Class getClassByTableName(String tableName) {
          Map<String,Class> map = new HashMap<>();
          map.put("edu_chapter", Chapter.class);
          map.put("edu_course", Course.class);
          return map.get(tableName);
      }
  
      public String getPath() {
          return this.getClass().getResource("/").getPath();
      }
  
  
      public Connection getConnection() {
          try {
              Class.forName("com.mysql.jdbc.Driver");
              String url = "jdbc:mysql://127.0.0.1:3306/zx_edu_course?useUnicode=true&characterEncoding=utf8";
              String username = "root";
              String password = "1234";
  
              Properties props =new Properties();
              props.setProperty("user", username);
              props.setProperty("password", password);
              props.setProperty("remarks", "true");               //设置可以获取remarks信息
              props.setProperty("useInformationSchema", "true");  //设置可以获取tables remarks信息
  
              return DriverManager.getConnection(url, props);
          } catch (Exception e) {
              throw new RuntimeException(e);
          }
      }
  
      @Test
      public void testDB() throws Exception {
          String file = getPath() + "db.xls";
          QueryRunner queryRunner = new QueryRunner();
          ExcelWriter excelWriter = EasyExcel.write(file).build();
          String dbName = "zx_edu_course";
          //获得连接
          Connection conn = getConnection();
          //语句执行者
          Statement st = conn.createStatement();
  
          //数据库的元数据
          DatabaseMetaData databaseMetaData = conn.getMetaData();
  
          //获得所有的数据库
          ResultSet catalogResultSet = databaseMetaData.getCatalogs();
  
          //遍历所有的数据库
          while(catalogResultSet.next()) {
              //获得数据库的名称
              String databaseName = catalogResultSet.getString(1);
              if(dbName.equals(databaseName)) {
                  //使用数据库
                  st.execute("use " + databaseName);
                  ResultSet tableResultSet = databaseMetaData.getTables(databaseName, null, null, null);
                  //遍历所有的表名
                  while(tableResultSet.next()) {
                      //表名
                      String tableName = tableResultSet.getString(3);             //TABLE_NAME
                      String tableRemarks = tableResultSet.getString("REMARKS"); //获得表的备注
                      // 通过表名获得excel处理类
                      Class excelBeanClass = getClassByTableName(tableName);
                      if(excelBeanClass != null) {
                          //获得当前表的所有数据
                          String sql = "select * from " + tableName;
                          //
                          List data = (List) queryRunner.query(conn, sql, new BeanListHandler<>(excelBeanClass, new BasicRowProcessor(new GenerousBeanProcessor()) ));
                          // 创建sheet
                          WriteSheet writeSheet = EasyExcel.writerSheet(tableRemarks != null ? tableRemarks : tableName).head(excelBeanClass).build();
                          excelWriter.write(data, writeSheet);
                      }
                  }
              }
          }
          //写入完成
          excelWriter.finish();
      }
  
  }
  
  

2. 课程科目操作

2.0 分析

2.1 环境搭建

2.1.1 数据库

CREATE DATABASE zx_edu_course;
USE zx_edu_course;

CREATE TABLE `edu_subject`  (
  `id` VARCHAR(32) NOT NULL PRIMARY KEY COMMENT '课程科目ID',
  `title` VARCHAR(10) NOT NULL COMMENT '科目名称',
  `parent_id` VARCHAR(32) NOT NULL DEFAULT '0' COMMENT '父ID',
  `sort` INT(10)  NOT NULL DEFAULT 0 COMMENT '排序字段',
  `gmt_create` DATETIME NOT NULL COMMENT '创建时间',
  `gmt_modified` DATETIME NOT NULL COMMENT '更新时间'
) COMMENT = '课程科目';

INSERT INTO `edu_subject` VALUES ('1', '云计算', '0', 0, '2020-06-26 09:41:21', '2020-02-20 23:25:58');
INSERT INTO `edu_subject` VALUES ('2', '系统/运维', '0', 0, '2020-02-20 23:29:59', '2020-02-20 23:29:59');
INSERT INTO `edu_subject` VALUES ('3', '数据库', '0', 0, '2020-02-20 23:30:13', '2020-02-20 23:30:13');
INSERT INTO `edu_subject` VALUES ('4', '服务器', '0', 0, '2020-02-20 23:30:19', '2020-02-20 23:30:19');

INSERT INTO `edu_subject` VALUES ('5', 'MySQL', '3', 1, '2020-02-20 23:30:13', '2020-02-20 23:30:13');
INSERT INTO `edu_subject` VALUES ('6', 'Oracle', '3', 2, '2020-02-20 23:30:13', '2020-02-20 23:30:13');
INSERT INTO `edu_subject` VALUES ('7', 'Tomcat', '4', 1, '2020-02-20 23:30:13', '2020-02-20 23:30:13');
INSERT INTO `edu_subject` VALUES ('8', 'Nginx ', '4', 2, '2020-02-20 23:30:13', '2020-02-20 23:30:13');

INSERT INTO `edu_subject` VALUES ('9', 'MySQL优化', '5', 1, '2020-02-20 23:30:13', '2020-02-20 23:30:13');

2.1.2 后端：环境

• 项目名：zx-service-course

• pom文件

  <dependencies>
          <!--web起步依赖-->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-web</artifactId>
          </dependency>
          <!-- nacos 客户端 -->
          <dependency>
              <groupId>com.alibaba.nacos</groupId>
              <artifactId>nacos-client</artifactId>
          </dependency>
  
          <!-- nacos 服务发现 -->
          <dependency>
              <groupId>com.alibaba.cloud</groupId>
              <artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
          </dependency>
  
          <!--swagger2-->
          <dependency>
              <groupId>io.springfox</groupId>
              <artifactId>springfox-swagger2</artifactId>
          </dependency>
          <dependency>
              <groupId>io.springfox</groupId>
              <artifactId>springfox-swagger-ui</artifactId>
          </dependency>
  
          <!-- feign 远程调用 -->
          <dependency>
              <groupId>org.springframework.cloud</groupId>
              <artifactId>spring-cloud-starter-openfeign</artifactId>
          </dependency>
  
          <!--测试-->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-test</artifactId>
          </dependency>
  
          <!-- mybatis plus-->
          <dependency>
              <groupId>com.baomidou</groupId>
              <artifactId>mybatis-plus-boot-starter</artifactId>
              <version>${mybatis.plus.version}</version>
          </dependency>
          <!-- mysql驱动 -->
          <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
          </dependency>
          <!--自定义项目-->
          <dependency>
              <groupId>com.czxy.zx</groupId>
              <artifactId>zx-common31</artifactId>
          </dependency>
          <dependency>
              <groupId>com.czxy.zx</groupId>
              <artifactId>zx-domain31</artifactId>
          </dependency>
  
          <!-- redis 启动器 -->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-data-redis</artifactId>
          </dependency>
          <!-- JavaMail 启动器 -->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-mail</artifactId>
          </dependency>
          <!-- MQ 启动器 -->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-amqp</artifactId>
          </dependency>
  
          <!-- fastjson -->
          <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>fastjson</artifactId>
          </dependency>
  
          <!--开发者工具-->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-devtools</artifactId>
              <optional>true</optional>
          </dependency>
  
          <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>easyexcel</artifactId>
              <version>3.0.5</version>
          </dependency>
  
      </dependencies>
  

• yml文件

  # 服务端口号
  server:
    port: 9020
  # 服务名
  spring:
    application:
      name: course-service
    datasource:
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/zx_edu_course?useUnicode=true&characterEncoding=utf8
      username: root
      password: 1234
      druid:    #druid 连接池配置
        initial-size: 1       #初始化连接池大小
        min-idle: 1           #最小连接数
        max-active: 20        #最大连接数
        test-on-borrow: true  #获取连接时候验证，会影响性能
    cloud:
      nacos:
        discovery:
          server-addr: 127.0.0.1:8848   #nacos服务地址
    redis:
      database: 0       #数据库索引，取值0-15，表示16个库可选择
      host: 127.0.0.1   #服务器地址
      port: 6379        #服务器连接端口号
    mail:
      host: smtp.126.com          #发送邮件服务器
      username: itcast_lt@126.com #账号
      password: 1qaz2wsx          #密码
      default-encoding: UTF-8     #默认编码时
    rabbitmq:
      host: 127.0.0.1
      port: 5672
      username: guest
      passowrd: guest
      virtualHost: /
    devtools:
      restart:
        enabled: true  #设置开启热部署
        additional-paths: src/main/java #重启目录
        exclude: WEB-INF/**
    freemarker:
      cache: false    #页面不加载缓存，修改即时生效
  
  #开启log4j打印SQL语句
  logging:
    level:
      com:
        czxy:
          mapper: debug
  
  # mp日志打印
  mybatis-plus:
    configuration:
      log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  
  
  

• 启动类

  package com.czxy.zx;
  
  import org.springframework.boot.SpringApplication;
  import org.springframework.boot.autoconfigure.SpringBootApplication;
  import org.springframework.cloud.client.discovery.EnableDiscoveryClient;
  import org.springframework.cloud.openfeign.EnableFeignClients;
  
  
  /**
   * @author txt
   * @email tanxintong9968@163.com
   */
  @SpringBootApplication
  @EnableDiscoveryClient
  @EnableFeignClients
  public class CourseServiceApplication {
      public static void main(String[] args) {
          SpringApplication.run(CourseServiceApplication.class,args);
      }
  }
  
  

• 拷贝配置

  

• 配置类 – MyBatisPlusConfig

package com.czxy.zx.course.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Component;

@Component
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return mybatisPlusInterceptor;
    }
}

• 配置类 – Swagger2ConfigurationV3

package com.czxy.zx.course.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.*;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spi.service.contexts.SecurityContext;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

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

/**
 * Swagger2 配置类，
 * 访问路径：swagger-ui.html
 * 自动注册：
 *     位置：resources/META-INF/spring.factories
 *     内容：
 *        org.springframework.boot.autoconfigure.EnableAutoConfiguration=\
 *          com.czxy.changgou4.config.Swagger2Configuration
 */
@Configuration
@EnableSwagger2
public class Swagger2ConfigurationV3 {

    @Bean
    public Docket createRestApi() {
        // 1 确定文档Swagger版本
        Docket docket = new Docket(DocumentationType.SWAGGER_2);
        // 2 设置 api基本信息
        docket.apiInfo(apiInfo());
        // 3 设置自定义加载路径
        docket = docket.select()
                .apis(RequestHandlerSelectors.basePackage("com.czxy"))
                .paths(PathSelectors.any())
                .build();
        //4 设置权限
        docket.securitySchemes(securitySchemes());
        docket.securityContexts(securityContexts());

        return docket;
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("API")
                .description("基于swagger接口文档")
                .contact(new Contact("梁桐","http://www.javaliang.com","liangtong@itcast.cn"))
                .version("1.0")
                .build();
    }

    private List<ApiKey> securitySchemes() {
        List<ApiKey> list = new ArrayList<>();
        // name 为参数名  keyname是页面传值显示的 keyname， name在swagger鉴权中使用
        list.add(new ApiKey("Authorization", "X-Token", "header"));
        return list;
    }

    private List<SecurityContext> securityContexts() {
        List<SecurityContext> list = new ArrayList<>();
        list.add(SecurityContext.builder()
                .securityReferences(defaultAuth())
                .forPaths(PathSelectors.regex("^(?!auth).*$"))
                .build());
        return list;
    }

    private List<SecurityReference> defaultAuth() {
        AuthorizationScope authorizationScope = new AuthorizationScope("global", "accessEverything");
        AuthorizationScope[] authorizationScopes = new AuthorizationScope[1];
        authorizationScopes[0] = authorizationScope;
        List<SecurityReference> list = new ArrayList();
        list.add(new SecurityReference("Authorization", authorizationScopes));
        return list;
    }

}

2.1.3 后端：基本模块

• 创建JavaBean

  package com.czxy.zx.domain;
  
  import com.baomidou.mybatisplus.annotation.IdType;
  import com.baomidou.mybatisplus.annotation.TableField;
  import com.baomidou.mybatisplus.annotation.TableId;
  import com.baomidou.mybatisplus.annotation.TableName;
  import com.fasterxml.jackson.annotation.JsonFormat;
  import lombok.Data;
  import org.springframework.format.annotation.DateTimeFormat;
  
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.List;
  
  /**
   * 课程科目(EduSubject)表实体类
   *
   * @author txt
   */
  @Data
  @TableName("edu_subject")
  public class EduSubject{
      @TableId(type = IdType.ASSIGN_UUID)
      //课程科目ID
      private String id;
      //科目名称
      private String title;
      //父ID
      private String parentId;
      //排序字段
      private Integer sort;
      //创建时间
      @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
      private Date gmtCreate;
      //更新时间
      @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
      private Date gmtModified;
  
      @TableField(exist = false)
      @JsonInclude(JsonInclude.Include.NON_EMPTY)     //生成json数据，不包含空元素
      private List<EduSubject> children = new ArrayList<>();
  
  }
  

• 创建mapper

package com.czxy.zx.course.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.czxy.zx.domain.EduSubject;
import org.apache.ibatis.annotations.Mapper;

/**
 * @author txt
 * @email tanxintong9968@163.com
 */
@Mapper
public interface EduSubjectMapper extends BaseMapper<EduSubject> {
}

• 创建service

  

  • 接口

    package com.czxy.zx.course.service;
    
    import com.baomidou.mybatisplus.extension.service.IService;
    import com.czxy.zx.domain.EduSubject;
    
      /**
       * @author txt
       * @email tanxintong9968@163.com
       */
    public interface EduSubjectService extends IService<EduSubject> {
    }
    
    

  • 实现类

    package com.czxy.zx.course.service.impl;
    
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import com.czxy.zx.course.mapper.EduSubjectMapper;
    import com.czxy.zx.course.service.EduSubjectService;
    import com.czxy.zx.domain.EduSubject;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
      /**
       * @author txt
       * @email tanxintong9968@163.com
       */
    @Service
    @Transactional
    public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
    }
    
    

• 创建controller

  

  package com.czxy.zx.course.controller;
  
  import com.czxy.zx.course.service.EduSubjectService;
  import org.springframework.web.bind.annotation.RequestMapping;
  import org.springframework.web.bind.annotation.RestController;
  
  import javax.annotation.Resource;
  
  /**
   * @author txt
   * @email tanxintong9968@163.com
   */
  @RestController
  @RequestMapping("/subject")
  public class EduSubjectController {
  
      @Resource
      private EduSubjectService eduSubjectService;
  
      
  }
  
  
  

• 配置类

  

• 配置类 – MyBatisPlusConfig

package com.czxy.zx.course.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Component;

@Component
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return mybatisPlusInterceptor;
    }
}

• 配置类 – Swagger2ConfigurationV3

package com.czxy.zx.course.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.*;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spi.service.contexts.SecurityContext;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

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

/**
 * Swagger2 配置类，
 * 访问路径：swagger-ui.html
 * 自动注册：
 *     位置：resources/META-INF/spring.factories
 *     内容：
 *        org.springframework.boot.autoconfigure.EnableAutoConfiguration=\
 *          com.czxy.changgou4.config.Swagger2Configuration
 */
@Configuration
@EnableSwagger2
public class Swagger2ConfigurationV3 {

    @Bean
    public Docket createRestApi() {
        // 1 确定文档Swagger版本
        Docket docket = new Docket(DocumentationType.SWAGGER_2);
        // 2 设置 api基本信息
        docket.apiInfo(apiInfo());
        // 3 设置自定义加载路径
        docket = docket.select()
                .apis(RequestHandlerSelectors.basePackage("com.czxy"))
                .paths(PathSelectors.any())
                .build();
        //4 设置权限
        docket.securitySchemes(securitySchemes());
        docket.securityContexts(securityContexts());

        return docket;
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("API")
                .description("基于swagger接口文档")
                .contact(new Contact("梁桐","http://www.javaliang.com","liangtong@itcast.cn"))
                .version("1.0")
                .build();
    }

    private List<ApiKey> securitySchemes() {
        List<ApiKey> list = new ArrayList<>();
        // name 为参数名  keyname是页面传值显示的 keyname， name在swagger鉴权中使用
        list.add(new ApiKey("Authorization", "X-Token", "header"));
        return list;
    }

    private List<SecurityContext> securityContexts() {
        List<SecurityContext> list = new ArrayList<>();
        list.add(SecurityContext.builder()
                .securityReferences(defaultAuth())
                .forPaths(PathSelectors.regex("^(?!auth).*$"))
                .build());
        return list;
    }

    private List<SecurityReference> defaultAuth() {
        AuthorizationScope authorizationScope = new AuthorizationScope("global", "accessEverything");
        AuthorizationScope[] authorizationScopes = new AuthorizationScope[1];
        authorizationScopes[0] = authorizationScope;
        List<SecurityReference> list = new ArrayList();
        list.add(new SecurityReference("Authorization", authorizationScopes));
        return list;
    }

}

2.1.4 前端

• 创建路由模块

  

  /** When your routing table is too long, you can split it into small modules **/
  
  import Layout from '@/layout'
  
  const courseRouter = {
    path: '/course',           // 当前模块前缀路径，必须以/开头
    component: Layout,          // 采用布局组件显示当前模块【默认】
    redirect: '/course/subjectList',  // “教师管理”默认显示路由
    name: '课程管理',            // 路由名称
    meta: {
      title: '课程管理',         // 一级菜单名称，children.length==0 隐藏
      icon: 'table'             // 一级菜单图标，children.length==0 隐藏
    },
    children: [
      {
        path: 'subjectList',
        component: () => import('@/views/edu/course/subjectList.vue'),
        name: '科目列表',
        meta: { title: '科目列表', icon: 'list' }   //二级菜单名称
      }
    ]
  }
  export default courseRouter
  
  

• 创建 subjectList.vue 页面

  

• 配置路由

  

2.2 查询所有

• 以树形table展示数据

2.2.1 后端实现

• 修改 EduSubjectController

package com.czxy.zx.course.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.czxy.zx.course.service.EduSubjectService;
import com.czxy.zx.domain.EduSubject;
import com.czxy.zx.vo.BaseResult;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author txt
 * @email tanxintong9968@163.com
 */
@RestController
@RequestMapping("/subject")
public class EduSubjectController {

    @Resource
    private EduSubjectService eduSubjectService;

    @GetMapping
    public BaseResult findAll() {
        //1 查询所有
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.orderByAsc("parent_id");
        List<EduSubject> list = eduSubjectService.list(queryWrapper);

        //2 处理父子关系
        List<EduSubject> resultList = new ArrayList<>();
        Map<String,EduSubject> cache = new HashMap<>();
        list.forEach(eduSubject -> {
            // 获得父
            EduSubject parentEduSubject = cache.get(eduSubject.getParentId());
            // 如果没有父表示第一层，如果有父追加
            if(parentEduSubject != null) {
                // 如果有孩子，判断父对象的集合
                List<EduSubject> temp = parentEduSubject.getChildren();
                if(temp == null) {
                    parentEduSubject.setChildren(new ArrayList<>());
                }
                // 将孩子添加到父对象的集合中
                parentEduSubject.getChildren().add(eduSubject);
            } else {
                resultList.add(eduSubject);
            }
            // 缓存当前
            cache.put(eduSubject.getId(),eduSubject);

        });

        return BaseResult.ok("查询成功", resultList);
    }

}

2.2.2 前端接口

import axios from '@/utils/request'

// 查询所有课程科目
export function findAllSub() {
  return axios.get('/course-service/subject');
}

2.2.3 前端实现

• 修改 @/views/edu/course/subjectList.vue

  

<template>
  <div>
    <el-table
      v-loading="listLoading"
      :data="subjectList"
      border
      fit
      highlight-current-row
      style="width: 100%;"
      row-key="title"
      :tree-props="{children: 'children'}"
    >
      <el-table-column label="科目名称" prop="title" align="center" width="200">
      </el-table-column>
      <el-table-column label="排序" prop="sort" width="80px" min-width="50px">
      </el-table-column>
      <el-table-column label="添加时间" width="200px" align="center">
        <template slot-scope="{row}">
          <span>{{ row.gmtCreate | parseTime('{y}-{m}-{d} {h}:{i}') }}</span>
        </template>
      </el-table-column>
      <el-table-column label="更新时间" width="200px" align="center">
        <template slot-scope="{row}">
          <span>{{ row.gmtModified | parseTime('{y}-{m}-{d} {h}:{i}') }}</span>
        </template>
      </el-table-column>
      <el-table-column label="操作" align="center" class-name="small-padding fixed-width">
        <template slot-scope="{row,$index}">
          <el-button type="primary" size="mini">
            修改
          </el-button>
          <el-button v-if="row.status!='deleted'" size="mini" type="danger" >
            删除
          </el-button>
        </template>
      </el-table-column>
    </el-table>
  </div>
</template>

<script>
import { findAllSub } from '@/api/edu/course'

export default {
  data() {
    return {
      subjectList: [] ,
      listLoading: false,
    }
  },
  methods: {
    async findAllSubject() {  // 查询所有
      this.listLoading = true
      let { data } = await findAllSub()
      this.subjectList = data
      this.listLoading = false
    }
  },
  mounted() {
    this.findAllSubject()
  },
}
</script>

<style>

</style>

2.3 导入科目

2.3.1 需求

2.3.2 前端

• 使用 upload组件

      <!-- 文件上传 -->
      <el-upload
        class="upload-demo"
        :action="updateUrl"
        :limit="1"
        :on-exceed="handleExceed"
        :before-upload="beforeUpload"
        :on-remove="handleRemove"
        :on-success="handleSuccess"
        :file-list="fileList">
        <el-button size="small" type="primary">点击上传</el-button>
        <div slot="tip" class="el-upload__tip">只能上传xls或xlsx文件，且不超过500kb</div>
      </el-upload>
  

• 声明变量

    data() {
      return {
        fileList: [],     //上传文件列表
        updateUrl: process.env.VUE_APP_BASE_API + '/course-service/subject/upload',   //上传路径
      }
    },
  

• 编写处理函数

  handleExceed(files, fileList) {   // 超出个数限制
        this.$message.warning(`当前选择1个文件`);
      },
      beforeUpload(file) {      // 上传文件之前
        // 是否是 xlsx 文件（2003）
        const isXlsx = file.type === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        // 是否是 xls 文件（2010）
        const isXls = file.type === 'application/vnd.ms-excel'
        const isLt2M = file.size / 1024 / 1024 < 2;
  
        if (!isXlsx && !isXls) {
          this.$message.error('上传文件不是excel文件!');
        }
        if (!isLt2M) {
          this.$message.error('上传文件大小不能超过 2MB!');
        }
        return (isXlsx || isXls) && isLt2M;
      },
      handleRemove(file, fileList) {  // 文件列表移除文件
        console.log(file, fileList);
      },
      handleSuccess(response, file, fileList) { // 文件上传成功
        // 成功提示
        this.$message.success(response.message)
        // 刷新
        this.findAllSubject()
      }
  

2.3.2 前端：完整版

<template>
  <div>
    <el-table
      v-loading="listLoading"
      :data="subjectList"
      border
      fit
      highlight-current-row
      style="width: 100%;"
      row-key="title"
      :tree-props="{children: 'children'}"
    >
      <el-table-column label="科目名称" prop="title" align="left" width="200">
      </el-table-column>
      <el-table-column label="排序" prop="sort" width="80px" min-width="50px">
      </el-table-column>
      <el-table-column label="添加时间" width="200px" align="center">
        <template slot-scope="{row}">
          <span>{{ row.gmtCreate | parseTime('{y}-{m}-{d} {h}:{i}') }}</span>
        </template>
      </el-table-column>
      <el-table-column label="更新时间" width="200px" align="center">
        <template slot-scope="{row}">
          <span>{{ row.gmtModified | parseTime('{y}-{m}-{d} {h}:{i}') }}</span>
        </template>
      </el-table-column>
      <el-table-column label="操作" align="center" class-name="small-padding fixed-width">
        <template slot-scope="{row}">
          <el-button type="primary" size="mini">
            修改
          </el-button>
          <el-button v-if="row.status!='deleted'" size="mini" type="danger" >
            删除
          </el-button>
        </template>
      </el-table-column>
    </el-table>

    <!-- 文件上传 -->
    <el-upload
      class="upload-demo"
      :action="updateUrl"
      :limit="1"
      :on-exceed="handleExceed"
      :before-upload="beforeUpload"
      :on-remove="handleRemove"
      :on-success="handleSuccess"
      :file-list="fileList">
      <el-button size="small" type="primary">点击上传</el-button>
      <div slot="tip" class="el-upload__tip">只能上传xls或xlsx文件，且不超过500kb</div>
    </el-upload>
  </div>
</template>

<script>
import { findAllSub } from '@/api/edu/course'

export default {
  data() {
    return {
      subjectList: [] ,
      listLoading: false,
      fileList: [],     //上传文件列表
      updateUrl: process.env.VUE_APP_BASE_API + '/course-service/subject/upload',   //上传路径
    }
  },
  methods: {
    async findAllSubject() {  // 查询所有
      this.listLoading = true
      let { data } = await findAllSub()
      this.subjectList = data
      this.listLoading = false
    },
    handleExceed(files, fileList) {   // 超出个数限制
      this.$message.warning(`当前选择1个文件`);
    },
    beforeUpload(file) {      // 上传文件之前
      // 是否是 xlsx 文件（2003）
      const isXlsx = file.type === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
      // 是否是 xls 文件（2010）
      const isXls = file.type === 'application/vnd.ms-excel'
      const isLt2M = file.size / 1024 / 1024 < 2;

      if (!isXlsx && !isXls) {
        this.$message.error('上传文件不是excel文件!');
      }
      if (!isLt2M) {
        this.$message.error('上传文件大小不能超过 2MB!');
      }
      return (isXlsx || isXls) && isLt2M;
    },
    handleRemove(file, fileList) {  // 文件列表移除文件
      console.log(file, fileList);
    },
    handleSuccess(response, file, fileList) { // 文件上传成功
      // 成功提示
      this.$message.success(response.message)
      // 刷新
      this.findAllSubject()
    }
  },
  mounted() {
    this.findAllSubject()
  },
}
</script>

<style>

</style>

2.3.4 后端

1） 完善JavaBean

package com.czxy.zx.domain;

import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

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

/**
 * 课程科目(EduSubject)表实体类
 *
 * @author txt
 */
@Data
@TableName("edu_subject")
public class EduSubject{
    @TableId(type = IdType.ASSIGN_UUID)
    //课程科目ID
    private String id;
    //科目名称
    private String title;
    //父ID
    private String parentId;
    //排序字段
    private Integer sort;
    //创建时间
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @TableField(fill = FieldFill.INSERT)
    private Date gmtCreate;
    //更新时间
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @TableField(fill = FieldFill.INSERT)
    private Date gmtModified;

    @TableField(exist = false)
    private List<EduSubject> children = new ArrayList<>();

}

2）填充数据处理类

package com.czxy.zx.course.handler;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.util.Date;

/**
 * @author txt
 * @email tanxintong9968@163.com
 */
@Component
public class SubjectMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        // 创建时间
        this.setFieldValByName("gmtCreate",new Date(), metaObject);
        // 修改时间
        this.setFieldValByName("gmtModified",new Date() , metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        // 修改时，填充的内容
        this.setFieldValByName("gmtModified",new Date() , metaObject);
    }
}

3）service：通过title查询

• 接口

  package com.czxy.zx.course.service;
  
  import com.baomidou.mybatisplus.extension.service.IService;
  import com.czxy.zx.domain.EduSubject;
  
  /**
   * @author txt
   * @email tanxintong9968@163.com
   */
  public interface EduSubjectService extends IService<EduSubject> {
      /**
       * 通过title查询
       * @param title
       * @return
       */
      EduSubject findByTitle(String title);
  }
  
  

• 实现类

  package com.czxy.zx.course.service.impl;
  
  import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
  import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
  import com.czxy.zx.course.mapper.EduSubjectMapper;
  import com.czxy.zx.course.service.EduSubjectService;
  import com.czxy.zx.domain.EduSubject;
  import org.springframework.stereotype.Service;
  import org.springframework.transaction.annotation.Transactional;
  
  /**
   * @author txt
   * @email tanxintong9968@163.com
   */
  @Service
  @Transactional
  public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
      @Override
      public EduSubject findByTitle(String title) {
          QueryWrapper queryWrapper = new QueryWrapper();
          queryWrapper.eq("title", title);
  
          EduSubject eduSubject = baseMapper.selectOne(queryWrapper);
          return eduSubject;
      }
  }
  
  

4）controller：上传

 /**
     * 文件上传
     * @param file
     * @return
     */
    @PostMapping("/upload")
    public BaseResult upload(MultipartFile file) {
        try {
            // 解析excel
            EasyExcel.read(file.getInputStream(), UploadSubjectVo.class, eduSubjectListener).sheet(0).doRead();

            return BaseResult.ok("上传成功");
        } catch (IOException e) {
            return BaseResult.error("上传失败");
        }
    }

5）excel内容封装类

package com.czxy.zx.course.upload;

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

/**
 * @author txt
 * @email tanxintong9968@163.com
 */
@Data
public class UploadSubjectVo {

    @ExcelProperty("一级分类")
    private String oneLevel;

    @ExcelProperty("二级分类")
    private String twoLevel;


}

6）上传内容处理类

package com.czxy.zx.course.upload;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.czxy.zx.course.service.EduSubjectService;
import com.czxy.zx.domain.EduSubject;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;

/**
 * @author txt
 * @email tanxintong9968@163.com
 */
@Component
public class EduSubjectListener extends AnalysisEventListener<UploadSubjectVo> {

    @Resource
    private EduSubjectService eduSubjectService;

    @Override
    public void invoke(UploadSubjectVo uploadSubjectVo, AnalysisContext analysisContext) {
        // 1. 处理一级
        // 1.1 查询一级
        EduSubject oneSubject = eduSubjectService.findByTitle(uploadSubjectVo.getOneLevel());
        // 1.2 保存一级
        if(oneSubject == null) {
            oneSubject = new EduSubject();
            oneSubject.setTitle(uploadSubjectVo.getOneLevel());
            oneSubject.setSort(0);
            oneSubject.setParentId("0");        // 一级默认0
            eduSubjectService.save(oneSubject);
        }

        // 2. 处理二级
        // 2.1 查询二级
        EduSubject twoSubject = eduSubjectService.findByTitle(uploadSubjectVo.getTwoLevel());
        // 2.2 保存二级
        if(twoSubject == null) {
            twoSubject = new EduSubject();
            twoSubject.setTitle(uploadSubjectVo.getTwoLevel());
            twoSubject.setSort(0);
            twoSubject.setParentId(oneSubject.getId()); //二级的父ID为一级的ID
            eduSubjectService.save(twoSubject);
        }

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

3.POI入门

3.1 POI 概述

3.1.1 简介

​ Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

功能	描述
HSSFWorkBook	提供读写Microsoft Excel格式档案的功能，xls文档
XSSFWorkBook	提供读写Microsoft Excel OOXML格式档案的功能，xlsx文件
HWPF	提供读写Microsoft Word格式档案的功能
HSLF	提供读写Microsoft PowerPoint格式档案的功能
HDGF	提供读写Microsoft Visio格式档案的功能

3.1.2 官网

http://poi.apache.org/

3.2 入门案例

3.2.1 环境搭建

• 创建项目：

• 修改pom

  <dependencies>
          <!--xls-->
          <dependency>
              <groupId>org.apache.poi</groupId>
              <artifactId>poi</artifactId>
              <version>3.9</version>
          </dependency>
          <!--xlsx-->
          <dependency>
              <groupId>org.apache.poi</groupId>
              <artifactId>poi-ooxml</artifactId>
              <version>3.9</version>
          </dependency>
          <!--日期格式化工具-->
          <dependency>
              <groupId>joda-time</groupId>
              <artifactId>joda-time</artifactId>
              <version>2.10.1</version>
          </dependency>
          <!--test-->
          <dependency>
              <groupId>junit</groupId>
              <artifactId>junit</artifactId>
              <version>4.12</version>
          </dependency>
      </dependencies>
  

3.2.2 xls文件写操作

• excel2003 文件扩展名为 xls
• 名词：
  • 工作簿：一个excel文件，就是一个工作簿
  • 工作表：一个工作簿中，可以所有多个工作表Sheet
  • 行：每一个工作表，包含多行row
  • 单元格：每行有多个单元格Cell组成。

package com.zx.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;

public class Excel03Test {

    @Test
    public void testWrite03() throws IOException {

        // 创建新的Excel 工作簿
        Workbook workbook = new HSSFWorkbook();

        // 在Excel工作簿中建一工作表，其名为缺省值 Sheet0
        //Sheet sheet = workbook.createSheet();

        // 如要新建一名为"信息统计"的工作表，其语句为：
        Sheet sheet = workbook.createSheet("信息统计");

        // 创建行（row 1）
        Row row1 = sheet.createRow(0);

        // 创建单元格（col 1-1）
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日人数");

        // 创建单元格（col 1-2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(666);

        // 创建行（row 2）
        Row row2 = sheet.createRow(1);

        // 创建单元格（col 2-1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        //创建单元格（第三列）
        Cell cell22 = row2.createCell(1);
        String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(dateTime);

        // 新建一输出文件流（注意：要先创建文件夹）
        FileOutputStream out = new FileOutputStream("d://zx/a.xls");
        // 把相应的Excel 工作簿存盘
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();

        System.out.println("文件生成成功");
    }
}

3.2.3 xlsx 文件写操作

excel2007+ 文件扩展名为 xlsx

package com.zx.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;

public class Excel07Test {

    @Test
    public void testWrite07() throws IOException {

        // 创建新的Excel 工作簿
        Workbook workbook = new XSSFWorkbook();

        // 在Excel工作簿中建一工作表，其名为缺省值 Sheet0
        //Sheet sheet = workbook.createSheet();

        // 如要新建一名为"信息统计"的工作表，其语句为：
        Sheet sheet = workbook.createSheet("信息统计");

        // 创建行（row 1）
        Row row1 = sheet.createRow(0);

        // 创建单元格（col 1-1）
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日人数");

        // 创建单元格（col 1-2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(666);

        // 创建行（row 2）
        Row row2 = sheet.createRow(1);

        // 创建单元格（col 2-1）
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");

        //创建单元格（第三列）
        Cell cell22 = row2.createCell(1);
        String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(dateTime);

        // 新建一输出文件流（注意：要先创建文件夹）
        FileOutputStream out = new FileOutputStream("d://zx/b.xlsx");
        // 把相应的Excel 工作簿存盘
        workbook.write(out);
        // 操作结束，关闭文件
        out.close();

        System.out.println("文件生成成功");
    }
}

3.2.4 xls 文件读操作

// xls 2003 文件读操作
    @Test
    public void testXlsRead() throws Exception {
        //1 确定文件流
        FileInputStream is = new FileInputStream("D:\\xml\\user.xls");
        //2 开始读
        // 2.1 工作簿
        HSSFWorkbook workbook = new HSSFWorkbook(is);
        // 2.2 工作表
        HSSFSheet sheet = workbook.getSheet("用户表");
        int rowStart = sheet.getFirstRowNum();      //第一行索引号（从0开始）
        int rowEnd = sheet.getLastRowNum();         //最后一行的索引号（从0开始）
        // 2.3 行
        for(int i = rowStart ; i <= rowEnd ; i ++) {
            HSSFRow row = sheet.getRow(i);
            int cellStart = row.getFirstCellNum();      //第一列的索引号（从0开始）
            int cellEnd = row.getLastCellNum() ;        //最后一列的编号（从1开始）
            // 2.4 列
            for(int c = cellStart; c < cellEnd ; c++) {
                HSSFCell cell = row.getCell(c);
//                System.out.println(cell.getCellType());
                if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    // 字符串
                    System.out.print(row.getCell(c).getStringCellValue() + ", ");
                }
                if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    // 字符串
                    System.out.print(row.getCell(c).getNumericCellValue() + ", ");
                }
            }
            System.out.println();
        }
        // 3 释放资源
        is.close();
    }

3.2.5 xlsx 文件读操作

 // xlsx 2007 文件读操作
    @Test
    public void testXlsxRead() throws Exception {
        //1 确定文件流
        FileInputStream is = new FileInputStream("D:\\xml\\user.xlsx");
        //2 开始读
        // 2.1 工作簿
        Workbook workbook = new XSSFWorkbook(is);
        // 2.2 工作表
        Sheet sheet = workbook.getSheet("用户表");
        int rowStart = sheet.getFirstRowNum();      //第一行索引号（从0开始）
        int rowEnd = sheet.getLastRowNum();         //最后一行的索引号（从0开始）
        // 2.3 行
        for(int i = rowStart ; i <= rowEnd ; i ++) {
            Row row = sheet.getRow(i);
            int cellStart = row.getFirstCellNum();      //第一列的索引号（从0开始）
            int cellEnd = row.getLastCellNum() ;        //最后一列的编号（从1开始）
            // 2.4 列
            for(int c = cellStart; c < cellEnd ; c++) {
                Cell cell = row.getCell(c);
//                System.out.println(cell.getCellType());
                if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    // 字符串
                    System.out.print(row.getCell(c).getStringCellValue() + ", ");
                }
                if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    // 字符串
                    System.out.print(row.getCell(c).getNumericCellValue() + ", ");
                }
            }
            System.out.println();
        }
        // 3 释放资源
        is.close();
    }

3.2.6 读取不同类型的数据

@Test
public void testRead07() throws Exception{

    InputStream is = new FileInputStream("d:/0704.xlsx");

    Workbook workbook = new XSSFWorkbook(is);
    Sheet sheet = workbook.getSheetAt(0);

    // 读取第一行第一列
    Row row = sheet.getRow(0);
    Cell cell1 = row.getCell(0);
    Cell cell2 = row.getCell(1);


    // 输出单元内容
    System.out.println(cell1.getStringCellValue());
    System.out.println(cell2.getNumericCellValue());

    // 操作结束，关闭文件
    is.close();
}

end

好啦，以上就是本期全部内容，能看到这里的人呀，都是能人。

十年修得同船渡，大家一起点关注。

我是话里人，感谢各位【能人】的：点赞、收藏和评论，我们下期见！

各位能人们的支持就是话里人前进的巨大动力~

注：如果本篇Blog有任何错误和建议，欢迎能人们留言！