MyBatis中多对一关系的两种处理方式：联表查询与子查询

红太狼 2021-09-09 04:22 315阅读 0赞

> **多对一关联关系在现在现实生活中的例子很多很多，比如一个学校可以包含很多个学生，一个学生只属于一个学校，那么学生和学校就是多对一的关系。**

下面用例子演示如何进行多对一的处理

首先是有两个表，分别是

学生表，有主键id,  姓名，tid（外键，对应哪个老师）

![20210724222919750.png][]

教师表，有主键id,  姓名

![20210724223007561.png][]

上面两个表的关系是多对一的关系

![20210724223723165.png][]

--------------------

现在我们要从上面的表中：**查询每个学生信息以及每个学生对应的老师信息**

**表对应实体类**

package com.ys.pojo;
    
    import lombok.Data;
    import org.apache.ibatis.type.Alias;
    
    @Data
    @Alias("Student")
    public class Student {
        private int ID;
        private String name;
    //    学生需要关联一个老师
        private Teacher teacher;
    }

package com.ys.pojo;
    
    import lombok.Data;
    import org.apache.ibatis.type.Alias;
    
    @Data
    @Alias("Teacher")
    public class Teacher {
        private int id;
        private String name;
    }

--------------------

###  **方式一：联表查询，即按结果嵌套处理** ###

1、接口

public interface StudentMapper {
        List<Student> selectStudnets2();
    }

2.mapper.xml        **(复杂的属性要单独处理，对象用association，如果是集合，用collection）**

<select id="selectStudnets2" resultMap="studentteacher">
            select stu.ID sid,stu.name sname,tea.name tname
            from stu,tea
            where stu.tid=tea.id;
        </select>
        <resultMap id="studentteacher" type="Student">
            <result property="ID" column="sid"/>
            <result property="name" column="sname"/>
            <association property="teacher" javaType="Teacher">
                <result property="name" column="tname"/>
            </association>
        </resultMap>

3.测试

@Test
        public void selectStudnets() {
            SqlSession sqlSession = mybatisUtils.getSqlSession();
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            List<Student> students = mapper.selectStudnets2();
            for (Student student : students) {
                System.out.println(student);
            }
            sqlSession.close();
        }

4.结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDEwNzE0MA_size_16_color_FFFFFF_t_70][]

--------------------

###  方式二：子查询，即按查询嵌套处理 ###

1.接口

public interface StudentMapper {
        List<Student> selectStudnets3();
    }

2.mapper.xml

<select id="selectStudnets3" resultMap="StudentTeacher">
            select * from stu
        </select>
        <resultMap id="StudentTeacher" type="Student">
            <result property="ID" column="ID"/>
            <result property="name" column="name"/>
            <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
        </resultMap>
        <select id="getTeacher" resultType="Teacher">
            select *
            from tea
            where id=#{id}
        </select>

3.测试

@Test
        public void selectStudnets() {
            SqlSession sqlSession = mybatisUtils.getSqlSession();
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            List<Student> students = mapper.selectStudnets3();
            for (Student student : students) {
                System.out.println(student);
            }
            sqlSession.close();
        }

4.结果同上

[20210724222919750.png]: /images/20210813/6ccb653d23b64d06813c8f8e6c21a9f6.png
[20210724223007561.png]: /images/20210813/6a7fcedf8f5c4299aa2c101cfff7c2b8.png
[20210724223723165.png]: /images/20210813/3470b1f114ce4975806b600b15ae0d79.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDEwNzE0MA_size_16_color_FFFFFF_t_70]: /images/20210813/48421d69405e40738d254c53406ea60c.png