Mybatis框架(四)—一对多、多对一处理和动态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, '秦老师');
CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');
实体类
@Data
public class Student {
private int id;
private String name;
private int tid;
}
@Data
public class Teacher {
private int id;
private String name;
//一个老师拥有多个学生
private List<Student> students;
}
实现方式(一)：按照结果嵌套处理
<!--按结果嵌套查询-->
<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"/>
<!--复杂的属性，我们需要单独处理 对象： association 集合： collection
javaType="" 指定属性的类型！
集合中的泛型信息，我们使用ofType获取
-->
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
实现方式(二)：按照查询嵌套处理
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from mybatis.teacher where id = #{tid}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
select * from mybatis.student where tid = #{tid}
</select>
多对一处理
实现方式(一)：按照结果嵌套处理
<!--按照结果嵌套处理-->
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid,s.name sname,t.name tname
from student s,teacher t
where s.tid = t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
实现方式(二)：按照查询嵌套处理
<!--
思路:
1. 查询所有的学生信息
2. 根据查询出来的学生的tid，寻找对应的老师！ 子查询
-->
<select id="getStudent" resultMap="StudentTeacher">
select * from student
</select>
<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="getTeacher" resultType="Teacher">
select * from teacher where id = #{id}
</select>
动态 SQL
什么是动态SQL：动态SQL就是指根据不同的条件生成不同的SQL语句
搭建环境
CREATE TABLE `blog` (
`id` varchar(50) NOT NULL COMMENT '博客id',
`title` varchar(100) NOT NULL COMMENT '博客标题',
`author` varchar(30) NOT NULL COMMENT '博客作者',
`create_time` datetime NOT NULL COMMENT '创建时间',
`views` int(30) NOT NULL COMMENT '浏览量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
编写实体类
@Data
public class Blog {
private int id;
private String title;
private String author;
private Date createTime;
private int views;
}
编写实体类对应Mapper接口 和 Mapper.XML文件
实现方式(一)：IF嵌套
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from mybatis.blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
实现方式(二)：choose (when, otherwise)
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
实现方式(三)：trim (where,set)
<update id="updateBlog" parameterType="map">
update mybatis.blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
实现方式(四)：SQL片段
1、使用SQL标签抽取公共的部分
<sql id="if-title-author">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
2、在需要使用的地方使用Include标签引用即可
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
实现方式(五)：Foreach
<!--
select * from mybatis.blog where 1=1 and (id=1 or id = 2 or id=3)
我们现在传递一个万能的map ， 这map中可以存在一个集合！
-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
