阅读量:0
目的：多表联查获取到每个班级里面所有的学生上课的信息。点击消课插入到消课主表和消课子表，主表记录班级信息，消课人员信息，上课时间。子表记录上课学员的信息，学员姓名、手机号、班级名称、班级类型、上课时间、老师名称
前端：
<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);
