CREATE TABLE `class` (
`c_id` int(11) NOT NULL AUTO_INCREMENT,
`c_name` varchar(20) DEFAULT NULL,
`teacher_id` int(11) DEFAULT NULL,
PRIMARY KEY (`c_id`),
KEY `fk_teacher_id` (`teacher_id`),
CONSTRAINT `fk_teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
CREATE TABLE `teacher` (
`t_id` int(11) NOT NULL AUTO_INCREMENT,
`t_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
CREATE TABLE `student` (
`s_id` int(11) NOT NULL AUTO_INCREMENT,
`s_name` varchar(20) DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
package com.yjj.test02.entity;
import java.util.List;
/**
* @Description:
* @Author: YinJunjie
* @CreateDate: 2018/11/7 15:38
* @Version: 1.0
*/
public class Classes {
//定义实体类的属性，与class表中的字段对应
private int id; //id===>c_id
private String name; //name===>c_name
/**
* class表中有一个teacher_id字段，所以在Classes类中定义一个teacher属性，
* 用于维护teacher和class之间的一对一关系，通过这个teacher属性就可以知道这个班级是由哪个老师负责的
*/
private Teacher teacher;
//使用一个List<Student>集合属性表示班级拥有的学生
private List<Student> students;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher
+ ", students=" + students + "]";
}
}
package com.yjj.test02.entity;
/**
* @Description:
* @Author: YinJunjie
* @CreateDate: 2018/11/7 15:38
* @Version: 1.0
*/
public class Teacher {
//定义实体类的属性，与teacher表中的字段对应
private int id; //id===>t_id
private String name; //name===>t_name
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Teacher [id=" + id + ", name=" + name + "]";
}
}
package com.yjj.test02.entity;
/**
* @Description:
* @Author: YinJunjie
* @CreateDate: 2018/11/7 15:38
* @Version: 1.0
*/
public class Student {
//定义属性，和student表中的字段对应
private int id; //id===>s_id
private String name; //name===>s_name
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + "]";
}
}
<?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的命名空间习惯上对应mapper的java文件以保证唯一性-->
<mapper namespace="com.yjj.test02.mapper.ClassMapper">
<!--
根据classId查询对应的班级信息,包括学生,老师
-->
<!--
方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集
SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.C_id=s.class_id AND c.c_id=1
-->
<select id="getClass" parameterType="int" resultMap="ClassResultMap1">
SELECT
c.c_id,
c.c_name,
c.teacher_id,
t.t_name,
s.s_id,
s.s_name
FROM class 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.yjj.test02.entity.Classes" id="ClassResultMap1">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<!--可以不写column column="teacher_id" -->
<association property="teacher" javaType="com.yjj.test02.entity.Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
<!-- ofType指定students集合中的对象类型 可以不写column-->
<collection property="students" ofType="com.yjj.test02.entity.Student">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
</collection>
</resultMap>
<!--方式2 嵌套查询 -->
<resultMap id="ClassResultMap2" type="com.yjj.test02.entity.Classes">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<!--必须要column 指定class的teacher_id字段传给getTeacher sql当参数-->
<association property="teacher" column="teacher_id" javaType="com.yjj.test02.entity.Teacher"
select="getTeacher"/>
<!--必须要column 指定class的c_id字段穿个getStudents sql当参数-->
<collection property="students" column="c_id" ofType="com.yjj.test02.entity.Student" select="getStudents"/>
</resultMap>
<resultMap id="teacher" type="com.yjj.test02.entity.Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</resultMap>
<resultMap id="student" type="com.yjj.test02.entity.Student">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
</resultMap>
<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
SELECT
c.c_id,
c.teacher_id
FROM class c WHERE c.c_id=#{id};
</select>
<select id="getTeacher" parameterType="int" resultMap="teacher">
SELECT
t.t_id,
t.t_name
FROM teacher t WHERE t.t_id=#{id};
</select>
<select id="getStudents" parameterType="int" resultMap="student">
SELECT
s.s_id,
s.s_name
FROM student s WHERE s.class_id=#{id};
</select>
</mapper>
其实可以不用配一对一关联，直接用dto把字段放进一个dto里边
