Mybatis多表查询

我们先创建两张表 一张为老师表，一张为学生表
数据库搭建：

CREATE TABLE teacher (
  id INT(10) NOT NULL,
  name VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(id, name) VALUES (1, '秦老师'); 
INSERT INTO teacher(id, name) VALUES (2, '张老师'); 

CREATE TABLE student (
  id INT(10) NOT NULL,
  name VARCHAR(30) DEFAULT NULL,
  tid INT(10) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY fktid (tid),
  CONSTRAINT fktid FOREIGN KEY (tid) REFERENCES teacher (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO student (id, name, tid) VALUES ('1', '小明', '1'); 
INSERT INTO student (id, name, tid) VALUES ('2', '小红', '1'); 
INSERT INTO student (id, name, tid) VALUES ('3', '小张', '1'); 
INSERT INTO student (id, name, tid) VALUES ('4', '小李', '2'); 
INSERT INTO student (id, name, tid) VALUES ('5', '小王', '2'); 
INSERT INTO student (id, name, tid) VALUES ('6', '小爽', '1'); 
INSERT INTO student (id, name, tid) VALUES ('7', '小杨', '2'); 
INSERT INTO student (id, name, tid) VALUES ('8', '小廖', '1'); 

编写实体类
student

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    private int tid;
    private Teacher teacher;
}

teacher

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher{
	private int id;
    private String 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">
<!--namespace=绑定一个对应的Dao/Mapper接口-->
<mapper namespace="com.zcm.dao.StudentMapper">

    <!--    两表查询 方法一 -->
    <select id="getStudent" resultMap="StuandTer">
        select student.id sid,student.name sname,teacher.id tid,teacher.name tname
        from student,teacher
        where student.tid=teacher.id
    </select>

<resultMap id="StuandTer" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

    <!--    两表查询 方法二 -->

    <select id="getStudent2" resultMap="st">
        select * from student;
    </select>

    <resultMap id="st" type="Student">
    <!--association关联属性  property属性名 javaType属性类型 column在多的一方的表中的列名-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"></association>
    </resultMap>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher
    </select>

</mapper>

我一般都习惯写方法一，方法二总感觉看着怪怪的 可读性没有方法一好

一对多的理解：

• 一个老师拥有多个学生

<?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">
<!--namespace=绑定一个对应的Dao/Mapper接口-->
<mapper namespace="com.zcm.dao.TeacherMapper">


    <select id="getTeacherStudent" resultMap="tstudent" >
        select  teacher.id tid,teacher.name tname,student.id sid,student.name sname
        from teacher,student
        where teacher.id=student.tid and teacher.id=#{tid}
    </select>
    <resultMap id="tstudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>
</mapper>

这边主要的差距就是collection主要是运用于一对多的情况，而association运用于多对一的场景