MyBatis五动态语句

动态SQL
动态SQL其实不难，但是很实用
if choose when otherwise where set trim foreach bind
----------------------------------------------------
<if test="roleName!=null and roleName!=''">
and role_name like concat('%',#{roleName},'%')
</if>
----------------------------------------------------
<choose>
<when test="roleNo!=null and roleNo!=''">
and roleNo = #{roleNo}
</when>
<when test="roleName!=null and roleNo!=''">
and roleName like concat('%',#{roleName},'%')
</when>
<otherwise>
and note is not null
</otherwise>
</choose>
----------------------------------------------------where元素如果条件成立可以自己加上 where
<where>
<if test="roleName!=null and roleName!=''">
and role_name like concat('%',#{roleName},'%')
</if>
</where>
----------------------------------------------------set元素遇到没用的逗号会去掉
<update id="updateRole" parameterType="role">
update t_role
<set>
<if test="roleName!=null and roleName!=''">
role_name = #{roleName},
</if>
<if test="note!=null and note!=''">
role_name = #{note}
</if>
</set>
</update>
----------------------------------------------------trim元素意味着我们要去掉一些特殊的字符串
<select id="findRole" parameterType="string" resultMap="roleResultMap">
select role_no,role_name,note from t_role
<trim prifx="where" prifxOverrides="and">
<if test="roleName!=null and roleName!=''">
and role_name like concat('%',#{roleName},'%')
</if>
</trim>
</select>
----------------------------------------------------foreach 遍历集合
<select id="findRole" parameterType="string" resultMap="roleResultMap">
select role_no,role_name,note from t_role
<where>
<if test="sexList"!=null">
<foreach item="sex" index="index" collection="sexList" open="(" separator="," close=")">
#{sex}
</foreach>
</if>
</where>
</select>
----------------------------------------------------bind元素，定义一个上下文变量，很有用的元素，_pattern是传递进来的参数
<select id="findRole" parameterType="string" resultMap="roleResultMap">
<bind name="pattern" value="'%'+_pattern+'%'"></bind>
select role_no,role_name,note from t_role
<where>
<if test="pattern!=null and pattern!=''">
and  pattern like #{pattern}
</if>
</where>
</select>