【在线教育】课程科目入门
文章目录
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 桐叔 * @email liangtong@itcast.cn */ @SpringBootApplication @EnableDiscoveryClient @EnableFeignClients public class CourseServiceApplication { public static void main(String[] args) { SpringApplication.run(CourseServiceApplication.class,args); } } -
拷贝配置
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 桐叔 */ @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 桐叔 * @email liangtong@itcast.cn */ @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 桐叔 * @email liangtong@itcast.cn */ 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 桐叔 * @email liangtong@itcast.cn */ @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 桐叔 * @email liangtong@itcast.cn */ @RestController @RequestMapping("/subject") public class EduSubjectController { @Resource private EduSubjectService eduSubjectService; } -
配置类
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 桐叔
* @email liangtong@itcast.cn
*/
@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 桐叔
*/
@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 桐叔
* @email liangtong@itcast.cn
*/
@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 桐叔 * @email liangtong@itcast.cn */ 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 桐叔 * @email liangtong@itcast.cn */ @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 桐叔
* @email liangtong@itcast.cn
*/
@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 桐叔
* @email liangtong@itcast.cn
*/
@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) {
}
}
