MyBatis中一对多和多对一处理

MyBatis 一对多和多对一处理

这里我们以老师和学生为例，一个老师对应多个学生，这是一对多；反过来，多个学生对应一个老师，这是多对一

MyBatis 参考文档：https://mybatis.org/mybatis-3/zh/getting-started.html 可以查看详细的 XML 映射器

数据准备

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, '秦老师');
INSERT INTO teacher(`id`, `name`) VALUES (2, '张老师');


CREATE TABLE `student` (
    `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=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', '小王', '2');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('6', '小刚', '2');

MyBatis 多对一处理

实体类

@Data
public class Student implements Serializable {
    private Integer id;

    private String name;

    private Integer tid;
	
	// 数据库不存在的字段，与下面的 association 标签形成映射
    private Teacher teacher;
}

@Data
public class Teacher implements Serializable {
    private Integer id;

    private String name;
}

Service 层接口

List<Student> getStudents(Integer tid);

mapper.xml 文件

<!--多对一处理示例，通过学生查找老师-->
<select id="getStudents" parameterType="java.lang.Integer" resultMap="selectTeacher">
	SELECT
    	s.id,
       	s.`name`,
        t.`name`
   	FROM
    	student AS s
        INNER JOIN teacher AS t ON s.tid = t.id
    WHERE
   		s.tid = #{tid}
</select>

<resultMap id="selectTeacher" type="com.atguigu.pojo.Student">
	<!--id：查询列中的唯一标识-->
	<id column="id" property="id"></id>
	<!--<result property="映射到实体类的属性" column="表字段" jdbcType="字段类型"></result>-->
    <result column="name" property="name"></result>
    <!--关联对象 property 关联对象在 Student 实体类中的属性-->
    <association property="teacher" javaType="com.atguigu.pojo.Teacher">
    	<result column="name" property="name"></result>
    </association>
</resultMap>

• column：数据库表对应的字段；如果有别名，就是数据库查询出来的字段的别名
• property：与实体类对应的属性
• jdbcType：可以不用写会自动映射

测试类

@SpringBootTest
@RunWith(SpringRunner.class)
public class AppTest {

    @Autowired
    private StudentServce studentServce;

    @Test
    public void shouldAnswerWithTrue() {
        List<Student> students = studentServce.getStudents(1);
        System.out.println(students);
    }
}

测试结果

MyBatis 一对多处理

实体类

@Data
public class Student implements Serializable {
    private Integer id;

    private String name;

    private Integer tid;
}

@Data
public class Teacher implements Serializable {
    private Integer id;

    private String name;

	// 数据库不存在的字段，与下面的 collection 标签形成映射
    private List<Student> students;
}

Service 层接口

List<Teacher> getTeacher(Integer id);

mapper.xml 文件

<!--一对多处理，通过教师查询学生-->
<select id="getTeacher" parameterType="java.lang.Integer" resultMap="selectTeacher">
	SELECT
		t.`name`AS t_name,
		s.id,
        s.`name`AS s_name
	FROM
        teacher AS t
        INNER JOIN student AS s ON t.id = s.tid
	WHERE
        t.id = #{id}
</select>

<resultMap id="selectTeacher" type="com.atguigu.pojo.Teacher">
	<id column="id" property="id"></id>
    <result column="t_name" property="name"></result>
    <collection property="students" ofType="com.atguigu.pojo.Student">
		<id column="id" property="id"></id>
        <result column="s_name" property="name"></result>
    </collection>
</resultMap>

• column：数据库表对应的字段；如果有别名，就是数据库查询出来的字段的别名
• property：与实体类对应的属性
• jdbcType：可以不用写会自动映射
• ofType：映射的属性（集合）中实体对象的类型

测试类

@SpringBootTest
@RunWith(SpringRunner.class)
public class AppTest {

    @Autowired
    private TeacherService teacherService;

    @Test
    public void findByTeacherToStudent() {
        List<Teacher> teachers = teacherService.getTeacher(1);
        System.out.println(teachers);
    }
}

测试结果