Mybatis实现一对一、一对多关联查询,关联查询:多个表联合查询,只查询一次,通过resultMap里面的<association>
、<collection>
标签配置一对一、一对多。
本篇文章涉及的一对一、一对多关系
班级classes、班主任teacher是一对一的关系
班级classes、学生student是一对多的关系
Teacher.java
package com.sun.pojo;
import lombok.Data;
/**
* @author JumperSun
* @date 2023-03-26-14:51
*/
@Data
public class Teacher {
private int id;
private String name;
}
Student.java
package com.sun.pojo;
import lombok.Data;
/**
* @author JumperSun
* @date 2023-03-26-14:53
*/
@Data
public class Student {
private int id;
private String name;
}
Classes.java
package com.sun.pojo;
import lombok.Data;
import java.util.List;
/**
* @author JumperSun
* @date 2023-03-26-14:52
*/
@Data
public class Classes {
private int id;
private String name;
private Teacher teacher;
private List<Student> studentList;
}
classMapper.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">
<mapper namespace="com.sun.mapper.ClassesMapper">
<!-- 一对一关联查询 -->
<select id="getClassesTeacher" parameterType="int" resultMap="ClassesResultMap">
select * from classes c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>
<resultMap type="com.sun.pojo.Classes" id="ClassesResultMap">
<!-- 实体类的字段名和数据表的字段名映射 -->
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" javaType="com.sun.pojo.Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
</resultMap>
<!-- 一对多关联查询 -->
<select id="getClassesTeacherStudentList" parameterType="int" resultMap="ClassesResultMap2">
select * from classes c,teacher t,student s where c.teacher_id=t.t_id and c.c_id=s.class_id and c.c_id=#{id}
</select>
<resultMap type="com.sun.pojo.Classes" id="ClassesResultMap2">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" javaType="com.sun.pojo.Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
<collection property="studentList" ofType="com.sun.pojo.Student">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
</collection>
</resultMap>
</mapper>