八.一对多和多对一 多表联查

创建数据库建表

一个老师有五个学生

多对一

多个学生对一个老师，学生对老师之间的关系是关联association

1. 编写实体类

@Data //GET,SET,ToString，有参，无参构造
public class Teacher {
    private int id;
    private String name;
}
@Data
public class Student {
    private int id;
    private String name;
    //多个学生可以是同一个老师，即多对一
    private Teacher teacher;
}

2.编写实体类对应的Mapper接口两个

package com.sy.dao;
import com.sy.pojo.Student;
import java.util.List;
public interface StudentMapper {
    public List<Student> getStudent();
    public List<Student> getStudent2();
}

package com.sy.dao;
import com.sy.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
public interface TeacherMapper {
    @Select("select * from mybatis.teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

3.编写Mapper接口对应的 mapper.xml配置文件（Student.xml）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sy.dao.StudentMapper">
<!--按照查询进行嵌套处理-->
<select id="getStudents" resultMap="StudentTeacher">
      select * from student
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <!--association关联属性  property属性名 javaType属性类型 column在多的一方的表中的列名-->
        <association property="teacher"  column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <!--
    这里传递过来的id，只有一个属性的时候，下面可以写任何值
    association中column多参数配置：
        column="{key=value,key=value}"
        其实就是键值对的形式，key是传给下个sql的取值名称，value是片段一中sql查询的字段名。
    -->
    <select id="getTeacher" resultType="teacher">
        select * from teacher where id = #{id}
    </select>
<!--按结果嵌套处理-->
    <select id="getStudent2" resultMap="s-t-2">
        select s.id sid,s.name sname,t.name tname
        from student s ,teacher t
        where s.tid=t.id
    </select>
    <resultMap id="s-t-2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
       <association property="teacher" javaType="Teacher" >
           <result property="name" column="tname"/>
       </association>
    </resultMap>

4.绑定接口

<resultMap id="t-d-2" type="Teacher">
    <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
    select * from mybatis.student where tid = #{tid }
</select>

<a name="otsea"></a>
### 4.绑定接口
<!--绑定接口--><br /><mappers><br />    <mapper class="com.sy.dao.StudentMapper"/><br />    <mapper class="com.sy.dao.TeacherMapper"/><br /></mappers>
<a name="K0HE1"></a>
### 5.测试
```java
   @Test
    public void getTeacher(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
    @Test
    public void getTeacher2(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}