MyBatis如何实现多表查询

先说结论,在MyBatis中我们有两种方式来实现多表查询

一、使用sqlmapper配置文件

二、使用注解

直入主题首先我们来看如何使用配置文件的方式来实现一对一，和一对多查询

首先我们有教师表和课程表，这里我们假定：一个课程可以有多个老师（一对多），一个老师只教一门课程（一对一）

课程表

教师表

先来看一对一怎么写

在实体类中我们需要创建一个 课程类型的外部属性

public class Teacher {
    private int tid;
    private String tname;
    private String tsex;
    private Date tbirthday;
    private String taddress;
    private String temail;
    private String tmoney;
    private int cid;
    //外部属性
    private Course course;
}

TeacherMapper.xml

这里在ResultMap标签中使用association标签来建立一对一的映射关系

<mapper namespace="com.day4.mapper.TeacherMapper">
    <!-- 类路径 -->
    <resultMap id="Teahcer_Scoure_Map" type="Teacher">
        <result property="tid" column="tid"/>
        <result property="tname" column="tname"/>
        <result property="tbirthday" column="tbirthday"/>
        <result property="tsex" column="tsex"/>
        <result property="taddress" column="taddress"/>
        <result property="temail" column="temail"/>
        <result property="tmoney" column="tmoney"/>
        <result property="cid" column="cid"/>

        <association property="course"  >
            <result column="cid" property="cid"/>
            <result column="cname" property="cname"/>
        </association>
    </resultMap>
    <select id="FindAllTeacherbysqlmapper" resultMap="Teahcer_Scoure_Map">
        select * from course inner join teacher
        on teacher.cid = course.cid

    </select>

</mapper>

结果：

一对多

我们需要在课程的实体类中创建一个老师类型的集合

public class Course {
    private int cid;
    private String cname;
    //外部属性
    private List<Teacher> tlsit;
   }

CourseMapper.xml

这里在ResultMap标签中使用collection标签来建立一对多的映射关系

<mapper namespace="com.day4.mapper.CourseMapper">
    <!-- 类路径 -->
    <resultMap id="Teahcer_Scoure_Map" type="course">

        <result column="cid" property="cid"/>
        <result column="cname" property="cname"/>

        <collection property="tlsit" ofType="Teacher" >

            <result property="tid" column="tid"/>
            <result property="tname" column="tname"/>
            <result property="tbirthday" column="tbirthday"/>
            <result property="tsex" column="tsex"/>
            <result property="taddress" column="taddress"/>
            <result property="temail" column="temail"/>
            <result property="tmoney" column="tmoney"/>

        </collection>
    </resultMap>
    <select id="FindAllCoursebysqlmapper" resultMap="Teahcer_Scoure_Map">
        select * from teacher inner join course
        on teacher.cid = course.cid
    </select>

</mapper>

结果：

使用注解的方式

同样我们需要在Teacher实体类中创建一个课程类型的外部属性

Teacher.java

public class Teacher {
    private int tid;
    private String tname;
    private String tsex;
    private Date tbirthday;
    private String taddress;
    private String temail;
    private String tmoney;
    private int cid;
    //外部属性
    private Course course;
}

TeacherMapper接口
使用@Results完成数据库字段和JavaBean属性的映射@Results中可以包含多个@Result

    @Results({
       @Result (column="cid" ,property="cid"),
       @Result( column="cid" ,property="course",
       <!-- @One 实现一对一-->
         one=@One(select = "com.day4.mapper.CourseMapper.FindAllCoursebyid"))
    })
    @Select("select * from teacher")
    public List<Teacher> FindAllTeacher();

CourseMapper接口

    @Select("select * from course where cid = #{v}")
    public Course FindAllCoursebyid(int v);

结果：

一对多

Course.java实体类

public class Course {
    private int cid;
    private String cname;
    private int tid;
    private List<Teacher> tlsit;
}

TeacherMapper.java实体类

 @Select("select * from teacher where cid = #{v}")
    public Teacher FindAllTeacherbyid(int cid);

CourseMapper接口

    @Results({
            @Result (column="cid" ,property="cid"),
            @Result( column="cid" ,property="tlsit",
            <!-- @Many 实现一对多-->
              many=@Many(select = "com.day4.mapper.TeacherMapper.FindAllTeacherbyid"))
    })
    @Select("select * from course")
    public List<Course> FindAllCourser();

结果：