7.mybatis一对多关联查询
和第5节一对一查询类似,但是不同的是,一对一使用的是association,而一对多使用collection。
实例:
1个班级Class,对应1个老师Teacher,对应多个学生Student
1.建表如下:
class[cid, cname, tid]
teacher[tid, tname]
student[sid, sname, cid]
插入数据:
class: insert into class values(1,'一年级',1); insert into class values(2,'二年级',2); teacher: insert into teacher values(1,'老师A'); insert into teacher values(2,'老师B'); student: INSERT INTO `mybatis`.`student`(`sid`,`sname`,`cid`) VALUES ( 1,'学生A',1); INSERT INTO `mybatis`.`student`(`sid`,`sname`,`cid`) VALUES ( 2,'学生B',1); INSERT INTO `mybatis`.`student`(`sid`,`sname`,`cid`) VALUES ( 3,'学生C',1); INSERT INTO `mybatis`.`student`(`sid`,`sname`,`cid`) VALUES ( 4,'学生D',2); INSERT INTO `mybatis`.`student`(`sid`,`sname`,`cid`) VALUES ( 5,'学生E',2);
2.新建Java实体类:
Clazz类:
public class Clazz { private int id; private String name; private Teacher teacher; private List<Student> stuList; public Clazz() { super(); } public Clazz(int id, String name, Teacher teacher, List<Student> stuList) { super(); this.id = id; this.name = name; this.teacher = teacher; this.stuList = stuList; } public List<Student> getStuList() { return stuList; } public void setStuList(List<Student> stuList) { this.stuList = stuList; } 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; } @Override public String toString() { return "Clazz [id=" + id + ", name=" + name + ", teacher=" + teacher + ", stuList=" + stuList + "]"; } }
Teacher:
public class Teacher { private int id; private String name; public Teacher() { super(); } public Teacher(int id, String name) { super(); this.id = id; this.name = 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 + "]"; } }
Teacher类:
public class Student { private int id; private String name; public Student(int id, String name) { super(); this.id = id; this.name = name; } public Student() { super(); } 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 + "]"; } }
3.要获取一个班级Clazz的完整关联信息,写clazzMapper.xml
<?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的标签,在此要在Window-Preference-Xml Catalog中配置mybatis-3-mapper.dtd, key=-//mybatis.org//DTD Mapper 3.0//EN --> <mapper namespace="com.mlxs.mybatis.test5.clazzMapper"> <!-- 一对多 关联查询: 方式一:嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集 SELECT * FROM class c, teacher t, student s WHERE c.tid=t.tid AND c.cid=s.cid AND c.cid=#{id} --> <!-- 查询班级及其老师信息 --> <select id="getClazz" parameterType="int" resultMap="getClazzMap"> SELECT * FROM class c, teacher t, student s WHERE c.tid=t.tid AND c.cid=s.cid AND c.cid=#{id} </select> <resultMap type="Clazz" id="getClazzMap"> <id property="id" column="cid"/> <result property="name" column="cname"/> <!-- 关联查询teacher 一对一 --> <association property="teacher" javaType="Teacher"> <id property="id" column="tid"/> <result property="name" column="tname"/> </association> <!-- 关联班级对应的student 一对多 ofType代表集合中存放的对象类型--> <collection property="stuList" ofType="Student"> <id property="id" column="sid"/> <result property="name" column="sname"/> </collection> </resultMap> <!-- 一对多 关联查询: 方式二:嵌套查询:使用嵌套结果映射来处理重复的联合结果的子集 SELECT * FROM class WHERE cid=#{id} //查询得到tid、cid用于给下面2条语句使用 SELECT * FROM teacher WHERE tid=#{id} //tid为第一条sql查询后的tid SELECT * FROM student WHERE cid=#{id} //cid为第一条sql查询后的cid --> <!-- 查询班级及其老师信息 --> <select id="getClazz2" parameterType="int" resultMap="getClazzMap2"> SELECT * FROM class WHERE cid=#{id} </select> <!-- 这里必须写成别名形式SELECT tid id, tname name,否则查询结果为null --> <select id="getTeacher" parameterType="int" resultType="Teacher"> SELECT tid id, tname name FROM teacher WHERE tid=#{id} </select> <select id="getStuList" parameterType="int" resultType="Student"> SELECT sid id, sname name FROM student WHERE cid=#{id} </select> <resultMap type="Clazz" id="getClazzMap2"> <id property="id" column="cid"/> <result property="name" column="cname"/> <!-- 关联查询teacher 一对一 --> <association property="teacher" column="tid" select="getTeacher"/> <!-- 关联班级对应的student 一对多 ofType代表集合中存放的对象类型--> <collection property="stuList" column="cid" select="getStuList"/> </resultMap> </mapper>
4.写测试类:
public class _Test6OneToMany { /** * 方式一:嵌套结果 * @author 魅力_小生 * */ @Test public void getClazz1(){ //创建session,设置事务为true SqlSession session = MyBatisUtil.getSessionFactory().openSession(true); String statement = "com.mlxs.mybatis.test5.clazzMapper.getClazz"; Clazz clazz = session.selectOne(statement, "1"); System.out.println("clazz--->"+clazz); session.close(); } /** * 方式二:嵌套查询,通过执行另外一个SQL 映射语句来返回预期的复杂类型 SELECT * FROM class WHERE cid=#{id} //查询得到tid、cid用于给下面2条语句使用 SELECT * FROM teacher WHERE tid=#{id} //tid为第一条sql查询后的tid SELECT * FROM student WHERE cid=#{id} //cid为第一条sql查询后的cid * @author 魅力_小生 * */ @Test public void getClazz2(){ //创建session,设置事务为true SqlSession session = MyBatisUtil.getSessionFactory().openSession(true); String statement = "com.mlxs.mybatis.test5.clazzMapper.getClazz2"; Clazz clazz = session.selectOne(statement, "2"); System.out.println("clazz--->"+clazz); session.close(); } }
5.结果:
getClazz1: clazz--->Clazz [id=1, name=一年级, teacher=Teacher [id=1, name=老师A], stuList=[Student [id=1, name=学生A], Student [id=2, name=学生B], Student [id=3, name=学生C]]] getClazz2: clazz--->Clazz [id=2, name=二年级, teacher=Teacher [id=2, name=老师B], stuList=[Student [id=4, name=学生D], Student [id=5, name=学生E]]]
逃避不一定躲得过,面对不一定最难过