Mybatis 动态SQL常用标签(模糊查询)

墨蓝 2022-04-16 07:05 351阅读 0赞

## 动态 SQL      ##

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。

动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。

*  if
 *  choose (when, otherwise)
 *  trim (where, set)
 *  foreach

### 一、if 标签        ###

动态 SQL 通常要做的事情是根据条件包含 where 子句的一部分

拼接条件的时候，加了一个恒等条件（1=1），避免多出and

public List<User> getUserBylike(@Param("username") String username,
    					@Param("state") Integer state);
    
            <select id="getUserBylike" resultType="cn.jq.mybatis.model.User">
    		select * from t_user where 1=1
    		<if test="username != null and username != ''">
    			and username like #{username}
    		</if>
    		<if test="state != null and state >= 0">
    			and state like #{state}
    		</if>
    	</select>

List<User> userList = userMapper.getUserBylike("%a%",1);
    
    select * from t_user where 1=1 and username like ? and state like ?

**强调一下：**

1）在接口中最好加@Param注解，以防止以下异常ReflectionException: There is no getter for property named...

2）if 标签里的 **test属性**里是用的OGNL表达式，这是apache下的一个标签，用法类似 jstl，但有些小差别，具体的内容可以在ognl官网上查询，有些特殊符号在xml文件里不能直接使用，可以在w3cschool里查[http://www.w3school.com.cn/tags/html\_ref\_entities.html][http_www.w3school.com.cn_tags_html_ref_entities.html]，比如：双引号（< 使用 <）

### 二、where标签 ###

上面恒等条件（1=1），避免多出and ，但是，Mybatis提供了一种解决办法就是 where标签，

***     where* 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，*where* 元素也会将它们去除。**

<select id="getUserBylike" resultType="cn.jq.mybatis.model.User">
    		select * from t_user 
    		<where>
    			<if test="username != null and username != ''">
    				and username like concat('%',concat(#{username},'%'))   
    			</if>
    			<if test="state != null and state >= 0">
    				and state like #{state}
    			</if>
    		</where>
    	</select>

List<User> userList = userMapper.getUserBylike("a",1);
    
    select * from t_user WHERE username like concat('%',concat(?,'%')) and state like ?

注意：where标签只能去掉语句的开头为“AND”或“OR” ，并不能去掉后面的，如果我们非要写在后面，这时可以通过自定义 trim 元素来定制 *where* 元素的功能。

### 三、 trim 标签（自定义 trim 元素） ###

**四个属性：**

prefix：表示在trim包裹的sql语句拼接的前缀
    suffix：表示在trim包裹的sql语句拼接的后缀
    prefixOverrides：表示去掉（覆盖）trim包裹的SQL的指定首部内容
    suffixOverrides：表示去掉（覆盖）trim包裹的SQL的指定尾部内容

如果是先要去掉and或者or，则必须这样写prefixOverrides=“AND |OR”，注意中间的空格。

<select id="getUserBylike" resultType="cn.jq.mybatis.model.User">
    		select * from t_user 
    		<trim prefix="where" suffixOverrides="AND |OR">
    			<if test="username != null and username != ''">
    				username like #{username} and
    			</if>
    			<if test="state != null and state >= 0">
    				state like #{state} and
    			</if>
    		</trim>
    	</select>

### 四、set标签 ###

用于动态更新语句的解决方案叫做 *set*。***set* 元素可以用于动态包含需要更新的列，而舍去其它的**。

public boolean updateUser(User user);

<update id="updateUser" parameterType="cn.jq.mybatis.model.User">
    		update t_user 
    		<set>
    			<if test="username != null and username != ''">
    				username=#{username},
    			</if>
    			<if test="pazzword != null and pazzword != ''">
    				pazzword=#{pazzword},
    			</if>
    			<if test="state != null and state != ''">
    				state=#{state},
    			</if>
    			<if test="regDate != null">
    				reg_date=#{regDate},
    			</if>
    		</set>
    		where id=#{id}
    	</update>

这里，*set* 元素会动态前置 SET 关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。（译者注：因为用的是“if”元素，若最后一个“if”没有匹配上而前面的匹配上，SQL 语句的最后就会有一个逗号遗留）

若你对 *set* 元素等价的自定义 trim 元素的代码感兴趣，那这就是它的真面目：

<update id="updateUser" parameterType="cn.jq.mybatis.model.User">
    		update t_user 
    		<trim prefix="SET" suffixOverrides=",">
    				<if test="username != null and username != ''">
    					username=#{username},
    				</if>
    				<if test="pazzword != null and pazzword != ''">
    					pazzword=#{pazzword},
    				</if>
    				<if test="state != null and state != ''">
    					state=#{state},
    				</if>
    				<if test="regDate != null">
    					reg_date=#{regDate},
    				</if>
    		</trim>
    		where id=#{id}
    	</update>

### 五、choose (when, otherwise)标签 ###

有时我们不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，MyBatis 提供了 choose元素，它有点像 Java 中的 switch...case，从上到下匹配，找到匹配的条件，就结束匹配其他的！

<select id="getUserBylike" resultType="cn.jq.mybatis.model.User">
    		select * from t_user where
    		<choose>
    			<when test="username != null and username != ''">
    				username like #{username}
    			</when>
    			<when test="state != null and state >= 0">
    				state like #{state}
    			</when>
    			<otherwise>
    				1=1
    			</otherwise>
    		</choose>
    	</select>

### 六、foreach标签 ###

动态 SQL的另外一个常用的操作需求是对一个集合/数组进行遍历，通常是在**构建 IN 条件语句**的时候

collection="ids" ： 接口上传过来的数值或list集合或者map集合都可以
    item="id" ：设定遍历集合或数组里的每一个值的迭代变量
    separator="," ： 因为要构造出 (1,2,3)这种样子的字符串，设定中间的分隔符
    open="(" ： 因为要构造出 (1,2,3)这种样子的字符串，设定前缀的符号(
    close=")"： 因为要构造出 (1,2,3)这种样子的字符串，设计结尾的后缀)
    index: 了解，数组或list集合的时候，设置索引变量，如果是Map集合就是map的key的迭代变量

//通过一组id值查询对应的user
    	public List<User> selectUserByIds(@Param("ids") int[] ids);
    
    	<select id="selectUserByIds" resultType="cn.jq.mybatis.model.User">
    		select * from t_user where id in 
    		<foreach collection="ids" item="id" separator="," open="(" close=")">
    			#{id}
    		</foreach>
    	</select>

int[] ids = new int[] {1,2};
    			List<User> userList = userMapper.selectUserByIds(ids);
    			System.out.println(Arrays.asList(userList));
    ----
    select * from t_user where id in ( ? , ? )

*     foreach* 元素的功能非常强大， 对于不同数据库之间的**批量操作**也是经常使用。

### 七、bind标签（了解) ###

bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文

上面例子中的模糊查询 传参，使用bind元素 拼接 % 号， 注意不能传null值

<select id="getUserBylike" resultType="cn.jq.mybatis.model.User">
    		<bind name="new_username" value="'%'+username+'%'"/>
    		select * from t_user where
    		<choose>
    			<when test="username != null and username != ''">
    				username like #{new_username}
    			</when>
    			<when test="state != null and state >= 0">
    				state like #{state}
    			</when>
    			<otherwise>
    				1=1
    			</otherwise>
    		</choose>
    	</select>

![20181120200921928.png][]

ends ~

[http_www.w3school.com.cn_tags_html_ref_entities.html]: http://www.w3school.com.cn/tags/html_ref_entities.html
[20181120200921928.png]: /images/20220416/0f6f7994d04c48eb8ec3bd63828ccf1f.png