Mybatis一对多查询（附带多条件查询）

场景：现有两张表

s_class（班级表）

s_student（学生表） 

需求：查询出班级和班级下的学生 

分析：首先班级和学生是一对多的关系，一个班级对应多个学生

实现方法：

方法一：通过查出班级，再查出班级对应学生的数据，组装数据返回

方法二：mybatis提供一对多的查询，一次查询解决

对比：方法二明显比方法一更合适，更符合需求，更方便操作

实际操作： 

创建实体类

班级学生实体类-StudentClass.java

public class StudentClass {

    private Integer id;     //主键id

    private String className;   //班级

    private String gradeName;   //年级

    private List<Student> studentList;  //学生集合

    //此处省略了get，set方法，*实际操作一定要加上*
}

学生实体类-Student.java

public class Student {

    private Integer id;     //主键id

    private String name;    //姓名

    private String sex;     //性别

    private Integer age;    //年龄

    //此处省略了get，set方法 *实际操作一定要加上*
}

 

创建mapper.xml文件

班级学生xml文件-StudentClassMapper.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.heliu.dao.mybatis.StudentClassDao">

    <!--一对多查询关联-->
    <resultMap id="getStudentClassMap" type="com.heliu.entity.test.StudentClass">
        <!-- 实体类属性对应数据库的主键字段，不然主键会查不到 -->
        <id property="id" column="id"/>
        <result property="className" column="class_name"/>
        <result property="gradeName" column="grade_name"/>
        <!-- 用collection标签 ,也是实体类属性要对应数据库字段-->
        <collection property="studentList" column="id" select="com.heliu.dao.mybatis.StudentDao.queryStudentList"/>
    </resultMap>

    <select id="queryClass" resultMap="getStudentClassMap">
        SELECT id,class_name,grade_name
        FROM s_class
    </select>
</mapper>

学生xml文件-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.heliu.dao.mybatis.StudentDao">

    <resultMap id="getStudentMap" type="com.heliu.entity.test.Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
    </resultMap>

    <select id="queryStudentList" parameterType="java.lang.Integer" resultMap="getStudentMap">
        SELECT 	id,name,sex, age
        FROM s_student WHERE class_id = #{value}
    </select>
</mapper>

 

模拟前端请求数据

 班级学生dao层-StudentClassDao.java

public interface StudentClassDao {
    
    //一对多查询方法
    List<StudentClass> queryClass();
}

学生dao层-StudentDao.java

public interface StudentDao {
    
    //用于一对多时查询学生
    List<Student> queryStudentList(Integer classId);
}

 

数据结果： 

{"code":0,"data":[{"id":1,"className":"一班","gradeName":"高三","studentList":[{"id":1,"name":"何老板","sex":"男","age":18},{"id":5,"name":"龙","sex":"男","age":20},{"id":6,"name":"花花","sex":"女","age":19}]},{"id":2,"className":"二班","gradeName":"高三","studentList":[{"id":4,"name":"Tony","sex":"男","age":18}]},{"id":3,"className":"三班","gradeName":"高三","studentList":[{"id":2,"name":"小红","sex":"女","age":19},{"id":3,"name":"Bob","sex":"男","age":17}]}],"success":true}

可以用在线json工具看一下：https://www.json.cn/

需要进行条件查询只需要稍微改一下

List<StudentClass> queryClass(Object object);    //方法里面传入需要条件查询的参数
    
//班级学生xml文件改一下

 <!--一对多查询关联-->
    <resultMap id="getStudentClassMap" type="com.heliu.entity.test.StudentClass">
        <!-- 实体类属性对应数据库的主键字段，不然主键会查不到 -->
        <id property="id" column="id"/>
        <result property="className" column="class_name"/>
        <result property="gradeName" column="grade_name"/>
        <!-- 用collection标签 ,也是实体类属性要对应数据库字段-->
        <collection property="studentList" column="{id=id,object=object}"  select="com.heliu.dao.mybatis.StudentDao.queryStudentList"/>  //这个地方column里的id一定要传
    </resultMap>

    <select id="queryClass" parameterType="object" resultMap="getStudentClassMap">
        SELECT id,class_name,grade_name,
        case when ('${object}' != '') then '${object}' else '' end as object
        FROM s_class
    </select>

List<Student> queryStudentList(Map<String,Object> map);    //方法参数改成Map接受

//学生xml文件改一下

 <resultMap id="getStudentMap" type="com.heliu.entity.test.Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
    </resultMap>

    <select id="queryStudentList" parameterType="java.util.Map"  resultMap="getStudentMap">     //这里传入的参数类型是Map类型的
        SELECT 	id,name,sex, age
        FROM s_student WHERE class_id = #{id} AND name=#{object}
    </select>