Mybatis进行多表查询的一些问题

1、实现多级嵌套查询

1）二级嵌套

在我们日常开发中，会遇到表关联查询的情况，我们一般的做法是可以通过SQL语句中提供的
left join, right join, inner join 来实现表关联；
当我们在项目中使用了Mybatis之后， 可以通过mapper.xml文件来实现关联查询，下面是一个（班级-学生）例子：
班级类


@Data
public class ClassPo implements Serializable {

    private String class_id;
    private String class_name;
    private List<StudentPo> studentList;
}

学生类

@Data
public class StudentPo implements Serializable {

    private String student_id;
    private String student_name;
}

mybatis映射文件

<?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.ClassDao">

    <resultMap id="ClassMap" type="com.example.demo.entity.ClassPo">
<!--        班级resultMap-->
        <result column="class_id" property="class_id"/>
        <result column="class_name" property="class_name"/>
        <!--
        在collection中，property是实体类的中属性的名称，
        column是用来传输给select调用函数的参数，格式为参数名 = 传参；
        其中
        参数名：名对应的是函数入参，可以在子查询selectStudentList中被#{参数名}使用；
        传参： 该对应的是从父查询中column字段。
         -->
        <collection property="studentList" column="class_id=class_id" select="selectStudentList"/>
    </resultMap>

    <resultMap id="StudentMap" type="com.example.demo.entity.StudentPo">
<!--        学生resultMap-->
        <result column="student_id" property="student_id"/>
        <result column="class_id" property="class_id"/>
        <result column="student_name" property="student_name"/>
    </resultMap>

    <select id="selectClassAndStudent" resultMap="ClassMap">
        select *
        from demo_class
    </select>

    <select id="selectStudentList" resultMap="StudentMap">
        select *
        from demo_student
        where class_id = #{class_id}   <!-- class_id来自collection中的column -->
    </select>

</mapper>

返回结果如下：


[
        {
            "class_id": "fb70638237c811edb6840242ac110003",
            "class_name": "1班",
            "studentList": [
                {
                    "student_id": "49112403*37c9*11ed*b684*0242ac110003",
                    "class_id": "fb70638237c811edb6840242ac110003",
                    "student_name": "王晓明"
                }
            ]
        },
        {
            "class_id": "fb7353a337c811edb6840242ac110003",
            "class_name": "2班",
            "studentList": []
        },
        {
            "class_id": "fb75475a37c811edb6840242ac110003",
            "class_name": "3班",
            "studentList": []
        },
        {
            "class_id": "fb76f24337c811edb6840242ac110003",
            "class_name": "4班",
            "studentList": [
                {
                    "student_id": "49041156*37c9*11ed*b684*0242ac110003",
                    "class_id": "fb76f24337c811edb6840242ac110003",
                    "student_name": "赵晓明"
                },
                {
                    "student_id": "4905e65b*37c9*11ed*b684*0242ac110003",
                    "class_id": "fb76f24337c811edb6840242ac110003",
                    "student_name": "钱晓明"
                }
            ]
        },
        {
            "class_id": "fb78b95037c811edb6840242ac110003",
            "class_name": "5班",
            "studentList": []
        },
        {
            "class_id": "fb7a75fb37c811edb6840242ac110003",
            "class_name": "6班",
            "studentList": [
                {
                    "student_id": "490801d4*37c9*11ed*b684*0242ac110003",
                    "class_id": "fb7a75fb37c811edb6840242ac110003",
                    "student_name": "孙晓明"
                },
                {
                    "student_id": "490a3521*37c9*11ed*b684*0242ac110003",
                    "class_id": "fb7a75fb37c811edb6840242ac110003",
                    "student_name": "李晓明"
                },
                {
                    "student_id": "490c1eb1*37c9*11ed*b684*0242ac110003",
                    "class_id": "fb7a75fb37c811edb6840242ac110003",
                    "student_name": "周晓明"
                },
                {
                    "student_id": "490e043d*37c9*11ed*b684*0242ac110003",
                    "class_id": "fb7a75fb37c811edb6840242ac110003",
                    "student_name": "吴晓明"
                },
                {
                    "student_id": "490fac32*37c9*11ed*b684*0242ac110003",
                    "class_id": "fb7a75fb37c811edb6840242ac110003",
                    "student_name": "郑晓明"
                }
            ]
        },
        {
            "class_id": "fb7c02ca37c811edb6840242ac110003",
            "class_name": "7班",
            "studentList": []
        },
        {
            "class_id": "fb7dd4ef37c811edb6840242ac110003",
            "class_name": "8班",
            "studentList": []
        }
    ]

2）多层嵌套（三级及以上）

面对更为复杂的业务逻辑，有时候我们需要多级嵌套，如果这个时候通过sql自带的连接查询的完成的话，查询后的结果必然是需要进行更为繁琐的处理操作才能满足我们的业务逻辑需要。而在mybatis中则可以通过collection标签来进行层层封装，达到sql语句简单，又可以减少后续数据处理的过程，下面是一个学校-校区-年级-班级-学生的五级嵌套查询的例子。

学校实体类
@Data
public class SchoolPo {

    private String school_id;
    private String school_name;
    private List<SchoolLocationPo> schoolLocationPoList;
}


校区实体类
@Data
public class SchoolLocationPo {

    private String schoolLocation_id;
    private String schoolLocation_name;
    private String school_id;
    private List<GradePo> gradePoList;
}


年级实体类
@Data
public class GradePo {

    private String grade_id;
    private String grade_name;
    private String schoolLocation_id;
    private String school_id;
    private List<ClassPo> classPoList;
}


班级实体类
@Data
public class ClassPo {

    private String class_id;
    private String grade_id;
    private String class_name;
    private List<StudentPo> studentList;
}


学生实体类
@Data
public class StudentPo {

    private String student_id;
    private String class_id;
    private String student_name;

}


mapper.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.SchoolDao">

    <!--    学校resultMap-->
    <resultMap id="schoolMap" type="com.example.demo.entity.SchoolPo">
        <result column="school_name" property="school_name"/>
        <result column="school_id" property="school_id"/>
        <collection column="schoolId=school_id"
                    property="schoolLocationPoList" javaType="java.util.List"  select="selectSchoolLocationInfo"/>
    </resultMap>

    <!--    校区resultMap-->
    <resultMap id="schoolLocationMap" type="com.example.demo.entity.SchoolLocationPo">
        <result column="schoolLocation_id" property="schoolLocation_id"/>
        <result column="schoolLocation_name" property="schoolLocation_name"/>
        <result column="school_id" property="school_id"/>
        <collection column="schoolLocationId=schoolLocation_id"
                    property="gradePoList" javaType="java.util.List" select="selectGradeInfo"/>
    </resultMap>
    <!--    年级resultMap-->
    <resultMap id="gradeMap" type="com.example.demo.entity.GradePo">
        <result column="grade_id" property="grade_id"/>
        <result column="grade_name" property="grade_name"/>
        <result column="schoolLocation_id" property="schoolLocation_id"/>
        <result column="school_id" property="school_id"/>
        <collection column="gradeId=grade_id"
                    property="classPoList" javaType="java.util.List" select="selectClassInfo"/>
    </resultMap>
    <!--    班级resultMap-->
    <resultMap id="classMap" type="com.example.demo.entity.ClassPo">
        <result column="grade_id" property="grade_id"/>
        <result column="class_id" property="class_id"/>
        <result column="class_name" property="class_name"/>
        <collection column="classId=class_id"
                    property="studentList" javaType="java.util.List" select="selectStudentInfo"/>

    </resultMap>
    <!--    学生resultMap-->
    <resultMap id="studentMap" type="com.example.demo.entity.StudentPo">
        <result column="student_id" property="student_id"/>
        <result column="class_id" property="class_id"/>
        <result column="student_name" property="student_name"/>
    </resultMap>
    <select id="selectSchoolDetailInfo" resultMap="schoolMap">
        select * from demo_school

    </select>
    <select id="selectSchoolLocationInfo" resultMap="schoolLocationMap">
        select * from demo_schoolLocation where school_id=#{schoolId}

    </select>
    <select id="selectGradeInfo" resultMap="gradeMap">
        select * from demo_grade where schoolLocation_id=#{schoolLocationId}

    </select>
    <select id="selectClassInfo" resultMap="classMap">
        select * from demo_class where grade_id=#{gradeId}

    </select>
    <select id="selectStudentInfo" resultMap="studentMap">
        select * from demo_student where class_id=#{classId}

    </select>


</mapper>


schoolDto
@Mapper
public interface SchoolDao {
    List<SchoolPo> selectSchoolDetailInfo();

    List<SchoolLocationPo> selectSchoolLocationInfo();

    List<GradePo> selectGradeInfo();

    List<ClassPo> selectClassInfo();

    List<StudentPo> selectStudentInfo();
}


控制器代码如下：
@RestController
public class HelloController {

    @Resource
    private ClassDao classDao;
    @Resource
    private SchoolDao schoolDao;

    @RequestMapping("/classInfo")
    public JSONObject getClassInfo(){
        JSONObject result = new JSONObject();
        List<ClassPo> classPoList = classDao.selectClassAndStudent();
        result.put("result",classPoList);
        return result;
    }

    @RequestMapping("/schoolInfo")
    public JSONObject getSchoolInfo(){
        JSONObject result = new JSONObject();
        List<SchoolPo> schoolPoList = schoolDao.selectSchoolDetailInfo();
        result.put("result",schoolPoList);
        return result;
    }

}

最后请求的结果如下：

{
    "result": [
        {
            "school_id": "0de7be4d37e111edb6840242ac110003",
            "school_name": "北京大学",
            "schoolLocationPoList": [
                {
                    "schoolLocation_id": "394c2eee37e111edb6840242ac110003",
                    "schoolLocation_name": "北京校区",
                    "school_id": "0de7be4d37e111edb6840242ac110003",
                    "gradePoList": [
                        {
                            "grade_id": "76220aed37e111edb6840242ac110003",
                            "grade_name": "大一年级",
                            "schoolLocation_id": "394c2eee37e111edb6840242ac110003",
                            "school_id": "0de7be4d37e111edb6840242ac110003",
                            "classPoList": [
                                {
                                    "class_id": "fb70638237c811edb6840242ac110003",
                                    "grade_id": "76220aed37e111edb6840242ac110003",
                                    "class_name": "1班",
                                    "studentList": [
                                        {
                                            "student_id": "49112403*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb70638237c811edb6840242ac110003",
                                            "student_name": "王晓明"
                                        }
                                    ]
                                },
                                {
                                    "class_id": "fb7353a337c811edb6840242ac110003",
                                    "grade_id": "76220aed37e111edb6840242ac110003",
                                    "class_name": "2班",
                                    "studentList": []
                                },
                                {
                                    "class_id": "fb75475a37c811edb6840242ac110003",
                                    "grade_id": "76220aed37e111edb6840242ac110003",
                                    "class_name": "3班",
                                    "studentList": []
                                }
                            ]
                        }
                    ]
                }
            ]
        },
        {
            "school_id": "fbea08fa37e011edb6840242ac110003",
            "school_name": "清华大学",
            "schoolLocationPoList": [
                {
                    "schoolLocation_id": "394a6e9a37e111edb6840242ac110003",
                    "schoolLocation_name": "北京校区",
                    "school_id": "fbea08fa37e011edb6840242ac110003",
                    "gradePoList": [
                        {
                            "grade_id": "7623e60f37e111edb6840242ac110003",
                            "grade_name": "大二年级",
                            "schoolLocation_id": "394a6e9a37e111edb6840242ac110003",
                            "school_id": "fbea08fa37e011edb6840242ac110003",
                            "classPoList": [
                                {
                                    "class_id": "fb76f24337c811edb6840242ac110003",
                                    "grade_id": "7623e60f37e111edb6840242ac110003",
                                    "class_name": "4班",
                                    "studentList": [
                                        {
                                            "student_id": "49041156*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb76f24337c811edb6840242ac110003",
                                            "student_name": "赵晓明"
                                        },
                                        {
                                            "student_id": "4905e65b*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb76f24337c811edb6840242ac110003",
                                            "student_name": "钱晓明"
                                        }
                                    ]
                                },
                                {
                                    "class_id": "fb78b95037c811edb6840242ac110003",
                                    "grade_id": "7623e60f37e111edb6840242ac110003",
                                    "class_name": "5班",
                                    "studentList": []
                                },
                                {
                                    "class_id": "fb7a75fb37c811edb6840242ac110003",
                                    "grade_id": "7623e60f37e111edb6840242ac110003",
                                    "class_name": "6班",
                                    "studentList": [
                                        {
                                            "student_id": "490801d4*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb7a75fb37c811edb6840242ac110003",
                                            "student_name": "孙晓明"
                                        },
                                        {
                                            "student_id": "490a3521*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb7a75fb37c811edb6840242ac110003",
                                            "student_name": "李晓明"
                                        },
                                        {
                                            "student_id": "490c1eb1*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb7a75fb37c811edb6840242ac110003",
                                            "student_name": "周晓明"
                                        },
                                        {
                                            "student_id": "490e043d*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb7a75fb37c811edb6840242ac110003",
                                            "student_name": "吴晓明"
                                        },
                                        {
                                            "student_id": "490fac32*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb7a75fb37c811edb6840242ac110003",
                                            "student_name": "郑晓明"
                                        }
                                    ]
                                },
                                {
                                    "class_id": "fb7c02ca37c811edb6840242ac110003",
                                    "grade_id": "7623e60f37e111edb6840242ac110003",
                                    "class_name": "7班",
                                    "studentList": []
                                },
                                {
                                    "class_id": "fb7dd4ef37c811edb6840242ac110003",
                                    "grade_id": "7623e60f37e111edb6840242ac110003",
                                    "class_name": "8班",
                                    "studentList": []
                                }
                            ]
                        }
                    ]
                },
                {
                    "schoolLocation_id": "394e339b37e111edb6840242ac110003",
                    "schoolLocation_name": "河北校区",
                    "school_id": "fbea08fa37e011edb6840242ac110003",
                    "gradePoList": []
                }
            ]
        }
    ]
}

从这个例子我们不难看出，我们在实际的mapper.xml中，我们只需要编写一些最简单的查询语句，而剩下的工作都是交给mybatis来替我们完成的；并且在后面的返回结果中，mybatis也会帮我们封装成我们所需要的样子。

2、嵌套查询传参问题

在上述的多级嵌套查询的实例中，我们通过collection完成了五级嵌套查询，但其实在实际开发业务中，还需要我们传递一些参数，比如当查询学校信息时，我们希望查询出具体某个校区，某个年级的所有班级出来，那么这里我们就需要传递两个参数即，

校区名称，年级名称。
那么我们的对应接口的函数声明则需要修改为：

     /**
     * 
     * @param locationName 校区名称
     * @param gradeName  年级名称
     * @return
     */
   List<SchoolPo> schoolConditionQuery(String locationName,String gradeName);

那么我们如何将这两个参数传递到具体的select语句中呢？我们的映射的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.SchoolConditionQueryDao">

    <!--    学校resultMap-->
    <resultMap id="schoolMap" type="com.example.demo.entity.SchoolPo">
        <result column="school_name" property="school_name"/>
        <result column="school_id" property="school_id"/>
        <!--
        将locationName 和gradeName 作为单独的列(查看select 语句）
        ，插入到查询结果中，再将查询结果作为入参，带到子查询中。
        再column传参格式如下 XX=xx,YY=yy,ZZ=zz
        -->

        <collection column="schoolId=school_id,locationName=locationName,gradeName=gradeName"
                    property="schoolLocationPoList" javaType="java.util.List"  select="selectSchoolLocationInfo"/>
    </resultMap>

    <!--    校区resultMap-->
    <resultMap id="schoolLocationMap" type="com.example.demo.entity.SchoolLocationPo">
        <result column="schoolLocation_id" property="schoolLocation_id"/>
        <result column="schoolLocation_name" property="schoolLocation_name"/>
        <result column="school_id" property="school_id"/>
        <!--
        这边校区locationName已经在校区查询中使用，因此不需要带入到下一子类查询，只需要
        将年级名称gradeName作为入参存入即可
        -->

        <collection column="schoolLocationId=schoolLocation_id,gradeName=gradeName"
                    property="gradePoList" javaType="java.util.List" select="selectGradeInfo"/>
    </resultMap>
    <!--    年级resultMap-->
    <resultMap id="gradeMap" type="com.example.demo.entity.GradePo">
        <result column="grade_id" property="grade_id"/>
        <result column="grade_name" property="grade_name"/>
        <result column="schoolLocation_id" property="schoolLocation_id"/>
        <result column="school_id" property="school_id"/>
        <collection column="gradeId=grade_id"
                    property="classPoList" javaType="java.util.List" select="selectClassInfo"/>
    </resultMap>
    <!--    班级resultMap-->
    <resultMap id="classMap" type="com.example.demo.entity.ClassPo">
        <result column="grade_id" property="grade_id"/>
        <result column="class_id" property="class_id"/>
        <result column="class_name" property="class_name"/>
        <collection column="classId=class_id"
                    property="studentList" javaType="java.util.List" select="selectStudentInfo"/>

    </resultMap>
    <!--    学生resultMap-->
    <resultMap id="studentMap" type="com.example.demo.entity.StudentPo">
        <result column="student_id" property="student_id"/>
        <result column="class_id" property="class_id"/>
        <result column="student_name" property="student_name"/>
    </resultMap>

<!--    学校查询语句-->
    <select id="schoolConditionQuery" resultMap="schoolMap">
        select *,'${gradeName}' as gradeName,'${locationName}' as locationName from demo_school
        <!--
        将入参gradeName和locationName作为单独的列与查询结果一起输出，带入到子类查询中
        -->
    </select>
<!--    校区查询语句-->
    <select id="selectSchoolLocationInfo" resultMap="schoolLocationMap">
        select *,'${gradeName}' as gradeName from demo_schoolLocation where school_id=#{schoolId}
        where schoolLocation_name like concat('%',#{locationName},'%')
    <!--          将入参gradeName作为单独的列与查询结果一起输出，带入到子类查询中-->
        </select>
    <!--    年级查询语句-->
    <select id="selectGradeInfo" resultMap="gradeMap">
        select * from demo_grade where schoolLocation_id=#{schoolLocationId}
        and grade_name like concat('%',#{gradeName},'%')

    </select>
<!--    班级查询语句-->
    <select id="selectClassInfo" resultMap="classMap">
        select * from demo_class where grade_id=#{gradeId}

    </select>
<!--    学生查询语句-->
    <select id="selectStudentInfo" resultMap="studentMap">
        select * from demo_student where class_id=#{classId}

    </select>


</mapper>

请求如下：
在这里插入图片描述
结果如下：

{
    "result": [
        {
            "school_id": "0de7be4d37e111edb6840242ac110003",
            "school_name": "北京大学",
            "schoolLocationPoList": [
                {
                    "schoolLocation_id": "394c2eee37e111edb6840242ac110003",
                    "schoolLocation_name": "北京校区",
                    "school_id": "0de7be4d37e111edb6840242ac110003",
                    "gradePoList": []
                }
            ]
        },
        {
            "school_id": "fbea08fa37e011edb6840242ac110003",
            "school_name": "清华大学",
            "schoolLocationPoList": [
                {
                    "schoolLocation_id": "394a6e9a37e111edb6840242ac110003",
                    "schoolLocation_name": "北京校区",
                    "school_id": "fbea08fa37e011edb6840242ac110003",
                    "gradePoList": [
                        {
                            "grade_id": "7623e60f37e111edb6840242ac110003",
                            "grade_name": "大二年级",
                            "schoolLocation_id": "394a6e9a37e111edb6840242ac110003",
                            "school_id": "fbea08fa37e011edb6840242ac110003",
                            "classPoList": [
                                {
                                    "class_id": "fb76f24337c811edb6840242ac110003",
                                    "grade_id": "7623e60f37e111edb6840242ac110003",
                                    "class_name": "4班",
                                    "studentList": [
                                        {
                                            "student_id": "49041156*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb76f24337c811edb6840242ac110003",
                                            "student_name": "赵晓明"
                                        },
                                        {
                                            "student_id": "4905e65b*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb76f24337c811edb6840242ac110003",
                                            "student_name": "钱晓明"
                                        }
                                    ]
                                },
                                {
                                    "class_id": "fb78b95037c811edb6840242ac110003",
                                    "grade_id": "7623e60f37e111edb6840242ac110003",
                                    "class_name": "5班",
                                    "studentList": []
                                },
                                {
                                    "class_id": "fb7a75fb37c811edb6840242ac110003",
                                    "grade_id": "7623e60f37e111edb6840242ac110003",
                                    "class_name": "6班",
                                    "studentList": [
                                        {
                                            "student_id": "490801d4*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb7a75fb37c811edb6840242ac110003",
                                            "student_name": "孙晓明"
                                        },
                                        {
                                            "student_id": "490a3521*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb7a75fb37c811edb6840242ac110003",
                                            "student_name": "李晓明"
                                        },
                                        {
                                            "student_id": "490c1eb1*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb7a75fb37c811edb6840242ac110003",
                                            "student_name": "周晓明"
                                        },
                                        {
                                            "student_id": "490e043d*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb7a75fb37c811edb6840242ac110003",
                                            "student_name": "吴晓明"
                                        },
                                        {
                                            "student_id": "490fac32*37c9*11ed*b684*0242ac110003",
                                            "class_id": "fb7a75fb37c811edb6840242ac110003",
                                            "student_name": "郑晓明"
                                        }
                                    ]
                                },
                                {
                                    "class_id": "fb7c02ca37c811edb6840242ac110003",
                                    "grade_id": "7623e60f37e111edb6840242ac110003",
                                    "class_name": "7班",
                                    "studentList": []
                                },
                                {
                                    "class_id": "fb7dd4ef37c811edb6840242ac110003",
                                    "grade_id": "7623e60f37e111edb6840242ac110003",
                                    "class_name": "8班",
                                    "studentList": []
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    ]
}

3、总结

mybatis映射文件通过使用collection实现多级层层嵌套，从而达到多表关联查询的目的，但在使用中也发现了一个问题：对于多级嵌套的条件查询，当子类中查询结果没有满足条件的查询结果时，其父类的结果依然会被返回。

如我们上面查询到了北京大学数据中的北京校区，其gradePoList中实际没有结果，但是依然会将校区信息返回给我们，因此如果想只想把符合年级条件的数据的校区信息返回出来，那么则需要对查询后的List结果进行筛选，将List为中size()为0的对象剔除掉。

# java # mysql # spring boot
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

华为云Stack8.3面向香港正式发布，六大亮点激发云上跃迁

avatar 华为云开发者联盟
cover

GaussDB SQL查询语句执行过程解析

avatar 华为云开发者联盟
cover

6个实例带你解读TinyVue 组件库跨框架技术

avatar 华为云开发者联盟