动态SQL,也就是说在配置文件里面可以实现判断、循环的处理。
1、if语句
所谓的if语句指的就是更新或查询语句的时候可以对传入内容进行判断操作。
<select id="findAllByTitle" parameterType="News" resultMap="NewsMap">
SELECT nid,title,pub_date FROM news
<if test="title != null">
WHERE title=#{title}
</if>
</select>
在分页上使用动态SQL 处理
<select id="findAllSplit" parameterType="java.util.Map" resultMap="NewsMap" useCache="false">
SELECT nid,title,pub_date FROM news
<if test="column != null">
WHERE ${column} LIKE #{keyWord}
</if>
LIMIT #{start},#{lineSize}
</select>
<select id="findAllSplit" parameterType="java.util.Map" resultMap="NewsMap" useCache="false">
SELECT nid,title,pub_date FROM news
<if test="column != null and keyWord !=null">
WHERE ${column} LIKE #{keyWord}
</if>
<if test="start > 0 and lineSize > 0 ">
LIMIT #{start},#{lineSize}
</if>
</select>
“: & g t ; ” 等于 “>”
2、choose 语句
使用if 语句的确可以进行简单的判断,但是如果要想进行多条件判断,就需要通过choose 语句完成,相当于java中的which。
<select id="findAllByTitle" parameterType="News" resultMap="NewsMap">
SELECT nid,title,pub_date FROM news
<where>
<choose>
<when test="nid != null and title != null">
nid=#{nid} AND title=#{title}
</when>
<when test="nid != null">
nid=#{nid}
</when>
<when test="title != null">
title=#{title}
</when>
</choose>
</where>
</select>
3、set 语句
set 语句主要出现在更新处理上,它出现的主要意义在于,可以利用set 语句在UPDATE 编写上自动出现SET 标记而已。
<update id="doUpdate" parameterType="News">
UPDATE news
<set>
title = #{title} ,
<if test="pubdate != null">
pub_date = #{pubdate} ,
</if>
</set>
<where>
<if test="nid != null">
nid=#{nid}
</if>
</where>
</update>
4、foreach 语句
<delete id="doRemoveBatch" parameterType="Integer">
DELETE FROM news
<where>
nid IN
<foreach collection="array" open="(" close=")" separator="," item="ele">
#{ele}
</foreach>
</where>
</delete>