复杂环境搭建
①创建SQL表
CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO teacher(`id`, `name`) VALUEs (1, 'qin');
CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ftkid` (`tid`),
CONSTRAINT `ftkid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO student(`id`, `name`, `tid`) VALUEs (1, 'yi', '1');
INSERT INTO student(`id`, `name`, `tid`) VALUEs (2, 'er', '1');
INSERT INTO student(`id`, `name`, `tid`) VALUEs (3, 'san', '1');
INSERT INTO student(`id`, `name`, `tid`) VALUEs (4, 'si', '1');
INSERT INTO student(`id`, `name`, `tid`) VALUEs (5, 'wu', '1');
建立老师、同学表,同学的tid对应老师外键id
②创建表对应实体类Student和Teacher
③建立Mapper接口
④建立Mapper对应xml文件
⑤核心配置中注册Mapper接口文件
多对一处理
多个学生由一个老师教学,查询出所有学生以及每个学生对应老师的信息。
嵌套子查询
<!-- ①查询所有学生
②依据tid查询对应老师
-->
<resultMap id="studentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!-- 复杂属性需要单独处理
对象使用association
集合使用collection
-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getStudent" resultMap="studentTeacher">
select * from student s, teacher t where s.tid = t.id;
</select>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id=#{tid};
</select>
因为学生对象中有Teacher复杂成员,用resultMap对其处理,该对象由tid属性再次执行查询命令。
按结果嵌套
<select id="getStudent" resultMap="StudentTeacher">
select s.id sid,s.name sname,t.name tname,t.id tid from student s, teacher t where s.tid = t.id;
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
该方式就是依据查询结果,用resultMap一一对应设置进去。
一对多处理
一个老师教学多个学生,对于老师就是一对多关系,老师实体类用List集合存储学生
结果嵌套处理
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid, s.name sname, t.name tname, t.id tid
from student s,teacher t
where s.tid=t.id and t.id=#{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
注意使用collection中,获得集合泛型类型时,需要用ofType="Student"
嵌套查询处理
<select id="getTeacher" resultMap="TeacherStudent">
select * from teacher where id = #{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
select * from student where tid=#{tid};
</select>
小结
①关联 association:对照复杂对象,多对一
②集合 collection:对照集合,一对多
③javaType:指定实体类中属性类型
④ofType:指定集合泛型类属性类型