业务需求:

从员工表中查询5天之内过生日的人,以及五天之内合同到期的人,返回一个 Map 集合,封装了员工的姓名及还有几天过生日;

Dao 层如下:

@Repository
public interface EmpMapper  extends BaseMapper<Employee> {

	@Select("select * from employee\n" +
            "where DATE_FORMAT(birthday,'%m-%d') >= DATE_FORMAT(now(),'%m-%d')\n" +
            " and DATE_FORMAT(birthday,'%m-%d') <= DATE_FORMAT(date_add(now(), INTERVAL 4 DAY),'%m-%d')")
    List<Employee> birthdayReminder();

    @Select("select * from employee\n" +
            "where DATE_FORMAT(endContract,'%m-%d') >= DATE_FORMAT(now(),'%m-%d')\n" +
            " and DATE_FORMAT(endContract,'%m-%d') <= DATE_FORMAT(date_add(now(), INTERVAL 4 DAY),'%m-%d')")
    List<Employee> contractExpires();

}

第二个需求是从数据库中查询员工各年龄段所占的比例,以及各性别所占的比例;

Controller 层如下:


	@Autowired
	EmpMapper empMapper;

 /** * 查询 5 天之内过生日的人 * * @return */
    @RequestMapping(value = "/birth", method = RequestMethod.GET)
    public RespBean birthdayReminder() {
        List<Employee> employees = empMapper.birthdayReminder();
        List<Map<String, String>> list = new ArrayList<>();
        Date date = new Date();
        Map map = new HashMap();
        if (employees != null) {
            //这里面的查询结果都是最近 5 天之内生日的
            for (Employee employee : employees) {
                int bir = getDay(employee.getBirthday());
                int now = getDay(date);
                map.put("name", employee.getName());
                map.put("day", bir - now);
                list.add(map);
                map.clear();
            }
            return RespBean.ok("查询成功", list);
        }
        return RespBean.error("查询为空", null);
    }

    /** * 检查 5 天之内合同到期的 * * @return */
    @RequestMapping(value = "/contract", method = RequestMethod.GET)
    public RespBean contractExpires() {
        List<Employee> employees = empMapper.contractExpires();
        List<Map<String, String>> list = new ArrayList<>();
        Date date = new Date();
        Map map = new HashMap();
        if (employees != null) {
            //这里面的查询结果都是最近 5 天之内合同到期的
            for (Employee employee : employees) {
                map.put("name", employee.getName());
                map.put("day", getDay(employee.getEndContract()) - getDay(date));
                list.add(map);
                map.clear();
            }
            return RespBean.ok("查询成功", list);
        }
        return RespBean.error("查询为空", null);
    }

    /** * 获取当前日期是该年中的多少天 * * @param date * @return */
    private int getDay(Date date) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        return c.get(Calendar.DAY_OF_YEAR);
    }

    /** * 统计一下性别信息和年龄信息,然后返回给前端绘图 * 性别分为 男,女,未统计; * - 存在 Map 中,键为 man women none ,值为他们所占的比例(均取整数,比如 30 代表 30%), * - 这样前端直接获取值然后按照所占的比例放在饼图中 * 年龄分为 0-30,31-40,41-50,51以上; * - 存在 Map 中,键为 thirty forty fifty older 中,值为它们所占的比例(整数) * - 前端根据键获取值,也就是获取比例,然后绘图展示 * 方法: GET * 返回值: Response,包括 状态信息 和 Map集合,集合中的内容上述内容 */
    @RequestMapping(value = "/genderAndAge", method = RequestMethod.GET)
    public RespBean getGenderAndAgeRatio() {
        //获取所有成员信息
        List<Employee> employees = empMapper.getAllEmployees();
        Map<String, Integer> count;
        //判断为空
        if (employees == null) {
            return RespBean.error("查询为空", null);
        } else {
            count = new HashMap<>();
            count.put("man", 0);
            count.put("woman", 0);
            count.put("none", 0);
            count.put("thirty", 0);
            count.put("forty", 0);
            count.put("fifty", 0);
            count.put("older", 0);
            //遍历统计给 Map 赋值
            for (Employee employee : employees) {
                String gender = employee.getGender();
                //性别计数
                if (gender.equals("男")) {
                    count.put("man", count.get("man") + 1);
                } else if (gender.equals("女")) {
                    count.put("woman", count.get("woman") + 1);
                } else {
                    count.put("none", count.get("none") + 1);
                }
                //求每一个员工的年龄
                int age = getAgeByBirth(employee.getBirthday());
                //年龄计数
                if (age >= 0 && age <= 30) {
                    count.put("thirty", count.get("thirty") + 1);
                } else if (age >= 31 && age <= 40) {
                    count.put("forty", count.get("forty") + 1);
                } else if (age >= 41 && age <= 50) {
                    count.put("fifty", count.get("fifty") + 1);
                } else {
                    count.put("older", count.get("older") + 1);
                }
            }
            //计算比例
            int total = employees.size();
            count.put("man", count.get("man") * 100 / total);
            count.put("woman", count.get("woman") * 100 / total);
            count.put("none", 100 - count.get("man") - count.get("woman"));
            count.put("thirty", count.get("thirty") * 100 / total);
            count.put("forty", count.get("forty") * 100 / total);
            count.put("fifty", count.get("fifty") * 100 / total);
            count.put("older", 100 - count.get("thirty") - count.get("forty") - count.get("fifty"));
            //经过这次循环之后,Map中的计数值就对了,可以返回了
        }
        return RespBean.ok("获取成功", count);
    }

    /** * 根据日期计算年龄 * * @param birthday * @return */
    private static int getAgeByBirth(Date birthday) {
        int age;
        try {
            Calendar now = Calendar.getInstance();
            now.setTime(new Date());// 当前时间

            Calendar birth = Calendar.getInstance();
            birth.setTime(birthday);

            if (birth.after(now)) {//如果传入的时间,在当前时间的后面,返回0岁
                age = 0;
            } else {
                age = now.get(Calendar.YEAR) - birth.get(Calendar.YEAR);
                if (now.get(Calendar.DAY_OF_YEAR) > birth.get(Calendar.DAY_OF_YEAR)) {
                    age += 1;
                }
            }
            return age;
        } catch (Exception e) {//兼容性更强,异常后返回数据
            return 0;
        }
    }