MyBatis-多表查询（多对多）

1、多表查询（多对多）

1.1、使用多表查询前的准备

教师表（teacher）：

教师学生表(tors)：

1.2、需求分析

查看所有老师下面的学生信息

老师信息与学生信息为多对多的关系，一个学生有多个老师，一个老师同样可以有多个学生。要想知道老师下的学生信息，不能直接查到我们的学生信息，只能通过中间表（tors）才能关联到学生信息。

SQL如下：

select t.tid,t.tname,s.* from teacher t left join tors ts on ts.tid=t.tid left join students s on s.sid=ts.sid order by t.tid

2、案例实现

2.1、创建Teacher.java文件

import java.io.Serializable;
import java.util.List;

public class Teacher implements Serializable {
    private int tid;
    private String tname;

    private List<StudentsNew> students;

    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;
    }

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

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

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

2.2、创建ITeacherDao.java文件

package com.tyschool.mb005.students.dao;

import com.tyschool.mb005.javabean.Teacher;

import java.util.List;

public interface ITeacherDao {
    List<Teacher> findAll();
}

2.3、创建ITeacherDao.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.tyschool.mb005.students.dao.ITeacherDao">
    <resultMap id="teacherMap" type="Teacher">
        <id column="tid" property="tid"></id>
        <result column="tname" property="tname"></result>
        <collection property="students" ofType="StudentsNew">
            <id column="sid" property="sid"></id>
            <result column="sname" property="sname"></result>
            <result column="ssex" property="ssex"></result>
            <result column="sage" property="sage"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="teacherMap">
        select s.*,t.tid,t.tname from teacher t left join tors ts on ts.tid=t.tid left join students s on s.sid=ts.sid order by t.tid
    </select>
</mapper>

<mappers>
<mapper resource="com/tyschool/mb005/students/dao/ITeacherDao.xml"></mapper>
</mappers>

2.4、创建MbTeacherTest.java文件

import com.tyschool.mb005.javabean.Classes;
import com.tyschool.mb005.javabean.Teacher;
import com.tyschool.mb005.students.dao.IClassesDao;
import com.tyschool.mb005.students.dao.ITeacherDao;
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 MbTeacherTest {
    private InputStream in;
    private SqlSession session;
    private ITeacherDao teacherDao;
    @Test
    public void findAll(){
        List<Teacher> list=teacherDao.findAll();
        for(Teacher t:list){
            System.out.println(t+":"+t.getStudents());
        }
    }
    @Before
    public void init()throws IOException {
        in= Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
        SqlSessionFactory factory=builder.build(in);
        session=factory.openSession();
        teacherDao=session.getMapper(ITeacherDao.class);
    }
    @After
    public void destroy() throws IOException {
        session.commit();;
        session.close();
        in.close();
    }
}