MyBatis连表查询(多对多)

1、创建实体类，学生、老师、学生老师表

<!--学生表-->
CREATE TABLE student(
sid INT NOT NULL AUTO_INCREMENT,
sname VARCHAR(30),
PRIMARY
KEY(sid)
);
<!--老师表-->
CREATE TABLE teacher(
tid INT NOT NULL AUTO_INCREMENT,
tname VARCHAR(30),
PRIMARY
KEY(tid)
);
<!--学生老师表-->
CREATE TABLE student_teacher(
s_id INT NOT NULL,
t_id INT NOT NULL,
PRIMARY KEY(s_id,t_id),
FOREIGN KEY(s_id) REFERENCES student(sid),
FOREIGN KEY(t_id)
REFERENCES teacher(tid)
);

<!--添加数据-->
INSERT INTO student(sname) VALUES('张三'),
('李四');
INSERT INTO teacher (tname) VALUES('刘老师'),
('李老师');
INSERT INTO student_teacher(s_id,t_id) VALUES(1,1),(1,2),(2,1);

2、IDEA中创建对应的实体类

学生:

public class Student {
    private int sId;
    private String sName;
    private List<StudentTeacher> studentTeacherList;

    public List<StudentTeacher> getStudentTeacherList() {
        return studentTeacherList;
    }

    public void setStudentTeacherList(List<StudentTeacher> studentTeacherList) {
        this.studentTeacherList = studentTeacherList;
    }

    public int getsId() {
        return sId;
    }

    public void setsId(int sId) {
        this.sId = sId;
    }

    public String getsName() {
        return sName;
    }

    public void setsName(String sName) {
        this.sName = sName;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sId=" + sId +
                ", sName='" + sName + '\'' +
                ", studentTeacherList=" + studentTeacherList +
                '}';
    }
}

老师:

public class Teacher {
    private int tId;
    private String tName;

    public int gettId() {
        return tId;
    }

    public void settId(int tId) {
        this.tId = tId;
    }

    public String gettName() {
        return tName;
    }

    public void settName(String tName) {
        this.tName = tName;
    }

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

学生老师:

public class StudentTeacher {
    private int sid;
    private int tid;
    private Teacher teacher;


    public Teacher getTeacher() {
        return teacher;
    }

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

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }

    @Override
    public String toString() {
        return "StudentTeacher{" +
                "sid=" + sid +
                ", tid=" + tid +
                ", teacher=" + teacher +
                '}';
    }
}

3、编写持久类接口方法

学生:

public interface StudentDao {
    Student fingByName(String name);
}

老师:

public interface TeacherDao {
    Teacher geTeacherByTid(int tid);
}

学生老师:

public interface StudentTeacherDao {
    List<StudentTeacher> getStudentTeacherBySid(int sid);
}

4、编写对应的映射接口文档

学生:

<?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.ujiuye.dao.StudentDao">


    <resultMap id="stuResult" type="com.ujiuye.entity.Student">
        <id column="sid" property="sId"/>
        <result column="sname" property="sName"/>
        <collection column="sid" property="studentTeacherList" ofType="com.ujiuye.entity.StudentTeacher" select="com.ujiuye.dao.StudentTeacherDao.getStudentTeacherBySid">
        </collection>
    </resultMap>
    <select id="fingByName" resultMap="stuResult">
        select sid,sname from student  where sname=#{name}
    </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">
<mapper namespace="com.ujiuye.dao.TeacherDao">
    <resultMap id="teaResult" type="com.ujiuye.entity.Teacher">
        <id column="tid" property="tId"/>
        <result column="tname" property="tName"/>
    </resultMap>

    <select id="geTeacherByTid" resultMap="teaResult">
        select tid,tname from teacher where tid=#{tid}
    </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">
<mapper namespace="com.ujiuye.dao.StudentTeacherDao">
    <resultMap id="stResult" type="com.ujiuye.entity.StudentTeacher">
        <id column="s_id" property="sid"/>
        <id column="t_id" property="tid"/>
        <association  column="t_id" property="teacher" javaType="com.ujiuye.entity.Teacher"
        select="com.ujiuye.dao.TeacherDao.geTeacherByTid">
        </association>
    </resultMap>

    <select id="getStudentTeacherBySid" resultMap="stResult">
        select s_id,t_id from student_teacher where s_id=#{sid}
    </select>
</mapper>