Mybatis关联查询

多对一和一对多

情景

一个班里的一个老师和班里的全部学生之间的关系就是一对多或多对一

• 多个学生对应一个老师
• 对于老师而言，可以用**集合(collection)**来描述这种关系。一个老师对应着多个学生形成的集合 【一对多】
• 对于学生而言，可以用**关联(association)**来描述这种关系。多个学生通过一个老师关联在了一起 【多对一】

多对一，一对一【关联】

数据库字段

Java实体类

public class Student{
    private int id;
    private String name;
    private Teacher teacher;
}

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

• 方式一：子查询

  <select id="getStudent" resultMap="StudentTeacher">
      select * from student
  </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  teacher where id=#{id}
  </select>
  

• 方式二：联表查询

     <!--    按照结果嵌套处理-->
  <select id="getStudent1" resultMap="StudentTeacher1">
     SELECT  student.id sid,student.name sname,teacher.name tname,teacher.id tid 
     FROM student  
     LEFT JOIN teacher  on student.tid = teacher.id
  </select>
  <resultMap id="StudentTeacher1" type="com.qcby.entity.Student">
      <result property="id" column="sid"/>
      <result property="name" column="sname"/>
      <association property="teacher" javaType="com.qcby.entity.Teacher">
          <result property="id" column="tid"/>
          <result property="name" column="tname"/>
      </association>
  </resultMap>
  

• 方式三：联表查询，借助VO类

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

  public class StudentVO{
      private int id;
      private String name;
      private String teacherName;
  }
  

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

  <select id="getStudent1" resultType="StudentVO">
      SELECT  student.id sid,student.name sname,teacher.name tname
      FROM student
      LEFT JOIN teacher  on student.tid = teacher.id
  </select>
  

一对多【集合】

Java实体类

public class Student{
    private int id;
    private String name;
    private Teacher teacher;
}

public class Teacher{
    private int id;
    private String name;
    private List<Student> student
}

通过老师的id查询老师信息，其中包括该老师的所有学生

方式一：联表查询

sql语句

SELECT t.id tid,t.name tname,s.id sid,s.name sname  
from teacher t 
left JOIN student s 
on t.id =s.tid
WHERE tid=1

xml

<select id="getTeacherById" resultMap="Teacher">
    SELECT t.id tid,t.name tname,s.id sid,s.name sname
    from teacher t
    left JOIN student s
    on t.id =s.tid
    WHERE t.id=#{id}
</select>
<!--老师的封装规则-->
<resultMap id="Teacher" type="com.qcby.model.Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <!--
        property="student" java类中的字段名
        ofType="com.qcby.model.Student"集合中元素的类型
    -->
    <collection property="student" ofType="com.qcby.model.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
    </collection>
</resultMap>

方式二：分布查询

<select id="getTeacherByIdStep" resultMap="TeacherStep">
    select * from teacher
</select>
<resultMap id="TeacherStep" type="com.qcby.model.Teacher">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <collection property="student" select="com.qcby.dao.TeacherDao.getStudentByTeacherId" column="{ tid = id }">
    </collection>
</resultMap> 

<!--根据老师的id找到所有的学生-->
<select id="getStudentByTeacherId" resultType="com.qcby.model.Student">
    select * from student where tid=#{tid}
</select>

当传递多个参数时可以

{ 待传入的参数名1=列名1，待传入的参数名2=列名2}