Mybatis08_动态SQL

爱被打了一巴掌 2022-12-12 02:27 106阅读 0赞

**Mybatis08\_动态SQL**

--------------------

*  **if 标签一般和 where 标签搭配使用，where 标签会自动判断是否要删除语句块中的 and 关键字**

<select id="findByAccount" parameterType="com.blu.entity.Account">
    	select * from t_account
    	<where>
    		<if test="id!=0">
    			id = #{id}
    		</if>
    		<if test="username!=null">
    			and username = #{username}
    		</if>
    		<if test="password!=null">
    			and password = #{password}
    		</if>
    		<if test="age!=0">
    			and age = #{age}
    		</if>
    	</where>
    </select>

*  **choose 标签与 where 标签搭配使用，将按顺序匹配，只要有条件满足则 choose 结束，相当于 or，类似Java 的 switch 语句**

<select id="findByAccount" parameterType="com.blu.entity.Account">
    	select * from t_account
    	<where>
    		<choose>
    			<when test="id!=0">
    				id = #{id}
    			</when>
    			<when test="username!=null">
    				username = #{username}
    			</when>
    			<when test="password!=null">
    				password = #{password}
    			</when>
    			<otherwise>
    				age = #{age}
    			</otherwise>
    		</choose>
    	</where>
    </select>

*  **trim 标签的prefix表示加上前缀where，prefixOverrides 表示去掉第一个关键字and，作用和 if 与 where 的搭配相同**

<select id="findByAccount" parameterType="com.blu.entity.Account">
    	select * from t_account
    	<trim prefix="where" prefixOverrides="and">			
    		<if test="id!=0">
    			and id = #{id}
    		</if>
    		<if test="username!=null">
    			and username = #{username}
    		</if>
    		<if test="password!=null">
    			and password = #{password}
    		</if>
    		<if test="age!=0">
    			and age = #{age}
    		</if>		
    	</trim>
    </select>

*  **set 标签用于在更新操作中拼接SQL**

<update id="update" parameterType="com.blu.entity.Account">
    	update t_account
    	<set>
    		<if test="username!=null">
    			username = #{username}
    		</if>
    		<if test="password!=null">
    			password = #{password}
    		</if>
    		<if test="age!=0">
    			age = #{age}
    		</if>
    	</set>
    	where id = #{id}
    </update>

*  **foreach 标签**

<delete id="deleteJzByDjpcAndXhList" parameterType="com.blu.entity.po.DeleteJzPo">
    	DELETE FROM CK_JZ
    	<where>
        	<if test="xhList!=null and xhList.size>0">
            	AND XH IN
                <foreach collection="xhList" item="xh" index="index" open="(" close=")" separator=",">
                	#{xh, jdbcType=INTEGER}
                </foreach>
            </if >
    		AND DJPC = #{djpc}
    	</where>
    </delete>