Mybatis学习笔记--多对一关系处理

本文章涉及环境版本：

• mysql 5.7
• Mybatis 3.5.x
• Maven 3.6.x
• JDK 1.8

项目代码仓库：
https://github.com/Gang-bb/Study-Record/tree/main/bzhan-mybatis-study
需要clone整个bzhan-mybatis-study项目
(整体是一个maven多module工程)
该文章对应：《bzhan-mybatis-study06 》module项目

1. 测试环境搭建

1. 建表sql

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(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  PRIMARY KEY (`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');

2. 创建对应的polo、Mapper接口和mapper.xml文件

主要关键类代码：

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private Integer id;
    private String name;
    
    private Teacher teacher;
}

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

注意：mybatis-config.xml文件中需要声明mapper.xml文件

2. 实例测试

查询所有学生信息，及其对应的教师信息

方式一：按照查询嵌套处理

1. StudentMapper

List<Student> getStudent();

2. StudentMapper.xml

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

<resultMap id="student_teacher" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{id}
</select>

3. 测试类

@Test
public void getStudent(){
	SqlSession sqlSession = null;
    try {
        sqlSession = MybatisUtil.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
   		List<Student> studentList = studentMapper.getStudent();
        for (int i = 0; i < studentList.size(); i++) {
                Student student =  studentList.get(i);
                System.out.println(student);
            }
    } finally {
    	sqlSession.close();
    }	
}

4. 结果

方式二：按照结果嵌套处理

1. StudentMapper

List<Student> getStudent2();

2. StudentMapper.xml

<!--按照结果嵌套处理-->
<select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid,s.name sname, t.name tname, t.id tid
    from student s,teacher t
    where s.tid = t.id;
</select>

<resultMap id="StudentTeacher2" type="Student">
    <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>

3. 测试类

@Test
public void getStudent(){
	SqlSession sqlSession = null;
    try {
        sqlSession = MybatisUtil.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
   		List<Student> studentList = studentMapper.getStudent2();
        for (int i = 0; i < studentList.size(); i++) {
                Student student =  studentList.get(i);
                System.out.println(student);
            }
    } finally {
    	sqlSession.close();
    }	
}

4. 结果