Prism代码语法高亮

Day11 后端Web实战：班级、学员，统计

需求

班级管理：班级列表查询、删除班级、添加班级、修改班级。
学员管理：学员列表查询、删除学员、添加学员、修改学员、违纪处理。
数据统计：班级人数统计、学员学历统计。
注意：所有的功能全部严格，根据接口文档进行开发，并进行前后端联调。

实战说明

1.以小组为单位，进行实战，每一个都需要完成实战需求。出现问题，务必自己先思考，然后组内讨论交流解决。
2.每一个组，需要选一名组员上来演示所完成的系统功能。
3.演示的时候，可以介绍一下，自己在实现这个功能的过程中，你自己的实现思路和方案。
4.自己实现这一块功能过程中遇到的典型的Bug，产生Bug的原因，以及解决方案。

1.数据准备
2.需求：班级管理
- 2.1条件分页查询接口
- 2.2查询所有员工接口
- 2.3新增班级信息接口
- 2.4根据ID查询班级接口
- 2.5修改班级信息接口
- 2.6删除班级信息接口
3.需求：学员管理
- 3.1查询所有班级接口
- .2条件分页查询接口
- 3.3新增学生信息接口
- 3.4根据ID查询学生接口
- 3.5修改学生信息接口
- 3.6删除学生信息接口
- 3.7违纪处理接口
4.需求：学员信息统计
5.需求：功能完善

1.数据准备

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 '学员表';

-- 第1行同学换成自己的姓名
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');

pojo基础代码

① cn/zjy/pojo/Clazz.java

@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; //班级状态 - 未开班 , 在读 , 已结课
}

② cn/zjy/pojo/Student.java

@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;//班级名称
}

③ cn/zjy/pojo/ClazzCountOption.java

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

2. 需求：班级管理

2.1 条件分页查询接口

2.2 查询所有员工接口

2.3 新增班级信息接口

2.4 根据ID查询班级接口

2.5 修改班级信息接口

2.6 删除班级信息接口

调用关系：

前端 --> ClazzController -->ClazzService(ClazzServiceImpl) --> ClazzMapper.java --> ClazztMapper.xml --> DB

cn/zjy/controller/ClazzController.java 6个Api接口

@RestController
@RequestMapping("/clazzs")
public class ClazzController {
    @Autowired
    private ClazzService clazzService;

    /**
     * 新增班级
     */
    @PostMapping
    public Result save(@RequestBody Clazz clazz){
        clazzService.save(clazz);
        return Result.success();
    }

    /**
     * 条件分页查询班级
     */
    @GetMapping
    public Result page(String name ,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin ,
                       @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end,
                       @RequestParam(defaultValue = "1") Integer page ,
                       @RequestParam(defaultValue = "10")Integer pageSize){
        PageResult pageResult = clazzService.page(name , begin , end , page , pageSize);
        return Result.success(pageResult);
    }

    /**
     * 根据ID查询班级详情
     */
    @GetMapping("/{id}")
    public Result getInfo(@PathVariable Integer id){
        Clazz clazz = clazzService.getInfo(id);
        return Result.success(clazz);
    }

    /**
     * 更新班级信息
     */
    @PutMapping
    public Result update(@RequestBody Clazz clazz){
        clazzService.update(clazz);
        return Result.success();
    }

    /**
     * 根据ID删除班级
     */
    @DeleteMapping("/{id}")
    public Result delete(@PathVariable Integer id){
        clazzService.deleteById(id);
        return Result.success();
    }

    /**
     * 查询全部班级
     */
    @GetMapping("/list")
    public Result findAll(){
        List<Clazz> clazzList = clazzService.findAll();
        return Result.success(clazzList);
    }
}

cn/zjy/service/ClazzService.java

public interface ClazzService {
    /**
     * 添加班级信息
     * @param clazz
     */
    void save(Clazz clazz);

    /**
     * 条件分页查询
     * @param name
     * @param begin
     * @param end
     * @param page
     * @param pageSize
     * @return
     */
    PageResult page(String name, LocalDate begin, LocalDate end, Integer page, Integer pageSize);

    /**
     * 根据ID查询班级详情
     * @param id
     * @return
     */
    Clazz getInfo(Integer id);

    /**
     * 修改班级信息
     * @param clazz
     */
    void update(Clazz clazz);

    /**
     * 删除班级
     * @param id
     */
    void deleteById(Integer id);

    /**
     * 查询全部班级
     * @return
     */
    List<Clazz> findAll();
}

cn/zjy/service/impl/ClazzServiceImpl.java

@Service
public class ClazzServiceImpl implements ClazzService {

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

    // 添加
    @Override
    public void save(Clazz clazz) {
        clazz.setCreateTime(LocalDateTime.now());
        clazz.setUpdateTime(LocalDateTime.now());
        clazzMapper.insert(clazz);
    }

    // 条件分页查询
    @Override
    public PageResult page(String name, LocalDate begin, LocalDate end, Integer page, Integer pageSize) {
        PageHelper.startPage(page, pageSize);

        List<Clazz> dataList = clazzMapper.list(name,begin,end);
        Page<Clazz> p = (Page<Clazz>) dataList;

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

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

    // 修改
    @Override
    public void update(Clazz clazz) {
        clazz.setUpdateTime(LocalDateTime.now());
        clazzMapper.update(clazz);
    }

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

    // 查询全部
    @Override
    public List<Clazz> findAll() {
        return clazzMapper.findAll();
    }
}

cn/zjy/exception/BusinessException.java () 5. 功能完善

用于 ClazzServiceImpl.java throw new BusinessException("班级下有学员, 不能直接删除~");

public class BusinessException extends RuntimeException{
    public BusinessException(String message) {
        super(message);
    }
}

cn/zjy/mapper/ClazzMapper.java

public interface ClazzMapper {
    /**
     * 添加班级信息
     */
    @Insert("insert into clazz VALUES (null,#{name},#{room},#{beginDate},#{endDate},#{masterId}, #{subject},#{createTime},#{updateTime})")
    void insert(Clazz clazz);

    /**
     * 动态条件查询
     */
    List<Clazz> list(String name, LocalDate begin, LocalDate end);

    /**
     * 根据ID查询班级详情
     */
    @Select("select * from clazz where  id = #{id}")
    Clazz getInfo(Integer id);

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

    /**
     * 根据ID删除班级
     */
    @Delete("delete from clazz where id = #{id}")
    void deleteById(Integer id);

    /**
     * 查询全部班级
     */
    @Select("select * from clazz")
    List<Clazz> findAll();
}

src/main/resources/cn/zjy/mapper/ClazzMapper.xml 2个动态sql

<?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="cn.zjy.mapper.ClazzMapper">

    <!--动态更新班级信息-->
    <update id="update">
        update clazz
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="room != null and room != ''">
                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>


    <select id="list" resultType="cn.zjy.pojo.Clazz">
        select
            c.*,
            e.name masterName,
            (case when begin_date &gt; now() then '未开班' when now() &gt; end_date then '已结课' else  '在读中' end ) status
        from clazz 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>


</mapper>

3. 需求：学员管理

3.1 查询所有班级接口

3.2 条件分页查询接口

3.3 新增学生信息接口

3.4 根据ID查询学生接口

3.5 修改学生信息接口

3.6 删除学生信息接口

3.7 违纪处理接口

调用关系：

前端 --> StudentController --> StudentService(StudentServiceImpl) --> StudentMapper.java --> StudentMapper.xml --> DB

cn/zjy/controller/StudentController.java 除查询所有班级外的6个API接口

@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();
    }

}

cn/zjy/service/impl/StudentServiceImpl.java

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);

}

cn/zjy/service/impl/StudentServiceImpl.java

@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());
    }

    // 根据ID查询
    @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);
    }

}

cn/zjy/mapper/StudentMapper.java

@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);

}

src/main/resources/cn/zjy/mapper/StudentMapper.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="cn.zjy.mapper.StudentMapper">

    <!-- 修改学生信息 -->
    <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="cn.zjy.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>

4.需求：学员信息统计

调用关系：

前端 --> ReportController --> ReportService(ReportServiceImpl) --> StudentMapper.java --> StudentMapper.xml --> DB

cn/zjy/controller/ReportController.java 添加下列2个Api接口

    /**
     * 统计学员的学历信息
     */
    @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);
    }

cn/zjy/service/ReportService.java 添加下列2个接口方法

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

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

cn/zjy/service/impl/ReportServiceImpl.java 添加下列2个实现方法

    @Autowired
    private StudentMapper studentMapper;

    /**
     * 获取学生学历数据
     * @return
     */
    @Override
    public List<Map> getStudentDegreeData() {
        return studentMapper.countStudentDegreeData();
    }

    /**
     * 获取学生数量统计数据
     * @return
     */
    @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;
    }

cn/zjy/mapper/StudentMapper.java 添加下列2个方法

@Mapper
public interface StudentMapper {

    /**
     * 统计班级人数
     */
    @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();


}

src/main/resources/cn/zjy/mapper/StudentMapper.xml 添加下列1个方法

<?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="cn.zjy.mapper.StudentMapper">


    <!--学员学历统计-->
    <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>

5.需求：功能完善

删除部门时：如果部门下有员工，则不允许删除该部门，并给前端提示错误信息：对不起，当前部门下有员工，不能直接删除！

cn/zjy/service/impl/ClazzServiceImpl.java

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

cn/zjy/exception/BusinessException.java (见班级管理)

用于 ClazzServiceImpl.java throw new BusinessException("班级下有学员, 不能直接删除~");

  public class BusinessException extends RuntimeException{
      public BusinessException(String message) {
          super(message);
      }
  }

Day11 后端Web实战：班级、学员，统计

需求

实战说明

目录

1.数据准备

pojo基础代码

① cn/zjy/pojo/Clazz.java

② cn/zjy/pojo/Student.java

③ cn/zjy/pojo/ClazzCountOption.java

2. 需求：班级管理

调用关系：

cn/zjy/controller/ClazzController.java 6个Api接口

cn/zjy/service/ClazzService.java

cn/zjy/service/impl/ClazzServiceImpl.java

cn/zjy/exception/BusinessException.java () 5. 功能完善

cn/zjy/mapper/ClazzMapper.java

src/main/resources/cn/zjy/mapper/ClazzMapper.xml 2个动态sql

3. 需求：学员管理

调用关系：

cn/zjy/controller/StudentController.java 除查询所有班级外的6个API接口

cn/zjy/service/impl/StudentServiceImpl.java

cn/zjy/service/impl/StudentServiceImpl.java

cn/zjy/mapper/StudentMapper.java

src/main/resources/cn/zjy/mapper/StudentMapper.xml

4.需求：学员信息统计

调用关系：

cn/zjy/controller/ReportController.java 添加下列2个Api接口

cn/zjy/service/ReportService.java 添加下列2个接口方法

cn/zjy/service/impl/ReportServiceImpl.java 添加下列2个实现方法

cn/zjy/mapper/StudentMapper.java 添加下列2个方法

src/main/resources/cn/zjy/mapper/StudentMapper.xml 添加下列1个方法

5.需求：功能完善

cn/zjy/service/impl/ClazzServiceImpl.java

cn/zjy/exception/BusinessException.java (见班级管理)