在设计数据库表的时候，当有某个字段需要（具体看自己的业务）跟其他模块的信息有所关联的时候，或者这个字段是跟角色相关的（可能不同的角色对这个模块看到的信息或者行为不一样）时候，该字段就要考虑关联了，我们可以设计为xxx_id。
逻辑
选课入口：
学生登录系统后，可以看到可选课程列表（CourseService提供数据）
每门课程会显示：课程名称、授课教师、剩余名额等信息
选课核心流程：
学生点击”选课”按钮时，系统会做两个重要检查：
- 检查课程是否已满（通过比较课程总人数
num和已选人数alreadyNum）
2) 检查该学生是否已经选过这门课（通过查询choice表）
选课成功的情况：
如果课程未满且学生未选过：
系统会在选课表(choice)中新增一条记录
同时把该课程的已选人数+1（更新course表）
选课失败的情况：
如果课程已满：提示”课程已经选满”
如果已经选过：提示”你已经选过这门课程”
关键代码位置：
选课逻辑主要在ChoiceService.java的add()方法
课程信息查询在CourseService.java
前端选课页面在Choice.vue
简单来说就是：选课前检查名额和重复选课，通过检查就记录选课信息并更新课程人数。
创建数据库
1
2
3
4
5
6
7
8
| CREATE TABLE `choice` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '课程名称',
`teacher_id` int DEFAULT NULL COMMENT '授课教师',
`student_id` int DEFAULT NULL COMMENT '学生信息',
`course_id` int DEFAULT NULL COMMENT '课程ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='选课信息';
|
entity—Choice.java
创建实体类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
| public class Choice {
private Integer id;
private String name;
private Integer studentId;
private Integer teacherId;
private String teacherName;
private String studentName;
private Integer courseId;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getStudentId() {
return studentId;
}
public void setStudentId(Integer studentId) {
this.studentId = studentId;
}
public Integer getTeacherId() {
return teacherId;
}
public void setTeacherId(Integer teacherId) {
this.teacherId = teacherId;
}
public String getTeacherName() {
return teacherName;
}
public void setTeacherName(String teacherName) {
this.teacherName = teacherName;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public Integer getCourseId() {
return courseId;
}
public void setCourseId(Integer courseId) {
this.courseId = courseId;
}
}
|
前端
Manager.vue
1
2
3
4
| <el-menu-item index="/choice">
<el-icon><Reading /></el-icon>
<span>选课信息</span>
</el-menu-item>
|
index.js
1
| { path: 'choice', component: () => import('@/views/manager/Choice.vue')},
|
Choice.vue
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
| <template>
<div>
<div class="card" style="margin-bottom: 5px;">
<el-input v-model="data.name" style="width: 300px; margin-right: 10px" placeholder="请输入课程名称查询"></el-input>
<el-button type="primary" @click="load">查询</el-button>
<el-button type="info" style="margin: 0 10px" @click="reset">重置</el-button>
</div>
<div class="card" style="margin-bottom: 5px">
<el-table :data="data.tableData" stripe>
<el-table-column label="课程名称" prop="name"></el-table-column>
<el-table-column label="授课教师" prop="teacherName"></el-table-column>
<el-table-column label="选课学生" prop="studentName"></el-table-column>
<el-table-column label="操作" align="center" width="160" v-if ="data.user.role === 'ADMIN'">
<template #default="scope" >
<el-button type="danger" @click="handleDelete(scope.row.id)">删除</el-button>
</template>
</el-table-column>
</el-table>
</div>
<div class="card">
<el-pagination background layout="prev, pager, next" v-model:page-size="data.pageSize" v-model:current-page="data.pageNum" :total="data.total"/>
</div>
</div>
</template>
<script setup>
import request from "@/utils/request";
import {reactive} from "vue";
import {ElMessageBox, ElMessage} from "element-plus";
const data = reactive({
user: JSON.parse(localStorage.getItem('system-user') || '{}'),// 获取当前登录的用户信息
pageNum: 1,
pageSize: 10,
total: 0,
formVisible: false,
form: {},
tableData: [],
name: null,
collegeDate: [],// 用于存储学院信息
teacherDate: []// 用于存储教师信息
})
// 分页查询
const load = () => {
let teacherId = null// 用于存储教师id
if (data.user.role === 'TEACHER') {
teacherId = data.user.id// 如果是教师，就查询自己的课程信息
}
request.get('/choice/selectPage', {
params: {
pageNum: data.pageNum,
pageSize: data.pageSize,
name: data.name,
teacherId: teacherId,// 如果是教师，就查询自己的课程信息
}
}).then(res => {
data.tableData = res.data?.list
data.total = res.data?.total
})
}
// 删除
const handleDelete = (id) => {
ElMessageBox.confirm('删除后数据无法恢复，您确定删除吗?', '删除确认', { type: 'warning' }).then(res => {
request.delete('/choice/deleteById/' + id).then(res => {
if (res.code === '200') {
load()
ElMessage.success('操作成功')
} else {
ElMessage.error(res.msg)
}
})
}).catch(err => {})
}
// 重置
const reset = () => {
data.name = null
load()
}
const loadTeacher = () => {//查询所有教师信息
request.get('/teacher/selectAll').then(res => {//调用后端接口查询所有教师信息
if (res.code === '200') {//如果查询成功，将查询到的教师信息赋值给data.teacherDate
data.teacherDate = res.data
}else {//如果查询失败，提示用户
ElMessage.error(res.msg)
}
})
}
const loadCollege= () => {//查询所有学院信息
request.get('/college/selectAll').then(res => {//调用后端接口查询所有学院信息
if (res.code === '200') {//如果查询成功，将查询到的学院信息赋值给data.collegeDate
data.collegeDate = res.data
load()//查询课程信息
}else {//如果查询失败，提示用户
ElMessage.error(res.msg)
}
})
}
load()
loadCollege()
loadTeacher()
</script>
|
Course.vue
1
2
3
4
| <template #default="scope" v-else>
<el-button type="primary" @click="ChoiceCouse(scope.row)" :disabled = "scope.row.num === scope.row.alreadyNum">选课</el-button>
</template>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
| const ChoiceCouse = (row) => {
let courseData = JSON.parse(JSON.stringify(row))
courseData.studentId = data.user.id
request.post('/choice/add', courseData).then(res => {
if (res.code === '200') {
ElMessage.success('选课成功')
load()
} else {
ElMessage.error(res.msg)
}
})
}
|
Controller— ChoiceController.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
@RestController
@RequestMapping("/choice")
public class ChoiceController {
@Resource
private ChoiceService choiceService;
@PostMapping("/add")
public Result add(@RequestBody Course course) {
choiceService.add(course);
return Result.success();
}
@PutMapping("/update")
public Result update(@RequestBody Choice choice) {
choiceService.updateById(choice);
return Result.success();
}
@DeleteMapping("/deleteById/{id}")
public Result deleteById(@PathVariable Integer id) {
choiceService.deleteById(id);
return Result.success();
}
@GetMapping("selectPage")
public Result selectPage(Choice choice,
@RequestParam(defaultValue = "1") Integer pageNum,
@RequestParam(defaultValue = "5") Integer pageSize){
PageInfo<Choice> pageInfo = choiceService.selectPage(choice,pageNum,pageSize);
return Result.success(pageInfo);
}
@GetMapping("/selectAll")
public Result selectAll(){
List<Choice> list = choiceService.selectAll();
return Result.success(list);
}
}
|
Service
ChoiceService.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
@Service
public class ChoiceService {
@Resource
private ChoiceMapper choiceMapper;
@Resource
private CourseMapper courseMapper;
public void add(Course course) {
if(course.getNum().equals(course.getAlreadyNum())){
throw new CustomException("课程已经选满");
}
List<Choice> list = choiceMapper.selectByStudentIdAndCourseId(course.getStudentId(),course.getId());
if(CollectionUtil.isNotEmpty(list)){
throw new CustomException("你已经选过这门课程");
}
Choice choice = new Choice();
choice.setStudentId(course.getStudentId());
choice.setCourseId(course.getId());
choice.setTeacherId(course.getTeacherId());
choice.setName(course.getName());
choiceMapper.insert(choice);
course.setAlreadyNum(course.getAlreadyNum()+1);
courseMapper.updateById(course);
}
public PageInfo<Choice> selectPage(Choice choice,Integer pageNum, Integer pageSize) {
List<Choice> list;
PageHelper.startPage(pageNum,pageSize);
if(ObjectUtil.isNotEmpty(choice.getName())){
list = choiceMapper.selectByName(choice.getName());
}else{
list = choiceMapper.selectAll();
}
return PageInfo.of(list);
}
public void updateById(Choice choice) {
choiceMapper.updateById(choice);
}
public void deleteById(Integer id) {
choiceMapper.deleteById(id);
}
public List<Choice> selectAll() {
return choiceMapper.selectAll();
}
}
|
CourseService.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
@Service
public class CourseService {
@Resource
private CourseMapper courseMapper;
public void add(Course course) {
course.setAlreadyNum(0);
courseMapper.insert(course);
}
public PageInfo<Course> selectPage(Course course,Integer pageNum, Integer pageSize) {
List<Course> list;
PageHelper.startPage(pageNum,pageSize);
if(ObjectUtil.isNotEmpty(course.getTeacherId())){
Integer teacherId = course.getTeacherId();
if (ObjectUtil.isNotEmpty(course.getName())) {
list = courseMapper.selectByNameAndTeacherId(course.getName(),teacherId);
} else {
list = courseMapper.selectAllByTeacherId(teacherId);
}
}
else {
if (ObjectUtil.isNotEmpty(course.getName())) {
list = courseMapper.selectByName(course.getName());
} else {
list = courseMapper.selectAll();
}
}
return PageInfo.of(list);
}
public void updateById(Course course) {
courseMapper.updateById(course);
}
public void deleteById(Integer id) {
courseMapper.deleteById(id);
}
public List<Course> selectAll() {
return courseMapper.selectAll();
}
}
|
Mapper
ChoiceMapper.java
注意：sql语句换行后面加空格
明确指定表名样MySQL就能明确知道我们要查询的是哪个表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
| @Mapper
public interface ChoiceMapper {
void insert(Choice choice);
@Select("select choice.*, course.name as courseName, teacher.name as teacherName, student.name as studentName " +
"from choice " +
"left join course on choice.course_id = course.id " +
"left join teacher on choice.teacher_id = teacher.id " +
"left join student on choice.student_id = student.id")
List<Choice> selectAll();
@Select("select choice.*, course.name as courseName, teacher.name as teacherName,student.name as studentName " +
"from choice " +
"left join course on choice.course_id = course.id " +
"left join teacher on choice.teacher_id = teacher.id " +
"left join student on choice.student_id = student.id " +
"where choice.name like concat('%',#{name},'%')")
List<Choice> selectByName(String name);
void updateById(Choice choice);
@Delete("delete from choice where id = #{id}")
void deleteById(Integer id);
@Select("select choice.*, course.name as courseName, teacher.name as teacherName,student.name as studentName " +
"from choice " +
"left join course on choice.course_id = course.id " +
"left join teacher on choice.teacher_id = teacher.id " +
"left join student on choice.student_id = student.id " +
"where choice.name like concat('%',#{name},'%') and choice.teacher_id = #{teacherId}")
List<Choice> selectByNameAndTeacherId(@Param("name") String name, @Param("teacherId") Integer teacherId);
@Select("select choice.*, course.name as courseName,teacher.name as teacherName,student.name as studentName " +
"from choice " +
"left join course on choice.course_id = course.id " +
"left join teacher on choice.teacher_id = teacher.id " +
"left join student on choice.student_id = student.id " +
"where choice.teacher_id = #{teacherId}")
List<Choice> selectAllByTeacherId(Integer teacherId);
@Select("select * from choice where student_id = #{studentId} and course_id = #{courseId}")
List<Choice> selectByStudentIdAndCourseId(@Param("studentId") Integer studentId, @Param("courseId") Integer courseId);
@Select("select choice.*, teacher.name as teacherName,student.name as studentName " +
"from choice " +
"left join teacher on choice.teacher_id = teacher.id " +
"left join student on choice.student_id = student.id " +
"where choice.name like concat('%',#{name},'%') and student_id = #{studentId}")
List<Choice> selectByNameAndStudentId(@Param("name") String name, @Param("studentId") Integer studentId);
@Select("select choice.*, teacher.name as teacherName,student.name as studentName " +
"from choice " +
"left join teacher on choice.teacher_id = teacher.id " +
"left join student on choice.student_id = student.id " + // 这里添加空格
"where student_id = #{studentId} ")
List<Choice> selectAllByStudentId(Integer studentId);
}
|
ChoiceMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| <?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.example.mapper.ChoiceMapper">
<insert id="insert" parameterType="com.example.entity.Choice" useGeneratedKeys="true">
insert into choice (name, teacher_id, student_id,course_id)
values (#{name}, #{teacherId}, #{studentId},#{courseId})
</insert>
<update id="updateById" parameterType="com.example.entity.Choice">
update choice
set name = #{name},
student_id = #{studentId},
teacher_id = #{teacherId},
course_id = #{courseId}
where id = #{id}
-- 这里的id是实体类中的属性名，不是数据库中的字段名
</update>
</mapper>
|
这个算是整个系统里面最难，最重要的一个逻辑之一吧，一定要仔细看！！！好好消化
