【暑期实训08】day18

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>