mysql中怎么统计表中每个班级的人数 mysql查询班级学生人数

目录

• 一、项目介绍
• 二、具体步骤

• 1、设计学生表
• 2、项目结构
• 3、配置文件

• pom.xml
• applicationContext.xml
• db.properties
• dispatcherServlet.xml
• mybatis.xml

• 4、业务层
• 5、持久层
• 6、视图层
• 7、控制层

• 三、测试

---

一、项目介绍

• 项目的主要功能是 ： 通过下拉列表选择某一班级，通过点击查询按钮页面下方呈现出显示学生信息的表格

如图：

• 主要用到的技术 ：SSM 三大框架、AJAX、jQuery、BootStrap 等
• 项目目的 ：通过这种小项目来提升对于 SSM 三大框架使用熟练程度，没有涉及较为复杂的概念

二、具体步骤

1、设计学生表

设计完成后加入学生信息(随便添加几个就行)

2、项目结构

如图

3、配置文件

在 IDEA 中我们新建 maven web-app 程序，新建完成后需要配置 pom.xml Maven 核心配置文件、applicationContext.xml Spring配置文件、dispatchServlet.xml SpringMVC 配置文件、mybaits.xml MyBatis 配置文件及 web.xml 等

pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.fancy</groupId>
  <artifactId>StudentList</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>StudentList Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <!--servlet-->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.1.0</version>
      <scope>provided</scope>
    </dependency>
    <!-- jsp 依赖 -->
    <dependency>
      <groupId>javax.servlet.jsp</groupId>
      <artifactId>jsp-api</artifactId>
      <version>2.2.1-b03</version>
      <scope>provided</scope>
    </dependency>
    <!--springmvc-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>5.2.5.RELEASE</version>
    </dependency>
    <!--事务的-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.2.5.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.2.5.RELEASE</version>
    </dependency>
    <!--aspectj 依赖-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aspects</artifactId>
      <version>5.2.5.RELEASE</version>
    </dependency>
    <!--jackson-->
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-core</artifactId>
      <version>2.9.0</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.9.0</version>
    </dependency>
    <!--mybatis 和 spring 整合的-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.3.1</version>
    </dependency>
    <!--mybatis-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.1</version>
    </dependency>
    <!--mysql 驱动-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.28</version>
    </dependency>
    <!--druid-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.12</version>
    </dependency>

    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.9.6</version>
    </dependency>
    <dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>2.4</version>
    </dependency>
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.3.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/javax.servlet/jstl -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>


  </dependencies>

  <build>
    <resources>
      <resource>
        <directory>src/main/java</directory>
        <includes>
          <include>**/*.xml</include>
          <include>**/*.properties</include>
        </includes>
      </resource>

      <resource>
        <directory>src/main/resources</directory>
        <includes>
          <include>**/*.xml</include>
          <include>**/*.properties</include>
        </includes>
      </resource>
    </resources>
    <finalName>StudentList</finalName>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-war-plugin</artifactId>
          <version>3.2.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

主要是别忘了加资源扫描器 <resources>

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">
    <context:component-scan base-package="com.fancy.service"></context:component-scan>

    <context:property-placeholder location="classpath:db.properties"></context:property-placeholder>

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"></property>
        <property name="configLocation" value="classpath:mybatis.xml"></property>
    </bean>

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
        <property name="basePackage" value="com.fancy.mapper"></property>
    </bean>
</beans>

db.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/qdu?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
jdbc.username=root
jdbc.password=password

dispatcherServlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.alibaba.com/schema/stat http://www.alibaba.com/schema/stat.xsd http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd">

    <context:component-scan base-package="com.fancy.controller"></context:component-scan>

<!--    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">-->
<!--        <property name="prefix" value="/WEB-INF/view"></property>-->
<!--        <property name="suffix" value=".jsp"></property>-->
<!--    </bean>-->

    <mvc:annotation-driven></mvc:annotation-driven>
</beans>

mybatis.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <package name="com.fancy.pojo"/>
    </typeAliases>
    <mappers>
        <package name="com.fancy.mapper"/>
    </mappers>
</configuration>

4、业务层

在业务层我们定义 StudentService 接口及其实现类 StudentServiceImpl， 实现方法为show，内部封装持久层获取学生信息的代码

StudentService 接口：

package com.fancy.service;

import com.fancy.pojo.Student;

import java.util.List;

public interface StudentService {
     List<Student> show(String sbatch);
}

StudentServiceImpl 实现类

package com.fancy.service;

import com.fancy.mapper.StudentMapper;
import com.fancy.pojo.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StudentServiceImpl implements StudentService{

    @Autowired
    StudentMapper studentMapper;

    @Override
    public List<Student> show(String sbatch) {

        List<Student> students = studentMapper.selectStudentsByClass(sbatch);
        return students;
    }
}

业务层要注意一定会有持久层对象

5、持久层

mapper 包下封装持久层代码，分别是 dao 接口与其 mapper 实现类

StudentMapper.java

package com.fancy.service;

import com.fancy.mapper.StudentMapper;
import com.fancy.pojo.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StudentServiceImpl implements StudentService{

    @Autowired
    StudentMapper studentMapper;

    @Override
    public List<Student> show(String sbatch) {

        List<Student> students = studentMapper.selectStudentsByClass(sbatch);
        return students;
    }
}

StudentMapper.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.fancy.mapper.StudentMapper">
    <select id="selectStudentsByClass" parameterType="java.lang.String" resultType="com.fancy.pojo.Student">
        select * from student where sbatch = #{sBatch};
    </select>
</mapper>

6、视图层

在 index.jsp 中我们通过 AJAX 向 controller 发送请求，同时携带<select>中数据，请求成功后我们重载 table 中的数据。在table中我们是通过 ${value} 加上 <c:forEach> 来遍历所有返回结果的

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%--
  Created by IntelliJ IDEA.
  User: 繁花fancy
  Date: 2022/3/5
  Time: 10:33
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>

<head>
    <title>查询界面</title>
    <link rel="stylesheet" href="css/bootstrap.min.css">
    <script src="js/jquery-3.6.0.min.js"></script>
    <script src="js/bootstrap.min.js"></script>
    <style>
        #title {
            height: 100px;
            width: 1000px;
            margin: 60px auto;
            color: rgb(205, 156, 50);
            background-color: #e8eb2649;
            text-align: center;
            border-radius: 10px;
        }

        #list {
            height: 50px;
            width: 300px;
            margin: 60px auto;
        }
        #btn {
            height: 50px;
            width: 100px;
            position: absolute;
            left:1120px;
            top:280px;
        }
        #table {
            width : 1200px;
            margin : 0px auto;
        }
    </style>
    <script>
        $(function(){
            $("#btn").click(function (){
                $.ajax({
                    url:"/MyWeb/show.action",
                    type:"GET",
                    dataTyp:"json",
                    data:{"sbatch":$("select").val()},
                    success:function(students){
                         $("#table").load("http://localhost:8080/MyWeb/index.jsp #table");
                    }
                })

            })

        })
    </script>
</head>

<body>
    <div id="title" class="container">
        <h1>查询班级学生列表</h1>
    </div>
    <hr>
    <select id="list" class="form-control">
        <option selected="selected">请选择班级</option>
        <option>20软件J01</option>
        <option>20软件BD01</option>
        <option>20软件BD02</option>
        <option>20软件BD03</option>
    </select>
   <button id="btn" class="btn btn-danger" >查询</button>
   <table id="table" class="table table-bordered table-striped">
       <tr>
           <th>学号</th>
           <th>姓名</th>
           <th>密码</th>
           <th>性别</th>
           <th>班级</th>
       </tr>
       <%

       %>
       <c:forEach items="${students}" var="stu">
        <tr>
           <td>${stu.sid}</td>
           <td>${stu.sname}</td>
           <td>${stu.spassword}</td>
           <td>${stu.sgender}</td>
           <td>${stu.sbatch}</td>
        </tr>
       </c:forEach>
   </table>
</body>

</html>

7、控制层

controller包下主要放处理请求的 controller 类代码，我们将select中数据取出并调用service方法进行查询。要注意 我们将方法定义成 void 并通过 session 来返回数据库中查询的数据。至于为什么不用请求转发的方式，这就是 Ajax 请求和普通 Http 请求的区别了

package com.fancy.controller;

import com.fancy.pojo.Student;
import com.fancy.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpSession;
import java.util.List;

@Controller
public class StudentController {

    @Autowired
    StudentService studentService;

    @RequestMapping("/show.action")
    @ResponseBody
    public void show(String sbatch, HttpSession session) {
        System.out.println(sbatch);
        List<Student> students = studentService.show(sbatch);
        session.setAttribute("students", students);
    }

}

三、测试