职位统计(柱状图)
准备工作:
这里我们的 SQL 语句要用到一个新函数 case;
select (case
when job = 1 then '班主任'
when job = 2 then '讲师'
when job = 3 then '学工主管'
when job = 4 then '教研主管'
when job = 5 then '咨询师'
else '其他' end) pos,
count(*) num
from emp group by job order by num;
创立一个实体类,用来封装返回信息,注意这里的属性名不能写错,不然会出BUG
package com.itheima.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class DeptNumberList {
private List jobList;
private List dataList;
}
首先来看 Controller 层,因为这里的请求路径和之前不一样,所以我们要新建一个类
package com.itheima.controller;
import com.itheima.pojo.DeptNumberList;
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;
@RestController
@RequestMapping("/report")
@Slf4j
public class ReportController {
@Autowired
private ReportService reportService;
@GetMapping("/empJobData")
public Result getDeptNumber(){
log.info("统计各个职位的员工人数");
DeptNumberList deptNumberList = reportService.getDeptList();
return Result.success(deptNumberList);
}
}
然后来看 Service 实现类
package com.itheima.service.impl;
import com.itheima.mapper.ReportMapper;
import com.itheima.pojo.DeptNumberList;
import com.itheima.service.ReportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
@Service
public class ReportServiceImpl implements ReportService {
@Autowired
private ReportMapper reportMapper;
@Override
public DeptNumberList getDeptList() {
List<Map<String, Object>> list = reportMapper.countDept();
List<Object> jobList = list.stream().map(dataMap -> dataMap.get("pos")).toList();
List<Object> dataList = list.stream().map(dataMap -> dataMap.get("num")).toList();
return (new DeptNumberList(jobList, dataList));
}
}
最后是 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.itheima.mapper.ReportMapper">
<select id="countDept" resultType="java.util.Map">
select (case
when job = 1 then '班主任'
when job = 2 then '讲师'
when job = 3 then '学工主管'
when job = 4 then '教研主管'
when job = 5 then '咨询师'
else '其他' end) pos,
count(*) num
from emp group by job order by num;
</select>
</mapper>
性别统计(饼状图)
@GetMapping("/empGenderData")
public Result getGenderData(){
log.info("统计不同性别的人数");
List<Map> genderList = reportService.getGenderData();
return Result.success(genderList);
}
@Override
public List<Map> getGenderData() {
return reportMapper.getGenderData();
}
<select id="getGenderData" resultType="java.util.Map">
select
if(gender = 1, '男性员工', '女性员工') name,
count(*) value
from emp group by gender
</select>
最后结果: