嵌套查询与嵌套结果

嵌套查询

嵌套查询是指在查询过程中，嵌套其他查询语句进行相关属性的查询，可以用于ResultMap的collection、asscoiation等属性中。

下述测试案例中，在查询student的同时，通过嵌套查询获得了学生上过的学校。

测试类：

public class Student {
    private int id;
    private List<School> schoolList;
}

public class School {
    private int id;
    private String name;
}

xml：

<resultMap id="testMap" type="Student">
    <id property="id" column="id"/>
    <collection property="schoolList" column="id" ofType="school"
                <!-- 嵌套的查询语句 -->
                select="com.springboot2.mapper.TestMapper.getSchoolListById"/>
</resultMap>
<select id="test" resultMap="testMap">
    select id
    from student
</select>

<select id="getSchoolListById" resultType="School">
    select *
    from school
    where student_id=#{id}
</select>

测试代码：

void test() {
    Student student = testMapper.test();
    System.out.println(student);
}

结果：Student(id=1, schoolList=[School(id=1, name=a), School(id=2, name=b)])

嵌套结果

将查询结果映射到子属性中，只执行了一次sql，一般是多表联合查询

测试类同上

xml：

<resultMap id="testMap" type="Student">
    <id property="id" column="student_id"/>
    <!-- 嵌套的查询结果 -->
    <collection property="schoolList" column="id" ofType="school">
        <id property="id" column="school_id"/>
        <result property="name" column="school_name"/>
    </collection>
</resultMap>
<select id="test" resultMap="testMap">
    select student.id student_id, school.id school_id, school.name school_name
    from student
    left join school
    on student.id = school.student_id
</select>

结果：Student(id=1, schoolList=[School(id=1, name=a), School(id=2, name=b)])