MyBatis学习笔记10 联合查询（二）

深碍√TFBOYSˉ_ 2024-03-27 09:24 50阅读 0赞

#### 1、多对一关联 ####

> 就是某一张表的多条数据、对应另一张表的一条数据，比如多个学生对应一个班级。

1.1、提出需求

> 以学生表和班级表为例、查询学生信息，并查询对应的班级，关系为多对一。

*  表字段以及对应关系

![e5dd1d3d5d6d10b3d5f4030030186f1a.png][]

1.2、创建实体类

> 学生表存放班级id，在实体类中取而代之的就是班级实体类类型，一个班级下有多名学生，所以、在班级实体类中必须有一个集合类型属性，集合里边类型为学生类型、将查询到的学生学生映射到该集合当中。

*  学生实体类

public class Student {
        private int id;
        private String stu_name;
        private int stu_age;
        private String address;
        private Classes classes;
        //getter and setter...
    }

*  班级实体类

public class Classes {
        private int id;
        private String class_name;
        //一个班级对应多名学生，使用集合、存放学生信息
        private List<Student> students;
        //getter and setter...
    }

*  定义Dao层接口

public interface StudentDao {
        List<Student> selectStudents();
    }

*  mapper映射文件

<mapper namespace="com.rg.dao.StudentDao">
        
        <select id="selectStudents" resultMap="student_class_map">
            select s.*, sc.id, sc.class_name
            from student s
                     inner join student_class sc on s.s_id = sc.id
        </select>
        <resultMap id="student_class_map" type="com.rg.bean.Student">
            <id property="id" column="id"/>
            <result property="stu_name" column="stu_name"/>
            <result property="stu_age" column="stu_age"/>
            <result property="address" column="address"/>
            
            <association property="classes" column="s_id" javaType="com.rg.bean.Classes">
                <id property="id" column="id"/>
                <result property="class_name" column="class_name"/>
            </association>
        </resultMap>
    
        
        <select id="selectStudents" resultMap="student_map">
            select id, stu_name, stu_age, address, s_id
            from student
        </select>
        <resultMap id="student_map" type="com.rg.bean.Student">
            <id property="id" column="id"/>
            <result property="stu_name" column="stu_name"/>
            <result property="stu_age" column="stu_age"/>
            <result property="address" column="address"/>
            
            <association property="classes" select="selectClasses" column="s_id"/>
        </resultMap>
        
        <select id="selectClasses" resultType="com.rg.bean.Classes">
            select id,class_name from student_class where id = #{id}
        </select>
    </mapper>

*  测试

@Test
    public void test02() {
        List<Student> students = studentDao.selectStudents();
        for (Student student : students) {
            System.out.println(student);
        }
    }

*  查询结果

Student{id=14, stu_name='小王', stu_age=15, address='河南', classes=Classes{id=1, class_name='软件2021班', students=null}}
    Student{id=15, stu_name='小李', stu_age=13, address='河南', classes=Classes{id=2, class_name='软件2022班', students=null}}
    Student{id=16, stu_name='小赵', stu_age=14, address='上海', classes=Classes{id=1, class_name='软件2021班', students=null}}

> 其实多对一就相当于多个一对一，每次查询到一条学生信息，就根据学生信息中的班级id关联到班级表、查询到对应的班级信息。

#### 2、一对多关联 ####

> 在mybatis中进行一对多关联要使用到一个集合标签，因为每次查询到一条数据，就要关联到另一张表中的多条数据，也就是说、查询到一条班级信息，就要拿着这条数据的id，到学生表中查对应的学生信息，将查到的这些学生对象，逐条添加集合当中。

2.1、提出需求

> 根据班级id，查询班级信息并查到学生表中对应的所有学生信息。

*  mapper映射文件

<mapper namespace="com.rg.dao.StudentDao">
        
        <select id="selectClasses" resultMap="classes_student_map">
            select c.*, s.stu_name
            from student_class c
                     inner join student s on c.id = s.s_id
            where c.id = #{id}
        </select>
        <resultMap id="classes_student_map" type="com.rg.bean.Classes">
            <id property="id" column="id"/>
            <result property="class_name" column="class_name"/>
            
            <collection property="students" ofType="com.rg.bean.Student">
                <result property="stu_name" column="stu_name"/>
            </collection>
            
        </resultMap>
    
        
        <select id="selectClasses" resultMap="classes_student_map">
            select id,class_name from student_class where id = #{id}
        </select>
        <resultMap id="classes_student_map" type="com.rg.bean.Classes">
            <id property="id" column="id"/>
            <result property="class_name" column="class_name"/>
            
            <collection property="students" column="id" select="selectStudents" ofType="com.rg.bean.Student"/>
        </resultMap>
        <select id="selectStudents" resultType="com.rg.bean.Student">
            select stu_name from student where s_id = #{id}
        </select>
    
        
        <select id="selectClasses" resultMap="classes_student_map">
            select c.*, s.stu_name,s.id as sid
            from student_class c
                     inner join student s on c.id = s.s_id
            where c.id = #{id}
        </select>
        <resultMap id="classes_student_map" type="com.rg.bean.Classes">
            <id property="id" column="id"/>
            <result property="class_name" column="class_name"/>
            
            <collection property="students" resultMap="classes_map"/>
        </resultMap>
        
        <resultMap id="classes_map" type="com.rg.bean.Student">
            
            <id property="id" column="sid"/>
            <result property="stu_name" column="stu_name"/>
        </resultMap>
    </mapper>

*  结果

软件2021班
    该班级有如下学生：
    Student{id=14, stu_name='小王', stu_age=0, address='null', classes=null}
    Student{id=16, stu_name='小赵', stu_age=0, address='null', classes=null}

[e5dd1d3d5d6d10b3d5f4030030186f1a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/3533deec0d914e299e89d87c7950de81.png