教师、学生、课程、班级、教师选课和学生选课后端的增删改查

最新推荐文章于 2024-04-28 17:10:22 发布

Road_slow

最新推荐文章于 2024-04-28 17:10:22 发布

阅读量549

点赞数

分类专栏：日常错误及解决方案文章标签： java spring spring boot 学习方法

本文链接：https://blog.csdn.net/Road_slow/article/details/132096151

版权

日常错误及解决方案专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、数据库的设计

1.表设计

总共六张表，包括教师表、学生表、课程表、班级表、教师选课表和学生选课表。
教师选课只能单选、学生选课可以多选。
教师和学生都有自己的班级，且唯一。

2.对应关系

3.代码实现

教师表、学生表、课程表、班级表

//学生表   
public class Student {
    private String stuId;
    private String stuName;
    private String stuSex;
    private String classId;
}

//教师表
public class Teacher {
    private String teacherId;
    private String teacherName;
    private String teacherSex;
    private String classId;
}
//课程表
public class Course {
    private String courseId;
    private String courseName;
}
//班级表
public class Classs {
    private String classId;
    private String className;
}

教师选课表和学生选课表

//教师选课表
public class TeacherCourse {
    private String tcId;
    private String teacherId;
    private String courseId;
}

//学生选课表
public class StudentCourse {
    private String scId;
    private String studentId;
    private String courseId;
}

二、学生表

1.学生表的查询

1.1 学生表的查询：查询对象为StudentDTO，可以根据学生姓名、学生班级和学生性别进行查询。

学生表的查询返回对象：返回对象为StudentVo，返回学生姓名、学生性别、班级名字和选课信息，选课信息是一个List集合。

学生查询条件展示

学生查询结果展示

学生查询对象和返回对象

//学生查询对象
public class StudentDTO {
    private String stuId;
    private String stuName;
    private String stuSex;
    private String className;
}

//学生查询返回对象
public class StudentVo {
    private String stuId;
    private String stuName;
    private String stuSex;
    private String classId;
    //班级名字
    private String className;
    //学生选课情况
    private List<Course> courseList;
}

学生查询Controller、Service、ServiceImpl

    //查询学生信息Controller
    @GetMapping("/getList")
    public Response<JsonDataset<StudentVo>> list(StudentDTO studentDTO) {
        log.info("获取学生数据");
        JsonDataset<StudentVo> res = new JsonDataset<>(studentService.list(studentDTO));
        return Response.OK(res);
    }


    //查询学生信息Service
    DataSet<StudentVo> list(StudentDTO studentDTO);

    //查询学生信息ServiceImpl
    @Override
    public DataSet<StudentVo> list(StudentDTO studentDTO) {
        //要根据查询条件进行查询 主要条件有 姓名，性别，课程名字
        return studentMapper.list(studentDTO);
    }

学生查询Mapper

//查询学生Mapper
DataSet<StudentVo> list(StudentDTO studentDTO);


//查询学生Mapper.xml
   <!--    查询全部学生信息-->
    <select id="list" resultMap="stuResultMap"             
        parameterType="com.backend.entity.dto.StudentDTO">
        select s.stu_id,s.stu_name,s.stu_sex,c.class_id,c.class_name
        from student s left join classs c on s.class_id= c.class_id
        <where>
            <if test=" stuId != null and  stuId != ''">
                and s.stu_id = #{stuId,jdbcType=VARCHAR}
            </if>
            <if test="stuName != null and stuName != ''">
                and s.stu_name = #{stuName,jdbcType=VARCHAR}
            </if>
            <if test="stuSex != null and stuSex != ''">
                and s.stu_sex = #{stuSex,jdbcType=VARCHAR}
            </if>
            <if test="className != null and className != ''">
                and c.class_name = #{className,jdbcType=VARCHAR}
            </if>
        </where>
    </select>

学生查询Mapper返回结果

    //这是学生实体对象 
    <resultMap type="com.backend.entity.po.Student" id="student">
        <result property="stuId" column="stu_id" jdbcType="VARCHAR"/>
        <result property="stuName" column="stu_name" jdbcType="VARCHAR"/>
        <result property="stuSex" column="stu_sex" jdbcType="VARCHAR"/>
        <result property="classId" column="class_id" jdbcType="VARCHAR"/>
    </resultMap>
    
    //这是学生返回Vo对象
    <resultMap type="com.backend.entity.vo.StudentVo" id="stuResultMap">
        <id column="stu_id" property="stuId"/>
        <result column="stu_name" property="stuName"/>
        <result column="stu_sex" property="stuSex"/>
        <result column="class_id" property="classId"/>
        <result column="class_name" property="className"/>
        //返回的课程List是用课程表的mapper进行查询的
        <collection property="courseList" ofType="com.backend.entity.po.Course"
                    column="stu_id"     
        select="com.backend.dao.CourseMapper.selectCourseIdsBystuId">
        </collection>
    </resultMap>

课程Mapper的相关查询（课程表和学生课程表进行关联、关联属性为课程id）

    <!--根据学生id查询学生所选课程-->
    <select id="selectCourseIdsBystuId" resultMap="course"     
        parameterType="java.lang.String">
        select c.course_id, c.course_name
        from student_course sc
                 left join course c on sc.course_id = c.course_id
        where student_id = #{stuId,jdbcType=VARCHAR};
    </select>


    <resultMap type="com.backend.entity.po.Course" id="course">
        <id column="course_id" property="courseId"/>
        <result column="course_name" property="courseName"/>
    </resultMap>

2.学生表的添加:

2.1 添加对象为StudentUpdateDTO，包含学生姓名、学生班级、学生性别、班级名称和课程列表。

添加条件展示

添加对象展示

//学生添加对象
public class StudentUpdateDTO {
    private String stuId;
    private String stuName;
    private String stuSex;
    private String className;
    private List<String> courseList;
}

学生插入Controller、Service、ServiceImpl

其中插入学生要插入学生表和学生选课表
插入学生表的时候要去根据课程名字去查询课程id
插入学生选课表的时候要拿出选课列表中的课程名称，之后去课程表查询相关的课程id

    //添加学生信息Controller
     @PostMapping("/insert")
    public Response<Boolean> insert(@RequestBody StudentUpdateDTO studentUpdateDTO) {
        log.info("新增学生: {}", studentUpdateDTO);
        int res = studentService.insert(studentUpdateDTO);
        return Response.OK(res > 0);
    }


    //添加学生信息Service
    int insert(StudentUpdateDTO studentUpdateDTO);

    //添加学生信息ServiceImpl
    @Override
    public int insert(StudentUpdateDTO studentUpdateDTO) {
        //1.生成UUID 作为主键
        String uuid = UUIDGenerator.getUUID();
        //2.根据班级名称查询班级id
        String classId = classsMapper.getClassIdByclassName(studentUpdateDTO.getClassName());
        //3.新建插入对象 拷贝属性
        Student student = new Student();
        BeanUtils.copyProperties(studentUpdateDTO, student);
        student.setStuId(uuid);
        student.setClassId(classId);
        //4.调用mapper进行插入学生表
        int res1 = studentMapper.insert(student);
        //5.根据课程名称列表去查课程对象
        List<String> courseNameList = studentUpdateDTO.getCourseList();
        for (String courseName : courseNameList) {
            Course course = courseMapper.getCourseIdByCourseName(courseName);
            //6.获取课程id
            String courseId = course.getCourseId();
            //7.构造学生选课对象 赋值后调用mapper进行插入
            StudentCourse studentCourse = new StudentCourse();
            String scid = UUIDGenerator.getUUID();
            studentCourse.setScId(scid);
            studentCourse.setStudentId(uuid);
            studentCourse.setCourseId(courseId);
            studentCourseMapper.insertStudentCourse(studentCourse);
        }
        return res1;
    }

学生插入相关Mapper

//插入学生Mapper
int insert(Student student);


//插入学生Mapper.xml
  <!--插入学生-->
    <insert id="insert" parameterType="com.backend.entity.po.Student">
        insert into student
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="stuId != null and stuId != ''">
                stu_id,
            </if>
            <if test="stuName != null and stuName != ''">
                stu_name ,
            </if>
            <if test="stuSex != null and stuSex != ''">
                stu_sex,
            </if>
            <if test="classId != null and classId != ''">
                class_id,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="stuId != null and stuId != ''">
                #{stuId,jdbcType=VARCHAR},
            </if>
            <if test="stuName != null and stuName != ''">
                #{stuName,jdbcType=VARCHAR},
            </if>
            <if test="stuSex != null and stuSex != ''">
                #{stuSex,jdbcType=VARCHAR},
            </if>
            <if test="classId != null and classId != ''">
                #{classId,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>

根据班级名称查询班级id

  <resultMap type="com.backend.entity.po.Classs" id="class">
        <id column="class_id" property="classId"/>
        <result column="class_name" property="className"/>
    </resultMap>

    <!--根据班级名字查询班级id-->
    <select id="getClassIdByclassName" resultType="string" parameterType="java.lang.String">
        select class_id
        from classs
        where class_name = #{className,jdbcType=VARCHAR}
    </select>

根据课程名称查询课程对象

    <resultMap type="com.backend.entity.po.Course" id="course">
        <id column="course_id" property="courseId"/>
        <result column="course_name" property="courseName"/>
    </resultMap> 


<!--根据课程名字查询课程-->
    <select id="getCourseIdByCourseName" resultMap="course"             
                          parameterType="java.lang.String">
        select course_id, course_name
        from course
        where course_name =
              #{courseName,jdbcType=VARCHAR};
    </select>

插入学生选课表

 <resultMap type="com.backend.entity.po.StudentCourse" id="studentCourse">
        <id column="sc_id" property="scId"/>
        <result column="student_id" property="studentId"/>
        <result column="course_id" property="courseId"/>
    </resultMap>


<!--插入学生选课-->
    <insert id="insertStudentCourse" parameterType="com.backend.entity.po.StudentCourse">
        insert into student_course
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="scId != null and scId != ''">
                sc_id,
            </if>
            <if test="studentId != null and studentId != ''">
                student_id ,
            </if>
            <if test="courseId != null and courseId != ''">
                course_id ,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="scId != null and scId != ''">
                #{scId,jdbcType=VARCHAR},
            </if>
            <if test="studentId != null and studentId != ''">
                #{studentId,jdbcType=VARCHAR},
            </if>
            <if test="courseId != null and courseId != ''">
                #{courseId,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>

3. 学生表的更新

3.1添加条件展示 (和学生表的添加类似，根据有没有学生的id传入判断是学生更新还是学生插入）

学生更新Controller、Service、ServiceImpl

其中更新学生要更新学生表和学生选课表
更新学生表的时候要去根据课程名字去查询课程id
更新学生选课表
1.根据学生id删除学生选课表的信息
2.根据传入的课程名字，查询课程id
3.根据学生id进行学生选课的插入

    //更新学生信息Controller
    @PostMapping("/update")
    public Response<Boolean> update(@RequestBody StudentUpdateDTO studentUpdateDTO) {
        log.info("更新学生: {}", studentUpdateDTO);
        Boolean res = studentService.update(studentUpdateDTO);
        return Response.OK(res);
    }

    //更新学生信息Service
    Boolean update(StudentUpdateDTO studentUpdateDTO);

    //更新学生信息ServiceImpl
     @Override
    public Boolean update(StudentUpdateDTO studentUpdateDTO) {
        //1.更新学生表信息
        Student student = new Student();
        BeanUtils.copyProperties(studentUpdateDTO, student);
        String classId = classsMapper.getClassIdByclassName(studentUpdateDTO.getClassName());
        student.setClassId(classId);
        int res1 = studentMapper.update(student);
        //2.更新学生选课信息
        //2.1 根据学生id删除学生选课id
        studentCourseMapper.deleteCourseIdByStuId(studentUpdateDTO.getStuId());
        //2.2根据学生传入的课程名字，查询课程id
        List<String> courseList = studentUpdateDTO.getCourseList();
        List<String> ids = new ArrayList<>();
        for (int i = 0; i < courseList.size(); i++) {
            Course course = courseMapper.getCourseIdByCourseName(courseList.get(i));
            ids.add(course.getCourseId());
        }
        //2.3.根据学生id进行学生选课的插入
        for (int i = 0; i < ids.size(); i++) {
            StudentCourse studentCourse = new StudentCourse();
            String uuid = UUIDGenerator.getUUID();
            studentCourse.setScId(uuid);
            studentCourse.setStudentId(studentUpdateDTO.getStuId());
            studentCourse.setCourseId(ids.get(i));
            studentCourseMapper.insertStudentCourse(studentCourse);
        }
        return res1 > 0;
    }

学生更新相关Mapper

//更新学生Mapper
int update(Student student);


//更新学生Mapper.xml
 <!--通过学生id修改数据-->
    <update id="update" parameterType="com.backend.entity.po.Student">
        update student
        <set>
            <if test="stuId != null and stuId != ''">
                stu_id= #{stuId,jdbcType=VARCHAR},
            </if>
            <if test="stuName != null and stuName != ''">
                stu_name= #{stuName,jdbcType=VARCHAR} ,
            </if>
            <if test="stuSex != null and stuSex != ''">
                stu_sex= #{stuSex,jdbcType=VARCHAR},
            </if>
            <if test="classId != null and classId != ''">
                class_id= #{classId,jdbcType=VARCHAR},
            </if>
        </set>
        where stu_id = #{stuId,jdbcType=VARCHAR}
    </update>

根据学生id删除学生选课表的信息

     /**
     * 根据学生id删除学生所选的课程
     * @param stuId
     * @return
     */
    @Delete("delete from student_course where student_id = #{stuId}")
    int deleteCourseIdByStuId(String stuId);

4 学生表的删除

4.1传入学生id即可

学生删除Controller、Service、ServiceImpl

其中删除学生要删除学生表和学生选课表

    //删除学生信息Controller
     @PostMapping("/delete")
    public Response<Boolean> delete(String stuId) {
        log.info("根据id删除学生：{}", stuId);
        Boolean res = studentService.delete(stuId);
        return Response.OK(res);
    }

    //删除学生信息Service
    Boolean delete(String stuId);

    //删除学生信息ServiceImpl
     @Override
    public Boolean delete(String stuId) {
        //1.删除学生表信息
        int res1 = studentMapper.delete(stuId);
        //2.删除学生选课信息
        int res2 = studentCourseMapper.deleteCourseIdByStuId(stuId);
        return res1 > 0;
    }

学生删除相关Mapper

    //删除学生Mapper
    int delete(String stuId);


    //删除学生Mapper.xml
    <delete id="delete" parameterType="java.lang.String">
        delete
        from student
        where stu_id = #{stuId,jdbcType=VARCHAR}
    </delete>


     /**
     * 根据学生id删除学生所选的课程
     * @param stuId
     * @return
     */
    @Delete("delete from student_course where student_id = #{stuId}")
    int deleteCourseIdByStuId(String stuId);

三、教师表

教师表的增删改查基本和学生表类似，就是一个教师只能教一门课，所以传入的课程不在是一个课程列表，就是一个课程名称。

教师管理页面展示

新增教师和更新教师弹窗

插入和更新教师传入的对象

//添加和修改教师传入的对象
public class TeacherUpdateDTO {
    private String teacherId;
    private String teacherName;
    private String teacherSex;
    private String className;
    //一个教师只能选一门课
    private String courseName;
}

这里只展示教师增删改的整体逻辑，基本和学生一样

@Service
public class TeacherServiceImpl implements TeacherService {

    @Autowired
    TeacherMapper teacherMapper;

    @Autowired
    ClasssMapper classsMapper;

    @Autowired
    TeacherCourseMapper teacherCourseMapper;

    @Autowired
    CourseMapper courseMapper;

    /**
     * 获取教师信息列表
     *
     * @param teacherDTO
     * @return 教师Vo对象，包含教师选的课
     */
    @Override
    public DataSet<TeacherVo> list(TeacherDTO teacherDTO) {
        return teacherMapper.list(teacherDTO);
    }

    /**
     * 根据教师id查询课程信息
     *
     * @param teacherId 教师id
     * @return 教师Vo对象，包含教师选的课
     */
    @Override
    public TeacherVo selectById(String teacherId) {
        return teacherMapper.selectCourseIdsByteacherId(teacherId);
    }

    /**
     * 插入教师
     *
     * @param teacherDTO
     * @return
     */
    @Override
    public Boolean insert(TeacherDTO teacherDTO) {
        //1. 新建插入教师对象，拷贝属性
        Teacher teacher = new Teacher();
        BeanUtils.copyProperties(teacherDTO, teacher);
        //2. 生成教师对象主键，并赋值
        String uuid = UUIDGenerator.getUUID();
        teacher.setTeacherId(uuid);
        //3. 根据插入的教师班级名称查询班级id，并赋值
        String classId = classsMapper.getClassIdByclassName(teacherDTO.getClassName());
        teacher.setClassId(classId);
        // 4.调用mapper进行插入教师表
        int res = teacherMapper.insert(teacher);
        //5.根据传入的课程名字查询课程
        Course course = courseMapper.getCourseIdByCourseName(teacherDTO.getCourseName());
        //6.构造教师代课对象，然后调用mapper进行插入教师带课表
        TeacherCourse teacherCourse = new TeacherCourse();
        String scId = UUIDGenerator.getUUID();
        teacherCourse.setTcId(scId);
        teacherCourse.setTeacherId(uuid);
        teacherCourse.setCourseId(course.getCourseId());
        int res2 = teacherCourseMapper.insertTeacherCourse(teacherCourse);
        return res > 0 && res2 > 0;
    }

    /**
     * 更新教师
     *
     * @param teacherUpdateDTO
     * @return
     */
    @Override
    public Boolean update(TeacherUpdateDTO teacherUpdateDTO) {
        //1.更新教师表
        Teacher teacher = new Teacher();
        BeanUtils.copyProperties(teacherUpdateDTO, teacher);
        //1.1 根据班级名字查询班级id
        String classId = classsMapper.getClassIdByclassName(teacherUpdateDTO.getClassName());
        teacher.setClassId(classId);
        //1.2调用mapper进行更新
        int res1 = teacherMapper.update(teacher);
        //2.更新教师选课表
        //2.1 一个教师只能教一门课，根据教师id删除教师的选课情况
        String teacherId = teacherUpdateDTO.getTeacherId();
        String courseName = teacherUpdateDTO.getCourseName();
        teacherCourseMapper.deleteCourseIdByteacherId(teacherId);
        //2.2根据传入的课程名称得到课程id
        Course course = courseMapper.getCourseIdByCourseName(courseName);
        String courseId = course.getCourseId();
        //2.3 进行教师课程的插入
        TeacherCourse teacherCourse = new TeacherCourse();
        String uuid = UUIDGenerator.getUUID();
        teacherCourse.setTcId(uuid);
        teacherCourse.setTeacherId(teacherId);
        teacherCourse.setCourseId(courseId);
        int res2 = teacherCourseMapper.insertTeacherCourse(teacherCourse);
        return res1 > 0 && res2 > 0;
    }

    /**
     * 根据教师id删除教师信息
     *
     * @param teacherId
     * @return
     */
    @Override
    public Boolean delete(String teacherId) {
        // 1.删除教师表信息
        int res1 = teacherMapper.delete(teacherId);
        // 2.删除教师的选课信息
        int res2 = teacherCourseMapper.deleteCourseIdByteacherId(teacherId);
        return res1 > 0 && res2 > 0;
    }
}

教师mapper.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.backend.dao.TeacherMapper">

    <resultMap type="com.backend.entity.po.Teacher" id="teacher">
        <result property="teacherId" column="teacher_id" jdbcType="VARCHAR"/>
        <result property="teacherName" column="teacher_name" jdbcType="VARCHAR"/>
        <result property="teacherSex" column="teacher_sex" jdbcType="VARCHAR"/>
        <result property="classId" column="class_id" jdbcType="VARCHAR"/>
    </resultMap>

    <resultMap type="com.backend.entity.vo.TeacherVo" id="teaResultMap">
        <id column="teacher_id" property="teacherId"/>
        <result column="teacher_name" property="teacherName"/>
        <result column="teacher_sex" property="teacherSex"/>
        <result column="class_id" property="classId"/>
        <result column="class_name" property="className"/>
        <collection property="courseList" ofType="com.backend.entity.po.Course"
                    column="teacher_id"         
         select="com.backend.dao.CourseMapper.selectCourseIdsByteaId">
        </collection>
    </resultMap>

    <!--根据教师id查询课程信息，返回教师Vo对象，包含教师选的课-->
    <select id="selectCourseIdsByteacherId" resultMap="teaResultMap" parameterType="java.lang.String">
        select t.teacher_id, t.teacher_name, t.teacher_sex, c.class_id, c.class_name
        from teacher t
                 left join classs c on t.class_id = c.class_id
        where t.teacher_id = #{teacherId,jdbcType=VARCHAR}
    </select>

    <!--    查询全部教师信息-->
    <select id="list" resultMap="teaResultMap" parameterType="com.backend.entity.dto.TeacherDTO">
        select t.teacher_id, t.teacher_name, t.teacher_sex, c.class_id, c.class_name
        from teacher t
        left join classs c on t.class_id = c.class_id
        <where>
            <if test="teacherId != null and teacherId != ''">
                and t.teacher_id = #{teacherId,jdbcType=VARCHAR}
            </if>
            <if test="teacherName != null and teacherName != ''">
                and t.teacher_name = #{teacherName,jdbcType=VARCHAR}
            </if>
            <if test="teacherSex != null and teacherSex != ''">
                and t.teacher_sex = #{teacherSex,jdbcType=VARCHAR}
            </if>
            <if test="className != null and className != ''">
                and c.class_name = #{className,jdbcType=VARCHAR}
            </if>
        </where>
    </select>

    <!--插入教师-->
    <insert id="insert" parameterType="com.backend.entity.po.Teacher">
        insert into teacher
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="teacherId != null and teacherId != ''">
                teacher_id ,
            </if>
            <if test="teacherName != null and teacherName != ''">
                teacher_name,
            </if>
            <if test="teacherSex != null and teacherSex != ''">
                teacher_sex ,
            </if>
            <if test="classId != null and classId != ''">
                class_id,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="teacherId != null and teacherId != ''">
                #{teacherId,jdbcType=VARCHAR},
            </if>
            <if test="teacherName != null and teacherName != ''">
                #{teacherName,jdbcType=VARCHAR},
            </if>
            <if test="teacherSex != null and teacherSex != ''">
                #{teacherSex,jdbcType=VARCHAR},
            </if>
            <if test="classId != null and classId != ''">
                #{classId,jdbcType=VARCHAR}
            </if>
        </trim>
    </insert>


    <!--更新教师-->
    <update id="update" parameterType="com.backend.entity.po.Teacher">
        update teacher
        <set>
            <if test="teacherId != null and teacherId != ''">
                teacher_id = #{teacherId,jdbcType=VARCHAR},
            </if>
            <if test="teacherName != null and teacherName != ''">
                teacher_name = #{teacherName,jdbcType=VARCHAR},
            </if>
            <if test="teacherSex != null and teacherSex != ''">
                teacher_sex = #{teacherSex,jdbcType=VARCHAR},
            </if>
            <if test="classId != null and classId != ''">
                class_id = #{classId,jdbcType=VARCHAR}
            </if>
        </set>
        where teacher_id = #{teacherId,jdbcType=VARCHAR}
    </update>

    <delete id="delete" parameterType="java.lang.String">
        delete
        from teacher
        where teacher_id = #{teacherId,jdbcType=VARCHAR}
    </delete>


</mapper>