MyBatis详解(4)
动态SQL
根据不同条件拼接 SQL 语句,实现对数据库更准确的操作;
两种方式:1.映射器配置文件; 2.注解
常用的动态 SQL 元素
if 元素:判断语句,单条件分支判断.
choose 元素(优先):(when,otherwise)多条件分支判断,等同于 java 的 switch
trim:(万能元素)(where,set):辅助元素,用于处理一些SQL 拼接的问题.
foreach 元素:循环语句,在 in 语句等列举条件常用
bind 元素:自定义上下文变量,传递参数
if元素
<select id="findStudents" parameterType="Student" resultType="Student">
select * from student where 1=1
<if test="ssex != null">
and ssex = #{ssex}
</if>
<if test="classid != 0">
and classid = #{classid}
</if>
</select>
where元素
1.自动根据是否有条件添加where
2.去掉第一个条件的and
<select id="findStudentwhere" parameterType="Student" resultType="Student">
select * from student
<!-- where
1.自动根据是否有条件添加where
2.去掉第一个条件的and
-->
<where>
<if test="ssex != null"> and ssex = #{ssex}</if>
<if test="classid != 0"> and classid = #{classid}</if>
</where>
</select>
set元素
1.自动添加set(如果 set 包含的内容为空的话则会出错。)
2.去掉set包含的语句中最后一个逗号
<update id="updateStudent" parameterType="Student">
update student
<set>
<if test="sname != null">sname =#{sname},</if>
<if test="birthday != null">birthday =#{birthday},</if>
<if test="ssex != null">ssex =#{ssex},</if>
<if test="classid != null">classid =#{classid},</if>
</set>
sid = #{sid}
</update>
choose元素(优先,只执行一个)
<select id="findStudentchoose" parameterType="Student" resultType="Student">
select * from student
<where>
<choose>
<when test="sid !=0"> and sid= #{sid}</when>
<when test="sname !=null"> and sname= #{sname}</when>
<otherwise> 满足其他条件的语句</otherwise>
</choose>
</where>
</select>
trim元素
prefix :之前要添加的字符
prefixOverrides:之前的后面要去除的字符
suffix:之后要添加的字符
suffixOverrides:后面要去除的字符
<insert id="insertStudent" parameterType="Student">
insert into student
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="sname != null">sname,</if>
<if test="birthday != null">birthday,</if>
<if test="ssex != null">ssex,</if>
<if test="classid != null">classid,</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="sname != null">#{sname},</if>
<if test="birthday != null">#{birthday},</if>
<if test="ssex != null">#{ssex},</if>
<if test="classid != null">#{classid},</if>
</trim>
</insert>
foreach 元素(批量处理)
<!-- 批量删除 -->
<delete id="deleteStudentArray" >
delete from student
<where>
<foreach collection="array" item="sid" open="sid in (" close=")" separator=",">#{sid}</foreach>
</where>
</delete>
<!-- 批量添加 -->
<insert id="addStudentList">
insert into student (sname,birthday,ssex,classid)
<foreach collection="list" item="x" open="values" separator=",">
(#{x.sname},#{x.birthday},#{x.ssex},#{x.classid})
</foreach>
</insert>
bind 元素
<select id="findStudentsLikeSname" parameterType="string" resultType="Student">
<!-- 方式一 :传入的参数就是(%x%) -->
select *from student where sname like #{sname}
<!-- 方式二 concat()拼接-->
select *from student where sname like concat("%","#{sname}","%")
<!-- 方式三 -->
select *from student where sname like "%${sname}%"
<!-- 方式四 '"交替出现 -->
select *from student where sname like "%"#{sname}"%"
<!--方式五 推荐 -->
<bind name="xxx" value="'%'+_parameter+'%'"/>
select *from student where sname like #{xxx}
</select>
#{} 和 ${}的区别
#{},防止SQL注入的占位符,防止SQL注入
${} 字符串的替换,不能防止SQL注入