10、Mybatis——多对一处理

多对一的理解：

• 多个学生对应一个老师

• 如果对于学生这边，就是一个多对一的现象，即从学生这边关联一个老师

10.1、数据库设计

CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师');

CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

10.2、测试环境搭建

1. IDEA安装Lombok插件
2. 引入Maven依赖

<dependency>
   <groupId>org.projectlombok</groupId>
   <artifactId>lombok</artifactId>
   <version>1.18.12</version>
</dependency>

3. 在代码中增加注解

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

4. 边界实体类对应的Mapper接口【两个】

       $\circ$ 无论有没有需求，都应该写上，以备后来之需要！

public interface StudentMapper {
}

public interface TeacherMapper {
}

5. 编写Mapper接口对应的mapper.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.kuang.mapper.StudentMapper">

</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.kuang.mapper.StudentMapper">

</mapper>

6. 在核心配置文件中注册mapper

<mappers>
   <mapper resource="dao/TeacherMapper.xml"/>
   <mapper resource="dao/StudentMapper.xml"/>
</mappers>

10.3、按查询嵌套处理

1. 给StudentMapper接口增加方法

//查询所有的学生信息，以及对应的老师的信息！
public List<Student> getStudent();

2. 编写对应的Mapper文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.chen.dao.StudentMapper">

    <!--
    思路：
        1.查询所有的学生信息
        2.根据查询出来的学生的id，寻找对应的老师！
    -->

    <select id="getStudent" resultMap="StudentTeacher">
        select * from student;
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <id property="id" column="id"/>
        <result property="name" column="id"/>
        <!-- 复杂的属性，我们需要单独处理 对象：association 集合：collection -->
        <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>
</mapper>

3. 编写完毕去Mybatis配置文件中，注册Mapper！

4. 注意点

<resultMap id="StudentTeacher" type="Student">
   <!--association关联属性 property属性名 javaType属性类型 column在多的一方的表中的列名-->
   <association property="teacher"  column="{id=tid,name=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} and name = #{name}
</select>

5. 测试

    @Test
    public void testStudent(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudent();
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.close();
    }

10.4、按结果嵌套处理

1. 给StudentMapper接口增加方法

public List<Student> getStudent2();

2. 编写对应的Mapper文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.chen.dao.StudentMapper">

    <resultMap id="StudentTeacher2" type="Student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from student s,teacher t
        where s.tid = t.id;
    </select>
</mapper>

3. 编写完毕去Mybatis配置文件中，注册Mapper！
4. 测试

    @Test
    public void testStudent2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudent2();
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.close();
    }

视频链接：https://www.bilibili.com/video/BV1NE411Q7Nx?p=20