mybatis-写法技巧

1.trim prefix prefixOverrides suffix suffixOverrides

---通常用法---

例子1-1（以字符“where”覆盖首个“and”或"or”字符）：

<!--prefix & prefixOverrides-->
<select id="findUserById" resultType="user">
	 select * from user
	 <trim prefix="where" prefixOverrides="and|or ">
		 <if test="id != null">
			id=#{id}
		 </if>
		 and deleteFlag=0;
	 </trim>
 </select>

 等效于

<select id="findUserById" resultType="user">
	 select * from user
	 <where>
		 <if test="id != null">
			id=#{id}
		 </if>
		 and deleteFlag=0;
	 </where>
 </select>

 

当id为空，生成的sql语句为：

//deleteFlag前的"and"被去掉
select * from user where deleteFlag=0;

 

 

例子1-2（为trim括起来的语句添加"set"字符前缀，并且覆盖去掉最后面的","字符）：

<!--主要看suffixOverrides的用法-->
<update id="updateUser" parameterType="com.dy.entity.User">
	 update user
	 <trim prefix="set" suffixOverrides=",">
		<if test="name != null">name = #{name},</if>
		 <if test="password != null">password = #{password},</if> 
     </trim>
	 <where>
		 <if test="id != null">
			id = #{id}
		 </if>
		 and deleteFlag = 0;
	 </where>
</update>

  等效于

<update id="updateUser" parameterType="com.dy.entity.User">
	 update user
	 <set>
		 <if test="name != null">name = #{name},</if>
		 <if test="password != null">password = #{password},</if> 
	 </set>
	 <where>
		 <if test="id != null">
			id = #{id}
		 </if>
		 and deleteFlag = 0;
	 </where>
</update>

 

生成的sql语句为：

//password后的","被去掉
update user set name = #{name},password = #{password} where id = #{id} and deleteFlag = 0;

 

注：prefix prefixOverrides suffix suffixOverrides并非需要配套使用，可以任意组合或者单个使用，具体情况按实际需求

 

2.foreach

<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>