MyBatis如何实现多对多查询

MyBatis如何实现多对多查询

多个学生有多个老师代课教师

创建：student(学生表)，teacher(老师表)，以及中间表

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)

pom

 <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

Xml配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--  环境配置：支持多环境开发  -->
    <environments default="development">
        <!--  开发环境  -->
        <environment id="development">
            <!--  事务管理器  -->
            <transactionManager type="JDBC"/>
            <!--  数据源:驱动、连接、用户名、密码  -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://localhost:3306/person?useUnicode=true&amp;characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="123"/>
            </dataSource>
        </environment>
    </environments>
    <!--  配置映射文件：SQL语句所在位置  -->
    <mappers>
        <package name="com.springbmybtis.mapper"/>
    </mappers>
</configuration>

dao类

Student

public class Student {

    private int id;
    private String name;
    private List<StudentTeacher> studentTeacherList;

    public Student() {
    }

    public Student(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public Student(int id, String name, List<StudentTeacher> studentTeacherList) {
        this.id = id;
        this.name = name;
        this.studentTeacherList = studentTeacherList;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

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

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

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

StudentTeacher

public class StudentTeacher {

    private int sid;
    private int tid;
    private Teacher teacher;

    public StudentTeacher() {
    }

    public StudentTeacher(int sid, int tid) {
        this.sid = sid;
        this.tid = tid;
    }


    public StudentTeacher(int sid, int tid, Teacher teacher) {
        this.sid = sid;
        this.tid = tid;
        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 +
                '}';
    }
}

Teacher

public class Teacher {

    private int id;
    private String name;

    public Teacher() {
    }

    public Teacher(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

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

Mapper

StudentMapper

public interface StudentMapper {
    Student queryById(int id);

}

Mapper.Xml

StudentMapper.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：命名空间，隔离SQL -->
<mapper namespace="com.springbmybtis.mapper.StudentMapper">
    <!--
       id：SQL语句名称，具有唯一性
       resultType：返回结果类型  实体类全限定名
       parameterType：输入参数类型  int  Integer
   -->
    <resultMap id="StudentMap" type="com.springbmybtis.dao.Student">
        <id column="sid" property="id"></id>
        <result column="sname" property="name"></result>
        <collection property="studentTeacherList" ofType="com.springbmybtis.dao.StudentTeacher">
            <result column="s_id" property="sid"></result>
            <result column="t_id" property="tid"></result>
            <association property="teacher" javaType="com.springbmybtis.dao.Teacher">
                <id column="tid" property="id"></id>
                <result column="tname" property="name"></result>
            </association>
        </collection>
    </resultMap>

    <select id="queryById" parameterType="int" resultMap="StudentMap">
        SELECT s.*,st.*,t.*
        FROM student s,student_teacher st,teacher t
        WHERE s.sid=st.s_id
        AND st.t_id=t.tid
        AND s.sid=#{id}
    </select>


</mapper>

测试类

@Test
    public void queryByIdplus() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = mapper.queryById(1);
        System.out.println(student);
        sqlSession.close();
    }

测试结果

log4j:WARN No appenders could be found for logger (org.apache.ibatis.logging.LogFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
Student{id=1, name='张三', studentTeacherList=[StudentTeacher{sid=1, tid=1, teacher=TeacherMapper{id=1, name='刘老师'}}, StudentTeacher{sid=1, tid=2, teacher=TeacherMapper{id=2, name='李老师'}}]}

Process finished with exit code 0