Mybatis动态SQL
Mybatis提供的拼接Sql语句的机制。
1.if
<select id="listSearchStudents" resultType="entity.Student">
select *
from stu
where sname = #{sname}
<if test="phone != null and phone !='' ">
and phone = #{phone}
</if>
</select>
2.choose when otherwise
<select id="listSearchStudents1" resultType="entity.Student">
select *
from stu
where 1=1
<choose>
<when test="sname != null and sname != '' ">
and sname like '%${sname}%'
</when>
<when test="phone != null and phone != '' ">
and phone like '%${phone}%'
</when>
<!-- 以上都不满足 -->
<otherwise>
</otherwise>
</choose>
</select>
3.where
子元素有返回值,在对应语句加where
<select id="listSearchStudents2" resultType="entity.Student">
select *
from stu
<where>
<if test="sname != null and sname != '' ">
and sname like '%${sname}%'
</if>
<if test="phone != null and phone != '' ">
and phone like '%${phone}%'
</if>
</where>
</select>
4.set
用于update
<update id="updateStudentBySid" parameterType="Integer">
update stu
<set>
<if test="sname != null and sname != '' ">
sname = #{sname},
</if>
<if test="phone != null and phone != '' ">
phone = #{phone},mj
</if>
</set>
where sid = #{sid}
</update>
5.trim
- prefix 指定给Sql语句增加的前缀
- prefixOverrides 指定给Sql语句要去掉的前缀
- suffix 指定给Sql语句增加的后缀
- suffixOverrides 指定给Sql语句要去掉的后缀
代替where和set使用
<trim prefix="where" prefixOverrides="and">
<if test="">
and sid = #{sid}
</if>
</trim>
<trim prefix="set" suffixOverrides=",">
<if test="">
sid = #{sid},
</if>
</trim>
6.foreach
用于遍历 支持数组 List Set 接口的集合
通常用于in关键字
<select id="listStudentsByForeach" resultType="entity.Student">
select *
from stu
where sid in
<foreach collection="list" item="sid" index="index" open="(" separator="," close=")">
#{sid}
</foreach>
</select>