多表关联查询-JavaBean(老师表-中间表-学生表)
要求:查询所有的老师的信息,同时查询老师教授的所有学生信息
首先根据表编写JavaBean
学生类:
public class Student {
private Integer sid;
private String name;
//多对多:不同的学生,可以上【不同老师】的课
private List<Teacher> teacherList = new ArrayList<>();
}
老师类:
public class Teacher {
private Integer tid;
private String name;
//多对多: 不同的老师,给【不同的学生】讲课
private List<Student> studentList = new ArrayList<>();
}
步骤:
1.编写TeacherMapper,查询所有的老师(此时不包含学生信息)
public interface TeacherMapper {
/**
* 查询所有老师
*/
@Select("select * from teacher")
public List<Teacher> selectAll();
}
2.编写Teacher测试类,查询所有老师的信息(不包含学生信息)
public class Test04_SelectAllTeacher {
public static void main(String[] args) {
//1 获得mapper
TeacherMapper teacherMapper = MyBatisUtils.getMapper(TeacherMapper.class);
//2 查询所有
List<Teacher> list = teacherMapper.selectAll();
for (Teacher teacher : list) {
System.out.println(teacher);
}
//3 释放
MyBatisUtils.commitAndclose();
}
}
(注意:需要导入Mybatis工具包可直接调用mybatis获得mapper)
3.编写StudentMapper,根据 tid 查询关联的所有学生(中间表–>学生表)
public interface StudentMapper {
/**
* 通过tid查询所有的学生
*/
@Select("SELECT s.* FROM teacher_student ts,student s WHERE ts.student_id = s.sid AND ts.teacher_id = #{tid}")
public List<Student> selectAllByTid(@Param("tid") Integer tid);
}
4.编写Student测试类
public class Test05_SelectAllStudent {
public static void main(String[] args) {
//1 获得mapper
StudentMapper studentMapper = MyBatisUtils.getMapper(StudentMapper.class);
//2 查询所有
List<Student> list = studentMapper.selectAllByTid(2);
for (Student student : list) {
System.out.println(student);
}
//3 释放
MyBatisUtils.commitAndclose();
}
}
5.将TeacherMapper和StudentMapper进行关联
在TeacherMapper中修改
/**
* 查询所有老师
*/
@Select("select * from teacher")
@Results({
@Result(property = "tid",column = "tid"),
@Result(property = "studentList", column = "tid", many = @Many(select="包名.StudentMapper.selectAllByTid"))//这里调用StudentMapper中的根据id查询方法
})
public List<Teacher> selectAll();
6.测试类测试,由此完成