目录
多对一处理
多个学生,对应一个老师
对于学生这边而言,关联…多个学生,关联一个老师【多对一】
对于老师而言,集合,一个老师,有很多学生【一对多】
按照查询嵌套处理
<!--自定义返回值-->
<resultMap id="StudentTeacher" type="Student">
<id column="id" property="id"/>
<result column="name" property="name"/>
<!--复杂的属性,我们需要单独处理
对象:association
集合:collection
-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacherById"/>
</resultMap>
<!--查询所有学生-->
<select id="getStudents" resultMap="StudentTeacher">
select * from student
</select>
<!--根据教师id查询教师信息-->
<select id="getTeacherById" resultType="Teacher">
select * from teacher where id = #{id}
</select>
按照结果嵌套处理
<select id="getStudents2" resultMap="StudentTeacher2">
select
student.id as sid,
student.name as sname,
teacher.name as tname
from student,teacher
where student.tid = teacher.id
</select>
<resultMap id="StudentTeacher2" type="Student">
<id column="id" property="sid"/>
<result column="name" property="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
使用方式:
多对一查询
子查询
连表查询
一对多处理
一个老师拥有多个学生,一对多关系
按照查询嵌套处理
<resultMap id="TeacherStudent" type="Teacher">
<id column="tid" property="id"/>
<result column="tname" property="name"/>
<!--
复杂的属性,我们需要单独处理
对象: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="getTeacherById" resultMap="TeacherStudent" parameterType="int">
select t.id tid,t.name tname,s.id sid,s.name sname
from teacher t,student s
where s.tid = t.id and t.id = #{id}
</select>
按照结果嵌套处理
<select id="getTeacherById2" resultMap="TeacherStudent2" parameterType="int">
select * from teacher where id = #{id}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<id property="id" column="id"/>
<result property="name" column="name"/>
<collection property="students" javaType="ArrayList" ofType="Student" select="cn.bloghut.dao.StudentMapper.getStudentsByTid" column="id"/>
</resultMap>
<!--这个方法是在Student mapper文件中的-->
<select id="getStudentsByTid" resultType="Student">
select * from student where tid = #{id}
</select>
关联-association
集合-collection
动态SQL
动态SQL就是根据不同的条件去执行一个逻辑代码,生成不同的SQL语句;
if
if进行条件判断,满足条件,才拼接成功
<select id="queryBlogIF" resultType="Blog" parameterType="map">
select * from blog where 1 = 1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
where
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除
<select id="queryBlogWhere" resultType="Blog" parameterType="map">
select * from blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
choose
类似Java中的switch,只满足一个,如果都不满足,则执行outherwise
<select id="queryBlogChoose" resultType="Blog" parameterType="map">
select * from 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>
set
set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号。
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title != null">
title=#{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
foreach
foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
- item: 当前元素
- index: 当前迭代号,类似数组下标
- open:开头
- separator:分割
- close:结尾