1.实现后端学生表统计各类信息
controller层
//统计学生总数
@RequestMapping(value = "/countTotal")
@ResponseBody
public int selectTotal(){
return StuService.selectTotal();
}
//统计学生性别
@RequestMapping(value = "/countGender")
@ResponseBody
public Integer selectGenderNum(HttpServletRequest request){
return StuService.selectGenderNum(request.getParameter("stu_gender"));
}
//统计学生类别
@RequestMapping(value = "/countType")
@ResponseBody
public int selectTypeNum(HttpServletRequest request){
return StuService.selectTypeNum(request.getParameter("stu_type"));
}
//统计学生家乡
@RequestMapping(value = "/countAddress")
@ResponseBody
public int selectAddressNum(HttpServletRequest request){
return StuService.selectAddressNum(request.getParameter("home_address"));
}
Dao层
int selectTotal();
Integer selectGenderNum(String stuGender);
int selectTypeNum(String stuType);
int selectAddressNum(String homeAddress);
xml
<select id="selectTotal" resultType="java.lang.Integer">
select count(*) as total from student
</select>
<select id="selectGenderNum" resultType="java.lang.Integer">
select count_gender from (select stu_id,count(*) as count_gender from student where stu_gender = #{stuGender,jdbcType=VARCHAR} group by stu_gender)as a
</select>
<select id="selectTypeNum" resultType="java.lang.Integer">
select count_type from (select stu_id,count(*) as count_type from student where stu_type = #{stuType,jdbcType=VARCHAR} group by stu_type)as a
</select>
<select id="selectAddressNum" resultType="java.lang.Integer">
select count_address from (select stu_id,count(*) as count_address from student where home_address like "%"#{homeAddress,jdbcType=VARCHAR}"%" )as a
</select>
2.实现后端老师表统计各类信息
controller层
//统计老师总数
@RequestMapping(value = "/countTotal")
@ResponseBody
public int selectTotal(){
return teacherService.selectTotal();
}
//统计老师性别
@RequestMapping(value = "/countGender")
@ResponseBody
public int selectGenderNum(HttpServletRequest request){
return teacherService.selectGenderNum(request.getParameter("tea_gender"));
}
//统计老师职称
@RequestMapping(value = "/countPosition")
@ResponseBody
public int selectPositionNum(HttpServletRequest request){
return teacherService.selectPositionNum(request.getParameter("position"));
}
//统计老师年龄
@RequestMapping(value = "/countAge30")
@ResponseBody
public int selectAgeNum1(){
return teacherService.selectAgeNum1();
}
@RequestMapping(value = "/countAge40")
@ResponseBody
public int selectAgeNum2(){
return teacherService.selectAgeNum2();
}
@RequestMapping(value = "/countAge50")
@ResponseBody
public int selectAgeNum3(){
return teacherService.selectAgeNum3();
}
@RequestMapping(value = "/countAge60")
@ResponseBody
public int selectAgeNum4(){
return teacherService.selectAgeNum4();
}
@RequestMapping(value = "/countAge70")
@ResponseBody
public int selectAgeNum5(){
return teacherService.selectAgeNum5();
}
Dao层
int selectTotal();
int selectGenderNum(String teaGender);
int selectPositionNum(String Position);
int selectAgeNum1();
int selectAgeNum2();
int selectAgeNum3();
int selectAgeNum4();
int selectAgeNum5();
xml
<select id="selectTotal" resultType="java.lang.Integer">
select count(*) as total from teacher
</select>
<select id="selectGenderNum" resultType="java.lang.Integer">
select count_gender from (select tea_id,count(*) as count_gender from teacher where tea_gender = #{teaGender,jdbcType=VARCHAR} group by tea_gender)as a
</select>
<select id="selectPositionNum" resultType="java.lang.Integer">
select count_position from (select tea_id,count(*) as count_position from teacher where position = #{position,jdbcType=VARCHAR} group by position)as a
</select>
<select id="selectAgeNum1" resultType="java.lang.Integer">
select count_age from (select tea_id,count(*) as count_age from teacher where year(tea_birthday) > 1991)as a
</select>
<select id="selectAgeNum2" resultType="java.lang.Integer">
select count_age from (select tea_id,count(*) as count_age from teacher where year(tea_birthday) > 1981 and year(tea_birthday) <![CDATA[ <= ]]> 1991)as a
</select>
<select id="selectAgeNum3" resultType="java.lang.Integer">
select count_age from (select tea_id,count(*) as count_age from teacher where year(tea_birthday) > 1971 and year(tea_birthday)<![CDATA[ <= ]]> 1981)as a
</select>
<select id="selectAgeNum4" resultType="java.lang.Integer">
select count_age from (select tea_id,count(*) as count_age from teacher where year(tea_birthday) > 1961 and year(tea_birthday) <![CDATA[ <= ]]> 1971)as a
</select>
<select id="selectAgeNum5" resultType="java.lang.Integer">
select count_age from (select tea_id,count(*) as count_age from teacher where year(tea_birthday) <![CDATA[ <= ]]> 1961)as a
</select>