Spring Hibernate多对多关联：查询教师信息及对应学生数量

嘿，这个需求完全可以通过一次SQL查询搞定，不用先查教师列表再循环统计学生数，既高效又简洁。下面分SQL语句和Java代码实现两部分来说：

1. 核心SQL语句 #

用LEFT JOIN关联教师表和中间表，再通过GROUP BY分组统计，这样能保证所有教师（哪怕没有学生的）都被返回，学生数为0的也会正确显示：

SELECT 
    t.Teacher_id,
    t.Teacher_name,
    COUNT(ts.Student_id) AS No_of_students
FROM Teacher t
LEFT JOIN Teacher_Student ts ON t.Teacher_id = ts.Teacher_id
GROUP BY t.Teacher_id, t.Teacher_name;

关键细节说明： #

• LEFT JOIN：确保所有教师都出现在结果中，即使没有关联的学生记录
• COUNT(ts.Student_id)：只统计非空的学生ID，没有学生的教师会返回0（如果用COUNT(*)会返回1，这是容易踩的坑哦）
• GROUP BY：按教师ID和姓名分组，保证每个教师对应一行结果

2. Java代码实现 #

根据你常用的持久层框架，这里给出两种常见的实现方式：

2.1 JPA（Hibernate）实现 #

首先需要创建一个DTO类来接收查询结果（因为原实体类没有学生数字段）：

public class TeacherStudentCountDTO {
    private Long teacherId;
    private String teacherName;
    private Long noOfStudents;

    // 注意：构造函数的参数顺序要和JPQL查询的字段顺序一致
    public TeacherStudentCountDTO(Long teacherId, String teacherName, Long noOfStudents) {
        this.teacherId = teacherId;
        this.teacherName = teacherName;
        this.noOfStudents = noOfStudents;
    }

    // Getter和Setter方法
    public Long getTeacherId() { return teacherId; }
    public void setTeacherId(Long teacherId) { this.teacherId = teacherId; }
    public String getTeacherName() { return teacherName; }
    public void setTeacherName(String teacherName) { this.teacherName = teacherName; }
    public Long getNoOfStudents() { return noOfStudents; }
    public void setNoOfStudents(Long noOfStudents) { this.noOfStudents = noOfStudents; }
}

然后在Teacher的Repository接口中定义查询方法：

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import java.util.List;

public interface TeacherRepository extends CrudRepository<Teacher, Long> {

    @Query("SELECT new com.yourpackage.dto.TeacherStudentCountDTO(t.teacherId, t.teacherName, COUNT(ts.studentId)) " +
           "FROM Teacher t LEFT JOIN t.teacherStudents ts " +
           "GROUP BY t.teacherId, t.teacherName")
    List<TeacherStudentCountDTO> findTeachersWithStudentCount();
}

注意：Teacher实体类需要添加和TeacherStudent的关联关系：

@OneToMany(mappedBy = "teacher")
private List<TeacherStudent> teacherStudents;

TeacherStudent实体类也要添加Teacher的关联：

@ManyToOne
@JoinColumn(name = "Teacher_id")
private Teacher teacher;

2.2 MyBatis实现 #

同样先准备好上面的TeacherStudentCountDTO类，然后在Mapper接口中定义方法：

方式1：注解式SQL

import java.util.List;
import org.apache.ibatis.annotations.Select;

public interface TeacherMapper {

    @Select("SELECT " +
            "    t.Teacher_id AS teacherId, " +
            "    t.Teacher_name AS teacherName, " +
            "    COUNT(ts.Student_id) AS noOfStudents " +
            "FROM Teacher t " +
            "LEFT JOIN Teacher_Student ts ON t.Teacher_id = ts.Teacher_id " +
            "GROUP BY t.Teacher_id, t.Teacher_name")
    List<TeacherStudentCountDTO> getTeachersWithStudentCount();
}

提示：如果你的MyBatis开启了驼峰命名自动映射（map-underscore-to-camel-case=true），字段会自动从Teacher_id映射到teacherId，无需额外配置。

方式2：XML配置式SQL

如果习惯用XML写SQL，可以在Mapper XML中配置：

<resultMap id="TeacherStudentCountResultMap" type="com.yourpackage.dto.TeacherStudentCountDTO">
    <result column="Teacher_id" property="teacherId"/>
    <result column="Teacher_name" property="teacherName"/>
    <result column="No_of_students" property="noOfStudents"/>
</resultMap>

<select id="getTeachersWithStudentCount" resultMap="TeacherStudentCountResultMap">
    SELECT 
        t.Teacher_id,
        t.Teacher_name,
        COUNT(ts.Student_id) AS No_of_students
    FROM Teacher t
    LEFT JOIN Teacher_Student ts ON t.Teacher_id = ts.Teacher_id
    GROUP BY t.Teacher_id, t.Teacher_name;
</select>

内容的提问来源于stack exchange，提问作者oneMoreDeveloper