AI 助理
备案控制台
开发者社区 数据库 文章 正文

学成在线----day2

2023-11-21 84
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 学成在线----day2

1、mybatis-plus分页

pom:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <artifactId>xuecheng-plus-content</artifactId>
        <groupId>com.xuecheng</groupId>
        <version>0.0.1-SNAPSHOT</version>
    </parent>
    <artifactId>xuecheng-plus-content-service</artifactId>
   <dependencies>
       <dependency>
           <groupId>com.xuecheng</groupId>
           <artifactId>xuecheng-plus-content-model</artifactId>
           <version>0.0.1-SNAPSHOT</version>
       </dependency>
       <!-- MySQL 驱动 -->
       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <scope>runtime</scope>
       </dependency>
       <!-- mybatis plus的依赖 -->
       <dependency>
           <groupId>com.baomidou</groupId>
           <artifactId>mybatis-plus-boot-starter</artifactId>
       </dependency>
       <dependency>
           <groupId>org.springframework.cloud</groupId>
           <artifactId>spring-cloud-context</artifactId>
       </dependency>
       <!-- Spring Boot 集成 Junit -->
       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-test</artifactId>
           <scope>test</scope>`在这里插入代码片`
       </dependency>
       <!-- 排除 Spring Boot 依赖的日志包冲突 -->
       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter</artifactId>
           <exclusions>
               <exclusion>
                   <groupId>org.springframework.boot</groupId>
                   <artifactId>spring-boot-starter-logging</artifactId>
               </exclusion>
           </exclusions>
       </dependency>
       <!-- Spring Boot 集成 log4j2 -->
       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-log4j2</artifactId>
       </dependency>
   </dependencies>
</project>

配置类：

package com.xuecheng.content.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
@MapperScan("com.xuecheng.content.mapper")
public class MybatisPlusConfig {
 /**
  * 定义分页拦截器
  */
 @Bean
 public MybatisPlusInterceptor mybatisPlusInterceptor() {
  MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
  interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
  return interceptor;
 }
}

分页插件的原理：

首先分页参数放到ThreadLocal中，拦截执行的sql，根据数据库类型添加对应的分页语句重写sql，例如：(select * from table where a) 转换为 (select count(*) from table where a)和(select * from table where a limit ,)

计算出了total总条数、pageNum当前第几页、pageSize每页大小和当前页的数据，是否为首页，是否为尾页，总页数等。

2、课程查询DAO接口

package com.xuecheng.content;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xuecheng.base.model.PageParams;
import com.xuecheng.base.model.PageResult;
import com.xuecheng.content.mapper.CourseBaseMapper;
import com.xuecheng.content.model.dto.QueryCourseParamsDto;
import com.xuecheng.content.model.po.CourseBase;
import org.apache.commons.lang3.StringUtils;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class CourseBaseMapperTests {
    @Autowired
    CourseBaseMapper courseBaseMapper;
    @Test
    public void testCourseBaseMapper() {
        CourseBase courseBase = courseBaseMapper.selectById(18);
        Assertions.assertNotNull(courseBase);
        //详细进行分页查询的单元测试
        //查询条件
        QueryCourseParamsDto courseParamsDto = new QueryCourseParamsDto();
        courseParamsDto.setCourseName("java");//课程名称查询条件
        //拼装查询条件
        LambdaQueryWrapper<CourseBase> queryWrapper = new LambdaQueryWrapper<>();
        //根据名称模糊查询,在sql中拼接 course_base.name like '%值%'
        queryWrapper.like(StringUtils.isNotEmpty(courseParamsDto.getCourseName()),CourseBase::getName,courseParamsDto.getCourseName());
        //根据课程审核状态查询 course_base.audit_status = ?
        queryWrapper.eq(StringUtils.isNotEmpty(courseParamsDto.getAuditStatus()), CourseBase::getAuditStatus,courseParamsDto.getAuditStatus());
        //根据课程的发布状态查询
        queryWrapper.eq(StringUtils.isNotEmpty(courseParamsDto.getPublishStatus()),CourseBase::getStatus,courseParamsDto.getPublishStatus());
        //todo:按课程发布状态查询
        //分页参数对象
        PageParams pageParams = new PageParams();
        pageParams.setPageNo(1L);
        pageParams.setPageSize(2L);
        //创建page分页参数对象，参数：当前页码，每页记录数
        Page<CourseBase> page = new Page<>(pageParams.getPageNo(), pageParams.getPageSize());
        //开始进行分页查询
        Page<CourseBase> pageResult = courseBaseMapper.selectPage(page, queryWrapper);
        //数据列表
        List<CourseBase> items = pageResult.getRecords();
        //总记录数
        long total = pageResult.getTotal();
        //List<T> items, long counts, long page, long pageSize
        PageResult<CourseBase> courseBasePageResult = new PageResult<CourseBase>(items,total,pageParams.getPageNo(), pageParams.getPageSize());
        System.out.println(courseBasePageResult);
    }
}

3、数据字典

为什么要有数据字典呢，是因为课程的审核状态如果要改的话（比如审核为通过改成审核通过），那么将会改动整张数据库表，如果我们建立一个数据字典，比如0代表未提交，1代表已提交，那么后续想改动只需要改动数据字典表即可。由于数据字典属于系统模块，不属于内容管理模块，所以不能和内容管理数据库一起。

4、课程查询-service

package com.xuecheng.content.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xuecheng.base.exception.XueChengPlusException;
import com.xuecheng.base.model.PageParams;
import com.xuecheng.base.model.PageResult;
import com.xuecheng.content.mapper.CourseBaseMapper;
import com.xuecheng.content.mapper.CourseCategoryMapper;
import com.xuecheng.content.mapper.CourseMarketMapper;
import com.xuecheng.content.model.dto.AddCourseDto;
import com.xuecheng.content.model.dto.CourseBaseInfoDto;
import com.xuecheng.content.model.dto.QueryCourseParamsDto;
import com.xuecheng.content.model.po.CourseBase;
import com.xuecheng.content.model.po.CourseMarket;
import com.xuecheng.content.service.CourseBaseInfoService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resources;
import java.time.LocalDateTime;
import java.util.List;
@Slf4j
@Service
public class CourseBaseInfoServiceImpl implements CourseBaseInfoService {
    @Autowired
    CourseBaseMapper courseBaseMapper;
    @Autowired
    CourseMarketMapper courseMarketMapper;
    @Autowired
    CourseCategoryMapper courseCategoryMapper;
    @Override
    public PageResult<CourseBase> queryCourseBaseList(PageParams pageParams, QueryCourseParamsDto courseParamsDto) {
        //拼装查询条件
        LambdaQueryWrapper<CourseBase> queryWrapper = new LambdaQueryWrapper<>();
        //根据名称模糊查询,在sql中拼接 course_base.name like '%值%'
        queryWrapper.like(StringUtils.isNotEmpty(courseParamsDto.getCourseName()),CourseBase::getName,courseParamsDto.getCourseName());
        //根据课程审核状态查询 course_base.audit_status = ?
        queryWrapper.eq(StringUtils.isNotEmpty(courseParamsDto.getAuditStatus()), CourseBase::getAuditStatus,courseParamsDto.getAuditStatus());
        //按课程发布状态查询
        queryWrapper.eq((StringUtils.isNotEmpty(courseParamsDto.getPublishStatus())),CourseBase::getStatus,courseParamsDto.getPublishStatus());
        //创建page分页参数对象，参数：当前页码，每页记录数
        Page<CourseBase> page = new Page<>(pageParams.getPageNo(), pageParams.getPageSize());
        //开始进行分页查询
        Page<CourseBase> pageResult = courseBaseMapper.selectPage(page, queryWrapper);
        //数据列表
        List<CourseBase> items = pageResult.getRecords();
        //总记录数
        long total = pageResult.getTotal();
        //List<T> items, long counts, long page, long pageSize
        PageResult<CourseBase> courseBasePageResult = new PageResult<CourseBase>(items,total,pageParams.getPageNo(), pageParams.getPageSize());
        return  courseBasePageResult;
    }
    @Transactional
    @Override
    public CourseBaseInfoDto createCourseBase(Long companyId, AddCourseDto dto){
        //参数的合法性校验
        if (StringUtils.isBlank(dto.getName())) {
//            throw new RuntimeException("课程名称为空");
            XueChengPlusException.cast("课程名称为空");
        }
        if (StringUtils.isBlank(dto.getMt())) {
            throw new RuntimeException("课程分类为空");
        }
        if (StringUtils.isBlank(dto.getSt())) {
            throw new RuntimeException("课程分类为空");
        }
        if (StringUtils.isBlank(dto.getGrade())) {
            throw new RuntimeException("课程等级为空");
        }
        if (StringUtils.isBlank(dto.getTeachmode())) {
            throw new RuntimeException("教育模式为空");
        }
        if (StringUtils.isBlank(dto.getUsers())) {
            throw new RuntimeException("适应人群为空");
        }
        if (StringUtils.isBlank(dto.getCharge())) {
            throw new RuntimeException("收费规则为空");
        }
        //向课程基本信息表course_base写入数据
        CourseBase courseBaseNew = new CourseBase();
        //将传入的页面的参数放到courseBaseNew对象
//        courseBaseNew.setName(dto.getName());
//        courseBaseNew.setDescription(dto.getDescription());
        //上边的从原始对象中get拿数据向新对象set，比较复杂
        BeanUtils.copyProperties(dto,courseBaseNew);//只要属性名称一致就可以拷贝
        courseBaseNew.setCompanyId(companyId);
        courseBaseNew.setCreateDate(LocalDateTime.now());
        //审核状态默认为未提交
        courseBaseNew.setAuditStatus("202002");
        //发布状态为未发布
        courseBaseNew.setStatus("203001");
        //插入数据库
        int insert = courseBaseMapper.insert(courseBaseNew);
        if(insert<=0){
            throw new RuntimeException("添加课程失败");
        }
        //向课程营销系courese_market写入数据
        CourseMarket courseMarketNew = new CourseMarket();
        //将页面输入的数据拷贝到courseMarketNew
        BeanUtils.copyProperties(dto,courseMarketNew);
        //课程的id
        Long courseId = courseBaseNew.getId();
        courseMarketNew.setId(courseId);
        //保存营销信息
        saveCourseMarket(courseMarketNew);
        //从数据库查询课程的详细信息，包括两部分
        CourseBaseInfoDto courseBaseInfo = getCourseBaseInfo(courseId);
        return courseBaseInfo;
    }
    //查询课程信息
    public CourseBaseInfoDto getCourseBaseInfo(long courseId){
        //从课程基本信息表查询
        CourseBase courseBase = courseBaseMapper.selectById(courseId);
        if(courseBase==null){
            return null;
        }
        //从课程营销表查询
        CourseMarket courseMarket = courseMarketMapper.selectById(courseId);
        //组装在一起
        CourseBaseInfoDto courseBaseInfoDto = new CourseBaseInfoDto();
        BeanUtils.copyProperties(courseBase,courseBaseInfoDto);
        if(courseMarket!=null){
            BeanUtils.copyProperties(courseMarket,courseBaseInfoDto);
        }
        //通过courseCategoryMapper查询分类信息，将分类名称放在courseBaseInfoDto对象
        //todo：课程分类的名称设置到courseBaseInfoDto
        return courseBaseInfoDto;
    }
    //单独写一个方法保存营销信息，逻辑：存在则更新，不存在则添加
    private int saveCourseMarket(CourseMarket courseMarketNew){
        //参数的合法性校验
        String charge = courseMarketNew.getCharge();
        if(StringUtils.isEmpty(charge)){
            throw new RuntimeException("收费规则为空");
        }
        //如果课程收费，价格没有填写也需要抛出异常
        if(charge.equals("201001")){
           if(courseMarketNew.getPrice() ==null || courseMarketNew.getPrice().floatValue()<=0){
//               throw new RuntimeException("课程的价格不能为空并且必须大于0");
               XueChengPlusException.cast("课程的价格不能为空并且必须大于0");
           }
        }
        //从数据库查询营销信息,存在则更新，不存在则添加
        Long id = courseMarketNew.getId();//主键
        CourseMarket courseMarket = courseMarketMapper.selectById(id);
        if(courseMarket == null){
            //插入数据库
            int insert = courseMarketMapper.insert(courseMarketNew);
            return insert;
        }else{
            //将courseMarketNew拷贝到courseMarket
            BeanUtils.copyProperties(courseMarketNew,courseMarket);
            courseMarket.setId(courseMarketNew.getId());
            //更新
            int i = courseMarketMapper.updateById(courseMarket);
            return i;
        }
    }
}

5、前后端联调-跨域的三种方案

1、JSONP

通过script标签的src属性进行跨域请求，如果服务端要响应内容则首先读取请求参数callback的值，callback是一个回调函数的名称，服务端读取callback的值后将响应内容通过调用callback函数的方式告诉请求方。如下图：

2、添加响应头

服务端在响应头添加 Access-Control-Allow-Origin：*

3、通过nginx代理跨域

由于服务端之间没有跨域，浏览器通过nginx去访问跨域地址。

6、cors过滤器

package com.xuecheng.system.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.cors.CorsConfiguration;
import org.springframework.web.cors.UrlBasedCorsConfigurationSource;
import org.springframework.web.filter.CorsFilter;
@Configuration
public class GlobalCorsConfig {
    @Bean
    public CorsFilter corsFilter() {
        CorsConfiguration config = new CorsConfiguration();
        //允许白名单域名进行跨域调用
        config.addAllowedOrigin("*");
        //允许跨越发送cookie
        config.setAllowCredentials(true);
        //放行全部原始头信息
        config.addAllowedHeader("*");
        //允许所有请求方法跨域调用
        config.addAllowedMethod("*");
        UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
        source.registerCorsConfiguration("/**", config);
        return new CorsFilter(source);
    }
}

7、前后端联调

在前端的.env里面定义了一些环境配置

# 前台管理页面-端口
VUE_APP_CLIENT_MANAGE_PORT=8601
# 首页、列表、学习
VUE_APP_CLIENT_PORTAL_URL=http://www.51xuecheng.cn
# 后台服务网关
# VUE_APP_SERVER_API_URL=http://www.51xuecheng.cn/api
#VUE_APP_SERVER_API_URL=http://localhost:63010
#VUE_APP_SERVER_API_URL=http://172.16.63.20:63010
VUE_APP_SERVER_API_URL=http://localhost:63040
# 权限认证
#VUE_APP_SERVER_AUTHORIZATION=ewogICAgImF1ZCI6IFsKICAgICAgICAieHVlY2hlbmctcmVzb3VyY2UiCiAgICBdLAogICAgInBheWxvYWQiOiB7CiAgICAgICAgIjExNzcxNDQyMDk0NjMxMjgxMjUiOiB7CiAgICAgICAgICAgICJyZXNvdXJjZXMiOiBbCiAgICAgICAgICAgIF0sCiAgICAgICAgICAgICJ1c2VyX2F1dGhvcml0aWVzIjogewogICAgICAgICAgICAgICAgInJfMDAxIjogWwogICAgICAgICAgICAgICAgICAgICJ4Y19jb21wYW55X21vZGlmeSIsCgkJCQkJInhjX2NvbXBhbnlfdmlldyIsCgkJCQkJInhjX2NvdXJzZV9iYXNlX2RlbCIsCgkJCQkJInhjX2NvdXJzZV9iYXNlX2VkaXQiLAoJCQkJCSJ4Y19jb3Vyc2VfYmFzZV9saXN0IiwKCQkJCQkieGNfY291cnNlX2Jhc2Vfc2F2ZSIsCgkJCQkJInhjX2NvdXJzZV9iYXNlX3ZpZXciLAoJCQkJCSJ4Y19jb3Vyc2VfcHVibGlzaCIsCgkJCQkJInhjX21hcmtldF9zYXZlX21vZGlmeSIsCgkJCQkJInhjX21hcmtldF92aWV3IiwKCQkJCQkieGNfbWVkaWFfZGVsIiwKCQkJCQkieGNfbWVkaWFfbGlzdCIsCgkJCQkJInhjX21lZGlhX3ByZXZpZXciLAoJCQkJCSJ4Y19tZWRpYV9zYXZlIiwKCQkJCQkieGNfdGVhY2hlcl9saXN0IiwKCQkJCQkieGNfdGVhY2hlcl9tb2RpZnkiLAoJCQkJCSJ4Y190ZWFjaGVyX3NhdmUiLAoJCQkJCSJ4Y193b3JrcmVjb3JkX2NvcnJlY3Rpb24iLAoJCQkJCSJ4Y193b3JrcmVjb3JkX2xpc3QiLAoJCQkJCSJ4Y190ZWFjaHBsYW53b3JrX2RlbCIsCgkJCQkJInhjX3RlYWNocGxhbndvcmtfbGlzdCIsCgkJCQkJInhjX3RlYWNocGxhbndvcmtfc2F2ZV9tb2RpZnkiLAoJCQkJCSJ4Y190ZWFjaHBsYW5fZGVsIiwKCQkJCQkieGNfdGVhY2hwbGFuX3NhdmVfbW9kaWZ5IiwKCQkJCQkieGNfdGVhY2hwbGFuX3ZpZXciCiAgICAgICAgICAgICAgICBdLAogICAgICAgICAgICAgICAgInJfMDAyIjogWwogICAgICAgICAgICAgICAgICAgICJ4Y19jb3Vyc2VfYWRtaW5fbGlzdCIsCgkJCQkJInhjX2NvdXJzZV9iYXNlX2NvbW1pdCIsCgkJCQkJInhjX3N5c3RlbV9jYXRlZ29yeSIsCgkJCQkJInhjX21fbWVkaWFfbGlzdCIsCgkJCQkJInhjX21lZGlhX2F1ZGl0IgogICAgICAgICAgICAgICAgXQogICAgICAgICAgICB9CiAgICAgICAgfQogICAgfSwKICAgICJ1c2VyX25hbWUiOiAieGMtdXNlci1maXJzdCIsCiAgICAic2NvcGUiOiBbCiAgICAgICAgInJlYWQiCiAgICBdLAogICAgIm1vYmlsZSI6ICIxNTAxMjM0NTY3OCIsCiAgICAiZXhwIjogMTYwNjUyNTEyMiwKICAgICJjbGllbnRfYXV0aG9yaXRpZXMiOiBbCiAgICAgICAgIlJPTEVfVVNFUiIKICAgIF0sCiAgICAianRpIjogIjFlYjdlOTg3LWQ3YzItNDBmNS1iMGQ2LWNkNjEzOWNiMThlMCIsCiAgICAiY2xpZW50X2lkIjogInhjLWNvbS1wbGF0Zm9ybSIsCiAgICAiY29tcGFueUlkIjogMTIzMjE0MTQyNQp9
VUE_APP_SERVER_AUTHORIZATION=
#ewogICAgImF1ZCI6IFsKICAgICAgICAieHVlY2hlbmctcmVzb3VyY2UiCiAgICBdLAogICAgInBheWxvYWQiOiB7CiAgICAgICAgIjExNzcxNDQyMDk0NjMxMjgxMjUiOiB7CiAgICAgICAgICAgICJyZXNvdXJjZXMiOiBbCiAgICAgICAgICAgIF0sCiAgICAgICAgICAgICJ1c2VyX2F1dGhvcml0aWVzIjogewogICAgICAgICAgICAgICAgInJfMDAxIjogWwogICAgICAgICAgICAgICAgICAgICJ4Y19jb21wYW55X21vZGlmeSIsCgkJCQkJInhjX2NvbXBhbnlfdmlldyIsCgkJCQkJInhjX2NvdXJzZV9iYXNlX2RlbCIsCgkJCQkJInhjX2NvdXJzZV9iYXNlX2VkaXQiLAoJCQkJCSJ4Y19jb3Vyc2VfYmFzZV9saXN0IiwKCQkJCQkieGNfY291cnNlX2Jhc2Vfc2F2ZSIsCgkJCQkJInhjX2NvdXJzZV9iYXNlX3ZpZXciLAoJCQkJCSJ4Y19jb3Vyc2VfcHVibGlzaCIsCgkJCQkJInhjX21hcmtldF9zYXZlX21vZGlmeSIsCgkJCQkJInhjX21hcmtldF92aWV3IiwKCQkJCQkieGNfbWVkaWFfZGVsIiwKCQkJCQkieGNfbWVkaWFfbGlzdCIsCgkJCQkJInhjX21lZGlhX3ByZXZpZXciLAoJCQkJCSJ4Y19tZWRpYV9zYXZlIiwKCQkJCQkieGNfdGVhY2hlcl9saXN0IiwKCQkJCQkieGNfdGVhY2hlcl9tb2RpZnkiLAoJCQkJCSJ4Y190ZWFjaGVyX3NhdmUiLAoJCQkJCSJ4Y193b3JrcmVjb3JkX2NvcnJlY3Rpb24iLAoJCQkJCSJ4Y193b3JrcmVjb3JkX2xpc3QiLAoJCQkJCSJ4Y190ZWFjaHBsYW53b3JrX2RlbCIsCgkJCQkJInhjX3RlYWNocGxhbndvcmtfbGlzdCIsCgkJCQkJInhjX3RlYWNocGxhbndvcmtfc2F2ZV9tb2RpZnkiLAoJCQkJCSJ4Y190ZWFjaHBsYW5fZGVsIiwKCQkJCQkieGNfdGVhY2hwbGFuX3NhdmVfbW9kaWZ5IiwKCQkJCQkieGNfdGVhY2hwbGFuX3ZpZXciCiAgICAgICAgICAgICAgICBdLAogICAgICAgICAgICAgICAgInJfMDAyIjogWwogICAgICAgICAgICAgICAgICAgICJ4Y19jb3Vyc2VfYWRtaW5fbGlzdCIsCgkJCQkJInhjX2NvdXJzZV9iYXNlX2NvbW1pdCIsCgkJCQkJInhjX3N5c3RlbV9jYXRlZ29yeSIsCgkJCQkJInhjX21fbWVkaWFfbGlzdCIsCgkJCQkJInhjX21lZGlhX2F1ZGl0IgogICAgICAgICAgICAgICAgXQogICAgICAgICAgICB9CiAgICAgICAgfQogICAgfSwKICAgICJ1c2VyX25hbWUiOiAieGMtdXNlci1maXJzdCIsCiAgICAic2NvcGUiOiBbCiAgICAgICAgInJlYWQiCiAgICBdLAogICAgIm1vYmlsZSI6ICIxNTAxMjM0NTY3OCIsCiAgICAiZXhwIjogMTYwNjUyNTEyMiwKICAgICJjbGllbnRfYXV0aG9yaXRpZXMiOiBbCiAgICAgICAgIlJPTEVfVVNFUiIKICAgIF0sCiAgICAianRpIjogIjFlYjdlOTg3LWQ3YzItNDBmNS1iMGQ2LWNkNjEzOWNiMThlMCIsCiAgICAiY2xpZW50X2lkIjogInhjLWNvbS1wbGF0Zm9ybSIsCiAgICAiY29tcGFueUlkIjogMTIzMjE0MTQyNQp9
# Cookie域
VUE_APP_SERVER_DOMAIN=51xuecheng.cn
# 七牛云静态页
VUE_APP_SERVER_QINIU_URL=http://localhost11
# 图片服务器地址
VUE_APP_SERVER_PICSERVER_URL=http://192.168.101.65:9000

8、树形表查询方法

with recursive t1 as (
select * from  course_category p where  id= '1'
union all
 select t.* from course_category t inner join t1 on t1.id = t.parentid
)
select *  from t1 order by t1.id, t1.orderby

9、树形表查询业务实现（难）

代码：

package com.xuecheng.content.service.impl;
import com.xuecheng.content.mapper.CourseCategoryMapper;
import com.xuecheng.content.model.dto.CourseCategoryTreeDto;
import com.xuecheng.content.service.CourseCategoryService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
@Slf4j
@Service
public class  CourseCategoryServiceImpl implements CourseCategoryService {
    @Autowired
    CourseCategoryMapper courseCategoryMapper;
    @Override
    public List<CourseCategoryTreeDto> queryTreeNodes(String id) {
        //调用mapper递归查询出分类信息
        List<CourseCategoryTreeDto> courseCategoryTreeDtos = courseCategoryMapper.selectTreeNodes(id);
        //找到每个节点的子节点，最终封装成List<CourseCategoryTreeDto>
        //先将list转成map，key就是结点的id，value就是CourseCategoryTreeDto对象，目的就是为了方便从map获取结点,filter(item->!id.equals(item.getId()))把根结点排除
        Map<String, CourseCategoryTreeDto> mapTemp = courseCategoryTreeDtos.stream().filter(item -> !id.equals(item.getId())).collect(Collectors.toMap(key -> key.getId(), value -> value/*, (key1, key2) -> key2*/));
        //定义一个list作为最终返回的list
        List<CourseCategoryTreeDto> courseCategoryList = new ArrayList<>();
        //从头遍历 List<CourseCategoryTreeDto> ，一边遍历一边找子节点放在父节点的childrenTreeNodes
        courseCategoryTreeDtos.stream().filter(item -> !id.equals(item.getId())).forEach(item -> {
            if (item.getParentid().equals(id)) {
                courseCategoryList.add(item);
            }
            //找到节点的父节点
            CourseCategoryTreeDto courseCategoryParent = mapTemp.get(item.getParentid());
            if(courseCategoryParent!=null){
                if(courseCategoryParent.getChildrenTreeNodes()==null){
                    //如果该父节点的ChildrenTreeNodes属性为空要new一个集合，因为要向该集合中放它的子节点
                    courseCategoryParent.setChildrenTreeNodes(new ArrayList<CourseCategoryTreeDto>());
                }
                //到每个节点的子节点放在父节点的childrenTreeNodes属性中
                courseCategoryParent.getChildrenTreeNodes().add(item);
            }
        });
        return courseCategoryList;
    }
}

10、新增课程接口定义

需要定义两个数据DTO模型（请求和响应）

### 创建课程
POST {{content_host}}/content/course
Content-Type: application/json
{
  "mt": "",
  "st": "",
  "name": "",
  "pic": "",
  "teachmode": "200002",
  "users": "初级人员",
  "tags": "",
  "grade": "204001",
  "description": "",
  "charge": "201000",
  "price": 0,
  "originalPrice":0,
  "qq": "",
  "wechat": "",
  "phone": "",
  "validDays": 365
}
###响应结果如下
#成功响应结果如下
{
  "id": 109,
  "companyId": 1,
  "companyName": null,
  "name": "测试课程103",
  "users": "初级人员",
  "tags": "",
  "mt": "1-1",
  "mtName": null,
  "st": "1-1-1",
  "stName": null,
  "grade": "204001",
  "teachmode": "200002",
  "description": "",
  "pic": "",
  "createDate": "2022-09-08 07:35:16",
  "changeDate": null,
  "createPeople": null,
  "changePeople": null,
  "auditStatus": "202002",
  "status": 1,
  "coursePubId": null,
  "coursePubDate": null,
  "charge": "201000",
  "price": null,
  "originalPrice":0,
  "qq": "",
  "wechat": "",
  "phone": "",
  "validDays": 365
}

11、接口开发

package com.xuecheng.content.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xuecheng.base.exception.XueChengPlusException;
import com.xuecheng.base.model.PageParams;
import com.xuecheng.base.model.PageResult;
import com.xuecheng.content.mapper.CourseBaseMapper;
import com.xuecheng.content.mapper.CourseCategoryMapper;
import com.xuecheng.content.mapper.CourseMarketMapper;
import com.xuecheng.content.model.dto.AddCourseDto;
import com.xuecheng.content.model.dto.CourseBaseInfoDto;
import com.xuecheng.content.model.dto.QueryCourseParamsDto;
import com.xuecheng.content.model.po.CourseBase;
import com.xuecheng.content.model.po.CourseCategory;
import com.xuecheng.content.model.po.CourseMarket;
import com.xuecheng.content.service.CourseBaseInfoService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resources;
import java.time.LocalDateTime;
import java.util.List;
/**
 * @author Mr.M
 * @version 1.0
 * @description TODO
 * @date 2023/2/12 10:16
 */
@Slf4j
@Service
public class CourseBaseInfoServiceImpl implements CourseBaseInfoService {
    @Autowired
    CourseBaseMapper courseBaseMapper;
    @Autowired
    CourseMarketMapper courseMarketMapper;
    @Autowired
    CourseCategoryMapper courseCategoryMapper;
    @Override
    public PageResult<CourseBase> queryCourseBaseList(PageParams pageParams, QueryCourseParamsDto courseParamsDto) {
        //拼装查询条件
        LambdaQueryWrapper<CourseBase> queryWrapper = new LambdaQueryWrapper<>();
        //根据名称模糊查询,在sql中拼接 course_base.name like '%值%'
        queryWrapper.like(StringUtils.isNotEmpty(courseParamsDto.getCourseName()),CourseBase::getName,courseParamsDto.getCourseName());
        //根据课程审核状态查询 course_base.audit_status = ?
        queryWrapper.eq(StringUtils.isNotEmpty(courseParamsDto.getAuditStatus()), CourseBase::getAuditStatus,courseParamsDto.getAuditStatus());
        //按课程发布状态查询
        queryWrapper.eq((StringUtils.isNotEmpty(courseParamsDto.getPublishStatus())),CourseBase::getStatus,courseParamsDto.getPublishStatus());
        //创建page分页参数对象，参数：当前页码，每页记录数
        Page<CourseBase> page = new Page<>(pageParams.getPageNo(), pageParams.getPageSize());
        //开始进行分页查询
        Page<CourseBase> pageResult = courseBaseMapper.selectPage(page, queryWrapper);
        //数据列表
        List<CourseBase> items = pageResult.getRecords();
        //总记录数
        long total = pageResult.getTotal();
        //List<T> items, long counts, long page, long pageSize
        PageResult<CourseBase> courseBasePageResult = new PageResult<CourseBase>(items,total,pageParams.getPageNo(), pageParams.getPageSize());
        return  courseBasePageResult;
    }
    @Transactional
    @Override
    public CourseBaseInfoDto createCourseBase(Long companyId, AddCourseDto dto){
        //参数的合法性校验
        if (StringUtils.isBlank(dto.getName())) {
//            throw new RuntimeException("课程名称为空");
            XueChengPlusException.cast("课程名称为空");
        }
        if (StringUtils.isBlank(dto.getMt())) {
            throw new RuntimeException("课程分类为空");
        }
        if (StringUtils.isBlank(dto.getSt())) {
            throw new RuntimeException("课程分类为空");
        }
        if (StringUtils.isBlank(dto.getGrade())) {
            throw new RuntimeException("课程等级为空");
        }
        if (StringUtils.isBlank(dto.getTeachmode())) {
            throw new RuntimeException("教育模式为空");
        }
        if (StringUtils.isBlank(dto.getUsers())) {
            throw new RuntimeException("适应人群为空");
        }
        if (StringUtils.isBlank(dto.getCharge())) {
            throw new RuntimeException("收费规则为空");
        }
        //向课程基本信息表course_base写入数据
        CourseBase courseBaseNew = new CourseBase();
        //将传入的页面的参数放到courseBaseNew对象
//        courseBaseNew.setName(dto.getName());
//        courseBaseNew.setDescription(dto.getDescription());
        //上边的从原始对象中get拿数据向新对象set，比较复杂
        BeanUtils.copyProperties(dto,courseBaseNew);//只要属性名称一致就可以拷贝
        courseBaseNew.setCompanyId(companyId);
        courseBaseNew.setCreateDate(LocalDateTime.now());
        //审核状态默认为未提交
        courseBaseNew.setAuditStatus("202002");
        //发布状态为未发布
        courseBaseNew.setStatus("203001");
        //插入数据库
        int insert = courseBaseMapper.insert(courseBaseNew);
        if(insert<=0){
            throw new RuntimeException("添加课程失败");
        }
        //向课程营销系courese_market写入数据
        CourseMarket courseMarketNew = new CourseMarket();
        //将页面输入的数据拷贝到courseMarketNew
        BeanUtils.copyProperties(dto,courseMarketNew);
        //课程的id
        Long courseId = courseBaseNew.getId();
        courseMarketNew.setId(courseId);
        //保存营销信息
        saveCourseMarket(courseMarketNew);
        //从数据库查询课程的详细信息，包括两部分
        CourseBaseInfoDto courseBaseInfo = getCourseBaseInfo(courseId);
        return courseBaseInfo;
    }
    //查询课程信息
    public CourseBaseInfoDto getCourseBaseInfo(long courseId){
        //从课程基本信息表查询
        CourseBase courseBase = courseBaseMapper.selectById(courseId);
        if(courseBase==null){
            return null;
        }
        //从课程营销表查询
        CourseMarket courseMarket = courseMarketMapper.selectById(courseId);
        //组装在一起
        CourseBaseInfoDto courseBaseInfoDto = new CourseBaseInfoDto();
        BeanUtils.copyProperties(courseBase,courseBaseInfoDto);
        if(courseMarket!=null){
            BeanUtils.copyProperties(courseMarket,courseBaseInfoDto);
        }
        //通过courseCategoryMapper查询分类信息，将分类名称放在courseBaseInfoDto对象
        CourseCategory courseCategory1 = courseCategoryMapper.selectById(courseBase.getMt());
        courseBaseInfoDto.setMtName(courseCategory1.getName());
        CourseCategory courseCategory2 = courseCategoryMapper.selectById(courseBase.getSt());
        courseBaseInfoDto.setStName(courseCategory2.getName());
        return courseBaseInfoDto;
    }
    //单独写一个方法保存营销信息，逻辑：存在则更新，不存在则添加
    private int saveCourseMarket(CourseMarket courseMarketNew){
        //参数的合法性校验
        String charge = courseMarketNew.getCharge();
        if(StringUtils.isEmpty(charge)){
            throw new RuntimeException("收费规则为空");
        }
        //如果课程收费，价格没有填写也需要抛出异常
        if(charge.equals("201001")){
           if(courseMarketNew.getPrice() ==null || courseMarketNew.getPrice().floatValue()<=0){
//               throw new RuntimeException("课程的价格不能为空并且必须大于0");
               XueChengPlusException.cast("课程的价格不能为空并且必须大于0");
           }
        }
        //从数据库查询营销信息,存在则更新，不存在则添加
        Long id = courseMarketNew.getId();//主键
        CourseMarket courseMarket = courseMarketMapper.selectById(id);
        if(courseMarket == null){
            //插入数据库
            int insert = courseMarketMapper.insert(courseMarketNew);
            return insert;
        }else{
            //将courseMarketNew拷贝到courseMarket
            BeanUtils.copyProperties(courseMarketNew,courseMarket);
//            courseMarket.setId(courseMarketNew.getId());感觉重复了
            //更新
            int i = courseMarketMapper.updateById(courseMarket);
            return i;
        }
    }
}

12、全局异常处理

和前端约定返回的异常信息模型

package com.xuecheng.base.exception;
import java.io.Serializable;
public class RestErrorResponse implements Serializable {
 private String errMessage;
 public RestErrorResponse(String errMessage){
  this.errMessage= errMessage;
 }
 public String getErrMessage() {
  return errMessage;
 }
 public void setErrMessage(String errMessage) {
  this.errMessage = errMessage;
 }
}

通用错误信息

package com.xuecheng.base.exception;
public enum CommonError {
  UNKOWN_ERROR("执行过程异常，请重试。"),
  PARAMS_ERROR("非法参数"),
  OBJECT_NULL("对象为空"),
  QUERY_NULL("查询结果为空"),
  REQUEST_NULL("请求参数为空");
  private String errMessage;
  public String getErrMessage() {
    return errMessage;
  }
  private CommonError( String errMessage) {
    this.errMessage = errMessage;
  }
}

本项目自定义异常类型

package com.xuecheng.base.exception;
public class XueChengPlusException extends RuntimeException {
    private String errMessage;
    public XueChengPlusException() {
    }
    public XueChengPlusException(String message) {
        super(message);
        this.errMessage = message;
    }
    public String getErrMessage() {
        return errMessage;
    }
    public void setErrMessage(String errMessage) {
        this.errMessage = errMessage;
    }
    public static void cast(String message){
        throw new XueChengPlusException(message);
    }
    public static void cast(CommonError error){
        throw new XueChengPlusException(error.getErrMessage());
    }
}
package com.xuecheng.base.exception;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;
@Slf4j
@ControllerAdvice
//@RestControllerAdvice
public class GlobalExceptionHandler {
 //对项目的自定义异常类型进行处理
   @ResponseBody//将信息返回为json格式
   @ExceptionHandler(XueChengPlusException.class)
   @ResponseStatus(HttpStatus.INTERNAL_SERVER_ERROR)
 public RestErrorResponse customException(XueChengPlusException e){
    //记录异常
    log.error("系统异常{}",e.getErrMessage(),e);
    //..
    //解析出异常信息
    String errMessage = e.getErrMessage();
    RestErrorResponse restErrorResponse = new RestErrorResponse(errMessage);
    return restErrorResponse;
   }
   @ResponseBody
   @ExceptionHandler(Exception.class)
   @ResponseStatus(HttpStatus.INTERNAL_SERVER_ERROR)
 public RestErrorResponse exception(Exception e){
    //记录异常
    log.error("系统异常{}",e.getMessage(),e);
    //解析出异常信息
    RestErrorResponse restErrorResponse = new RestErrorResponse(CommonError.UNKOWN_ERROR.getErrMessage());
    return restErrorResponse;
   }
}


文章标签：
云数据库 RDS MySQL 版
数据库
前端开发
应用服务中间件
SQL
nginx
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
杭州下小雨~
目录
相关文章
杭州下小雨~
|
12月前
|
缓存 监控 算法
学成在线----day6
学成在线----day6
杭州下小雨~
150 0
爱你三千遍斯塔克
|
4月前
|
运维
自媒体运维----公众号如何开启打赏功能，
自媒体运维----公众号如何开启打赏功能，
爱你三千遍斯塔克
26 0
自媒体运维----公众号如何开启打赏功能，
杭州下小雨~
|
12月前
|
缓存 负载均衡 NoSQL
学成在线----项目优化
学成在线----项目优化
杭州下小雨~
83 1
杭州下小雨~
|
12月前
|
存储 前端开发 Java
学成在线----day5
学成在线----day5
杭州下小雨~
93 0
杭州下小雨~
|
12月前
|
存储 前端开发 Java
学成在线----day3
学成在线----day3
杭州下小雨~
58 0
杭州下小雨~
|
12月前
|
编解码 NoSQL 应用服务中间件
学成在线----day7
学成在线----day7
杭州下小雨~
95 0
杭州下小雨~
|
12月前
|
NoSQL Java 关系型数据库
学成在线----day1
学成在线----day1
杭州下小雨~
100 0
杭州下小雨~
|
12月前
|
存储 NoSQL 调度
学成在线----day8
学成在线----day8
杭州下小雨~
56 0
杭州下小雨~
|
12月前
|
存储 JSON 算法
学成在线----认证服务
学成在线----认证服务
杭州下小雨~
91 0
CaptainDrake
|
移动开发 前端开发 HTML5
HTML+CSS-项目：学成在线
HTML+CSS-项目：学成在线
CaptainDrake
147 1