03_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,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `tid`(`tid`) USING BTREE,
  CONSTRAINT `tid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;

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,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, 'teacher_zs');

SET FOREIGN_KEY_CHECKS = 1;

实体类的构建:

@Data
public class Student {
    private int id;
    private String name;
   //多个学生可以是同一个老师，即多对一
    private Teacher teacher;
}

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

接口的构建：

public interface TeacherMapper {
    @Select("select * from teacher where id = #{id}")
    Teacher getTeacherById(@Param("id") int id);
}

1.1 按查询嵌套处理

StudentMapper.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">
<!--namespace 绑定一个对应的Dao/Mapper接口-->
<!-- SELECT s.id , s.name , t.name FROM student s , teacher t WHERE s.tid = t.id;-->
<!--查询所有学生的信息
根据查询的学生信息tid 寻找对应的老师-->
<mapper namespace="com.zs.mapper.StudentMapper">
    <select id="getStudents" resultMap="StudentTeacher">
        select * from student
    </select>

    <resultMap id="StudentTeacher" type="com.xxxx.pojo.Student">
    	<!-- 复杂的属性我们需要单独处理
    		对象：assocication
    		集合：collection
    		-->
        <association property="teacher" column="tid" javaType="com.zs.pojo.Teacher" select="getTeacherById"/>
    </resultMap>
    
    <select id="getTeacherById" resultType="com.zs.pojo.Teacher">
        select * from teacher where id = #{id}
    </select>
</mapper>

测试类

    @Test
    public void getStudent() {
        try(SqlSession sqlSession = MyBatisUtils.getSqlSesion();){
            StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
            List<Student> studentList = mapper.getStudents();
            System.out.println(studentList);
        }
    }

1.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">
<!--namespace 绑定一个对应的Dao/Mapper接口-->
<!-- SELECT s.id , s.name , t.name FROM student s , teacher t WHERE s.tid = t.id;-->
<!--查询所有学生的信息
根据查询的学生信息tid 寻找对应的老师-->
<mapper namespace="com.xxxx.dao.StudentMapper">
    <select id="getStudents" resultMap="StudentTeacher">
        SELECT s.id sid , s.name sname , t.name tname
        FROM student s , teacher t
        WHERE s.tid = t.id
    </select>
    <resultMap id="StudentTeacher" type="com.xxxx.pojo.Student">
        <result property="name" column="sname"/>
        <result property="id" column="sid"/>
        <association property="teacher" column="tid" javaType="com.zs.pojo.Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>
</mapper>

2 一对多处理

2.1 按结果嵌套处理

@Data
public class Student {
   private int id;
   private String name;
   private int tid;
}

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

public interface TeacherMapper {
    Teacher getTeacher(@Param("id") int id);
}

<?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.xxxx.dao.TeacherMapper">

    <select id="getTeacher" resultMap="TeacherStudent">
        SELECT s.id sid, s.name sname , t.name tname, t.id tid
        FROM student s,teacher t
        WHERE s.tid = t.id and t.id=#{id}
    </select>
    <resultMap id="TeacherStudent" type="com.xxxx.pojo.Teacher">
        <result property="name" column="tname"/>
        <collection property="students" ofType="com.zs.pojo.Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>
</mapper>

public class DaoTest {
    //注意导包：org.apache.log4j.Logger
    static Logger logger = Logger.getLogger(DaoTest.class);
    @Test
    public  void test(){
       try(SqlSession sqlSession = MyBatisUtils.getSqlSesion();){
           TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
           Teacher teacher = mapper.getTeacher(1);
           System.out.println(teacher);
       }
    }

}