黑马Tlias JavaWeb后台管理系统 11 后端实战班级管理学生管理

我这也

已于 2025-04-03 11:44:02 修改

阅读量1.3k

点赞数 12

CC 4.0 BY-SA版权

分类专栏：黑马 JavaWeb Tlias后台管理系统文章标签：状态模式

于 2025-01-06 16:51:30 首次发布

原文链接：https://heuqqdmbyk.feishu.cn/wiki/NRjLwaRqDiEiFVkHLWGct6zFnNg

黑马 JavaWeb Tlias后台管理系统专栏收录该内容

12 篇文章

订阅专栏

数据准备

在数据库中，创建学生表 clazz , student

create table clazz(
    id   int unsigned primary key auto_increment comment 'ID,主键',
    name  varchar(30) not null unique  comment '班级名称',
    room  varchar(20) comment '班级教室',
    begin_date date not null comment '开课时间',
    end_date date not null comment '结课时间',
    master_id int unsigned null comment '班主任ID, 关联员工表ID',
    subject tinyint unsigned not null comment '学科, 1:java, 2:前端, 3:大数据, 4:Python, 5:Go, 6: 嵌入式',
    create_time datetime  comment '创建时间',
    update_time datetime  comment '修改时间'
)comment '班级表';

INSERT INTO clazz VALUES (1,'JavaEE就业163期','212','2024-04-30','2024-06-29',10,1,'2024-06-01 17:08:23','2024-06-01 17:39:58'),
    (2,'前端就业90期','210','2024-07-10','2024-01-20',3,2,'2024-06-01 17:45:12','2024-06-01 17:45:12'),
    (3,'JavaEE就业165期','108','2024-06-15','2024-12-25',6,1,'2024-06-01 17:45:40','2024-06-01 17:45:40'),
    (4,'JavaEE就业166期','105','2024-07-20','2024-02-20',20,1,'2024-06-01 17:46:10','2024-06-01 17:46:10'),
    (5,'大数据就业58期','209','2024-08-01','2024-02-15',7,3,'2024-06-01 17:51:21','2024-06-01 17:51:21'),
    (6,'JavaEE就业167期','325','2024-11-20','2024-05-10',36,1,'2024-11-15 11:35:46','2024-12-13 14:31:24');


create table student(
  id int unsigned primary key auto_increment comment 'ID,主键',
  name varchar(10)  not null comment '姓名',
  no char(10)  not null unique comment '学号',
  gender tinyint unsigned  not null comment '性别, 1: 男, 2: 女',
  phone  varchar(11)  not null unique comment '手机号',
  id_card  char(18)  not null unique comment '身份证号',
  is_college tinyint unsigned  not null comment '是否来自于院校, 1:是, 0:否',
  address  varchar(100)  comment '联系地址',
  degree  tinyint unsigned  comment '最高学历, 1:初中, 2:高中, 3:大专, 4:本科, 5:硕士, 6:博士',
  graduation_date date comment '毕业时间',
  clazz_id  int unsigned not null comment '班级ID, 关联班级表ID',
  violation_count tinyint unsigned default '0' not null comment '违纪次数',
  violation_score tinyint unsigned default '0' not null comment '违纪扣分',
  create_time  datetime  comment '创建时间',
  update_time  datetime  comment '修改时间'
) comment '学员表';


INSERT INTO student VALUES (1,'段誉','2022000001',1,'18800000001','110120000300200001',1,'北京市昌平区建材城西路1号',1,'2021-07-01',2,0,0,'2024-11-14 21:22:19','2024-11-15 16:20:59'),
    (2,'萧峰','2022000002',1,'18800210003','110120000300200002',1,'北京市昌平区建材城西路2号',2,'2022-07-01',1,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (3,'虚竹','2022000003',1,'18800013001','110120000300200003',1,'北京市昌平区建材城西路3号',2,'2024-07-01',1,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (4,'萧远山','2022000004',1,'18800003211','110120000300200004',1,'北京市昌平区建材城西路4号',3,'2024-07-01',1,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (5,'阿朱','2022000005',2,'18800160002','110120000300200005',1,'北京市昌平区建材城西路5号',4,'2020-07-01',1,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (6,'阿紫','2022000006',2,'18800000034','110120000300200006',1,'北京市昌平区建材城西路6号',4,'2021-07-01',2,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (7,'游坦之','2022000007',1,'18800000067','110120000300200007',1,'北京市昌平区建材城西路7号',4,'2022-07-01',2,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (8,'康敏','2022000008',2,'18800000077','110120000300200008',1,'北京市昌平区建材城西路8号',5,'2024-07-01',2,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (9,'徐长老','2022000009',1,'18800000341','110120000300200009',1,'北京市昌平区建材城西路9号',3,'2024-07-01',2,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (10,'云中鹤','2022000010',1,'18800006571','110120000300200010',1,'北京市昌平区建材城西路10号',2,'2020-07-01',2,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (11,'钟万仇','2022000011',1,'18800000391','110120000300200011',1,'北京市昌平区建材城西路11号',4,'2021-07-01',1,0,0,'2024-11-14 21:22:19','2024-11-15 16:21:24'),
    (12,'崔百泉','2022000012',1,'18800000781','110120000300200018',1,'北京市昌平区建材城西路12号',4,'2022-07-05',3,6,17,'2024-11-14 21:22:19','2024-12-13 14:33:58'),
    (13,'耶律洪基','2022000013',1,'18800008901','110120000300200013',1,'北京市昌平区建材城西路13号',4,'2024-07-01',2,0,0,'2024-11-14 21:22:19','2024-11-15 16:21:21'),
    (14,'天山童姥','2022000014',2,'18800009201','110120000300200014',1,'北京市昌平区建材城西路14号',4,'2024-07-01',1,0,0,'2024-11-14 21:22:19','2024-11-15 16:21:17'),
    (15,'刘竹庄','2022000015',1,'18800009401','110120000300200015',1,'北京市昌平区建材城西路15号',3,'2020-07-01',4,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (16,'李春来','2022000016',1,'18800008501','110120000300200016',1,'北京市昌平区建材城西路16号',4,'2021-07-01',4,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (17,'王语嫣','2022000017',2,'18800007601','110120000300200017',1,'北京市昌平区建材城西路17号',2,'2022-07-01',4,0,0,'2024-11-14 21:22:19','2024-11-14 21:22:19'),
    (18,'郑成功','2024001101',1,'13309092345','110110110110110110',0,'北京市昌平区回龙观街道88号',5,'2021-07-01',3,2,7,'2024-11-15 16:26:18','2024-11-15 16:40:10');

表结构关系

实体类Clazz：

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Clazz {
    private Integer id; //ID
    private String name; //班级名称
    private String room; //班级教室
    private LocalDate beginDate; //开课时间
    private LocalDate endDate; //结课时间
    private Integer masterId; //班主任
    private Integer subject; //学科
    private LocalDateTime createTime; //创建时间
    private LocalDateTime updateTime; //修改时间

    private String masterName; //班主任姓名
    private String status; //班级状态 - 未开班 , 在读 , 已结课
}

实体类Student：

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private Integer id; //ID
    private String name; //姓名
    private String no; //序号
    private Integer gender; //性别 , 1: 男 , 2 : 女
    private String phone; //手机号
    private String idCard; //身份证号
    private Integer isCollege; //是否来自于院校, 1: 是, 0: 否
    private String address; //联系地址
    private Integer degree; //最高学历, 1: 初中, 2: 高中 , 3: 大专 , 4: 本科 , 5: 硕士 , 6: 博士
    private LocalDate graduationDate; //毕业时间
    private Integer clazzId; //班级ID
    private Short violationCount; //违纪次数
    private Short violationScore; //违纪扣分
    private LocalDateTime createTime; //创建时间
    private LocalDateTime updateTime; //修改时间

    private String clazzName;//班级名称
}

需求：班级管理

条件分页查询接口

参照接口文档 **班级管理** -> **班级列表查询**

注意：班级状态，显示为：**未开班、已结课、在读中**这三种。

如果：

当前时间 > 结课时间：状态未 已结课。
当前时间 < 开课时间：状态未 未开班。
否则，就是 在读中。

查询所有员工接口

那其实，对于培训机构来说，班主任就是这个企业的员工。所以，班主任下拉列表中展示的就是所有的员工数据。

参照接口文档 **员工管理** -> **查询全部员工**

新增班级信息接口

参照接口文档 **班级管理** -> **添加班级**

根据ID查询班级接口

参照接口文档 **班级管理** -> **根据ID查询**

修改班级信息接口

参照接口文档 **班级管理** -> **修改班级**

删除班级信息接口

参照接口文档 **班级管理** -> **删除班级**

注意：在页面原型中，要求如果该班级下关联的有学生，是不允许删除的，并提示错误信息：“对不起, 该班级下有学生, 不能直接删除”。 (提示：可以通过自定义异常 + 全局异常处理器实现)

到此，关于班级管理的增删改查功能，我们就已经全部实现了。

代码实现

Controller

package com.itheima.controller;

import com.itheima.pojo.Clazz;
import com.itheima.pojo.ClazzQueryParam;
import com.itheima.pojo.PageResult;
import com.itheima.pojo.Result;
import com.itheima.service.ClazzService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@Slf4j
@RequestMapping("/clazzs")
@RestController
public class ClazzController {

    @Autowired
    private ClazzService clazzService;

    //分页条件查询
    @GetMapping
    public Result page(ClazzQueryParam clazzQueryParam) {
        log.info("查询请求参数：{}", clazzQueryParam);
        PageResult pageResult = clazzService.page(clazzQueryParam);
        return Result.success(pageResult);
    }

    //添加班级
    @PostMapping
    public Result save(@RequestBody Clazz clazz) {
        log.info("请求参数clazz：{}",clazz);
        clazzService.save(clazz);
        return Result.success();
    }

    //根据id查询回显
    @GetMapping("/{id}")
    public Result getInfo(@PathVariable Integer id) {
        log.info("根据id查询班级的详细信息");
        Clazz clazz = clazzService.getInfo(id);
        return Result.success(clazz);
    }

    @PutMapping
    public Result update(@RequestBody Clazz clazz) {
        log.info("修改班级信息，{}",clazz);
        clazzService.update(clazz);
        return Result.success();
    }

    @DeleteMapping("/{id}")
    public Result delete(@PathVariable Integer id) {
        log.info("删除班级：id={}",id);
        clazzService.deleteById(id);
        return Result.success();
    }

    @GetMapping("/list")
    public Result list() {
        log.info("查询所有班级");
        List<Clazz> clazzList = clazzService.list();
        return Result.success(clazzList);
    }
}

Service

package com.itheima.service;


import com.itheima.pojo.Clazz;
import com.itheima.pojo.ClazzQueryParam;
import com.itheima.pojo.PageResult;

import java.util.List;

public interface ClazzService {


    /**
     * 分页查询
     */
    PageResult page(ClazzQueryParam clazzQueryParam);

    /**
     * 添加班级
     */
    void save(Clazz clazz);

    /**
     * 根据ID查询班级的详细信息
     */
    Clazz getInfo(Integer id);

    /**
     * 更新班级信息
     */
    void update(Clazz clazz);

    /**
     * 删除班级信息
     */
    void deleteById(Integer id);

    /**
     * 查询所有班级
     */
    List<Clazz> list();
}

实现类

package com.itheima.service.impl;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.itheima.exception.BusinessException;
import com.itheima.mapper.ClazzMapper;
import com.itheima.mapper.StudentMapper;
import com.itheima.pojo.Clazz;
import com.itheima.pojo.ClazzQueryParam;
import com.itheima.pojo.PageResult;
import com.itheima.service.ClazzService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.List;


@Service
public class ClazzServiceImpl implements ClazzService {

    @Autowired
    private ClazzMapper clazzMapper;
    @Autowired
    private StudentMapper studentMapper;

    //分页查询
    @Override
    public PageResult page(ClazzQueryParam clazzQueryParam) {
        //1.设置PageHelper分页参数
        PageHelper.startPage(clazzQueryParam.getPage(), clazzQueryParam.getPageSize());
        //2.执行查询
        List<Clazz> clazzList = clazzMapper.list(clazzQueryParam);
        //3.封装分页结果
        Page<Clazz> p = (Page<Clazz>)clazzList;
        return new PageResult(p.getTotal(), p.getResult());
    }

    //新增
    @Override
    public void save(Clazz clazz) {
        //1.补全基础属性
        clazz.setCreateTime(LocalDateTime.now());
        clazz.setUpdateTime(LocalDateTime.now());
        //2.保存班级基本信息
        clazzMapper.insert(clazz);
    }

    //根据ID查询
    @Override
    public Clazz getInfo(Integer id) {
        return clazzMapper.getById(id);
    }

    //更新班级信息
    @Override
    public void update(Clazz clazz) {
        //根据ID更新班级基本信息
        clazz.setUpdateTime(LocalDateTime.now());
        clazzMapper.updateById(clazz);
    }

    @Override
    public void deleteById(Integer id) {
        //1.查询班级下是否有学员
        Integer count = studentMapper.countByClazzId(id);
        if (count > 0) {
            throw new BusinessException("班级下有学员，不能直接删除");
        }
        //2.如果没有，再删除班级信息
        clazzMapper.deleteById(id);
    }

    @Override
    public List<Clazz> list() {
        return clazzMapper.findAll();
    }
}

Mapper

package com.itheima.mapper;

import com.itheima.pojo.Clazz;
import com.itheima.pojo.ClazzQueryParam;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface ClazzMapper {

    /**
     * 分页动态条件查询
     */
    List<Clazz> list(ClazzQueryParam clazzQueryParam);

    /**
     * 新增班级
     */
    void insert(Clazz clazz);

    /**
     * 根据ID查询班级信息
     */
    Clazz getById(Integer id);

    /**
     * 更新员工基本信息
     */
    void updateById(Clazz clazz);

    /**
     * 删除班级信息
     */
    void deleteById(Integer id);

    /**
     * 查询所有班级
     */
    List<Clazz> findAll();
}

xml

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace = "com.itheima.mapper.ClazzMapper">
    <!--分页条件查询-->
    <select id="list" resultType="com.itheima.pojo.Clazz">
        select c.*, e.name masterName,
            (case when begin_date > now() then '未开班' when now() > end_date then '已结课' else '在读中' end) status
        from clazz as c left join emp e on c.master_id = e.id
        <where>
            <if test="name != null and name != ''">
                c.name like concat('%',#{name},'%')
            </if>
            <if test="begin !=null and end != null">
                and c.end_date between #{begin} and #{end}
            </if>
        </where>
        order by c.update_time desc
    </select>

    <!--新增班级-->
    <insert id="insert">
        insert into clazz(name, room, begin_date, end_date, master_id, subject, create_time, update_time)
        values (#{name},#{room},#{beginDate},#{endDate},#{masterId},#{subject},#{createTime},#{updateTime})
    </insert>

    <!--自定义结果集ResultMap-->
    <resultMap id="clazzResultMap" type="com.itheima.pojo.Clazz">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="room" property="room" />
        <result column="begin_date" property="beginDate" />
        <result column="end_date" property="endDate" />
        <result column="master_id" property="masterId" />
        <result column="subject" property="subject" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
    </resultMap>

    <!--根据ID查询班级的详细信息-->
    <select id="getById" resultMap="clazzResultMap">
        select c.*
        from clazz c
        where c.id = #{id}
    </select>

    <!--根据ID更新班级信息-->
    <update id="updateById">
        update clazz
        <set>
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="room != null and name != ''">room = #{room},</if>
            <if test="beginDate != null">begin_date= #{beginDate},</if>
            <if test="endDate != null">end_date = #{endDate},</if>
            <if test="masterId != null">master_id = #{masterId},</if>
            <if test="subject != null">subject = #{subject},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </set>
        where id = #{id}
    </update>

    <!--根据ID更新班级信息-->
    <delete id="deleteById">
        delete from clazz where id = #{id}
    </delete>

    <!--查询所有班级-->
    <select id="findAll" resultType="com.itheima.pojo.Clazz">
        select name,room,begin_date,end_date,master_id,subject,create_time,update_time
        from clazz;
    </select>

</mapper>

需求：学员管理

需要开发如下几个接口，建议按照如下顺序开发接口：

查询所有班级接口

参照接口文档 **班级管理** -> **查询所有班级**

在新增学员的时候，要展示出所有的班级信息。

条件分页查询接口

参照接口文档 **学员管理** -> **学员列表查询**

新增学生信息接口

参照接口文档 **学员管理** -> **添加学员**

根据ID查询学生接口

参照接口文档 **学员管理** -> **根据ID查询学员**

修改学生信息接口

参照接口文档 **学员管理** -> **修改学员**

删除学生信息接口

参照接口文档 **学员管理** -> **删除学员**

违纪处理接口

参照接口文档 **学员管理** -> **违纪处理**

违纪处理一次，需要将违纪次数+1，违纪扣分+前端输入的分数。

到此，关于学生管理的增删改查功能，我们就已经全部实现了。

代码实现

Controller

package com.itheima.controller;

import com.itheima.pojo.PageResult;
import com.itheima.pojo.Result;
import com.itheima.pojo.Student;
import com.itheima.service.StudentService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;

@Slf4j
@RestController
@RequestMapping("/students")
public class StudentController {

    @Autowired
    private StudentService studentService;

    /**
     * 添加学生
     */
    @PostMapping
    public Result save(@RequestBody Student student){
        studentService.save(student);
        return Result.success();
    }

    /**
     * 条件分页查询
     */
    @GetMapping
    public Result page(String name ,
                       Integer degree,
                       Integer clazzId,
                       @RequestParam(defaultValue = "1") Integer page ,
                       @RequestParam(defaultValue = "10") Integer pageSize){
        PageResult pageResult = studentService.page(name,degree,clazzId,page,pageSize);
        return Result.success(pageResult);
    }

    /**
     * 根据ID查询学生信息
     */
    @GetMapping("/{id}")
    public Result getInfo(@PathVariable Integer id){
        Student student = studentService.getInfo(id);
        return Result.success(student);
    }

    /**
     * 修改学生信息
     */
    @PutMapping
    public Result update(@RequestBody Student student){
        studentService.update(student);
        return Result.success();
    }

    /**
     * 删除学生信息
     */
    @DeleteMapping("/{ids}")
    public Result delete(@PathVariable List<Integer> ids){
        studentService.delete(ids);
        return Result.success();
    }

    /**
     * 违纪处理
     */
    @PutMapping("/violation/{id}/{score}")
    public Result violationHandle(@PathVariable Integer id , @PathVariable Integer score){
        studentService.violationHandle(id, score);
        return Result.success();
    }

}

Service

package com.itheima.service;

import com.itheima.pojo.ClazzCountOption;
import com.itheima.pojo.PageResult;
import com.itheima.pojo.Student;
import java.util.List;
import java.util.Map;

public interface StudentService {

    /**
     * 添加学生信息
     */
    void save(Student student);

    /**
     * 条件分页查询
     */
    PageResult page(String name, Integer degree, Integer clazzId, Integer page, Integer pageSize);

    /**
     * 根据ID查询学生信息
     */
    Student getInfo(Integer id);

    /**
     * 修改学生信息
     */
    void update(Student student);

    /**
     * 删除学生信息
     */
    void delete(List<Integer> ids);

    /**
     * 违纪处理
     */
    void violationHandle(Integer id, Integer score);

}

实现类

package com.itheima.service.impl;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.itheima.mapper.StudentMapper;
import com.itheima.pojo.ClazzCountOption;
import com.itheima.pojo.PageResult;
import com.itheima.pojo.Student;
import com.itheima.service.StudentService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentMapper studentMapper;

    @Override
    public void save(Student student) {
        student.setCreateTime(LocalDateTime.now());
        student.setUpdateTime(LocalDateTime.now());
        studentMapper.insert(student);
    }


    @Override
    public PageResult page(String name, Integer degree, Integer clazzId, Integer page, Integer pageSize) {
        PageHelper.startPage(page, pageSize);

        List<Student> studentList = studentMapper.list(name,degree,clazzId);
        Page<Student> p = (Page<Student>) studentList;

        return new PageResult(p.getTotal(), p.getResult());
    }


    @Override
    public Student getInfo(Integer id) {
        return studentMapper.getById(id);
    }


    @Override
    public void update(Student student) {
        student.setUpdateTime(LocalDateTime.now());
        studentMapper.update(student);
    }


    @Override
    public void delete(List<Integer> ids) {
        studentMapper.delete(ids);
    }


    @Override
    public void violationHandle(Integer id, Integer score) {
        studentMapper.updateViolation(id, score);
    }

}

Mapper

package com.itheima.mapper;

import com.itheima.pojo.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface StudentMapper {

    /**
     * 添加学生
     */
    @Insert("insert into student(name, no, gender, phone,id_card, is_college, address, degree, graduation_date,clazz_id, create_time, update_time) VALUES " +
            "(#{name},#{no},#{gender},#{phone},#{idCard},#{isCollege},#{address},#{degree},#{graduationDate},#{clazzId},#{createTime},#{updateTime})")
    void insert(Student student);

    /**
     * 动态条件查询
     */
    List<Student> list(String name, Integer degree, Integer clazzId);

    /**
     * 根据ID查询学生信息
     */
    @Select("select * from student where id = #{id}")
    Student getById(Integer id);

    /**
     * 修改学生信息
     */
    void update(Student student);

    /**
     * 批量删除学生信息
     */
    void delete(List<Integer> ids);

    /**
     * 违纪处理
     */
    @Update("update student set violation_count = violation_count + 1 , violation_score = violation_score + #{score} , update_time = now() where id = #{id}")
    void updateViolation(Integer id, Integer score);

    /**
     * 统计班级人数
     */
    @Select("select c.name cname , count(s.id) scount from clazz c  left join student s on s.clazz_id = c.id group by c.name order by count(s.id) desc ")
    List<Map<String,Object>> getStudentCount();

    /**
     * 统计学员学历
     */
    @MapKey("name")
    List<Map> countStudentDegreeData();



    @Select("select count(*) from student where clazz_id = #{id}")
    Integer countByClazzId(Integer id);

    /**
     * 批量插入
     */
    void insertBatch(List<Student> studentList);
}

xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.StudentMapper">

    <!--批量插入学员信息-->
    <insert id="insertBatch">
        insert into student(name, no, gender, phone,id_card, is_college, address, degree, graduation_date,clazz_id, create_time, update_time)
        VALUES
        <foreach collection="studentList" item="s" separator=",">
            (#{s.name},#{s.no},#{s.gender},#{s.phone},#{s.idCard},#{s.isCollege},#{s.address},#{s.degree},#{s.graduationDate},
            #{s.clazzId},#{s.createTime},#{s.updateTime})
        </foreach>
    </insert>


    <!-- 修改学生信息 -->
    <update id="update">
        update student
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="no != null and no != ''">
                no = #{no},
            </if>
            <if test="gender != null">
                gender = #{gender},
            </if>
            <if test="phone != null and phone != ''">
                phone = #{phone},
            </if>
            <if test="idCard != null and idCard != ''">
                id_card = #{idCard},
            </if>
            <if test="isCollege != null and isCollege != ''">
                is_college = #{isCollege},
            </if>
            <if test="address != null and address != ''">
                address = #{address},
            </if>
            <if test="graduationDate != null">
                graduation_date = #{graduationDate},
            </if>
            <if test="degree != null">
                degree = #{degree},
            </if>
            <if test="violationCount != null">
                violation_count = #{violationCount},
            </if>
            <if test="violationScore != null">
                violation_score = #{violationScore},
            </if>
            <if test="clazzId != null">
                clazz_id = #{clazzId},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime}
            </if>
        </set>
        where id = #{id}
    </update>

    <!--批量删除学生信息-->
    <delete id="delete">
        delete from student where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

    <!-- 动态条件查询 -->
    <select id="list" resultType="com.itheima.pojo.Student">
        select s.*, c.name clazzName from student s left join clazz c on s.clazz_id = c.id
        <where>
            <if test="name != null and name != ''">
                s.name like concat('%',#{name},'%')
            </if>
            <if test="degree != null">
                and s.degree = #{degree}
            </if>
            <if test="clazzId != null">
                and s.clazz_id = #{clazzId}
            </if>
        </where>
        order by s.update_time desc
    </select>


    <!--学员学历统计-->
    <select id="countStudentDegreeData" resultType="java.util.Map">
        select
        (case degree
        when 1 then '初中'
        when 2 then '高中'
        when 3 then '大专'
        when 4 then '本科'
        when 5 then '硕士'
        when 6 then '博士'
        else '其他' end)  name,
        count(*) value
        from student group by degree
        order by degree
    </select>



</mapper>

需求：学员信息统计

需要开发如下几个接口：

班级人数统计接口开发：参照接口文档 **数据统计** -> **班级人数统计**
学员学历信息统计接口开发：参照接口文档 **数据统计** -> **学员学历统计**

pojo

package com.itheima.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

/**
 * 班级人数统计
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ClazzCountOption {
    private List clazzList; //职位列表
    private List dataList; //人数列表
}

controller

package com.itheima.controller;

import com.itheima.pojo.ClazzCountOption;
import com.itheima.pojo.JobOption;
import com.itheima.pojo.Result;
import com.itheima.service.ReportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@Slf4j
@RequestMapping("/report")
@RestController
public class ReportController {

    @Autowired
    private ReportService reportService;

    //统计各个职位的员工人数
    @GetMapping("/empJobData")
    public Result getEmpJobData() {
        log.info("统计各个职位的员工人数");
        JobOption jobOption = reportService.getEmpJobData();
        return Result.success(jobOption);
    }

    //统计员工性别信息
    @GetMapping("/empGenderData")
    public Result getEmpGenderData() {
        log.info("统计员工性别信息");
        List<Map> genderList = reportService.getEmpGenderData();
        return Result.success(genderList);
    }

    /**
     * 统计学员的学历信息
     */
    @GetMapping("/studentDegreeData")
    public Result getStudentDegreeData(){
        log.info("统计学员的学历信息");
        List<Map> dataList = reportService.getStudentDegreeData();
        return Result.success(dataList);
    }

    /**
     * 班级人数统计
     */
    @GetMapping("/studentCountData")
    public Result getStudentCountData(){
        log.info("班级人数统计");
        ClazzCountOption clazzCountOption = reportService.getStudentCountData();
        return Result.success(clazzCountOption);
    }
}

service

package com.itheima.service;

import com.itheima.pojo.ClazzCountOption;
import com.itheima.pojo.JobOption;

import java.util.List;
import java.util.Map;

public interface ReportService {
    /**
     * 统计各个职位的员工人数
     * @return
     */
    JobOption getEmpJobData();

    /**
     * 统计员工性别信息
     */
    List<Map> getEmpGenderData();

    /**
     * 统计学历人数
     */
    List<Map> getStudentDegreeData();

    /**
     * 统计班级人数
     */
    ClazzCountOption getStudentCountData();

}

实现类

package com.itheima.service.impl;

import com.itheima.mapper.EmpMapper;
import com.itheima.mapper.StudentMapper;
import com.itheima.pojo.ClazzCountOption;
import com.itheima.pojo.JobOption;
import com.itheima.service.ReportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.Map;

@Service
public class ReportServiceImpl implements ReportService {

    @Autowired
    private EmpMapper empMapper;
    @Autowired
    private StudentMapper studentMapper;

    @Override
    public JobOption getEmpJobData() {
        List<Map<String, Object>> list = empMapper.countEmpJobData();
        List<Object> jobList = list.stream().map(dataMap -> dataMap.get("pos")).toList();
        List<Object> dataList = list.stream().map(dataMap -> dataMap.get("total")).toList();
        return new JobOption(jobList,dataList);

    }

    @Override
    public List<Map> getEmpGenderData() {
        return empMapper.countEmpGenderData();
    }

    @Override
    public List<Map> getStudentDegreeData() {
        return studentMapper.countStudentDegreeData();
    }

    @Override
    public ClazzCountOption getStudentCountData() {
        List<Map<String, Object>> countList = studentMapper.getStudentCount();
        if(!CollectionUtils.isEmpty(countList)){
            List<Object> clazzList = countList.stream().map(map -> {
                return map.get("cname");
            }).toList();

            List<Object> dataList = countList.stream().map(map -> {
                return map.get("scount");
            }).toList();

            return new ClazzCountOption(clazzList, dataList);
        }
        return null;
    }
}

Mapper

    /**
     * 统计班级人数
     */
    @Select("select c.name cname , count(s.id) scount from clazz c  left join student s on s.clazz_id = c.id group by c.name order by count(s.id) desc ")
    List<Map<String,Object>> getStudentCount();

    /**
     * 统计学员学历
     */
    @MapKey("name")
    List<Map> countStudentDegreeData();

<!--学员学历统计-->
    <select id="countStudentDegreeData" resultType="java.util.Map">
        select
        (case degree
        when 1 then '初中'
        when 2 then '高中'
        when 3 then '大专'
        when 4 then '本科'
        when 5 then '硕士'
        when 6 then '博士'
        else '其他' end)  name,
        count(*) value
        from student group by degree
        order by degree
    </select>