后端-框架-MyBatis-动态SQL-trim

trim(where, set)互相替换

<trim prefix="?" prefixOverrides="? | ? " suffix="?" suffixOverrides="? | ?"><trim>

where标签

<select id="getUserList" resultMap="getUserList"> select user.* from smbms_user as user <!-- 通过where标签动态剔除拼接的and和or --> <where> user.userRole=role.id <!-- 通过if标签防止输入null导致无查询结果 --> <if test="userName != null and userName != '' "> and user.userName like Concat('%',#{userName},'%') </if> <if test="userRole != null"> and user.userRole=#{userRole} </if> </where> </select>

trim替代where

<select id="getUserList" resultMap="getUserList"> select user.* from smbms_user as user <!-- 通过trim标签动态剔除拼接的and和or --> <trim prefix="Where" prefixOverrides="and | or "> user.userRole=role.id <if test="userName != null and userName != '' "> and user.userName like Concat('%',#{userName},'%') </if> <if test="userRole != null"> and user.userRole=#{userRole} </if> </trim> </select>

set标签

<update id="updateUser" parameterType="User"> update smbms_user <!-- 使用set标签动态更新,并自动剔除, --> <set> <!-- 通过if标签防止输入null导致更新失败 --> <if test="userName != null">userName = #{userName},</if> <if test="modifyBy != null">modifyBy = #{modifyBy},</if> <if test="modifyDate != null">modifyDate = #{modifyDate},</if> </set> where id = #{id} </update>

trim替代set

<update id="updateUser" parameterType="User"> update smbms_user <trim prefix="set" suffixOverrides="," suffix="where id = #{id}"> <if test="userName != null">userName = #{userName},</if> <if test="modifyBy != null">modifyBy = #{modifyBy},</if> <if test="modifyDate != null">modifyDate = #{modifyDate},</if> </trim> </update>