在线教育系统项目之学生信息的管理

SQL
CREATE TABLE `student` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '账号',
`password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '密码',
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '姓名',
`role` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '角色',
`sex` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '性别',
`code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '学号',
`college_id` int(10) DEFAULT NULL COMMENT '学院ID',
`score` int(10) DEFAULT NULL COMMENT '学分',
`avatar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '头像',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生信息';

TypeScript
@Data
public class Student extends Account{

private String sex;
private String code;
private Integer collegeId;
private Integer score;
}

XML
<el-menu-item index="/student"><el-icon><User /></el-icon><span>学生信息</span></el-menu-item>

TypeScript
{ path: 'student', component: () => import('@/views/manager/Student.vue')},

Java
package com.example.controller;

/**
* 学生模块前端请求的接口入口
*/
@RestController
@RequestMapping("/student")
public class StudentController {
@Resource
private StudentService studentService;


/**
* 新增
*/
@PostMapping("/add")
public Result add(@RequestBody Student student) {
studentService.add(student);
return Result.success();
}
}

TypeScript
package com.example.service;

public interface StudentService {
void add(Student student);
}

Java
package com.example.service.impl;

@Service
public class StudentServiceImpl implements StudentService {
@Resource
private StudentMapper studentMapper;

@Override
public void add(Student student) {
//判断账号是否唯一
Student dbStudent = studentMapper.selectByUsername(student.getUsername());
if (ObjectUtil.isNotEmpty(dbStudent)) {
throw new CustomException("用户名已存在");
}
//设置默认密码
if (ObjectUtil.isEmpty(student.getPassword())) {
student.setPassword("123456");
}
//设置默认姓名
if (ObjectUtil.isEmpty(student.getName())) {
student.setName(student.getUsername());
}
//设置默认角色
student.setRole("STUDENT");
//设置默认分数
student.setScore(0);
studentMapper.insert(student);
}
}

TypeScript
package com.example.mapper;

import com.example.entity.Student;

public interface StudentMapper {
void insert(Student student);

@Select("select * from student where username = #{username}")
Student selectByUsername(String username);
}

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.example.mapper.StudentMapper">

<insert id="insert" parameterType="com.example.entity.Student" useGeneratedKeys="true">
insert into student (username, password, name, avatar, role, sex, code, college_id, score)
values (#{username}, #{password}, #{name}, #{avatar}, #{role}, #{sex}, #{code}, #{collegeId}, #{score})
</insert>
</mapper>

SQL
CREATE TABLE `college` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '学院名称',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '学院描述',
`score` int NULL DEFAULT NULL COMMENT '最低学分',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学院信息表';

TypeScript
//学院名称
private String collegeName;

TypeScript
/**
* 分页查询
*/
@GetMapping("/selectPage")
public Result selectPage(Student student,
@RequestParam(defaultValue = "1") Integer pageNum,
@RequestParam(defaultValue = "5") Integer pageSize) {
PageInfo<Student> pageInfo = studentService.selectPage(student, pageNum, pageSize);
return Result.success(pageInfo);
}

TypeScript
public PageInfo<Student> selectPage(Student student, Integer pageNum, Integer pageSize) {
List<Student> list = new ArrayList<>();
PageHelper.startPage(pageNum, pageSize);
if (ObjectUtil.isNotEmpty(student.getName())) {
list = studentMapper.selectByName(student.getName());
} else {
list = studentMapper.selectAll();
}
return PageInfo.of(list);
}

TypeScript
@Select("SELECT s.*,c.`name` collegeName FROM student s " +
"LEFT JOIN college c ON s.college_id=c.id" +
" WHERE s.`name` LIKE CONCAT('%',#{name},'%')")
List<Student> selectByName(String name);

@Select("SELECT s.*,c.`name` collegeName FROM student s LEFT JOIN college c ON s.college_id=c.id")
List<Student> selectAll();

SQL
/**
* 更新
*/
@PutMapping("/update")
public Result update(@RequestBody Student student) {
studentService.updateById(student);
return Result.success();
}

TypeScript
@Override
public void update(Student student) {
//判断账号是否唯一
//1.通过ID获取原student，然后判断当前的账号是否和原账号一致
Student myStudent = studentMapper.selectById(student.getId());
//2.如果原账号和新账号不一致，则判断账号是否已存在
if (ObjectUtil.isNotNull(myStudent) && !myStudent.getUsername().equals(student.getUsername())) {
//查询账号收否存在
Student dbStudent = studentMapper.selectByUsername(student.getUsername());
if (ObjectUtil.isNotNull(dbStudent)) {
throw new CustomException("用户名已存在");
}
}
studentMapper.updateById(student);
}

TypeScript
@Select("select * from student where id=#{id}")
Student selectById(Integer id);

@Update("UPDATE student " +
"SET username = #{username}, password = #{password}, name = #{name}," +
"avatar = #{avatar}, role = #{role}, sex = #{sex}, code = #{code}," +
"college_id = #{collegeId}, score = #{score} where id = #{id}")
void updateById(Student student);

SQL
/**
* 根据id删除
*/
@DeleteMapping("/deleteById/{id}")
public Result deleteById(@PathVariable Integer id) {
studentService.deleteById(id);
return Result.success();
}

TypeScript
@Override
public void deleteById(Integer id) {
studentMapper.deleteById(id);
}

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