MyBatis常用动态SQL

MyBatis常用动态SQL总结

在实际开发中，经常需要根据不同的条件动态拼接SQL，并且还确保空格、列名最后的逗号、多余的AND、OR条件等。在MyBatis中处理这种情况是比较方便容易的。

MyBatis是基于OGNL表达式的，使用和JSTL标签等类似，可以被用在SQL映射语句中。

常用的动态SQL元素：

• if
• choose(when、otherwise)
• where
• set
• trim
• foreach
• bind

1.if

假如status 不为空，则拼接SQL" user_status = #{status}"，同理sex也是。但是在status为空、sex不为空的时候拼接的SQl为"select * from t_user where and user_sex=#{status}"，此时因为多出的","就会出现错误。

<select id="findById" resultType="User">
	select * from t_user where 
	<if test="status != null">
		and user_status = #{status} 
	</if>
	<if test="sex != null">
		and user_sex = #{sex}
	</if>
</select>

2.choose

只想使用其中的某个条件语句，就可以使用choose元素，类似于Strus2中的choose标签。若status和username、pwd都不为空，则拼接"user_status = #{status}";若都为空，则拼接"user_sex = 1"。若status为空，username、pwd不为空，此时拼接的SQL就会出现问题，多出了一个and，应当注意一下。

<select id="findByChoose" resultType="User">
	select * from t_user where 
	<choose>
		<when test="status != null">
			user_status = #{status} 
		</when>
		<when test="username != null && pwd != null">
			and user_name = #{username} and user_pwd = #{pwd}
		</when>
		<otherwise>
			user_sex = 1
		</otherwise>
	</choose>
</select>

3.where

where元素会判断只有if条件成立时才会插入WHERE子句，若最后的SQL为AND或者OR开头，where元素会将其去除。如本例中，stauts为空、sex不为空时，拼接的SQL为"and user_sex = #{sex}"，where元素会为SQL添加where子句，并去掉多余的and。

<select id="findByWhere" resultType="User">
	select * from t_user
	<where>
		<if test="status != null">
			user_status = #{status}
		</if>
		<if test="sex != null">
			and user_sex = #{sex}
		</if>
	</where>		
</select>

4.set

动态更新可以使用set元素，通过if元素动态包含需要更新的列。set元素会前置set关键字，也会去除SQL中多余的","。例如pwd为空、status不为空时，拼接的SQL为"user_status = #{status},"，多出的","会被set元素去掉。

<update id="updateBySet" parameterType="User">
	update t_user
	<set>
		<if test="username != null">user_name = #{username},</if>
		<if test="sex != null">user_sex = #{sex},</if>
		<if test="status != null">user_status = #{status},</if>
		<if test="pwd != null">user_pwd = #{pwd}</if>
	</set>
	where user_id = #{id}
</update>

5.trim

trim元素是一个格式化标签，可用于完成set或where元素的功能。

• prefix：前缀，trim元素中有语句成立则在sql语句加上的前缀
• prefixOverrides：前缀覆盖条件，多个条件以'|'分隔
• suffix：后缀，trim元素中有语句成立，则在sql语句加上的后缀
• suffixOverrides：后缀覆盖条件，多个条件以'|'分隔

(1).模拟set

若name、sex不为空，拼接的SQL为set user_name = #{name}, user_sex = #{sex}，会根据suffixOverrides属性覆盖掉多的','，同时添加上'set'

<update id="updateByTrim" parameterType="User">
	update t_user
	<trim prefix="set" suffixOverrides=",">
		<if test="name != null">user_name = #{name},</if>
		<if test="sex != null">user_sex = #{sex},</if>
	</trim>
	where id = #{id}
</update>

(2).模拟where

若name、status不为空，拼接的SQL为：where user_name = #{name} and user_status = #{status}，会根据prefixOverrides属性覆盖掉多的'and或or'，同时添加上'where'。

<select id="findByTrim" parameterType="User" resultMap="userMap">
	select * from t_user
	<trim prefix="where" prefixOverrides="and | or">
		<if test="name != null">
			and user_name = #{name}
		</if>
		<if test="status != null">
			and user_status = #{status}
		</if>
	</trim>
</select>

6.foreach

构造IN条件语句时需要对集合进行遍历，这时候可以使用foreach元素。foreach会去掉多余","。若集合为空，则不会执行foreach元素中的操作，但此时会多出"in"关键字，报错。

• item：集合中的元素
• index：元素所在集合的下标，迭代map时，index是键
• collection：集合的类型，可选值list,array，除此之外还可以是@Param("name")、Map中的key、类的成员变量
• open、close：在首、尾拼接的字符
• separator：每个元素间的分隔符

注：

• foreach标签支持List、Set、Map、Array等的遍历
• 迭代数组或者List、Set集合时，index是迭代次数，item是本次迭代获取的元素；
• 迭代Map（或Map.Entry对象的集合）时，index是键，item是值

collection属性介绍

• 传入单参数且是List时，collection="list"
• 传入单参数且是Array时，collection="array"
• 传入单参数且是Set时，需使用@Param注解，同下
• 传入单参数且使用@Param("name")时，collection="name"，即和@Param注解的value属性值相同，此时list、array无效
• 传入多参数且封装成Map时，如map.put("ids", Arrays.asList(1, 2))，此时collection="ids"
• 传入多参数且封装成类时，如User类中有成员变量List<Integer> roleIds，此时collection="roleIds"；若User类中有成员变量Role role，Role类中有成员变量prilIds，此时collection="role.prilIds"

// 示例1.单参数List
// UserMapper
public List<User> findByForeach(List<Integer> ids);
// UserMapper.xml
<select id="findByForeach" resultType="User">
	select * from t_user
	where id in
	<foreach item="id" index="index" collection="list" open="(" separator="," close=")">
		#{id}
	</foreach>
</select>

// 示例2.单参数Set
// UserMapper
public List<User> findByForeach(@Param("ids") Set<Integer> ids);
// UserMapper.xml
<select id="findByForeach" resultType="User">
	select * from t_user
	where id in
	<foreach item="id" index="index" collection="ids" open="(" separator="," close=")">
		#{id}
	</foreach>
</select>

// 示例3.单参数Set
// UserMapper
public List<User> findByForeach(int[] ids);
// UserMapper.xml
<select id="findByForeach" resultType="User">
	select * from t_user
	where id in
	<foreach item="id" index="index" collection="array" open="(" separator="," close=")">
		#{id}
	</foreach>
</select>

示例4.封装成Map
... map.put("ids", Arrays.asList(1, 2)); ...
// UserMapper
public List<User> findByForeach(Map<String, Object> params);
// UserMapper.xml
<select id="findByForeach" resultType="User">
	select * from t_user
	where id in
	<foreach item="id" index="index" collection="ids" open="(" separator="," close=")">
		#{id}
	</foreach>
</select>


示例5.封装成类，支持多级'.'
... user.setIds(Arrays.asList(1, 2)); ...
// UserMapper
public List<User> findByForeach(User user);
// UserMapper.xml
<select id="findByForeach" resultType="User">
	select * from t_user
	where id in
	<foreach item="id" index="index" collection="ids" open="(" separator="," close=")">
		#{id}
	</foreach>
</select>

7.bind

bind元素可以从OGNL表达式中创建一个变量并将其绑定到上下文。假如User实体中username属性为null，则会提示设置参数错误。常用于模糊查询

<select id="findByBind" parameterType="User" resultType="User">
	<bind name="pattern" value="'%' + _parameter.getUsername() + '%'"/>
	select * from t_user
	where user_name like #{pattern}
</select>

测试代码：

public List<User> findByBind(User user);