在线教育项目之学生选课功能

SQL
CREATE TABLE `choice` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '课程名称',
`teacher_id` int(10) DEFAULT NULL COMMENT '授课教师',
`student_id` int(10) DEFAULT NULL COMMENT '选课学生',
`course_id` int(10) DEFAULT NULL COMMENT '课程ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='选课信息表';

TypeScript
package com.example.entity;

import lombok.Data;

@Data
public class Choice {

private Integer id;
//课程名称
private String name;
//教师id
private Integer teacherId;
//学生id
private Integer studentId;
//课程id
private Integer courseId;
//教师名称
private String teacherName;
//学生名称
private String studentName;

}

Java
//学生id
private Integer studentId;

SQL
/**
* 新增
*/
@PostMapping("/add")
public Result add(@RequestBody Course course) {
choiceService.add(course);
return Result.success();
}

Java
package com.example.service;

import com.example.entity.Course;

public interface ChoiceService {
void add(Course course);
}

Java
package com.example.service.impl;

@Service
public class ChoiceServiceImpl implements ChoiceService {
@Resource
private CourseMapper courseMapper;
@Resource
private ChoiceMapper choiceMapper;
@Resource
private StudentMapper studentMapper;

@Transactional
@Override
public void add(Course course) {
//1.判断课程是否满员
if (ObjectUtil.isEmpty(course.getAlreadyNum())){
course.setAlreadyNum(0);
}
if (course.getNum() <= course.getAlreadyNum()){
throw new CustomException("课程已满");
}
//2.判断该学生是否选过这门课程
Choice choice = choiceMapper.selectByCourseIdAndStudentId(course.getStudentId(), course.getId());
if (ObjectUtil.isNotEmpty(choice)){
throw new CustomException("该学生已选过该课程");
}
//3.添加选课信息
Choice myChoice = new Choice();
myChoice.setCourseId(course.getId());
myChoice.setStudentId(course.getStudentId());
myChoice.setName(course.getName());
myChoice.setTeacherId(course.getTeacherId());
choiceMapper.insert(myChoice);
//4.更新课程已选人数
course.setAlreadyNum(course.getAlreadyNum() + 1);
courseMapper.updateById(course);
//5.该学生学分增加
Student student = studentMapper.selectById(course.getStudentId());
student.setScore(student.getScore() + course.getScore());
studentMapper.updateById(student);
}

}

Java
package com.example.mapper;

public interface ChoiceMapper {
@Select("SELECT * FROM choice WHERE student_id=#{studentId} AND course_id=#{courseId}")
Choice selectByCourseIdAndStudentId(Integer studentId, Integer courseId);

@Insert("INSERT INTO choice(name,course_id, student_id, teacher_id) VALUES(#{name},#{courseId}, #{studentId}, #{teacherId})")
void insert(Choice myChoice);
}

XML
<el-menu-item index="/choice">
<el-icon><Reading /></el-icon>
<span>选课信息</span>
</el-menu-item>

Java
{ path: 'choice', component: () => import('@/views/manager/Choice.vue')},

TypeScript
/**
* 分页查询
*/
@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);
}

TypeScript
@Override
public PageInfo<Choice> selectPage(Choice choice, Integer pageNum, Integer pageSize) {
List<Choice> list;
PageHelper.startPage(pageNum, pageSize);
//学生登录
if (ObjectUtil.isNotEmpty(choice.getStudentId())){
if (ObjectUtil.isNotEmpty(choice.getName())){
list = choiceMapper.selectByNameAndStudentId(choice.getName(), choice.getStudentId());
}else{
list = choiceMapper.selectAllByStudentId(choice.getStudentId());
}
}else if (ObjectUtil.isNotEmpty(choice.getTeacherId())){ //教师登录
if (ObjectUtil.isNotEmpty(choice.getName())){
list = choiceMapper.selectByNameAndTeacherId(choice.getName(), choice.getTeacherId());
}else {
list = choiceMapper.selectAllByTeacherId(choice.getTeacherId());
}
}else {//管理员登录
if (ObjectUtil.isNotEmpty(choice.getName())){
list = choiceMapper.selectByName(choice.getName());
}else{
list = choiceMapper.selectAll();
}

}
return PageInfo.of(list);
}

Python
@Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c " +
"LEFT JOIN teacher t ON c.teacher_id=t.id\n" +
"LEFT JOIN student s ON c.student_id=s.id \n" +
"WHERE c.`name` LIKE CONCAT('%',#{name},'%') AND c.student_id=#{studentId}")
List<Choice> selectByNameAndStudentId(String name, Integer studentId);

@Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c " +
"LEFT JOIN teacher t ON c.teacher_id=t.id\n" +
"LEFT JOIN student s ON c.student_id=s.id \n" +
"WHERE c.student_id=#{studentId}")
List<Choice> selectAllByStudentId(Integer studentId);

@Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c " +
"LEFT JOIN teacher t ON c.teacher_id=t.id\n" +
"LEFT JOIN student s ON c.student_id=s.id \n" +
"WHERE c.`name` LIKE CONCAT('%',#{name},'%') AND c.teacher_id=#{teacherId}")
List<Choice> selectByNameAndTeacherId(String name, Integer teacherId);

@Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c " +
"LEFT JOIN teacher t ON c.teacher_id=t.id\n" +
"LEFT JOIN student s ON c.student_id=s.id \n" +
"WHERE c.teacher_id=#{teacherId}")
List<Choice> selectAllByTeacherId(Integer teacherId);

@Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c " +
"LEFT JOIN teacher t ON c.teacher_id=t.id\n" +
"LEFT JOIN student s ON c.student_id=s.id \n" +
"WHERE c.`name` LIKE CONCAT('%',#{name},'%')")
List<Choice> selectByName(String name);

@Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c " +
"LEFT JOIN teacher t ON c.teacher_id=t.id\n" +
"LEFT JOIN student s ON c.student_id=s.id")
List<Choice> selectAll();

SQL
/**
* 取消选课
*/
@DeleteMapping("/deleteById/{id}")
public Result deleteById(@PathVariable Integer id){
choiceService.deleteById(id);
return Result.success();
}

Java
@Transactional
@Override
public void deleteById(Integer id) {
//1.删除选课信息
//获取选课信息
Choice choice = choiceMapper.selectById(id);
choiceMapper.deleteById(id);
//2.更新课程已选人数
//获取课程
Course course = courseMapper.selectById(choice.getCourseId());
course.setAlreadyNum(course.getAlreadyNum() - 1);
courseMapper.updateById(course);
//3.该学生学分减少
Student student = studentMapper.selectById(choice.getStudentId());
student.setScore(student.getScore() - course.getScore());
studentMapper.updateById(student);
}

Python
@Delete("delete from choice where id=#{id}")
void deleteById(Integer id);

@Select("select * from choice where id=#{id}")
Choice selectById(Integer id);

Java
@Select("select * from course where id = #{id}")
Course selectById(Integer courseId);