myBatis学习笔记（4）——一对一关联表查询

Bertha 。 2022-08-08 06:57 158阅读 0赞

# 需求 #

现有两个实体，student和teacher，假设一个学生对应一个老师，他们是一对一的关系，我们想要在查询学生的时候，也能得到该学生的老师的信息

# 准备 #

数据库两张表：student和teacher  
![这里写图片描述][20150512164629491]![这里写图片描述][20150512164703109]  
实体类

package com.bank.entities;
    
    public class Teacher { 
        private int teacherId;
        private String teacherName;
    
        public int getTeacherId() {
            return teacherId;
        }
        public void setTeacherId(int teacherId) {
            this.teacherId = teacherId;
        }
        public String getTeacherName() {
            return teacherName;
        }
        public void setTeacherName(String teacherName) {
            this.teacherName = teacherName;
        }
        @Override
        public String toString() {
            return "Teacher [teacherId=" + teacherId + ", teacherName="
                    + teacherName + "]";
        }
    
    }

package com.bank.entities;
    
    public class Student { 
    
        private int stuId;
        private String stuName;
        private Teacher teacher;
        public int getStuId() {
            return stuId;
        }
        public void setStuId(int stuId) {
            this.stuId = stuId;
        }
        public String getStuName() {
            return stuName;
        }
        public void setStuName(String stuName) {
            this.stuName = stuName;
        }
        public Teacher getTeacher() {
            return teacher;
        }
        public void setTeacher(Teacher teacher) {
            this.teacher = teacher;
        }
        @Override
        public String toString() {
            return "Student [stuId=" + stuId + ", stuName=" + stuName
                    + ", teacher=" + teacher + "]";
        }
    
    }

> 注意：在Student类中，有一个属性是Teacher，而不是teacherId

# 问题 #

很显然，我们需要关联student和teacher两张表，那么，多表查询的结果集，如何填充到student实体类中的同时，也把teacher属性中的属性对应填充好呢？

# 方案一 #

<resultMap type="Student" id="studentResultMap1">
            <id property="stuId" column="stu_id"/>
            <result property="stuName" column="stu_name"/>
            
            <association property="teacher" javaType="Teacher">
                <id property="teacherId" column="teacher_id"/>
                <result property="teacherName" column="teacher_name"/>
            </association>
        </resultMap>
    
        
        <select id="getStudent1" parameterType="int" resultMap="studentResultMap1">
            select S.stu_id,S.stu_name,T.teacher_id,T.teacher_name
                from student S,teacher T
                where stu_id=#{id}
                and   S.stu_id=T.teacher_id
        </select>

> 这种方法是直接关联两张表，执行一次查询

# 方案二 #

<select id="getStudent2" parameterType="int" resultMap="studentResultMap2">
            select * from student where stu_id=#{id}
        </select>
        <select id="getTeacher" parameterType="int" resultType="Teacher">
            select teacher_id teacherId,teacher_name teacherName from teacher where teacher_id=#{id}
        </select>
    
        <resultMap type="Student" id="studentResultMap2">
            <id property="stuId" column="stu_id"/>
            <result property="stuName" column="stu_name"/>
            
            <association property="teacher" column="teacher_id" select="getTeacher">
            </association>
        </resultMap>

> 这种方法执行了两次查询：先查询学生信息，然后再用查询出来的teacherId去做第二次查询

两种方案得到的结果是一致的  
![这里写图片描述][20150512165156372]

[20150512164629491]: https://img-blog.csdn.net/20150512164629491
[20150512164703109]: /images/20220805/50da25e623dd47bda55f23b9f90292d0.png
[20150512165156372]: /images/20220805/db474d3015994e929dd9f10877bc024e.png