Mybatis中的动态Sql

Mybatis中的动态Sql

9种：if choose when otherwise trim where set foreach bind

< if > 标签

	<!-- 动态sql: <if> -->
	<select id="selectUserByIf" parameterType="cn.htj.domain.User" resultType="cn.htj.domain.User">
		select * from user where 1=1
		<if test="username != null and username != ''">
			and username like concat('%',#{username},'%')
		</if>
		<if test="sex != null and sex != ''">
			and sex=#{sex}
		</if>
	</select>

choose、when、otherwise 元素

只选择一个，类似switch。
若第一个条件满足，就不会向下走了break了；若不满足，依次判断；若都不满足，到 otherwise

	<select id="selectUserByChoose" parameterType="cn.ljq.domain.User" resultType="cn.ljq.domain.User">
		select * from user where 1=1 
		<choose>
			<when test="username != null and username != ''">
				and username like concat('%',#{username},'%')
			</when>
			<when test="sex != null and sex != ''">
				and sex = #{sex}
			</when>
			<otherwise>
				and id > 10
			</otherwise>
		</choose>
	</select>

trim元素

prefix 在包含的内容的首部部添加前缀，prefixOverris把包含的内容的首部某些内容忽略

<select id="selectUserByTrim" parameterType="cn.htj.domain.User" resultType="cn.htj.domain.User">
	select * from user
	<trim prefix="where" prefixOverrides="and|or"> <!-- 注意 or前面不能加空格 -->
		<if test="username != null and username != ''">
			and username like "%"#{username}"%"
		</if>
		<if test="sex != null and sex != ''">
			or sex = #{sex}
		</if>
	</trim>
</select>

where元素

where元素会智能处理条件中的 and 和 or

<select id="selectUserByWhere" parameterType="cn.htj.domain.User" resultType="cn.htj.domain.User">
	select * from user
	<where>
		<if test="username != null and username != ''">
			and username like concat('%',#{username},'%')
		</if>
		<if test="sex != null and sex != ''">
			or sex = #{sex}
		</if>
	</where>
</select>

set元素

在update语句中可以使用 set 元素动态更新列。

<update id="updateUserBySet" parameterType="cn.htj.domain.User">
	update user
	<set>
		<if test="username != null">
			username = #{username}
		</if>
		<if test="sex != null">
			, sex = #{sex}
		</if>
		<if test="birthday != null">
			, birthday = #{birthday}
		</if>
		where id = #{id}
	</set>
</update>

foreach元素

foreach元素主要用于in条件

<!-- [foreach标签]：表示一个foreach循环 -->
<!-- [collection]：集合参数的名称，如果是直接传入集合参数，则该处的参数名称只能填写[list]，若是数组，则值为array。 -->
<!-- [item]：每次遍历出来的对象 -->
<!-- [idex]：迭代的索引 -->
<!-- [open]：开始遍历时拼接的串 -->
<!-- [close]：结束遍历时拼接的串 -->
<!-- [separator]：遍历出的每个对象之间需要拼接的字符 -->
<select id="selectUserByForeach" parameterType="List" resultType="cn.htj.domain.User">
	select * from user where 1=1
	<if test="list != null and list.size() >0">
	<foreach collection="list" item="item" index="i" open="and id in (" close=")" separator=",">
		#{item}
	</foreach>
	</if>
</select>

public List<User> selectUserByForeach(List<Integer> ids);

bind元素

使用bind元素进行模糊查询。

<!-- 使用bind元素进行模糊查询 -->
<select id="selectUserByBind" parameterType="cn.htj.domain.User" resultType="cn.htj.domain.User">
	<bind name="param_1" value="'%'+username+'%'"/>
	select * from user where username like #{param_1}
</select>