Mybatis关联映射多表查询（三种写法优缺点、主流性）以及分步查询延迟加载（按需加载、懒加载）

Scyth1

已于 2022-11-04 21:21:08 修改

阅读量1.9k

点赞数 9

文章标签： mybatis mysql 数据库

于 2022-11-04 21:20:57 首次发布

本文链接：https://blog.csdn.net/Scyth1/article/details/127675700

版权

Mybatis关联映射

真是保姆级的教程，因为我很菜。。。主要是给自己反复看的，所以非常非常详细

一、创建表结构

一对多、多对一（以查询学生的老师为例）三种写entity层的方式

一、创建表结构

我的MySQL表

student表

teacher表

mybatis配置问题这里就不提了，主要是记录一下mybatis的关联映射，方便我以后自己看

关联查询

一对多、多对一（以查询学生的老师为例）三种写entity层的方式

第一种：设置联合的实体类（不常用）

新建一个StudentTeacher关联实体类

package com.qcby.entity;

//这样做 只能查询 不能增删改查 无法做一些关联性的东西
public class StudentTeacher {
    private Integer SId;//学生的数据
    private String Sname;
    private String sex;
    private Integer age;
    private Integer t_id;
    private Integer TId;
    private String Tname;

    public Integer getSId() {
        return SId;
    }

    public void setSId(Integer SId) {
        this.SId = SId;
    }

    public String getSname() {
        return Sname;
    }

    public void setSname(String sname) {
        Sname = sname;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getT_id() {
        return t_id;
    }

    public void setT_id(Integer t_id) {
        this.t_id = t_id;
    }

    public Integer getTId() {
        return TId;
    }

    public void setTId(Integer TId) {
        this.TId = TId;
    }

    public String getTname() {
        return Tname;
    }

    public void setTname(String tname) {
        Tname = tname;
    }

    @Override
    public String toString() {
        return "StudentTeacher{" +
                "SId=" + SId +
                ", Sname='" + Sname + '\'' +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                ", t_id=" + t_id +
                ", TId=" + TId +
                ", Tname='" + Tname + '\'' +
                '}';
    }
}

新建StudentTeacher Dao层接口

新建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">
<mapper namespace="com.qcby.dao.StudentTeacherDao">
<!--映射-->
    <resultMap id="getStudentTeacher" type="com.qcby.entity.StudentTeacher"><!--type做具体映射-->
        <!--数据库关联映射-->
        <result column="id" property="SId"/>
        <result column="Sname" property="Sname"/>
        <result column="Tname" property="Tname"/>
    </resultMap>

    <select id="getStudentTeacher" resultMap="getStudentTeacher">
        select student.id,student.Sname,teacher.Tname from student left join teacher on student.t_id = teacher.id;
    </select>
</mapper>

resultMap做映射，在数据库学生表中id为id ，但是实体层我们定义的是SId

property:属性=>对应就是java类中的属性，也就是类中定义的字段；

column =>对应就是数据库表中的字段。

resultMap中的type要连接实体类

然后sql语句的标签中id要跟resultMap 的 id 一致

另一个属性是resultMap，不要写resultType

然后在Dao层去给方法定义

package com.qcby.dao;

import com.qcby.entity.StudentTeacher;

import java.util.List;

public interface StudentTeacherDao {
    List<StudentTeacher> getStudentTeacher();
}

在SqlMapConfig.xml中要映射

最后写Test类进行测试

package com.qcby;

import com.qcby.dao.StudentTeacherDao;
import com.qcby.entity.StudentTeacher;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class StudentTeacherDaoTest {
    private InputStream in = null;
    private SqlSession session = null;
    private StudentTeacherDao mapper = null;

    @Before  //前置通知, 在方法执行之前执行
    public void init() throws IOException {
        //加载主配置文件，目的是为了构建SqlSessionFactory对象
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //通过SqlSessionFactory工厂对象创建SqlSesssion对象
        session = factory.openSession(true);  //关闭
        //通过Session创建UserDao接口代理对象
        mapper = session.getMapper(StudentTeacherDao.class);
    }

    @After  //@After: 后置通知, 在方法执行之后执行 。
    public void destory() throws IOException {
        //释放资源
        session.close();
        in.close();
    }
    @Test
    public void StudentTeacherDao(){
        List<StudentTeacher> studentTeachers = mapper.getStudentTeacher();
        for (StudentTeacher  s:studentTeachers){
            System.out.println(s.toString());
        }
    }

}

运行test 可以看到控制台查询到的结果

我们发现一个问题，这样做单独建了一个StudentTeacher实体类

但是这个表只可以做一个查询，没有太多的操作空间

一般表有哪些，就应该有哪些实体类

第二种：连表查询

我们新建表的实体类

学生的实体类

package com.qcby.entity;

public class Student {
    private Integer id;
    private String Sname;
    private String sex;
    private Integer age;
    private Integer t_id;

    //学生当中包含老师
     private Teacher teacher;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getSname() {
        return Sname;
    }

    public void setSname(String sname) {
        Sname = sname;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getT_id() {
        return t_id;
    }

    public void setT_id(Integer t_id) {
        this.t_id = t_id;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", Sname='" + Sname + '\'' +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                ", t_id=" + t_id +
                ", teacher=" + teacher +
                '}';
    }
}

这里不要忘记加上老师这个对象

学生和老师是多对一的关系

在学生表中写一个teacher类学生中包含着老师

Teacher类

package com.qcby.entity;

public class Teacher {
    private Integer id;
    private String Tname;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTname() {
        return Tname;
    }

    public void setTname(String tname) {
        Tname = tname;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", Tname='" + Tname + '\'' +
                '}';
    }
}

新建一个学生的StudentDao

在mapper中新建StudentDao.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">
<mapper namespace="com.qcby.dao.StudentDao">
    <!---->

    <select id="getStudentTeacher1" resultMap="StudentTeacher1">
        select student.id,student.Sname,teacher.Tname from student left join teacher on student.t_id = teacher.id;
    </select>
    <resultMap id="StudentTeacher1" type="com.qcby.entity.Student">
        <result property="id" column="id"/>
        <result property="Sname" column="Sname"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
        <result property="t_id" column="t_id"/>
        <!--复杂属性单独处理 对象用association-->
        <!--javaType:该属性的类型-->
        <association property="teacher" javaType="com.qcby.entity.Teacher">
            <result property="id" column="id"/>
            <result property="Tname" column="Tname"/>
        </association>
    </resultMap>
</mapper>

特殊属性特殊处理，我们在创建entity层Student的实体类时，定义了Teacher类

这里应该用<association>标签进行处理

接下来通过Test测试一下

编写StudentDao层

package com.qcby.dao;

import com.qcby.entity.Student;

import java.util.List;

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

编写StudentDaoTest测试

package com.qcby;

import com.qcby.dao.StudentDao;
import com.qcby.entity.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class StudentDaoTest {
    private InputStream in = null;
    private SqlSession session = null;
    private StudentDao mapper = null;

    @Before  //前置通知, 在方法执行之前执行
    public void init() throws IOException {
        //加载主配置文件，目的是为了构建SqlSessionFactory对象
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //通过SqlSessionFactory工厂对象创建SqlSesssion对象
        session = factory.openSession(true);  //关闭
        //通过Session创建UserDao接口代理对象
        mapper = session.getMapper(StudentDao.class);
    }

    @After  //@After: 后置通知, 在方法执行之后执行 。
    public void destory() throws IOException {
        //释放资源
        session.close();
        in.close();
    }
@Test
    public void getStudentTeacher1(){
        List<Student> studentTeachers1 = mapper.getStudentTeacher1();
        for (Student s:studentTeachers1){
            System.out.println(s.toString());
        }

    }

}

测试前注意一下SqlMapConfig.xml中有没有映射到Dao层，

<mappers>
        <mapper resource="mapper/StudentDao.xml"/>
</mappers>

如果没有映射到，是无法加载到的。

通过Test运行测试成功查询到了每个学生的老师的信息

三、分步查询

编写StudentDao.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">
<mapper namespace="com.qcby.dao.StudentDao">
    <!--分步查询-->
    <select id="getStudent" resultMap="StudentTeacher2">
        select * from student;
    </select>
        <resultMap id="StudentTeacher2" type="com.qcby.entity.Student">
            <result property="id" column="id"/>
            <result property="Sname" column="Sname"/>
            <result property="sex" column="sex"/>
            <result property="age" column="age"/>
            <result property="t_id" column="t_id"/>
            <!--column:传值-->
            <!--select:调用-->
            <association property="teacher" javaType="com.qcby.entity.Teacher"
                         column="t_id" select="getTeacher"/>
        </resultMap>
        <select id="getTeacher" resultType="com.qcby.entity.Teacher">
            select teacher.Tname from teacher where id = #{id}
        </select>
</mapper>

column是用来传值的，select是调用的，里面填写select的语句标签的id

编写Dao层的StudentDao接口

package com.qcby.dao;

import com.qcby.entity.Student;

import java.util.List;

public interface StudentDao {

    List<Student> getStudent();
}

最后再写Test 来进行测试

package com.qcby;

import com.qcby.dao.StudentDao;
import com.qcby.entity.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class StudentDaoTest {
    private InputStream in = null;
    private SqlSession session = null;
    private StudentDao mapper = null;

    @Before  //前置通知, 在方法执行之前执行
    public void init() throws IOException {
        //加载主配置文件，目的是为了构建SqlSessionFactory对象
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //通过SqlSessionFactory工厂对象创建SqlSesssion对象
        session = factory.openSession(true);  //关闭
        //通过Session创建UserDao接口代理对象
        mapper = session.getMapper(StudentDao.class);
    }

    @After  //@After: 后置通知, 在方法执行之后执行 。
    public void destory() throws IOException {
        //释放资源
        session.close();
        in.close();
    }

    @Test
    public void getStudent(){
        List<Student> studentTeachers1 = mapper.getStudent();
        for (Student student:studentTeachers1){
            System.out.println(student.getSname());//查询学生的名字（跟老师没关系）
        }

    }

}

运行测试

我们可以看到以下结果

StudentDao.xml的

这句话其实可以新建一个TeacherDao.xml写在里面做一个单独的存在

这样做的好处就是两条sql语句都可以单独的出现

在Dao层新建TeacherDao

在mapper中新建TeacherDao.xml

然后做定义 select id为getTeacherById

<?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">
<mapper namespace="com.qcby.dao.TeacherDao">

    <select id="getTeacherById" resultType="com.qcby.entity.Teacher" parameterType="java.lang.Integer">
        select * from teacher where id = #{t_id}
    </select>
</mapper>

在dao层中进行调用具体实现

package com.qcby.dao;

import com.qcby.entity.Teacher;

import java.util.List;

public interface TeacherDao {
    public Teacher getTeacherById(Integer id);
}

进行改动过后，我们StudentDao.xml 中的select调用也要进行改变

这里就不能再写getTeacher了，改成刚定义的getTeacherById

注意：select里用是dao层

Test测试中不用进行改变，我们进行测试，结果如下

可以看到，分步查询正常测试出来了

把sql语句单独体现在TeacherDao 中，我们也可以新建一个TeacherTest测试，进行单独调用

新建TeacherDao

package com.qcby.dao;

import com.qcby.entity.Teacher;


public interface TeacherDao {
    public Teacher getTeacherById(Integer id);
}

Test测试

package com.qcby;

import com.qcby.dao.TeacherDao;
import com.qcby.entity.Teacher;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;

public class TeacherDaoTest {
    private InputStream in = null;
    private SqlSession session = null;
    private TeacherDao mapper = null;

    @Before  //前置通知, 在方法执行之前执行
    public void init() throws IOException {
        //加载主配置文件，目的是为了构建SqlSessionFactory对象
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //通过SqlSessionFactory工厂对象创建SqlSesssion对象
        session = factory.openSession(true);  //关闭
        //通过Session创建UserDao接口代理对象
        mapper = session.getMapper(TeacherDao.class);
    }

    @After  //@After: 后置通知, 在方法执行之后执行 。
    public void destory() throws IOException {
        //释放资源
        session.close();
        in.close();
    }
    @Test
    public void getTeacherById(){
       Teacher teacherById = mapper.getTeacherById(1);
        System.out.println(teacherById.toString());
    }
}

查询id为1的老师来测试

运行，结果如下

相当于一个参数两用，自身也可以进行使用，相当于这个sql语句可以进行重复性使用

以上有两种写法来表示查询信息，分别是连表查询和分步查询的方法。那么既然我么能用一个SQL语句能够执行完，那为什么还要分开来写呢？

原因很简单：可以发现如果我们把他们连在一起那么他们就是一个多表查询语句，如果不放在一起执行，那那就是单独一个表的查询语句。

分步查询的优点:可以实现延迟加载，但是必须在核心配置文件中设置全局配置信息

一对多（以查询老师的学生为例）

一个老师有多个学生，意味着一个老师有多个对象

在Teacher实体类中定义一个List集合重新get set tostring方法

一、关联映射

entity层的Teacher类

package com.qcby.entity;

import java.util.List;

public class Teacher {
    private Integer id;
    private String Tname;
    List<Student> students;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTname() {
        return Tname;
    }

    public void setTname(String tname) {
        Tname = tname;
    }

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", Tname='" + Tname + '\'' +
                ", students=" + students +
                '}';
    }
}

编写mapper中的TeacherDao.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">
<mapper namespace="com.qcby.dao.TeacherDao">

    <select id="getTeacher" resultMap="TeacherStudent">
        select teacher.*,student.Sname from teacher left join student on student.t_id = teacher.id
    </select>
    <resultMap id="TeacherStudent" type="com.qcby.entity.Teacher">
        <result property="id" column="id"/>
        <result property="Tname" column="Tname"/>
        <!--复杂字段特殊处理，对象用association 集合用collection-->
        <!--ofType：集合中泛型的信息 这里写students的类型-->
        <collection property="students" ofType="com.qcby.entity.Student">
            <!--只查询出来学生的名字，可以只写Sname-->
            <result property="Sname" column="Sname"/>
        </collection>
    </resultMap>
</mapper>

在Dao层的TeacherDao中添加方法

package com.qcby.dao;

import com.qcby.entity.Teacher;

import java.util.List;


public interface TeacherDao {
    public Teacher getTeacherById(Integer id);

    public List<Teacher> getTeacher();
}

写一个测试方法进行测试

@Test
    public void getTeacher(){
        List<Teacher> teachers = mapper.getTeacher();
        for (Teacher teacher:teachers){
            System.out.println(teacher.toString());
        }
    }

测试通过，输出结果

老师下的所有学生都被输出了出来，通过测试

因为只查询了Sname，所以其他字段显示为null

二、分步查询

先查询老师，根据老师的id去匹配学生的t_id 字段

1.select * from teacher;

2.select * from student where t_id = #{id}

写TeacherDao.xml

 <select id="getTeacher1" resultMap="TeacherStudent1">
        select * from teacher;
    </select>
    <resultMap id="TeacherStudent1" type="com.qcby.entity.Teacher">
        <result property="id" column="id"/>
        <result property="Tname" column="Tname"/>
        <collection property="students" ofType="com.qcby.entity.Student"
                    column="id" select="getStudntByTid"/>
    </resultMap>
    <select id="getStudntByTid" resultType="com.qcby.entity.Student">
        select * from student where t_id = #{id};
    </select>

配置dao层的TeacherDao

public List<Teacher> getTeacher1();

新建Test测试进行测试

@Test
    public void getTeacher1(){
        List<Teacher> teachers = mapper.getTeacher1();
        for (Teacher teacher:teachers){
            System.out.println(teacher.toString());
        }
    }

输出结果

老师所有的学生都成功被输出

当然我们也可以进行改进，将第二个sql语句放在StudentDao.xml里

并在Dao层的StudentDao中进行使用返回Student对象

Student getStudentByTid(Integer tid);

调用的配置进行修改

测试发现依旧能通过测试

多对多

这里有两个例子，一个是查询老师教的班级，一个是班级的授课老师

例如教师和授课班级之间的关系，甲乙丙三个老师分别教ABC三个班

多对多一般用分步的形式

因为组合形式sql太大，影响我们的执行时间

我们先创建一个班级表

再建一个老师班级关系表

c_id 班级id

t_id 教师id

分步实现

例1.查询老师上课的班级

1.select * from teacher ;//查询出老师的id

2.匹配查询，让班级和关系表关联查询

select classes.id,classes.className,rel.t_id from classes left join rel on rel.c_id = classes. id where t_id = #{id}

用Mybatis实现

新建Classes实体类

package com.qcby.entity;

import java.util.Date;

public class Classes {
    private Integer id;
    private String classesName;
    private Date creatTime;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getClassesName() {
        return classesName;
    }

    public void setClassesName(String classesName) {
        this.classesName = classesName;
    }

    public Date getCreatTime() {
        return creatTime;
    }

    public void setCreatTime(Date creatTime) {
        this.creatTime = creatTime;
    }

    @Override
    public String toString() {
        return "Classes{" +
                "id=" + id +
                ", classesName='" + classesName + '\'' +
                ", creatTime=" + creatTime +
                '}';
    }
}

新建rel实体类

package com.qcby.entity;

public class RelationShip {
    private Integer id;
    private Integer t_id;
    private Integer c_id;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getT_id() {
        return t_id;
    }

    public void setT_id(Integer t_id) {
        this.t_id = t_id;
    }

    public Integer getC_id() {
        return c_id;
    }

    public void setC_id(Integer c_id) {
        this.c_id = c_id;
    }

    @Override
    public String toString() {
        return "RelationShip{" +
                "id=" + id +
                ", t_id=" + t_id +
                ", c_id=" + c_id +
                '}';
    }
}

查询老师教的班级，主体在老师当中

更改Teacher实体类

package com.qcby.entity;

import java.util.List;

public class Teacher {
    private Integer id;
    private String Tname;
    private List<Classes> classes;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTname() {
        return Tname;
    }

    public void setTname(String tname) {
        Tname = tname;
    }

    public List<Classes> getClasses() {
        return classes;
    }

    public void setClasses(List<Classes> classes) {
        this.classes = classes;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", Tname='" + Tname + '\'' +
                ", classes=" + classes +
                '}';
    }
    
}

编写TeacherDao.xml

<select id="getTeacherClasses" resultMap="TeacherClasses">
        select * from teacher;
    </select>
    <resultMap id="TeacherClasses" type="com.qcby.entity.Teacher">
        <result property="id" column="id"/>
        <result property="Tname" column="Tname"/>
        <collection property="classes" ofType="com.qcby.entity.Classes" column="id" select="getClassesByTeacher"/>
    </resultMap>
    <select id="getClassesByTeacher" resultType="com.qcby.entity.Classes">
        select classes.id,classes.className,rel.t_id from classes left join rel on rel.c_id = classes.id where t_id = #{id}
    </select>

在Dao层实现

public List<Teacher> getTeacherClasses();

编写测试

@Test
    public void getClassesByTeacher(){
        List<Teacher> teacherClasses = mapper.getTeacherClasses();
        for(Teacher teacher:teacherClasses){
            System.out.println(teacher.toString());
        }
    }

运行测试结果如下

改进

将第二步分开

新建一个RelationShipDao

将dao层放进config里

然后在Dao层新建一个RelationShipDao.xml

将第二步sql语句拿过来

<?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">
<mapper namespace="com.qcby.dao.RelationShipDao">
    <select id="getClassesByTeacher" resultType="com.qcby.entity.Classes">
        select classes.id,classes.className,rel.t_id from classes left join rel on rel.c_id = classes.id where t_id = #{id}
    </select>
</mapper>

改变TeacherDao.xml 中的select

在Dao层实现

package com.qcby.dao;

import com.qcby.entity.Classes;

import java.util.List;

public interface RelationShipDao {
   List<Classes> getClassesByTeacher();
}

运行Test方法进行测试正常输出

例2.查询班级的老师

1.select * from classes

2.select teacher.* from teacher left join rel on rel.t_id = teacher.id where c_id = #{id}

新建ClassesDao.xml

在Classes实体类中添加老师类，重写get set tostring方法

package com.qcby.entity;

import java.util.Date;
import java.util.List;

public class Classes {
    private Integer id;
    private String classesName;
    private Date creatTime;

    private List<Teacher> teachers;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getClassesName() {
        return classesName;
    }

    public void setClassesName(String classesName) {
        this.classesName = classesName;
    }

    public Date getCreatTime() {
        return creatTime;
    }

    public void setCreatTime(Date creatTime) {
        this.creatTime = creatTime;
    }

    public List<Teacher> getTeachers() {
        return teachers;
    }

    public void setTeachers(List<Teacher> teachers) {
        this.teachers = teachers;
    }

    @Override
    public String toString() {
        return "Classes{" +
                "id=" + id +
                ", classesName='" + classesName + '\'' +
                ", creatTime=" + creatTime +
                ", teachers=" + teachers +
                '}';
    }
}

编写ClassesDao.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">
<mapper namespace="com.qcby.dao.ClassesDao">
    <select id="getClassesTeacher" resultMap="ClassTeacher">
        select * from classes;
    </select>
    <resultMap id="ClassTeacher" type="com.qcby.entity.Classes">
        <result property="id" column="id"/>
        <result property="className" column="className"/>
        <result property="createTime" column="CreateTime"/>
        <collection property="teachers" ofType="com.qcby.entity.Teacher"
                    column="id" select="getTeacherById"/>
    </resultMap>
    <select id="getTeacherById" resultType="com.qcby.entity.Teacher">
        select teacher.* from teacher left join rel on rel.t_id = teacher.id where c_id = #{id}
    </select>
</mapper>

编写Dao层ClassesDao

package com.qcby.dao;

import com.qcby.entity.Classes;

import java.util.List;

public interface ClassesDao {
    public List<Classes> getClassesTeacher();
}

编写Test测试

package com.qcby;

import com.qcby.dao.ClassesDao;
import com.qcby.entity.Classes;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class ClassesDaoTest {
    private InputStream in = null;
    private SqlSession session = null;
    private ClassesDao mapper = null;

    @Before  //前置通知, 在方法执行之前执行
    public void init() throws IOException {
        //加载主配置文件，目的是为了构建SqlSessionFactory对象
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //通过SqlSessionFactory工厂对象创建SqlSesssion对象
        session = factory.openSession(true);  //关闭
        //通过Session创建UserDao接口代理对象
        mapper = session.getMapper(ClassesDao.class);
    }

    @After  //@After: 后置通知, 在方法执行之后执行 。
    public void destory() throws IOException {
        //释放资源
        session.close();
        in.close();
    }
    @Test
    public void getClassesTeacher(){
        List<Classes> classesTeachers = mapper.getClassesTeacher();
        for (Classes classes:classesTeachers){
            System.out.println(classes.toString());
        }
    }
}

测试结果

实现延迟加载以及一些对比

lazyLoadingEnabled:延迟加载的全局开关。当开启时，所有关联对象都会延迟加载

aggressiveLazyLoding:当开启时，任何方式的调用都会加载该对象的所有属性。否则，该属性会按需加载

此时就可以实现按需加载，需要获取的数据是什么，就只会执行相应的sql.此时会通过association和collection中的fetchType属性设置当前的分步查询是否使用懒加载

fetchType=“lazy(延迟加载) | eager(立即加载)”

在SqlMapConfig.xml的settings中配置延迟加载

<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>

设置好延迟加载的配置后，我们在刚刚的Test测试类中测试一下

@Test
    public void getStudent(){
        List<Student> studentTeachers1 = mapper.getStudent();
        for (Student students:studentTeachers1){
            System.out.println(students.getSname());//查询学生的名字（跟老师没关系）
        }

    }

我们可以看到，加上延迟加载后，students.getSname只是查询出了学生的信息，并没有进行第二个sql语句查询老师的信息

再写一个输出进行测试

 @Test
    public void getStudent(){
        List<Student> studentTeachers1 = mapper.getStudent();
        for (Student students:studentTeachers1){
            //System.out.println(students.getSname());//查询学生的名字（跟老师没关系）
            System.out.println(students.getTeacher().getTname());
        }

    }

输出结果为