Mybatis一对多和多对一处理

呓语

2022 年 02 月 04 日

258 次浏览

1 条评论

3345字数

MyBatis

10、多对一处理

多对一：

多个学生，对应一个老师
对于学生这边而言，关联... 多个学生，关联一个老师【多对一】
对于老师而言，集合，一个老师，有很多学生【一对多】

SQL：

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `id` int NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `teacher` VALUES (1, '呓语');

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int NOT NULL,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tid` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fktid`(`tid`) USING BTREE,
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

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

测试环境搭建

导入lombok
新建实体类 Teacher，Student
建立Mapper接口
建立Mapper.xml文件
在核心配置文件中绑定注册我们的Mapper接口或者文件！【方式很多，随心选】
测试查询是否能够成功

按照查询嵌套处理

<!--按照查询嵌套处理-->

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

<!--    查询单个对象-->
<select id="getStudent2" resultMap="StudentTeacher2">
    select *
    from student
    where id = #{sid};
</select>
<resultMap id="StudentTeacher2" type="Student">
    <result column="id" property="id"/>
    <result property="name" column="name"/>
    <result property="tid" column="tid"/>
    <!--        复杂的属性，我们需要单独处理 对象：association  集合： collection-->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher2"/>
</resultMap>
<select id="getTeacher2" resultType="Teacher">
    select *
    from teacher
    where id = #{id};
</select>


<!--查询集合-->
<select id="getStudentList2" resultMap="StudentTeacherList2">
    select *
    from student;
</select>
<resultMap id="StudentTeacherList2" type="Student">
    <result column="id" property="id"/>
    <result property="name" column="name"/>
    <result property="tid" column="tid"/>
    <!--        复杂的属性，我们需要单独处理 对象：association  集合： collection-->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacherList2"/>
</resultMap>
<select id="getTeacherList2" resultType="Teacher">
    select *
    from teacher
    where id = #{id};
</select>

按照结果嵌套处理

<!--    按照结果嵌套处理-->

<!--    查询单个对象-->
<select id="getStudent" resultMap="StudentTeacher">
    select s.id sid, s.name sname, t.id, tid, t.name tname
    from student s,
    teacher t
    where s.tid = t.id
    and s.id = #{sid};
</select>
<resultMap id="StudentTeacher" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <result property="tid" column="tid"/>
    <association property="teacher" javaType="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

<!--    查询集合-->
<select id="getStudentList" resultMap="StudentList">
    select s.id sid, s.name sname, t.id, tid, t.name tname
    from student s,
    teacher t
    where s.tid = t.id;
</select>
<resultMap id="StudentList" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <result property="tid" column="tid"/>
    <association property="teacher" javaType="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

11、一对多处理

按照结果嵌套处理

<!--    按照结果嵌套处理-->

<!--    查询单个对象-->
<select id="getTeacher" resultMap="TeacherStudent">
    select t.id tid, t.name tname, s.id sid, s.name sname
    from teacher t,
    student s
    where t.id = s.tid
    and t.id = #{tid};
</select>
<resultMap id="TeacherStudent" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

<!--    查询集合-->
<select id="getTeacherList" resultMap="TeacherList">
    select t.id tid, t.name tname, s.id sid, s.name sname
    from teacher t,
    student s
    where t.id = s.tid
</select>
<resultMap id="TeacherList" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

按照查询嵌套处理

<!--    按照查询嵌套处理-->

<!--    查询单个对象-->
<select id="getTeacher2" resultMap="TeacherStudent2">
    select *
    from teacher
    where id = #{tid}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <collection property="students" ofType="Student" javaType="ArrayList" select="getStudent2" column="id"/>
</resultMap>
<select id="getStudent2" resultType="Student">
    select *
    from student
    where tid = #{tid};
</select>

<!--查询集合-->
<select id="getTeacherList2" resultMap="TeacherStudentList2">
    select *
    from teacher
    where id = #{tid}
</select>
<resultMap id="TeacherStudentList2" type="Teacher">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <collection property="students" ofType="Student" javaType="ArrayList" select="getStudentList2" column="id"/>
</resultMap>
<select id="getStudentList2" resultType="Student">
    select *
    from student
    where tid = #{tid};
</select>