MyBatis多对一、多对一处理

10.多对一、多对一处理

一个班级有多个学生
学生角度：（关联）多个学生关联一个班级
班级角度：（集合）一个班级集合了多个学生

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 `students` (
  `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=utf8INSERT INTO `students` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
INSERT INTO `students` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); 
INSERT INTO `students` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); 
INSERT INTO `students` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
INSERT INTO `students` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
    private int id;
    private String name;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Students {
    private  int id;
    private   String   name;
    //多个学生对应一个老师
    private Teacher teacher;
}

建Mapper接口和mapper.xml
可以将mapper.xml放到项目的resources下同包目录下，dao包中只保留Mapper接口，编译后同包目录下的文件都输出到target文件中
在核心配置文件中注册mapper接口
测试查询是否成功

10.2 多对一实现方式

按照查询进行嵌套处理：子查询
题目：查询所有的学生消息（附带老师信息）

<?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.zk.dao.StudentsMapper">
    <!--包扫描的别名方式,返回值、映射对象类型的首字母大小写无关-->
    <resultMap id="StudentsTeacher" type="students">
        <result property="id" column="id"></result>
        <!--复杂属性单独处理
            对象使用association
            集合使用collection
        -->
        <!--按照查询进行嵌套处理，子查询
        column="tid"是作为子查询参数来映射的，但字段来自已经查处来的students对象-->
        <association property="teacher" column="tid" javaType="teacher" select="getTeacherByTID">
        </association>
    </resultMap>
    <select id="getStudents" resultMap="StudentsTeacher">
        select * from students ;
    </select>
    <select id="getTeacherByTID" resultType="teacher">
        select * from teacher where id = #{tid};
    </select>
</mapper>

按照结果进行嵌套处理：关联查询

<select id="getStudents2" resultMap="StudentsTeacher2">
    SELECT s.id sid,s.name sname ,t.id tid,t.name tname FROM students s,teacher t WHERE s.tid = t.id
</select>
<resultMap id="StudentsTeacher2" type="students">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher"  javaType="teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

10.3 一对多实现方式

题目：查询一个老师代课的学生消息
实体类修改

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Students {
    private  int id;
    private   String   name;
    private int tid;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
    private int id;
    private String name;
    //一个老师关联多个学生
    private List<Students> students;
}

按查询进行嵌套处理，子查询方式

 <!--子查询-->
    <select id="getTeacherListByIds02" resultMap="TeacherStudents02">
        select * from teacher where id = #{id}
    </select>
    <resultMap id="TeacherStudents02" type="Teacher">
        <result property="id" column="id"/>
        <!--为什么要加javaType的原因：
         1.上下面的是联表查询，两个表联合的一个个记录查出来，每条记录包含老师学生信息，
         2.这个是先查出来了要查询的老师id后，得到了查询结果，但结果中的的student是null值，
         所以要拿老师id进行对Students表子查询，子查询中结果是多条只有学生学习的记录。这些记录组成list
         形成resultMap-->
        <!--column="id"是作为子查询参数来映射的，但字段来自已经查处来的Teacher对象-->
        <collection property="students" column="id" javaType="ArrayList" ofType="Students" select="getTeacherByID"/>
    </resultMap>
    <select id="getTeacherByID" resultType="Students">
        select * from students where tid = #{id}
    </select>

按结果进行嵌套处理，关联查询

<mapper namespace="com.zk.dao.TeacherMapper">

    <!--按照结果进行嵌套处理：关联查询-->
<select id="getTeacherListByIds" resultMap="TeacherStudents" >
  SELECT s.id sid,s.name sname ,t.id tid,t.name tname FROM students s,teacher t WHERE s.tid = t.id
    and t.id = #{id}
</select>

    <resultMap id="TeacherStudents" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--复杂属性单独处理
           对象使用associatiogetTeacherListByIdsn
           集合使用collection
           javaType="";指定属性的类型
           ofType="";集合中的泛型属性
           从集合中取出的-数据-来对应
       -->
        <collection property="students" ofType="Students">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>
  
</mapper>

测试

@Test
public void test03(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = mapper.getTeacherListByIds02(1);
    System.out.println(teacher.toString());
    sqlSession.close();
    /*Teacher(id=1, name=王老师,
    students=[Students(id=1, name=小明, tid=0),
    Students(id=2, name=小红, tid=0), Students(id=3, name=小张, tid=0),
     Students(id=4, name=小李, tid=0),  Students(id=5, name=小王, tid=0)])
     */
}