多表联查一个小实例

现在有一个学生表t_student:(stu_id,stu_name)，课程表t_course(cou_id,cou_name)，课程学生表student_course(stu_id,cou_id)。然后我要实现一个页面，就是学生选课页面

现在就是但是后端向前端传一个这个类

@Data
public class StudentCourseVO {
    private Integer stuId;
    private String stuName;
    private List<Integer> courseIdList;
}

。
然后想这这样做用这个sql:

SELECT 
    ts.stu_id AS stuId,
    ts.stu_name AS stuName,
    GROUP_CONCAT(sc.cou_id) AS courseIdList
FROM 
    t_student ts
LEFT JOIN 
    student_course sc ON ts.stu_id = sc.stu_id
GROUP BY 
    ts.stu_id, ts.stu_name

然后就这样实现的：
StudentCourseController

@RestController
@RequestMapping("/studentCourse")
public class StudentCourseController {
    @Resource
    private StudentCourseService studentCourseService;

    @PostMapping("/page")
    public ResponseResult<IPage> findPage(@RequestBody Map<String, Object> params) {
        Integer pageNum = (Integer) params.get("pageNum");
        Integer pageSize = (Integer) params.get("pageSize");
        String stuName = (String) params.getOrDefault("stuName", "");
        Page<StudentCourseVO> result = studentCourseService.findPage(new Page<>(pageNum, pageSize), stuName);

        return ResponseResult.success(result);
    }
}

StudentCourseService

public interface StudentCourseService extends IService<StudentCourse> {

    Page<StudentCourseVO> findPage(Page<StudentCourseVO> objectPage, String stuName);
}

StudentCourseServiceImpl

@Service
public class StudentCourseServiceImpl extends ServiceImpl<StudentCourseMapper, StudentCourse> implements StudentCourseService {
   @Resource
    private StudentCourseMapper studentCourseMapper;

   @Override
    public Page<StudentCourseVO> findPage(Page<StudentCourseVO> page, String stuName) {
        return studentCourseMapper.findPage(page,stuName);
    }
}

StudentCourseMapper

@Mapper
public interface StudentCourseMapper extends BaseMapper<StudentCourse> {

    Page<StudentCourseVO> findPage(Page<StudentCourseVO> page, String stuName);
}

StudentCourseMapper.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.example.demo.mapper.StudentCourseMapper">

    <select id="findPage" resultType="com.example.demo.controller.dto.StudentCourseVO">
        SELECT
            ts.stu_id AS stuId,
            ts.stu_name AS stuName,
            GROUP_CONCAT(sc.cou_id) AS courseIdList
        FROM
            t_student ts
                LEFT JOIN
            student_course sc ON ts.stu_id = sc.stu_id
        WHERE
            ts.stu_name LIKE CONCAT('%', #{stuName}, '%')
        GROUP BY
            ts.stu_id, ts.stu_name
    </select>

</mapper>

StudentCourseVO

@Data
public class StudentCourseVO {
    private Integer stuId;
    private String stuName;
    private List<Integer> courseIdList;

}

但是这样是不行的


因为sql查出来的这个courseIdList是一个字符串，我们将这个StudentCourseVO中的courseIdList换成String然后就能查出来


当然我们可以先定义成string，然后返回之后在前端或者怎么样处理一下。或者换成另一种方法，把两个表中的东西都查出来，然后在拼接成VO

然后应该怎么做呢？
一种做法就是把用两次sql语句，就是先从t_student中查出来全部的学生，然后再从student_course(stu_id,cou_id)中查出全部的信息，之后再拼接成VO
StudentCourseController

@RestController
@RequestMapping("/studentCourse")
public class StudentCourseController {
    @Resource
    private StudentCourseService studentCourseService;

    @PostMapping("/page")
    public ResponseResult<IPage> findPage(@RequestBody Map<String, Object> params) {
        Integer pageNum = (Integer) params.get("pageNum");
        Integer pageSize = (Integer) params.get("pageSize");
        String stuName = (String) params.getOrDefault("stuName", "");
        Page<StudentCourseVO> result = studentCourseService.findPage(new Page<>(pageNum, pageSize), stuName);

        return ResponseResult.success(result);
    }
}

StudentCourseService

public interface StudentCourseService extends IService<StudentCourse> {

    Page<StudentCourseVO> findPage(Page<StudentCourseVO> objectPage, String stuName);
}

StudentCourseServiceImpl

@Service
public class StudentCourseServiceImpl extends ServiceImpl<StudentCourseMapper, StudentCourse> implements StudentCourseService {
   @Resource
    private StudentCourseMapper studentCourseMapper;

   @Resource
   private StudentMapper studentMapper;

   @Override
    public Page<StudentCourseVO> findPage(Page<StudentCourseVO> page, String stuName) {
       // 第一次查询：分页获取学生信息
       IPage<Student> studentPage = studentMapper.findStudentPage(new Page<>(page.getCurrent(), page.getSize()), stuName);
       // 第二次查询：获取所有课程信息（根据学生 ID）
       List<Integer> stuIds = studentPage.getRecords().stream().map(Student::getStuId).collect(Collectors.toList());
       System.out.println(stuIds);
       List<StudentCourse> studentCourses = studentCourseMapper.findByStuIds(stuIds);
       // 将学生信息和课程信息组合成 VO 对象
       // 拼接数据
       List<StudentCourseVO> studentCourseVOList = studentPage.getRecords().stream().map(student -> {
           StudentCourseVO vo = new StudentCourseVO();
           vo.setStuId(student.getStuId());
           vo.setStuName(student.getStuName());
           // 找到当前学生的所有课程 ID
           List<Integer> courseIdList = studentCourses.stream()
                   .filter(sc -> sc.getStuId().equals(student.getStuId()))
                   .map(StudentCourse::getCouId)
                   .collect(Collectors.toList());

           vo.setCourseIdList(courseIdList);
           return vo;
       }).collect(Collectors.toList());
       // 将拼接后的结果重新设置到分页对象中
       page.setRecords(studentCourseVOList);
       page.setTotal(studentPage.getTotal());
       return page;
    }
}

StudentMapper

@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    @Select("SELECT * from t_student")
    List<Student> getallstudents();


    int updateStudent(Student student);

    IPage<Student> findStudentPage(Page<Student> page, String stuName);
}

studentMapper.xml

<select id="findStudentPage" resultType="com.example.demo.entity.Student">
        select * from t_student
        <where>
            <if test="stuName != null and stuName != ''">
                AND stu_name LIKE CONCAT('%', #{stuName}, '%')
            </if>
        </where>


    </select>

StudentCourseMapper

@Mapper
public interface StudentCourseMapper extends BaseMapper<StudentCourse> {

    List<StudentCourse> findByStuIds(List<Integer> stuIds);
}

StudentCourseMapper.xml

<select id="findByStuIds" resultType="com.example.demo.entity.StudentCourse">
        SELECT * from student_course
        WHERE stu_id IN
        <foreach collection="list" item="stuId" open="(" separator="," close=")">
            #{stuId}
        </foreach>
    </select>

其中这一块的意思就是：

<foreach collection="list" item="stuId" open="(" separator="," close=")">
            #{stuId}
        </foreach>

遍历stuId，就是将[1,2,3]转化为(1,2,3)这种sql语句中就是这种：

然后就可以实现了：