第十章、多对一处理
1 CREATE TABLE `teacher` (
2 `id` INT(10) NOT NULL,
3 `name` VARCHAR(30) DEFAULT NULL,
4 PRIMARY KEY (`id`)
5 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
6
7 INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师');
8
9 CREATE TABLE `student` (
10 `id` INT(10) NOT NULL,
11 `name` VARCHAR(30) DEFAULT NULL,
12 `tid` INT(10) DEFAULT NULL,
13 PRIMARY KEY (`id`),
14 KEY `fktid` (`tid`),
15 CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
16 ) ENGINE=INNODB DEFAULT CHARSET=utf8;
17 INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
18 INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
19 INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
20 INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
21 INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');
按照查询嵌套处理
1 <resultMap id="StudentTeacher" type="Student">
2 <result column="id" property="id"/>
3 <result column="name" property="name"/>
4 <!--
5 复杂的属性，我们需要单独处理
6 对象：association
7 集合：collection
8
9 -->
10 <association column="tid" property="teacher" javaType="Teacher" select="getTeacher"/>
11 </resultMap>
12
13
14 <!--
15 思路：
16 1.查询所有学生的信息
17 2.根据查询出来的学生的tid，寻找对应的老师
18 -->
19 <select id="getStudent" resultMap="StudentTeacher">
20 select *from student
21 </select>
22
23 <select id="getTeacher" resultType="Teacher">
24 select * from teacher where id = #{id}
25 </select>
按照结果嵌套处理
1 <resultMap id="StudentTeacher2" type="Student">
2 <result property="id" column="id"/>
3 <result property="name" column="sname"/>
4 <association property="teacher" javaType="Teacher">
5 <result property="name" column="tname"/>
6 </association>
7 </resultMap>
8
9
10 <!--按照结果嵌套处理-->
11 <select id="getStudent2" resultMap="StudentTeacher2">
12 select s.id,s.name sname,t.name tname
13 from student s,teacher t
14 where s.tid = t.id
15 </select>
浙公网安备 33010602011771号