MyBatis （六）MyBatis关联映射（上）

关联映射

一、创建表结构

1.学生表

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `t_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', '男', 18, 1);
INSERT INTO `student` VALUES (2, '李四', '女', 18, 1);
INSERT INTO `student` VALUES (3, '王五', '男', 18, 1);
INSERT INTO `student` VALUES (4, '小白', '女', 18, 1);
INSERT INTO `student` VALUES (5, '小黑', '男', 18, 1);
INSERT INTO `student` VALUES (6, '小红', '女', 20, 2);
INSERT INTO `student` VALUES (7, '小李', '男', 20, 2);
INSERT INTO `student` VALUES (8, '小张', '女', 20, 2);
INSERT INTO `student` VALUES (9, '小赵', '男', 20, 2);
INSERT INTO `student` VALUES (10, '小王', '女', 20, 2);

SET FOREIGN_KEY_CHECKS = 1;

 2.老师表

 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '张老师');
INSERT INTO `teacher` VALUES (2, '李老师');

SET FOREIGN_KEY_CHECKS = 1;

二、一对一、多对一

1.第一种形式 连表查询

SELECT  student.id,student.name,teacher.name FROM student LEFT JOIN teacher  on student.t_id = teacher.id

 ①:设置实体类

public class Student {

    private Integer id;
    private String Sname;
    private String sex;
    private Integer age;
    private Integer t_id;
    //这个是重点
    private Teacher teacher;
}

public class Teacher {
    private Integer id;
    private String Tname;
}

 ②:查询语句

   <!--    按照结果嵌套处理-->
<select id="getStudent1" resultMap="StudentTeacher1">
   SELECT  student.id,student.Sname,teacher.Tname FROM student  LEFT JOIN teacher  on student.t_id = teacher.id
</select>
   <resultMap id="StudentTeacher1" type="com.qcby.entity.Student">
       <result property="id" column="id"/>
       <result property="Sname" column="Sname"/>
       <result property="sex" column="sex"/>
       <result property="age" column="age"/>
       <result property="t_id" column="t_id"/>
        <!-- 复杂的属性我们需要单独去处理 对象:association   集合：collection   -->
   	 <!-- property="teacher" student类当中的关联字段 -->
   	 <!-- javaType="com.javen.model.Teacher" 为复杂属性设置类类型-->	
       <association property="teacher" javaType="com.qcby.entity.Teacher">
           <result property="id" column="id"/>
           <result property="Tname" column="Tname"/>
       </association>
   </resultMap>

查询每个学生的对应的老师

SELECT s.id,s.Sname,t.Tname FROM student s,teacher t where s.t_id = t.id

①:设置实体类

这里的实体类不需要变动

②:查询语句

<select id = "getStudent"  resultMap="StudentTeacher">
    select * from student;
</select>
<!--结果映射集-->
<resultMap id="StudentTeacher"  type="com.qcby.entity.Student">
    <result property="id" column="id"/>
    <result property="Sname" column="Sname"/>
    <result property="sex" column="sex"/>
    <result property="age" column="age"/>
    <result property="t_id" column="t_id"/>
    <!-- select="getTeacher"  :调用下一个查询语句       -->
    <!-- column="t_id" 两个表的关联字段-->
    <association property="teacher" column="t_id" javaType="com.qcby.entity.Teacher" select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="com.qcby.entity.Teacher">
    select  * from  teacher where id = #{t_id};    <!-- #{id}; 可以写任何东西，因为会自动匹配 t_id -->
</select>