看一段Oracle存储过程代码:
由于前台传入的查询参数不同,所以写了很多的if else,还需要非常注意SQL语句里面的and、空格、逗号和转移的单引号这些,拼接和调试SQL就是一件非常耗时的工作。 MyBaits的动态SQL就帮助我们解决了这个问题,它是基于OGNL表达式的。
按照官网的分类,MyBatis 的动态标签主要有四类:
需要判断的时候,条件写在test中:
<!-- 动态SQL where 和 if -->
<select id="selectBlogListIf" parameterType="blog" resultMap="BaseResultMap" >
select bid, name, author_id authorId from blog
<where>
<if test="bid != null">
AND bid = #{bid}
</if>
<if test="name != null and name != ''">
AND name LIKE '%${name}%'
</if>
<if test="authorId != null">
AND author_id = #{authorId}
</if>
</where>
</select>
需要选择—个条件的时候:
<!-- 动态SQL choose -->
<select id="selectBlogListChoose" parameterType="blog" resultMap="BaseResultMap" >
select bid, name, author_id authorId from blog
<where>
<choose>
<when test="bid !=null">
bid = #{bid, jdbcType=INTEGER}
</when>
<when test="name != null and name != ''">
AND name LIKE CONCAT(CONCAT('%', #{name, jdbcType=VARCHAR}),'%')
</when>
<when test="authorId != null ">
AND author_id = #{authorId, jdbcType=INTEGER}
</when>
<otherwise>
</otherwise>
</choose>
</where>
</select>
需要去掉where, and、逗号之类的符号的时候:
<!-- 动态SQL set -->
<update id="updateByPrimaryKey" parameterType="blog">
update blog
<set>
<if test="name != null">
name = #{name,jdbcType=VARCHAR},
</if>
<if test="authorId != null">
author_id = #{authorId,jdbcType=CHAR},
</if>
</set>
where bid = #{bid,jdbcType=INTEGER}
</update>
用来指定或者去掉前缀或者后缀:
<insert id="insertBlog" parameterType="blog">
insert into blog
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="bid != null">
bid,
</if>
<if test="name != null">
name,
</if>
<if test="authorId != null">
author_id,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="bid != null">
#{bid,jdbcType=INTEGER},
</if>
<if test="name != null">
#{name,jdbcType=VARCHAR},
<!-- #{name,jdbcType=VARCHAR,typeHandler=com.gupaoedu.type.MyTypeHandler}, -->
</if>
<if test="authorId != null">
#{authorId,jdbcType=INTEGER},
</if>
</trim>
</insert>
需要遍历集合的时候:
<!-- foreach 动态SQL 批量删除 -->
<delete id="deleteByList" parameterType="java.util.List">
delete from blog where bid in
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item.bid,jdbcType=INTEGER}
</foreach>
</delete>