今天记录一下 【Mybatis高级映射】之一对多映射
一、一对多查询:
以老师表和课程表为例子,一个老师可以教多门课程。。。。。。。。
老师表:
课程表:
具体sql语句脚本如下:
/*Table structure for table `t_course` */
DROP TABLE IF EXISTS `t_course`;
CREATE TABLE `t_course` (
`t_course_id` varchar(50) DEFAULT NULL COMMENT '课程id',
`t_name` varchar(50) DEFAULT NULL COMMENT '课程名字',
`t_description` varchar(50) DEFAULT NULL COMMENT '课程描述',
`t_startdate` timestamp NULL DEFAULT NULL COMMENT '课程开始时间',
`t_enddate` timestamp NULL DEFAULT NULL COMMENT '课程结束时间',
`t_teacher_id` varchar(50) DEFAULT NULL COMMENT '老师id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `t_course` */
insert into `t_course`(`t_course_id`,`t_name`,`t_description`,`t_startdate`,`t_enddate`,`t_teacher_id`) values ('200','英语',NULL,'2018-03-07 00:59:26','2018-03-23 00:59:29','100'),('201','数学',NULL,'2018-03-07 00:59:32','2018-03-07 00:59:35','100'),('202','外语',NULL,'2018-03-23 00:59:40','2018-03-07 00:59:42','101'),('203','体育',NULL,'2018-03-07 00:59:45','2018-03-20 01:00:05','101'),('204','生物',NULL,'2018-03-07 00:59:47','2018-03-30 01:00:09','101'),('205','体操',NULL,'2018-03-30 00:59:51','2018-03-07 01:00:12','101'),('206','美术',NULL,'2018-03-28 00:59:53','2018-03-07 01:00:14','101'),(NULL,NULL,NULL,'2018-03-21 00:59:56','2018-03-07 01:00:17','101');
/*Table structure for table `t_teacher` */
DROP TABLE IF EXISTS `t_teacher`;
CREATE TABLE `t_teacher` (
`t_teacher_id` varchar(50) DEFAULT NULL COMMENT '老师id',
`t_name` varchar(50) DEFAULT NULL COMMENT '老师名字',
`t_email` varchar(50) DEFAULT NULL COMMENT '老师email',
`t_phone` varchar(50) DEFAULT NULL COMMENT '老师电话',
`t_addr_id` varchar(50) DEFAULT NULL COMMENT '老师地址id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `t_teacher` */
insert into `t_teacher`(`t_teacher_id`,`t_name`,`t_email`,`t_phone`,`t_addr_id`) values ('100','肖丹','123468@qq.com','123456123','10000'),('101','何贵英','7845@.qomc','12345678','10001');
建立pojo对象:------->
teacher对象:
package com.tgb.model;
import java.util.List;
public class Teacher {
private Integer teacherId;
private String name;
private String email;
private List<Course> courses;
public Integer getTeacherId() {
return teacherId;
}
public void setTeacherId(Integer teacherId) {
this.teacherId = teacherId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public List<Course> getCourses() {
return courses;
}
public void setCourses(List<Course> courses) {
this.courses = courses;
}
}
课程对象:
package com.tgb.model;
import java.util.Date;
public class Course {
private Integer courseId;
private String name;
private String description;
private Date startDate;
private Date endDate;
private Integer teacherId;
public Integer getCourseId() {
return courseId;
}
public void setCourseId(Integer courseId) {
this.courseId = courseId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Date getStartDate() {
return startDate;
}
public void setStartDate(Date startDate) {
this.startDate = startDate;
}
public Date getEndDate() {
return endDate;
}
public void setEndDate(Date endDate) {
this.endDate = endDate;
}
public Integer getTeacherId() {
return teacherId;
}
public void setTeacherId(Integer teacherId) {
this.teacherId = teacherId;
}
}
最关键的是TeacherMapper.xml文件的编写,这里一共有三种编写方式,
方法一: 使用嵌套ResultMap + collection关键字
<resultMap type="com.tgb.model.Course" id="CourseResult">
<id column="t_course_id" property="courseId"/>
<result column="t_name" property="name"/>
<result column="t_description" property="description"/>
<result column="t_startdate" property="startDate"/>
<result column="t_enddate" property="endDate"/>
</resultMap>
<resultMap type="com.tgb.model.Teacher" id="TeacherResult">
<id column="t_teacher_id" property="teacherId"/>
<result column="t_name" property="name"/>
<result column="t_email" property="email"/>
<collection property="courses" resultMap="CourseResult"/>
</resultMap>
<select id="findTeacherById" parameterType="int" resultMap="TeacherResult">
SELECT
t.`t_teacher_id`,
t.`t_addr_id`,
t.`t_email`,
t.`t_name`,
t.`t_phone` ,
tt.`t_course_id`,
tt.`t_name`,
tt.`t_description`,
tt.`t_startdate`,
tt.`t_enddate`,
tt.`t_teacher_id`
FROM
t_teacher t
JOIN t_course tt
ON (
t.`t_teacher_id` = tt.`t_teacher_id`
)
WHERE t.`t_teacher_id`=#{teacherId}
</select>
从方法一可以看到,我们使用的是两个resultMap,一个课程,一个教师,教师下面有一个<collection 标签 ,同时使用的是<collection标签的两个属性property和resultMap,其中property属性的值就是Teacher.java中的课程属性,如下图:
注意,名字一定要一样的哟!!!
而resultMap对应的值就是课程中的id,如下图:
这里还有一个小问题,注意看查询语句,其中teacher和course对象都有 名字(name) 自然在,teacher和course两个java对象中也有对应的name属性,因为我们在查询的时候区分两个名字(没有取别名) ,映射的时候也没有注意,所以,在具体的映射,课程的名字不会被取出,而是取的老师的名字,这个错误,在方法二中进行更正。。。。。。。。
方法二: 使用嵌套ResultMap + collection关键字
<resultMap type="com.tgb.model.Teacher" id="TeacherResult">
<id column="t_teacher_id" property="teacherId"/>
<result column="t_name" property="name"/>
<result column="t_email" property="email"/>
<collection property="courses" ofType="com.tgb.model.Course">
<id column="t_course_id" property="courseId"/>
<!--
<id column="t_teacher_id" property="teacherId"/>
-->
<result column="tt_name" property="name"/>
<result column="t_description" property="description"/>
<result column="t_startdate" property="startDate"/>
<result column="t_enddate" property="endDate"/>
</collection>
</resultMap>
<select id="findTeacherById" parameterType="int" resultMap="TeacherResult">
SELECT
t.`t_teacher_id`,
t.`t_addr_id` ,
t.`t_email`,
t.`t_name` t_name,
t.`t_phone` ,
tt.`t_course_id`,
tt.`t_name` tt_name,
tt.`t_description`,
tt.`t_startdate`,
tt.`t_enddate`,
tt.`t_teacher_id`
FROM
t_teacher t
JOIN t_course tt
ON (
t.`t_teacher_id` = tt.`t_teacher_id`
)
WHERE t.`t_teacher_id`=#{teacherId}
</select>
从方法二可以看到,我们使用的任然是<collection标签来实现 不过,这次使用的是 它的另外一个属性
ofType 其中 ofType属性值就是课程这个对象啦!!!
还有一点需要注意哟,老师和课程是通过老师的id进行关联的哟,在teacher.java和course.java对象中都是有teacherId属性的哟,事实上是在course中,不对t_teacher_id进行映射也是可以正常查询出来的哟,当然我觉得最好还是写出来,这样比较好。
当然,这里还需要说明的,如果你在这里不进行映射,那么在拿到的对象里面,课程中的teacherId属性是没有值的。
可以看到在方法二中的sql语句中,已经给老师名字和课程名字都取了别名了!!!!!!!
方法三: 使用嵌套collection + select实现:
<resultMap type="com.tgb.model.Course" id="CourseResult">
<id column="t_course_id" property="courseId"/>
<result column="t_name" property="name"/>
<result column="t_description" property="description"/>
<result column="t_startdate" property="startDate"/>
<result column="t_enddate" property="endDate"/>
</resultMap>
<resultMap type="com.tgb.model.Teacher" id="TeacherResult">
<id column="t_teacher_id" property="teacherId"/>
<result column="t_name" property="name"/>
<result column="t_email" property="email"/>
<collection property="courses" column="t_teacher_id" select="findCoursesByTeacher"/>
</resultMap>
<select id="findTeacherById" parameterType="int" resultMap="TeacherResult">
SELECT
t.`t_teacher_id`,
t.`t_addr_id`,
t.`t_email`,
t.`t_name`,
t.`t_phone`
FROM
t_teacher t WHERE t.`t_teacher_id`=#{teacherId}
</select>
<select id="findCoursesByTeacher" parameterType="int" resultMap="CourseResult">
SELECT * FROM t_course t WHERE t.`t_teacher_id`=#{teacherId}
</select>
映射接口:
package com.tgb.mapper;
import com.tgb.model.Teacher;
public interface TeacherMapper {
Teacher findTeacherById(int id);
}
参考网址:https://www.cnblogs.com/davidgu/p/6270761.html
我的源码下载地址: https://gitee.com/lwh_zfj/mybatis_advanced_mapping_use/tree/master
我的码云,如图所示: