业务需求：
从员工表中查询5天之内过生日的人，以及五天之内合同到期的人，返回一个 Map 集合，封装了员工的姓名及还有几天过生日；
Dao 层如下：
代码语言：javascript复制@Repository
public interface EmpMapper extends BaseMapper<Employee> {
@Select("select * from employeen"
"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 employeen"
"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 层如下：
代码语言：javascript复制 @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;
}
}
