我这里是判断是否为空。
当where元素里面没有值的时候 where 也不会插入 ,会自动过滤and和or
<selectid="getProviderList"resultType="Provider">
select * from smbms_provider
<where><iftest="proCode!=null and proCode!=''">
and proCode LIKE CONCAT('%',#{proCode},'%')
</if><iftest="proName!=null and proName!=''">
and proName LIKE CONCAT('%',#{proName},'%')
</if></where></select>
<selectid="getUserList_choose"resultType="User">
select * from smbms_user where 1=1
<choose><whentest="userName!=null and userName!=''">
and userName like CONCAT('%',#{userName},'%')
</when><whentest="userCode!=null and userCode!=''">
and userCode like CONCAT('%',#{userCode},'%')
</when><whentest="userRole!=null">
and userRole=#{userRole}
</when><otherwise>
and YEAR(creationDate) =YEAR(#{creationDate})
</otherwise></choose></select>
if-trim
trim 有四个属性
prefix:前缀,trim元素里面 有值就在 trim 包含的内容前加上前缀
suffix:后缀,内容后加上后缀
prefixOverrides:trim 包含的内容的首部进行指定内容忽略
suffixOverrides:trim 包含的内容的尾部进行指定内容忽略
<updateid="modifyProvider"parameterType="Provider">
UPDATE smbms.smbms_provider
<trimprefix="set"suffixOverrides=","suffix="WHERE id = #{id} ;"><iftest="proCode!=null and proCode!=''">proCode = #{proCode} ,</if><iftest="proName!=null and proName!=''">proName = #{proName} , </if><iftest="proDesc!=null and proDesc!=''">proDesc = #{proDesc} ,</if><iftest="proContact!=null and proCode!=''">proContact = #{proContact} , </if><iftest="proPhone!=null and proPhone!=''">proPhone = #{proPhone} , </if><iftest="proAddress!=null and proAddress!=''">proAddress = #{proAddress} , </if><iftest="proFax!=null and proFax!=''">proFax = #{proFax} , </if><iftest="modifyDate!=null and modifyDate!=''">modifyDate = #{modifyDate} , </if><iftest="modifyBy!=null">modifyBy = #{modifyBy},</if></trim></update>
<!-- 这就是map里面有list --><!-- 根据用户角色列表和性别(多参数),获取该角色列表下用户列表信息 map --><selectid="getUserByConditionMap_foreach_map"resultMap="userMapByRole">
select * from smbms_user where gender=#{gender} and userRole in
<foreachcollection="roleIds"item="roleIdList"open="("separator=","close=")">
#{roleIdList}
</foreach></select>