Mybatis动态SQL映射

电玩女神 2023-10-05 10:21 6阅读 0赞

#### Mybatis动态SQL映射 ####

*  动态SQL映射
 *   *  1.if 结构 test里面的 and 或 or 必须小写
     *  2.trim-if 多条件结构
     *  3.where-if 多条件结构
     *  4.choose-when-otherwise 多选一结构
     *  5.foreach 循环结构 代替 WHERE id IN (?,?...)
     *  6. set-if 或 trim-if 修 改数据结构

## 动态SQL映射 ##

### 1.if 结构 test里面的 and 或 or 必须小写 ###

<if test="id != null and id != -1">
        WHERE id=#{id}
    </if>

### 2.trim-if 多条件结构 ###

<trim prefix="WHERE" prefixOverrides="AND|OR">
        <if test="id != null">
            AND id=#{id}
        </if>
        <if test="name != null">
            AND userName like CONCAT('%',#{name},'%')
        </if>
    </trim>

### 3.where-if 多条件结构 ###

<where>
        <if test="id != null">
            AND id=#{id}
        </if>
        <if test="name != null">
            AND userName like CONCAT('%',#{name},'%')
        </if>
    </where>

### 4.choose-when-otherwise 多选一结构 ###

<choose>
        <when test="id!=null">
            WHERE id=#{id}
        </when>
        <when test="name!=null">
            WHERE userName like CONCAT('%',#{name},'%')
        </when>
        <otherwise>
            
        </otherwise>
    </choose>

### 5.foreach 循环结构 代替 WHERE id IN (?,?..) ###

WHERE id IN
    
    <foreach collection="ids" item="id" open="(" separator=","
        close=")">
        #{id}
    </foreach>

### 6. set-if 或 trim-if 修 改数据结构 ###

<update id="setUser">
        UPDATE smbms_user
        <set>
            <if test="userCode != null and userCode != ''">userCode = #{userCode} , </if>
            <if test="userName != null">userName = #{userName}, </if>
        </set>
        WHERE id = #{id}
        
        
        <trim prefix="SET" suffixOverrides="," suffix="WHERE id = #{id}">
            <if test="userCode != null">userCode = #{userCode} , </if>
            <if test="userName != null">userName = #{userName}, </if>
        </trim>
    </update>