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是元素所在集合的下标，collection是集合的类型，open、close是在首、尾拼接的字符，separator是每个元素间的分隔符。

<select id="findByForeach" parameterType="list" resultType="User">select * from t_userwhere id in<foreach item="id" index="index" collection="list" open="(" separator="," close=")">#{id}</foreach></select>

测试代码：

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

7.bind

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

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

测试代码：

public List<User> findByBind(User user);