Mybatis——多表操作（多对多）

多对多

1学生和老师数据模型

1.1表间关系

#老师表
CREATE TABLE teacher(
tid INT PRIMARY KEY,
NAME VARCHAR(50)
);
#学生表
CREATE TABLE student(
sid INT PRIMARY KEY,
NAME VARCHAR(50)
);
#中间表
CREATE TABLE teacher_student(
teacher_id INT ,
student_id INT,
CONSTRAINT ts_t_fk FOREIGN KEY (teacher_id) REFERENCES teacher(tid),
CONSTRAINT ts_s_fk FOREIGN KEY (student_id) REFERENCES student(sid)
);
INSERT INTO teacher VALUES (1,'肖老师');
INSERT INTO teacher VALUES (2,'马老师');
1.2 JavaBean及其关系
JavaBean：Student
INSERT INTO student VALUES (1,'张三');
INSERT INTO student VALUES (2,'李四');
INSERT INTO student VALUES (3,'王五');
INSERT INTO teacher_student VALUES (1,1);
INSERT INTO teacher_student VALUES (1,2);
INSERT INTO teacher_student VALUES (1,3);
INSERT INTO teacher_student VALUES (2,1);
INSERT INTO teacher_student VALUES (2,2);

1.2 JavaBean及其关系

JavaBean：Student

/**
* #学生表
* CREATE TABLE student(
* sid INT PRIMARY KEY,
* NAME VARCHAR(50)
*/
public class Student {
private Integer sid;
private String name;
private List<Teacher> teacherList = new ArrayList<>();
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getName() {
JavaBean：Teacher
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Teacher> getTeacherList() {
return teacherList;
}
public void setTeacherList(List<Teacher> teacherList) {
this.teacherList = teacherList;
}
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", name='" + name + '\'' +
", teacherList=" + teacherList +
'}';
}
}

JavaBean：Teacher

/**
* #老师表
* CREATE TABLE teacher(
* tid INT PRIMARY KEY,
* NAME VARCHAR(50)
* );
*/
public class Teacher {
private Integer tid;
private String name;
private List<Student> studentList = new ArrayList<>();
public Integer getTid() {
return tid;
}
public void setTid(Integer tid) {
this.tid = tid;
}
6.2 多对多：老师-->学生
需要根据老师tid查询中间表中，对应的所有学生id
6.2.1 student 映射
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Student> getStudentList() {
return studentList;
}
public void setStudentList(List<Student> studentList) {
this.studentList = studentList;
}
@Override
public String toString() {
return "Teacher{" +
"tid=" + tid +
", name='" + name + '\'' +
", studentList=" + studentList +
'}';
}
}

1.3.2 student 映射

public interface StudentMapper {
/**
* 通过tid查询对应的学生
* @param tid
* @return
* @throws Exception
*/
@Select("select * from student s where s.sid in (select student_id from
teacher_student where teacher_id = #{tid} )")
public Student findStudentByTeacherId(@Param("tid") Integer tid) throws
Excep

1.3.3teacher映射

/**

*/
public interface TeacherMapper {
@Select("select * from teacher t where t.tid = #{tid}")
@Results({
@Result(property="tid" , column="tid"),
@Result(property="name" , column="name"),
@Result(property="studentList" ,
many=@Many(select="com.czxy.ssm.mapper.StudentMapper.findStudentByTeacherId") ,
column="tid"),
})
public Teacher selectById(@Param("tid") Integer tid);
}

1.3.4测试

/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class Test09_SelectTeacher {
  	public static void main(String[] args) {
	TeacherMapper teacherMapper =MyBatisUtils.getMapper(TeacherMapper.class);
	Teacher teacher = teacherMapper.selectById(1);
	// 打印
	System.out.println(teacher);
	MyBatisUtils.commitAndclose();
	}
}