1、准备表和数据
drop table if exists classes;
create table classes(
id bigint(20) primary key auto_increment,
class_name varchar(20) not null comment'班级名',
amount int not null comment '班级人数',
head_teacher_id bigint(20) null comment '班主任'
)ENGINE=InnoDB comment '班级表';
drop table if exists student;
create table student(
stu_id bigint(20) primary key auto_increment,
stu_name varchar(20) not null comment '姓名',
stu_age tinyint(2) not null comment '年龄',
class_id bigint(20) not null comment '所属班级id'
)ENGINE=InnoDB comment '学生表';
drop table if exists teacher;
create table teacher(
tea_id bigint primary key auto_increment,
tea_name varchar(20) not null comment '教师id',
tea_age tinyint(2) not null comment '教师年龄'
)engine = InnoDB comment '教师表';
public class classes{
private Long id;
private String className;
private Integer amount;
private Long headTeacherId;
@tableFild(exit = false)
private teacher headTeacher;
@tableFild(exit = false)
private List<student> studentList;
}
public class teacher{
private Long teaId;
private String teaName;
private Integer teaAge;
}
public class student{
private Long sruId;
private String stuName;
private Integer stuAge;
}
2、一对一关系的结果映射
<association property="author" column="blog_author_id" javaType="Author">
<id property="id" column="author_id"/>
<result property="username" column="author_username"/>
</association>
关联:处理一个类型的关系,相当于将一些属性可以组装成一个类型。
关联查询一般是有两种形式:
- 关联嵌套select查询,类似子查询
property | 映射到列结果的字段或者属性,或者是JavaBean中的属性 |
column | 数据库中的字段,用于嵌套select查询中的条件字段,可以多个字段 |
javaType | 嵌套查询对应的JavaBean |
select | 对应select查询映射的ID |
<mapper>
<resultMap type="io.renren.modules.sys.entity.teacher" id="teacherMap">
<id property="teaId" column="tea_id"/>
<result property="teaName" column="tea_name"/>
<result property="teaAge" column="teaAge"/>
</resultMap>
<select id ="getTeacherById" resultMap="teacherMap">
select * from teacher from tea_id =#{teaId}
</select>
</mapper>
<mapper>
<resultMap id="classesMap">
<id property="id" column="id"/>
<result property="className" column="class_name"/>
<result property="amount" column="amount"/>
<association property="headTeacher" column="head_teacher_id " javaType="teacher"
select="teacherMap"/>
</resultMap>
<select id="getClasses" resultMap = "classesMap">
select * from classes
</select>
</mapper>
- 关联嵌套结果查询
resultMap | 结果映射的ID |
<mapper>
-- 方式一:使用关联的resultMap,可以重复使用
<resultMap id="classesMap">
<id property="id" column="id"/>
<result property="className" column="class_name"/>
<result property="amount" column="amount"/>
<association property="headTeacher" column="head_teacher_id " javaType="teacher"
resultMap="teacherMap"/>
</resultMap>
--方式二:关联的字段写在一个association中
<resultMap id="classesMap">
<id property="id" column="id"/>
<result property="className" column="class_name"/>
<result property="amount" column="amount"/>
<association property="headTeacher" javaType="teacher"/>
<id property="teaId" column="tea_id"/>
<result property="teaName" column="tea_name"/>
<result property="teaAge" column="teaAge"/>
</association >
</resultMap>
<select id="getClasses" resultMap = "classesMap">
select * from classes
</select>
</mapper>
3、一对多关系的结果映射
集合的嵌套select查询
private List<student> studentList;
<resultMap id="classesMap">
<id property="id" column="id"/>
<result property="className" column="class_name"/>
<result property="amount" column="amount"/>
<collecation property="studentList" column="stu_id" ofType="student"
select="getStudent"/>
</resultMap>
<select id="getStudent" resultMap ="studentMap">
select * from student where stu_id = #{stuId}
</select>
集合嵌套结果集查询
--方式一
<resultMap id="classesMap">
<id property="id" column="id"/>
<result property="className" column="class_name"/>
<result property="amount" column="amount"/>
<collecation property="studentList" ofType="student">
<id property="stuId" column="stu_id"/>
<result property="stuName" column="stu_name"/>
<result property="stuAge" column="stuAge"/>
</collection>
</resultMap>
-- 方式二
<resultMap id="classesMap">
<id property="id" column="id"/>
<result property="className" column="class_name"/>
<result property="amount" column="amount"/>
<collecation property="studentList" column="stu_id" ofType="student"
resultMap="studentMap"/>
</resultMap>
参考:mybatis官方文档