SpringCloud+Vue3主子表插入数据（芋道）

小宋1021

于 2024-07-29 21:00:46 发布

阅读量762

点赞数 15

本文链接：https://blog.csdn.net/qq_50684356/article/details/140780253

版权

目的：多表联查获取到每个班级里面所有的学生上课的信息。点击消课插入到消课主表和消课子表，主表记录班级信息，消课人员信息，上课时间。子表记录上课学员的信息，学员姓名、手机号、班级名称、班级类型、上课时间、老师名称

前端：

<template>
  <Dialog :title="dialogTitle" v-model="dialogVisible">
<!-- 列表 -->

<ContentWrap>
  <el-button
          type="primary"
          plain
         @click="submitForm"
        >
          消课
        </el-button>
  <el-table v-loading="loading" :data="list" 
  :stripe="true" 
  :show-overflow-tooltip="true"
  @selection-change="handleSelectionChange"
  >
  <el-table-column
    label="序号"
    type="index"
    header-align="center"
    align="center"
    width="60px"
    fixed
   />
   <el-table-column type="selection" width="55" />
    <el-table-column label="班级名称" align="center" prop="className" />
    <el-table-column label="班级类型" align="center" prop="classType" width="100px">
          <template #default="scope">
            <dict-tag :type="DICT_TYPE.COURSE_TYPE" :value="scope.row.classType" />
          </template>
        </el-table-column>
    <el-table-column label="学生姓名" align="center" prop="stsStudentName" />
    <el-table-column label="手机号" align="center" prop="stsPhone" />
    <el-table-column label="所报课程" align="center" prop="courseName" />
    <el-table-column label="上课时间" align="center" prop="firstClassStartTime" />
    <!-- <el-table-column
      label="上课时间"
      align="center"
      prop="classTime"
      :formatter="dateFormatter"
      width="180px"
    /> -->
    <el-table-column label="授课教师" align="center" prop="teacherName" />
    
  </el-table>

  <!-- 分页 -->
  <Pagination
    :total="total"
    v-model:page="queryParams.pageNo"
    v-model:limit="queryParams.pageSize"
    @pagination="getList"
  />
</ContentWrap>


</Dialog>
</template>

<script setup lang="ts">
import { dateFormatter } from '@/utils/formatTime'
import { getIntDictOptions, DICT_TYPE } from '@/utils/dict'
import { CancleClassApi, CancleClassVO } from '@/api/study/cancleclass'
import { ref } from 'vue';

/** 消课记录 列表 */
defineOptions({ name: 'CancleClass' })
const dialogVisible = ref(false) // 弹窗的是否展示
const dialogTitle = ref('') // 弹窗的标题
const loading = ref(true) // 列表的加载中
const list = ref([]) // 列表的数据
const queryParams = reactive({
pageNo: 1,
pageSize: 10,

})
const formData = ref({
  id: undefined,
  studentId: undefined
})

const emit = defineEmits(['clickChild'])
const studentId = ref([])
const handleSelectionChange = (val: CancleClassVO[]) => {
  studentId.value = []
  for (let i = 0; i < val.length; i++) {
    studentId.value.push(val[i])
  }
}
const submitForm=async ()=>{
  const tableData = formData.value as unknown as CancleClassVO
  tableData.studentId = studentId.value
  await CancleClassApi.createCancleClass(tableData)
  //传递给父组件
  dialogVisible.value = false;  
}
const open = async (type: string, classId?: number) => {
  dialogVisible.value = true
  loading.value = true
try {
  const data = await CancleClassApi.getCancleClassDetail(classId)
  list.value = data
} finally {
  loading.value = false
}
}
defineExpose({ open }) // 提供 open 方法，用于打开弹窗
/** 查询列表 */
// const getList = async () => {

// }


// onMounted(() => {
// getList()
// })
</script>
<style scoped lang="scss">

:deep(.el-dialog__body){
  width: 1000px !important;
  height: 1000px !important; 
}
</style>

关键：

const formData = ref({
  id: undefined,
  studentId: undefined
})


const studentId = ref([])
const handleSelectionChange = (val: CancleClassVO[]) => {
  studentId.value = []
  for (let i = 0; i < val.length; i++) {
    studentId.value.push(val[i])
  }
}
const submitForm=async ()=>{
  const tableData = formData.value as unknown as CancleClassVO
  tableData.studentId = studentId.value
  await CancleClassApi.createCancleClass(tableData)
  //传递给父组件
  dialogVisible.value = false;  
}

index.ts

import request from '@/config/axios'

// 消课记录 VO
export interface CancleClassVO {
  id: number // 主键id
  className: string // 班级名称
  classType: string // 班级类型
  reportCourse: string // 所报课程
  classTime: Date // 上课时间
  classTeacher: string // 授课教师
  cancelClassPerson: string // 消课人
  cancelClassTime: Date // 消课时间
  studentName: string // 学员姓名
  phone: string // 手机号
  arrivedNum: number // 实到人数
  arrivingNum: number // 应到人数
  rollCallPerson: string // 点名操作人员
  rollCallTime: Date // 店面时间
  operaName: string // 操作人
  operaTime: Date // 操作时间
  operaType: string // 操作类型
  operaExplain: string // 操作说明
  studentId: any
}

// 消课记录 API
export const CancleClassApi = {
  // 查询消课记录分页
  getCancleClassPage: async (params: any) => {
    return await request.get({ url: `/study/cancle-class/page`, params })
  },

    // 查询消课记录分页
    getCancleClassPage2: async (params: any) => {
      return await request.get({ url: `/study/cancle-class/page2`, params })
    },
  

  // 查询消课记录详情
  getCancleClass: async (id: number) => {
    return await request.get({ url: `/study/cancle-class/get?id=` + id })
  },

    // 查询消课记录详情
    getCancleClass2: async (id: number) => {
      return await request.get({ url: `/study/cancle-class/get2?id=` + id })
    },

  // 查询消课记录详情
  getCancleClassDetail: async (id: number) => {
   return await request.gets({ url: `/study/cancle-class/findByIds?id=` + id })
 },

   // 查询消课记录详情2
   getFindDetail: async (id: number) => {
    return await request.gets({ url: `/study/cancle-class/findDetail?id=` + id })
  },

  // 新增消课记录
  createCancleClass: async (data: CancleClassVO) => {
    return await request.post({ url: `/study/cancle-class/create`, data })
  },

  // 修改消课记录
  updateCancleClass: async (data: CancleClassVO) => {
    return await request.put({ url: `/study/cancle-class/update`, data })
  },

  // 删除消课记录
  deleteCancleClass: async (id: number) => {
    return await request.delete({ url: `/study/cancle-class/delete?id=` + id })
  },

  // 导出消课记录 Excel
  exportCancleClass: async (params) => {
    return await request.download({ url: `/study/cancle-class/export-excel`, params })
  },
}

关键：

studentId: any

解析：通过遍历studentId来往后端传值，传的是对象的数组，后端通过studentId来遍历插入值

通过const tableData = formData.value as unknown as CancleClassVO映射往后端传值

// 消课记录 VO
export interface CancleClassVO {
  id: number // 主键id
  className: string // 班级名称
  classType: string // 班级类型
  reportCourse: string // 所报课程
  classTime: Date // 上课时间
  classTeacher: string // 授课教师
  cancelClassPerson: string // 消课人
  cancelClassTime: Date // 消课时间
  studentName: string // 学员姓名
  phone: string // 手机号
  arrivedNum: number // 实到人数
  arrivingNum: number // 应到人数
  rollCallPerson: string // 点名操作人员
  rollCallTime: Date // 店面时间
  operaName: string // 操作人
  operaTime: Date // 操作时间
  operaType: string // 操作类型
  operaExplain: string // 操作说明
  studentId: any
}

调用新增方法往后台插入值

await CancleClassApi.createCancleClass(tableData)

后端：

不仅要给主表建实体类，保存回显方法，还要给子表建立相应的实体类，保存回显方法。

项目架构：

controller：

实体类：

Mapper：

Service和实现类：

主表

实体类：

package com.todod.education.module.study.dal.dataobject.cancleclass;

import lombok.*;

import java.sql.Time;
import java.time.*;
import java.util.*;
import java.time.LocalDateTime;
import java.time.LocalDateTime;
import java.time.LocalDateTime;
import java.time.LocalDateTime;
import java.time.LocalDateTime;
import com.baomidou.mybatisplus.annotation.*;
import com.todod.education.framework.mybatis.core.dataobject.BaseDO;

/**
 * 消课记录 DO
 *
 * @author 平台管理员
 */
@TableName("study_cancle_class")
@KeySequence("study_cancle_class_seq") // 用于 Oracle、PostgreSQL、Kingbase、DB2、H2 数据库的主键自增。如果是 MySQL 等数据库，可不写。
@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class CancleClassDO extends BaseDO {

    /**
     * 主键id
     */
    @TableId
    private Long id;

    /**
     * 班级id
     */
    private Long classId;
    /**
     * 课程id
     */
    private Long courseId;
    /**
     * 学员姓名
     */
    @TableField(exist = false)
    private String stsStudentName;
    /**
     * 手机号
     */
    @TableField(exist = false)
    private String stsPhone;
    /**
     * 班级名称
     */
    @TableField(exist = false)
    private String className;
    /**
     * 班级类型
     */
    @TableField(exist = false)
    private String classType;
    /**
     * 所报课程
     */
    @TableField(exist = false)
    private String courseNames;
    /**
     * 所报课程
     */
    @TableField(exist = false)
    private String courseName;
    /**
     * 授课教师
     */
    @TableField(exist = false)
    private String teacherNames;

    /**
     * 授课教师
     */
    @TableField(exist = false)
    private String teacherName;

    /**
     * 上课时间
     */
    @TableField(exist = false)
    private Time firstClassStartTime;

    /**
     * 上课日期
     */
    @TableField(exist = false)
    private Date firstClassDate;

    /**
     * 消课人
     */
    private String cancelClassPerson;
    /**
     * 消课时间
     */
    private LocalDateTime cancelClassTime;

    /**
     * 实到人数
     */
    private Integer arrivedNum;
    /**
     * 应到人数
     */
    private Integer arrivingNum;
    /**
     * 点名操作人员
     */
    private String rollCallPerson;
    /**
     * 点名时间
     */
    private LocalDateTime rollCallTime;
    /**
     * 操作人
     */
    private String operaName;
    /**
     * 操作时间
     */
    private LocalDateTime operaTime;
    /**
     * 操作类型
     */
    private String operaType;
    /**
     * 操作说明
     */
    private String operaExplain;

}

由于多表联查所以在这个表里的实体类映射的时候，很多字段是从别的表里获取的，所以要加上这句话

@TableField(exist = false)

否则会去数据库里查相应字段报错。

controller：

package com.todod.education.module.study.controller.admin.cancleclass;

import org.springframework.web.bind.annotation.*;
import jakarta.annotation.Resource;
import org.springframework.validation.annotation.Validated;
import org.springframework.security.access.prepost.PreAuthorize;
import io.swagger.v3.oas.annotations.tags.Tag;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.Operation;

import jakarta.validation.constraints.*;
import jakarta.validation.*;
import jakarta.servlet.http.*;
import java.util.*;
import java.io.IOException;

import com.todod.education.framework.common.pojo.PageParam;
import com.todod.education.framework.common.pojo.PageResult;
import com.todod.education.framework.common.pojo.CommonResult;
import com.todod.education.framework.common.util.object.BeanUtils;
import static com.todod.education.framework.common.pojo.CommonResult.success;

import com.todod.education.framework.excel.core.util.ExcelUtils;

import com.todod.education.framework.apilog.core.annotation.ApiAccessLog;
import static com.todod.education.framework.apilog.core.enums.OperateTypeEnum.*;

import com.todod.education.module.study.controller.admin.cancleclass.vo.*;
import com.todod.education.module.study.dal.dataobject.cancleclass.CancleClassDO;
import com.todod.education.module.study.service.cancleclass.CancleClassService;

@Tag(name = "管理后台 - 消课记录")
@RestController
@RequestMapping("/study/cancle-class")
@Validated
public class CancleClassController {

    @Resource
    private CancleClassService cancleClassService;

    @PostMapping("/create")
    @Operation(summary = "创建消课记录")
    @PreAuthorize("@ss.hasPermission('study:cancle-class:create')")
    public CommonResult<Long> createCancleClass(@Valid @RequestBody CancleClassSaveReqVO createReqVO) {
        return success(cancleClassService.createCancleClass(createReqVO));
    }

    @PutMapping("/update")
    @Operation(summary = "更新消课记录")
    @PreAuthorize("@ss.hasPermission('study:cancle-class:update')")
    public CommonResult<Boolean> updateCancleClass(@Valid @RequestBody CancleClassSaveReqVO updateReqVO) {
        cancleClassService.updateCancleClass(updateReqVO);
        return success(true);
    }

    @DeleteMapping("/delete")
    @Operation(summary = "删除消课记录")
    @Parameter(name = "id", description = "编号", required = true)
    @PreAuthorize("@ss.hasPermission('study:cancle-class:delete')")
    public CommonResult<Boolean> deleteCancleClass(@RequestParam("id") Long id) {
        cancleClassService.deleteCancleClass(id);
        return success(true);
    }

    @GetMapping("/get")
    @Operation(summary = "获得消课记录")
    @Parameter(name = "id", description = "编号", required = true, example = "1024")
    @PreAuthorize("@ss.hasPermission('study:cancle-class:query')")
    public CommonResult<CancleClassRespVO> getCancleClass(@RequestParam("id") Long id) {
        CancleClassDO cancleClass = cancleClassService.getCancleClass(id);
        return success(BeanUtils.toBean(cancleClass, CancleClassRespVO.class));
    }

    @GetMapping("/findByIds")
    public List<CancleClassDO> findUsersByIds(@RequestParam Long id) {
        return cancleClassService.selectCheck(id);
    }

    @GetMapping("/findDetail")
    public List<CancleClassDO> findDetail(@RequestParam Long id) {
        return cancleClassService.selectDetail(id);
    }

    @GetMapping("/get2")
    @Operation(summary = "获得消课记录2")
    @Parameter(name = "id", description = "编号", required = true, example = "1024")
    @PreAuthorize("@ss.hasPermission('study:cancle-class:query')")
    public CommonResult<CancleClassRespVO> getCancleClass2(@RequestParam("id") Long id) {
        CancleClassDO cancleClass = cancleClassService.getCancleClass2(id);
        return success(BeanUtils.toBean(cancleClass, CancleClassRespVO.class));
    }

    @GetMapping("/page")
    @Operation(summary = "获得消课记录分页")
    @PreAuthorize("@ss.hasPermission('study:cancle-class:query')")
    public CommonResult<PageResult<CancleClassRespVO>> getCancleClassPage(@Valid CancleClassPageReqVO pageReqVO) {
        PageResult<CancleClassDO> pageResult = cancleClassService.getCancleClassPage(pageReqVO);
        return success(BeanUtils.toBean(pageResult, CancleClassRespVO.class));
    }

    @GetMapping("/page2")
    @Operation(summary = "获得消课记录分页2")
    @PreAuthorize("@ss.hasPermission('study:cancle-class:query')")
    public CommonResult<PageResult<CancleClassRespVO>> getCancleClassPage2(@Valid CancleClassPageReqVO pageReqVO) {
        PageResult<CancleClassDO> pageResult = cancleClassService.getCancleClassPage2(pageReqVO);
        return success(BeanUtils.toBean(pageResult, CancleClassRespVO.class));
    }

    @GetMapping("/export-excel")
    @Operation(summary = "导出消课记录 Excel")
    @PreAuthorize("@ss.hasPermission('study:cancle-class:export')")
    @ApiAccessLog(operateType = EXPORT)
    public void exportCancleClassExcel(@Valid CancleClassPageReqVO pageReqVO,
              HttpServletResponse response) throws IOException {
        pageReqVO.setPageSize(PageParam.PAGE_SIZE_NONE);
        List<CancleClassDO> list = cancleClassService.getCancleClassPage(pageReqVO).getList();
        // 导出 Excel
        ExcelUtils.write(response, "消课记录.xls", "数据", CancleClassRespVO.class,
                        BeanUtils.toBean(list, CancleClassRespVO.class));
    }

}

主要看create新增方法，插入主表子表有这一个新增方法即可

CancleClassSaveReqVO：

package com.todod.education.module.study.controller.admin.cancleclass.vo;

import com.todod.education.module.study.dal.dataobject.cancleclass.CancleClassInfoDO;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.*;
import java.util.*;
import jakarta.validation.constraints.*;
import org.springframework.format.annotation.DateTimeFormat;
import java.time.LocalDateTime;

@Schema(description = "管理后台 - 消课记录新增/修改 Request VO")
@Data
public class CancleClassSaveReqVO {

    @Schema(description = "主键id", requiredMode = Schema.RequiredMode.REQUIRED, example = "4886")
    private Long id;

    @Schema(description = "学员id", requiredMode = Schema.RequiredMode.REQUIRED, example = "4886")
    private List<CancleClassInfoDO> studentId;

    @Schema(description = "课程id", requiredMode = Schema.RequiredMode.REQUIRED, example = "4886")
    private Long courseId;

    @Schema(description = "班级id", requiredMode = Schema.RequiredMode.REQUIRED, example = "4886")
    private Long classId;

    @Schema(description = "上课时间")
    private LocalDateTime classTime;

    @Schema(description = "消课人")
    private String cancelClassPerson;

    @Schema(description = "消课时间")
    private LocalDateTime cancelClassTime;

    @Schema(description = "实到人数")
    private Integer arrivedNum;

    @Schema(description = "应到人数")
    private Integer arrivingNum;

    @Schema(description = "点名操作人员")
    private String rollCallPerson;

    @Schema(description = "点名时间")
    private LocalDateTime rollCallTime;

    @Schema(description = "操作人", example = "王五")
    private String operaName;

    @Schema(description = "操作时间")
    private LocalDateTime operaTime;

    @Schema(description = "操作类型", example = "2")
    private String operaType;

    @Schema(description = "操作说明")
    private String operaExplain;

}

CancleClassRespVO：

package com.todod.education.module.study.controller.admin.cancleclass.vo;

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.*;

import java.sql.Time;
import java.time.LocalTime;
import java.util.*;
import java.util.*;
import org.springframework.format.annotation.DateTimeFormat;
import java.time.LocalDateTime;
import com.alibaba.excel.annotation.*;

@Schema(description = "管理后台 - 消课记录 Response VO")
@Data
@ExcelIgnoreUnannotated
public class CancleClassRespVO {


    @Schema(description = "主键id", requiredMode = Schema.RequiredMode.REQUIRED, example = "18505")
    private Long id;

    @Schema(description = "学员id", requiredMode = Schema.RequiredMode.REQUIRED, example = "18505")
    private Long studentId;

    @Schema(description = "班级id", requiredMode = Schema.RequiredMode.REQUIRED, example = "18505")
    private Long classId;

    @Schema(description = "课程id", requiredMode = Schema.RequiredMode.REQUIRED, example = "18505")
    private Long courseId;

    @Schema(description = "学员姓名", example = "芋艿")
    private String stsStudentName;

    @Schema(description = "手机号")
    private String stsPhone;

    @Schema(description = "班级名称", example = "李四")
    @ExcelProperty("班级名称")
    private String className;

    @Schema(description = "班级类型", example = "1")
    @ExcelProperty("班级类型")
    private String classType;

    @Schema(description = "所报课程")
    @ExcelProperty("所报课程")
    private String courseNames;

    @Schema(description = "所报课程")
    @ExcelProperty("所报课程")
    private String courseName;

    @Schema(description = "上课时间")
    private Time firstClassStartTime;

    @Schema(description = "上课日期")
    private Date firstClassDate;

    @Schema(description = "授课教师")
    @ExcelProperty("授课教师")
    private String teacherNames;

    @Schema(description = "授课教师")
    @ExcelProperty("授课教师")
    private String teacherName;

    @Schema(description = "消课人")
    @ExcelProperty("消课人")
    private String cancelClassPerson;

    @Schema(description = "消课时间")
    @ExcelProperty("消课时间")
    private LocalDateTime cancelClassTime;

    @Schema(description = "创建时间")
    @ExcelProperty("创建时间")
    private LocalDateTime createTime;


    @Schema(description = "操作人", example = "王五")
    private String operaName;

    @Schema(description = "操作时间")
    private LocalDateTime operaTime;

    @Schema(description = "操作类型", example = "2")
    private String operaType;

    @Schema(description = "操作说明")
    private String operaExplain;

    @Schema(description = "实到人数")
    private Integer arrivedNum;

    @Schema(description = "应到人数")
    private Integer arrivingNum;

    @Schema(description = "点名操作人员")
    private String rollCallPerson;

    @Schema(description = "点名时间")
    private LocalDateTime rollCallTime;
}

CancleClassPageReqVO:

package com.todod.education.module.study.controller.admin.cancleclass.vo;

import lombok.*;
import java.util.*;
import io.swagger.v3.oas.annotations.media.Schema;
import com.todod.education.framework.common.pojo.PageParam;
import org.springframework.format.annotation.DateTimeFormat;
import java.time.LocalDateTime;

import static com.todod.education.framework.common.util.date.DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND;

@Schema(description = "管理后台 - 消课记录分页 Request VO")
@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
public class CancleClassPageReqVO extends PageParam {


    @Schema(description = "主键id", requiredMode = Schema.RequiredMode.REQUIRED, example = "18505")
    private Long id;

    @Schema(description = "班级名称", example = "李四")
    private String className;

    @Schema(description = "上课时间")
    @DateTimeFormat(pattern = FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND)
    private LocalDateTime[] classTime;

    @Schema(description = "消课时间")
    @DateTimeFormat(pattern = FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND)
    private LocalDateTime[] cancelClassTime;

    @Schema(description = "创建时间")
    @DateTimeFormat(pattern = FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND)
    private LocalDateTime[] createTime;

    @Schema(description = "所报课程", example = "李四")
    private String courseName;

    @Schema(description = "学员姓名")
    private String stsStudentName;

    @Schema(description = "手机号")
    private String stsPhone;
}

Mapper:

package com.todod.education.module.study.dal.mysql.cancleclass;

import java.util.*;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.todod.education.framework.common.pojo.PageResult;
import com.todod.education.framework.mybatis.core.query.LambdaQueryWrapperX;
import com.todod.education.framework.mybatis.core.mapper.BaseMapperX;
import com.todod.education.module.study.controller.admin.entranceexam.vo.EntranceExamPageReqVO;
import com.todod.education.module.study.dal.dataobject.cancleclass.CancleClassDO;
import com.todod.education.module.study.dal.dataobject.entranceexam.EntranceExamDO;
import org.apache.ibatis.annotations.Mapper;
import com.todod.education.module.study.controller.admin.cancleclass.vo.*;
import org.apache.ibatis.annotations.Param;

/**
 * 消课记录 Mapper
 *
 * @author 平台管理员
 */
@Mapper
public interface CancleClassMapper extends BaseMapperX<CancleClassDO> {

    default PageResult<CancleClassDO> selectPage(CancleClassPageReqVO reqVO) {
        return selectPage(reqVO, new LambdaQueryWrapperX<CancleClassDO>()
                .likeIfPresent(CancleClassDO::getClassName, reqVO.getClassName())
                .betweenIfPresent(CancleClassDO::getCancelClassTime, reqVO.getCancelClassTime())
                .betweenIfPresent(CancleClassDO::getCreateTime, reqVO.getCreateTime())
                .orderByDesc(CancleClassDO::getId));
    }

    IPage<CancleClassDO> fetchPageResults(IPage<CancleClassDO> page, @Param("queryEntry") CancleClassPageReqVO pageReqVO);

    List<CancleClassDO> selectCheck(@Param("id") Long id);

    List<CancleClassDO> selectDetail(@Param("id") Long id);


}

Service:

package com.todod.education.module.study.service.cancleclass;

import java.util.*;
import jakarta.validation.*;
import com.todod.education.module.study.controller.admin.cancleclass.vo.*;
import com.todod.education.module.study.dal.dataobject.cancleclass.CancleClassDO;
import com.todod.education.framework.common.pojo.PageResult;
import com.todod.education.framework.common.pojo.PageParam;

/**
 * 消课记录 Service 接口
 *
 * @author 平台管理员
 */
public interface CancleClassService {

    /**
     * 创建消课记录
     *
     * @param createReqVO 创建信息
     * @return 编号
     */
    Long createCancleClass(@Valid CancleClassSaveReqVO createReqVO);

    /**
     * 更新消课记录
     *
     * @param updateReqVO 更新信息
     */
    void updateCancleClass(@Valid CancleClassSaveReqVO updateReqVO);

    /**
     * 删除消课记录
     *
     * @param id 编号
     */
    void deleteCancleClass(Long id);

    /**
     * 获得消课记录
     *
     * @param id 编号
     * @return 消课记录
     */
    CancleClassDO getCancleClass(Long id);

    /**
     * 获得消课记录
     *
     * @param id 编号
     * @return 消课记录
     */
    CancleClassDO getCancleClass2(Long id);



    /**
     * 获得消课记录分页
     *
     * @param pageReqVO 分页查询
     * @return 消课记录分页
     */
    PageResult<CancleClassDO> getCancleClassPage(CancleClassPageReqVO pageReqVO);

    /**
     * 获得消课记录分页2
     *
     * @param pageReqVO 分页查询
     * @return 消课记录分页
     */
    PageResult<CancleClassDO> getCancleClassPage2(CancleClassPageReqVO pageReqVO);

    List<CancleClassDO> selectCheck(Long id);

    List<CancleClassDO> selectDetail(Long id);



}

实现类：

package com.todod.education.module.study.service.cancleclass;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mzt.logapi.context.LogRecordContext;
import com.mzt.logapi.service.impl.DiffParseFunction;
import com.mzt.logapi.starter.annotation.LogRecord;
import com.todod.education.module.study.controller.admin.monthexam.vo.MonthExamPageReqVO;
import com.todod.education.module.study.controller.admin.plan.vo.PlanSaveReqVO;
import com.todod.education.module.study.dal.dataobject.cancleclass.CancleClassInfoDO;
import com.todod.education.module.study.dal.dataobject.monthexam.MonthExamDO;
import com.todod.education.module.study.dal.dataobject.plan.PlanDO;
import com.todod.education.module.study.dal.mysql.cancleclass.CancleClassInfoMapper;
import org.springframework.stereotype.Service;
import jakarta.annotation.Resource;
import org.springframework.validation.annotation.Validated;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import com.todod.education.module.study.controller.admin.cancleclass.vo.*;
import com.todod.education.module.study.dal.dataobject.cancleclass.CancleClassDO;
import com.todod.education.framework.common.pojo.PageResult;
import com.todod.education.framework.common.pojo.PageParam;
import com.todod.education.framework.common.util.object.BeanUtils;

import com.todod.education.module.study.dal.mysql.cancleclass.CancleClassMapper;

import static com.todod.education.framework.common.exception.util.ServiceExceptionUtil.exception;
import static com.todod.education.module.study.enums.ErrorCodeConstants.*;
import static com.todod.education.module.system.enums.LogRecordConstants.*;

/**
 * 消课记录 Service 实现类
 *
 * @author 平台管理员
 */
@Service
@Validated
public class CancleClassServiceImpl implements CancleClassService {

    @Resource
    private CancleClassMapper cancleClassMapper;

    @Resource
    private CancleClassInfoMapper cancleClassInfoMapper;

    @Override
    @LogRecord(type = STUDY_CANCLE_CLASS_TYPE, subType = STUDY_CANCLE_CLASS_SUB_TYPE, bizNo = "{{#cancleClass.id}}",
            success = STUDY_CANCLE_CLASS_CREATE_SUCCESS)
    public Long createCancleClass(CancleClassSaveReqVO createReqVO) {
        if(createReqVO.getStudentId().size() == 0){
            throw exception(CANCLE_CLASS_NOT_EXISTS);
        }
        // 插入
        CancleClassDO cancleClass = BeanUtils.toBean(createReqVO, CancleClassDO.class);
        cancleClassMapper.insert(cancleClass);

        List<CancleClassInfoDO> studentIds = createReqVO.getStudentId();
        for (CancleClassInfoDO studentId : studentIds) {
            CancleClassInfoDO cancleClassInfoDO = new CancleClassInfoDO();
            cancleClassInfoDO.setStudentId(studentId.getStudentId());
            cancleClassInfoDO.setStudentName(studentId.getStudentName());
            cancleClassInfoDO.setStudentPhone(studentId.getStudentPhone());
            cancleClassInfoDO.setClassType(studentId.getClassType());
            cancleClassInfoDO.setClassName(studentId.getClassName());
            cancleClassInfoDO.setCourseName(studentId.getCourseName());
            cancleClassInfoDO.setTeacherName(studentId.getTeacherName());
            cancleClassInfoDO.setClassStartTime(studentId.getClassStartTime());
            cancleClassInfoDO.setId(studentId.getId());
            cancleClassInfoMapper.insert(cancleClassInfoDO);
        }

// 3. 记录操作日志上下文
        LogRecordContext.putVariable("cancleClass", cancleClass);
        return cancleClass.getId();

    }

//    @Override
//    @LogRecord(type = STUDY_CANCLE_CLASS_TYPE, subType = STUDY_CANCLE_CLASS_SUB_TYPE, bizNo = "{{#cancleClass.id}}",
//            success = STUDY_CANCLE_CLASS_CREATE_SUCCESS)
//    public Long createCancleClass(CancleClassSaveReqVO createReqVO) {
//        // 插入
//        CancleClassDO cancleClass = BeanUtils.toBean(createReqVO, CancleClassDO.class);
//        cancleClassMapper.insert(cancleClass);
//
//        // 3. 记录操作日志上下文
//        LogRecordContext.putVariable("cancleClass", cancleClass);
//
//        // 返回
//        return cancleClass.getId();
//    }

    @Override
    @LogRecord(type = STUDY_PLAN_TYPE, subType = STUDY_CANCLE_CLASS_UPDATE_SUB_TYPE, bizNo = "{{#updateReqVO.id}}",
            success = STUDY_CANCLE_CLASS_UPDATE_SUCCESS)
    public void updateCancleClass(CancleClassSaveReqVO updateReqVO) {
        // 校验存在
        validateCancleClassExists(updateReqVO.getId());

        CancleClassDO oldCancleClassDO = cancleClassMapper.selectById(updateReqVO.getId());

        // 更新
        CancleClassDO updateObj = BeanUtils.toBean(updateReqVO, CancleClassDO.class);
        cancleClassMapper.updateById(updateObj);
        // 3. 记录操作日志上下文
        LogRecordContext.putVariable(DiffParseFunction.OLD_OBJECT, BeanUtils.toBean(oldCancleClassDO, PlanSaveReqVO.class));
        LogRecordContext.putVariable("cancleClass", oldCancleClassDO);
    }

    @Override
    @LogRecord(type = STUDY_PLAN_TYPE, subType = STUDY_CANCLE_CLASS_DELETE_SUB_TYPE, bizNo = "{{#id}}",
            success = STUDY_CANCLE_CLASS_DELETE_SUCCESS)
    public void deleteCancleClass(Long id) {
        // 校验存在
        validateCancleClassExists(id);

        CancleClassDO oldCancleClassDO = cancleClassMapper.selectById(id);

        // 删除
        cancleClassMapper.deleteById(id);

        // 3. 记录操作日志上下文
        LogRecordContext.putVariable("cancleClass", oldCancleClassDO);
    }

    private void validateCancleClassExists(Long id) {
        if (cancleClassMapper.selectById(id) == null) {
            throw exception(CANCLE_CLASS_NOT_EXISTS);
        }
    }

    @Override
    public CancleClassDO getCancleClass(Long id) {
        return cancleClassMapper.selectById(id);
    }

    @Override
    public CancleClassDO getCancleClass2(Long id) {
        return cancleClassMapper.selectById(id);
    }

    @Override
    public PageResult<CancleClassDO> getCancleClassPage(CancleClassPageReqVO pageReqVO) {
        return cancleClassMapper.selectPage(pageReqVO);
    }

    @Override
    public PageResult<CancleClassDO> getCancleClassPage2(CancleClassPageReqVO pageReqVO) {
        IPage<CancleClassDO> page = new Page<>(pageReqVO.getPageNo(), pageReqVO.getPageSize());
        cancleClassMapper.fetchPageResults(page, pageReqVO);
        return new PageResult<>(page.getRecords(), page.getTotal());
    }

    @Override
    public List<CancleClassDO> selectCheck(Long id) {
        return cancleClassMapper.selectCheck(id);
    }

    @Override
    public List<CancleClassDO> selectDetail(Long id) {
        return cancleClassMapper.selectDetail(id);
    }





}

XML文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.todod.education.module.study.dal.mysql.cancleclass.CancleClassMapper">

    <select id="fetchPageResults" resultType="com.todod.education.module.study.dal.dataobject.cancleclass.CancleClassDO">
        SELECT tcm.class_name,tcm.id AS class_id,tcm.class_type,tcmc.teacherNames,tcmc.courseNames,tcms.students,
        FIRST_VALUE(stt.class_start_time) OVER (PARTITION BY tcm.id ORDER BY stt.class_date) AS first_class_start_time,
        FIRST_VALUE(stt.class_date) OVER (PARTITION BY tcm.id ORDER BY stt.class_date) AS first_class_start_date

        FROM teach_class_manage tcm
        INNER JOIN (
        SELECT tcmc.class_id,string_agg(htm.teacher_name, ',') AS teacherNames,string_agg(tcm.course_name, ',') AS courseNames

        FROM teach_class_manage_course tcmc
        INNER JOIN hr_teacher_manage htm ON htm."id" = tcmc.teacher_id
        INNER JOIN teach_course_manage tcm ON tcm."id" = tcmc.course_id
        GROUP BY tcmc.class_id
        ) tcmc ON tcm."id" = tcmc.class_id
        LEFT JOIN (
        SELECT tcms.class_id,COALESCE(COUNT(*), 0) AS students

        FROM teach_class_manage_student tcms
        GROUP BY tcms.class_id
        ) tcms ON  tcm."id" = tcms.class_id
        LEFT JOIN study_time_table stt ON stt.study_course_id = ANY(ARRAY(SELECT tcmc2.course_id FROM teach_class_manage_course tcmc2 WHERE tcmc2.class_id = tcm.id))
        WHERE 1 = 1 AND tcm.deleted = 0

        <if test=" queryEntry.stsStudentName != null and queryEntry.stsStudentName != '' and queryEntry.stsStudentName != 'null' ">
            AND ss.sts_student_name like '%${queryEntry.stsStudentName}'
        </if>
        ORDER BY
        tcm.create_time desc
    </select>

    <select id="selectCheck" resultType="com.todod.education.module.study.dal.dataobject.cancleclass.CancleClassDO">
        SELECT
        tcm.class_name,
        tcm.class_type,
        ss.sts_student_name,
        FIRST_VALUE(stt.class_start_time) OVER (PARTITION BY tcm.id ORDER BY stt.class_date) AS first_class_start_time,
--                 FIRST_VALUE(stt.class_date) OVER (PARTITION BY tcm.id ORDER BY stt.class_date) AS first_class_start_date
        ss.sts_phone,
        tcs.student_id,
        tcmc.course_id,
        tcm2.course_name,
        htm.teacher_name
        FROM
        teach_class_manage tcm
        JOIN teach_class_manage_student tcs ON tcm.id = tcs.class_id
        JOIN study_student ss ON tcs.student_id = ss.id
        JOIN teach_class_manage_course tcmc ON tcm.id = tcmc.class_id
        JOIN teach_course_manage tcm2 ON tcmc.course_id = tcm2.id
        JOIN hr_teacher_manage htm ON tcmc.teacher_id = htm.id
        LEFT JOIN study_time_table stt ON stt.study_course_id = ANY(ARRAY(SELECT tcmc2.course_id FROM teach_class_manage_course tcmc2 WHERE tcmc2.class_id = tcm.id))
        WHERE
        tcm.id = #{id}
        ORDER BY
        tcs.student_id, tcmc.course_id;


    </select>

    <select id="selectDetail" resultType="com.todod.education.module.study.dal.dataobject.cancleclass.CancleClassDO">
        SELECT
            tcm.class_name,
            tcm.id AS class_id,
            tcm.class_type,
            tcmc.teacherNames,
            tcmc.courseNames,
            tcms.students,
            FIRST_VALUE(stt.class_start_time) OVER (PARTITION BY tcm.id ORDER BY stt.class_date) AS first_class_start_time,
                FIRST_VALUE(stt.class_date) OVER (PARTITION BY tcm.id ORDER BY stt.class_date) AS first_class_start_date
        FROM
            teach_class_manage tcm
                INNER JOIN (
                SELECT
                    tcmc.class_id,
                    string_agg(htm.teacher_name, ',') AS teacherNames,
                    string_agg(tcm.course_name, ',') AS courseNames
                FROM
                    teach_class_manage_course tcmc
                        INNER JOIN hr_teacher_manage htm ON htm.id = tcmc.teacher_id
                        INNER JOIN teach_course_manage tcm ON tcm.id = tcmc.course_id
                GROUP BY
                    tcmc.class_id
            ) tcmc ON tcm.id = tcmc.class_id
                LEFT JOIN (
                SELECT
                    tcms.class_id,
                    COALESCE(COUNT(*), 0) AS students
                FROM
                    teach_class_manage_student tcms
                GROUP BY
                    tcms.class_id
            ) tcms ON tcm.id = tcms.class_id
                LEFT JOIN study_time_table stt ON stt.study_course_id IN (SELECT tcmc2.course_id FROM teach_class_manage_course tcmc2 WHERE tcmc2.class_id = tcm.id)
        WHERE
            tcm.deleted = 0
          AND tcm.id = 9 -- 替换为实际的参数或变量
        ORDER BY
            tcm.create_time DESC;
    </select>

</mapper>

从表

CancleClassInfoSaveReqVO：

package com.todod.education.module.study.controller.admin.cancleclass.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.todod.education.module.study.dal.dataobject.cancleclass.CancleClassInfoDO;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.sql.Time;
import java.time.LocalDateTime;
import java.util.List;

@Schema(description = "管理后台 - 消课记录新增/修改 Request VO")
@Data
public class CancleClassInfoSaveReqVO {

    @Schema(description = "主键id", requiredMode = Schema.RequiredMode.REQUIRED, example = "4886")
    private Long id;

    @Schema(description = "学员id", example = "21490")
    private List<CancleClassInfoDO> studentId;

    @Schema(description = "学员姓名", example = "芋艿")
    private String studentName;

    @Schema(description = "手机号")
    private String studentPhone;

    @Schema(description = "班级名称", example = "李四")
    @ExcelProperty("班级名称")
    private String className;

    @Schema(description = "班级类型", example = "1")
    @ExcelProperty("班级类型")
    private String classType;

    @Schema(description = "所报课程")
    @ExcelProperty("所报课程")
    private String courseName;

    @Schema(description = "上课时间")
    @ExcelProperty("上课时间")
    private Time classStartTime;

    @Schema(description = "授课教师")
    @ExcelProperty("授课教师")
    private String teacherName;

    @Schema(description = "操作人", example = "王五")
    private String operaName;

    @Schema(description = "操作时间")
    private LocalDateTime operaTime;

    @Schema(description = "操作类型", example = "2")
    private String operaType;

    @Schema(description = "操作说明")
    private String operaExplain;

}

关键

@Schema(description = "学员id", example = "21490")
private List<CancleClassInfoDO> studentId;

要把学员id定义成一个list对象集合，用来往子表批量插入学生的数据

CancleClassInfoRespVO：

package com.todod.education.module.study.controller.admin.cancleclass.vo;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.sql.Time;
import java.time.LocalDateTime;

@Schema(description = "管理后台 - 消课记录 Response VO")
@Data
@ExcelIgnoreUnannotated
public class CancleClassInfoRespVO {

    @Schema(description = "主键id", requiredMode = Schema.RequiredMode.REQUIRED, example = "18505")
    private Long id;

    @Schema(description = "学员姓名", example = "芋艿")
    private String studentName;

    @Schema(description = "手机号")
    private String studentPhone;

    @Schema(description = "班级名称", example = "李四")
    @ExcelProperty("班级名称")
    private String className;

    @Schema(description = "班级类型", example = "1")
    @ExcelProperty("班级类型")
    private String classType;

    @Schema(description = "所报课程")
    @ExcelProperty("所报课程")
    private String courseName;

    @Schema(description = "上课时间")
    @ExcelProperty("上课时间")
    private Time classStartTime;

    @Schema(description = "授课教师")
    @ExcelProperty("授课教师")
    private String teacherName;

    @Schema(description = "创建时间")
    @ExcelProperty("创建时间")
    private LocalDateTime createTime;

    @Schema(description = "操作人", example = "王五")
    private String operaName;

    @Schema(description = "操作时间")
    private LocalDateTime operaTime;

    @Schema(description = "操作类型", example = "2")
    private String operaType;

    @Schema(description = "操作说明")
    private String operaExplain;

}

实现类：

package com.todod.education.module.study.dal.dataobject.cancleclass;

import com.baomidou.mybatisplus.annotation.KeySequence;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.todod.education.framework.mybatis.core.dataobject.BaseDO;
import lombok.*;

import java.sql.Time;
import java.time.LocalDateTime;

/**
 * 消课记录 DO
 *
 * @author 平台管理员
 */
@TableName("study_cancle_class_info")
@KeySequence("study_cancle_class_info_seq") // 用于 Oracle、PostgreSQL、Kingbase、DB2、H2 数据库的主键自增。如果是 MySQL 等数据库，可不写。
@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class CancleClassInfoDO extends BaseDO {

    /**
     * 主键id
     */
    @TableId
    private Long id;

    private Long studentId;

    /**
     * 学员姓名
     */

    private String studentName;
    /**
     * 手机号
     */
    private String studentPhone;
    /**
     * 班级名称
     */
    private String className;
    /**
     * 班级类型
     */
    private String classType;
    /**
     * 所报课程
     */
    private String courseName;
    /**
     * 授课教师
     */
    private String teacherName;

    /**
     * 上课时间
     */
    private Time classStartTime;

    /**
     * 操作人
     */
    private String operaName;
    /**
     * 操作时间
     */
    private LocalDateTime operaTime;
    /**
     * 操作类型
     */
    private String operaType;
    /**
     * 操作说明
     */
    private String operaExplain;


}

关键：

  @Override
    @LogRecord(type = STUDY_CANCLE_CLASS_TYPE, subType = STUDY_CANCLE_CLASS_SUB_TYPE, bizNo = "{{#cancleClass.id}}",
            success = STUDY_CANCLE_CLASS_CREATE_SUCCESS)
    public Long createCancleClass(CancleClassSaveReqVO createReqVO) {
        if(createReqVO.getStudentId().size() == 0){
            throw exception(CANCLE_CLASS_NOT_EXISTS);
        }
        // 插入
        CancleClassDO cancleClass = BeanUtils.toBean(createReqVO, CancleClassDO.class);
        cancleClassMapper.insert(cancleClass);

        List<CancleClassInfoDO> studentIds = createReqVO.getStudentId();
        for (CancleClassInfoDO studentId : studentIds) {
            CancleClassInfoDO cancleClassInfoDO = new CancleClassInfoDO();
            cancleClassInfoDO.setStudentId(studentId.getStudentId());
            cancleClassInfoDO.setStudentName(studentId.getStudentName());
            cancleClassInfoDO.setStudentPhone(studentId.getStudentPhone());
            cancleClassInfoDO.setClassType(studentId.getClassType());
            cancleClassInfoDO.setClassName(studentId.getClassName());
            cancleClassInfoDO.setCourseName(studentId.getCourseName());
            cancleClassInfoDO.setTeacherName(studentId.getTeacherName());
            cancleClassInfoDO.setClassStartTime(studentId.getClassStartTime());
            cancleClassInfoDO.setId(studentId.getId());
            cancleClassInfoMapper.insert(cancleClassInfoDO);
        }

// 3. 记录操作日志上下文
        LogRecordContext.putVariable("cancleClass", cancleClass);
        return cancleClass.getId();

    }

主表插入：

// 插入
CancleClassDO cancleClass = BeanUtils.toBean(createReqVO, CancleClassDO.class);
cancleClassMapper.insert(cancleClass);

直接用Mapper以及mybatis-plus插入到数据库里面

子表插入：

List<CancleClassInfoDO> studentIds = createReqVO.getStudentId();
for (CancleClassInfoDO studentId : studentIds) {
    CancleClassInfoDO cancleClassInfoDO = new CancleClassInfoDO();
    cancleClassInfoDO.setStudentId(studentId.getStudentId());
    cancleClassInfoDO.setStudentName(studentId.getStudentName());
    cancleClassInfoDO.setStudentPhone(studentId.getStudentPhone());
    cancleClassInfoDO.setClassType(studentId.getClassType());
    cancleClassInfoDO.setClassName(studentId.getClassName());
    cancleClassInfoDO.setCourseName(studentId.getCourseName());
    cancleClassInfoDO.setTeacherName(studentId.getTeacherName());
    cancleClassInfoDO.setClassStartTime(studentId.getClassStartTime());
    cancleClassInfoDO.setId(studentId.getId());
    cancleClassInfoMapper.insert(cancleClassInfoDO);
}

List<CancleClassInfoDO> studentIds = createReqVO.getStudentId();

遍历前端传过来的studentId，往子表里插入数据

for (CancleClassInfoDO studentId : studentIds) {
CancleClassInfoDO cancleClassInfoDO = new CancleClassInfoDO();
cancleClassInfoDO.setStudentId(studentId.getStudentId());
cancleClassInfoDO.setStudentName(studentId.getStudentName());
cancleClassInfoDO.setStudentPhone(studentId.getStudentPhone());
cancleClassInfoDO.setClassType(studentId.getClassType());
cancleClassInfoDO.setClassName(studentId.getClassName());
cancleClassInfoDO.setCourseName(studentId.getCourseName());
cancleClassInfoDO.setTeacherName(studentId.getTeacherName());
cancleClassInfoDO.setClassStartTime(studentId.getClassStartTime());
cancleClassInfoDO.setId(studentId.getId());
cancleClassInfoMapper.insert(cancleClassInfoDO);
}

CancleClassInfoDO cancleClassInfoDO = new CancleClassInfoDO();

新建一个CancleClassInfoDO 实体类对象。

cancleClassInfoDO.setStudentId(studentId.getStudentId());
cancleClassInfoDO.setStudentName(studentId.getStudentName());
cancleClassInfoDO.setStudentPhone(studentId.getStudentPhone());
cancleClassInfoDO.setClassType(studentId.getClassType());
cancleClassInfoDO.setClassName(studentId.getClassName());
cancleClassInfoDO.setCourseName(studentId.getCourseName());
cancleClassInfoDO.setTeacherName(studentId.getTeacherName());
cancleClassInfoDO.setClassStartTime(studentId.getClassStartTime());
cancleClassInfoDO.setId(studentId.getId());

往数据库插入数据

cancleClassInfoMapper.insert(cancleClassInfoDO);

小宋1021

关注

15
点赞
踩
16

收藏

觉得还不错? 一键收藏
0
评论
SpringCloud+Vue3主子表插入数据（芋道）

目的：多表联查获取到每个班级里面所有的学生上课的信息。点击消课插入到消课主表和消课子表，主表记录班级信息，消课人员信息，上课时间。子表记录上课学员的信息，学员姓名、手机号、班级名称、班级类型、上课时间、老师名称。
复制链接

扫一扫