和第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]]]
