mybatis-动态sql-if，choose，when，otherwis，trim，where，set,foreach,test,bind

       如果使用JDBC或者其他框架，很多时候你得根据需要去拼装sql，这是一个麻烦的事情。而mybatis提供对sql语句动态的组装能力，而且它只有几个基本的元素，十分简单明了，大量的判断都可以在mybatis的映射xml文件里面配置，以达到许多我们需要大量代码才能实现的功能，大大减少了我们的编程工作量，这体现了mybatis的灵活性，高度可配置和可维护性。mybatis也可以在注解中配置sql，但是注解中配置功能受限，对于复杂sql而言可读性很差。

    

     一：概述

      mybatis的动态sql包括一下几种元素，如下所示：

  if：判断语句，单条件分支判断

 choose（when，otherwise）：相当于java中的case when语句，多条件分支判断

 trim(where,set) ：辅助元素，用于处理一些sql拼装问题

 foreach：循环语句，在in语句等列举条件常用

   二：if

   if是最常用的判断语句，相当于java中的if语句。常常与test属性联合使用。

   在大部分的情况下，if元素使用还是比较简单的。比如现在我们要根据角色名称roleName去查找角色，但是角色名称是一个可填可不填的条件，不填就不用它作为查询条件。这是查询的常见场景之一。如下所示：

<select id="findRoles" parameterType="string" resultMap="roleMap">
	   select id,role_name,note from role where 1= 1
	   <if test="roleName != null and roleName != '' ">
	   and role_name like concat('%',#{roleName},'%')
	   </if>
	</select>

      上面是表示我们将参数roleName传递入到映射器中，采用构造对roleName的模糊查询。如果这个参数为空，就不要去构造这个条件。

     三：choose，when，otherwise元素

     在mybatis中提供了choose来进行多种条件的选择，不像if只能进行单条件的选择判断。下面有这样一个场景：

    1，当角色编号不为空，则只用角色编号作为查询条件

    2，当角色编号为空，而角色名称不为空，则角色名称作为条件进行模块查询

    3，当角色编号和角色名称都为空，则要求角色备注不为空

   下面看看choose，when，otherwise元素去实现，代码如下：

    

<select id="findRoles" parameterType="role" resultMap="roleMap">
	select id,role_name,note from role where 1= 1
	<choose>
		<when test="id != null and id != '' ">
			and id = #{id}
		</when>
		<when test="roleName != null and roleName != '' ">
			and role_name = #{roleName}
		</when>
		<otherwise>
			and note is not null
		</otherwise>
	</choose>
</select>

       这样mybatis就会根据参数的设置来进行判断动态的组装sql，以满足不同业务的要求。

     四：trim，where，set元素

     前面用了一个小技巧在where后面加入了1 = 1这个条件，如果不加的话，如果判断条件满足会出现where and。。。语句错误，sql会异常。而加入1 = 1条件显得有点奇怪，没关系我们可以用where元素来达到预期的效果，如下我们去掉1=1的条件，只用where元素就可以了：

<select id="findRoles" parameterType="string" resultMap="roleMap">
	select id,role_name,note from role
	<where>
		<if test="roleName != null and roleName != '' ">
			and role_name like concat('%',#{roleName},'%')
		</if>
	</where>
    </select>

      这样当where元素里面的条件成立的时候才会加入这个sql关键字到组装的sql里面，否则不添加，有多余的and，mybatis会帮助我们去掉。

      有时候我们需要去掉一些特殊的sql语法，比如and，or，而使用trim元素就可以达到我们的效果。代码入戏如下：

   

<select id="findRoles" parameterType="string" resultMap="roleMap">
	select id,role_name,note from role
	<trim prefix="where" prefixOverrides="and">
		<if test="roleName != null and roleName != '' ">
			and role_name like concat('%',#{roleName},'%')
		</if>
	</trim>
	
    </select>

     trim元素就意味着我们需要去掉哪些特殊的字符串，prefix代表的是语句的前缀，而prefixOverrides代表的是你需要去掉那种多余的字符串，上面的写法基本于where元素等效。

  

       set元素用在对表的更新中，通过判断参数来决定更新哪些字段，动态的组装sql。如下代码:

<update id="updateRole" parameterType="role">
	update role
	<set>
		<if test="roleName != null and roleName != ''">
			role_name = #{roleName},
		</if>
		<if test="note != null and note != ''">
			note = #{note},
		</if>
	</set>
   </update>

      set元素会把多余的逗号去掉，上面的代码也可以使用trim元素替代：

    <trim prefix="set" suffixOverrides=",">....</set>

     

     五：foreach元素

    一看foreach元素应该可以联系到java中的循环了，没错foreach就是mybatis元素的循环元素，它的作用是遍历集合。它能够很好的支持数组和List，Set接口的集合，对此提供遍历的功能

     下面有这么一个场景，比如用户表中，性别有男，女，未知，我们把性别作为一个字典，定义如下：

      1-男，2-女，0-未知

      用户可能查询非未知性别的用户，也可能查找女性或者未知性别的用户，或者男性和未知性别的用户，具体的参数需要使用foreach元素去确定，代码如下：

     

<select id="findUserBySex" parameterType="list" resultType="user">
	select * from t_user where sex in
	<foreach collection="list" index="index" item="sex" open="("
		separator="," close=")">
		#{sex}
	</foreach>
</select>

    collection配置的是传进来的集合，可以是一个数组，list或者Set集合

    item配置的是循环中当前的元素

    index配置的是当前元素在集合的位置下标

    open和close配置的是以什么符号将这些集合元素包装起来

    separator是各个元素的间隔符

    在sql中对于in语句我们常常使用，对于大量数据的in语句我们需要特别注意，因为它会消耗大量的性能，还有一些数据库的sql对sql长度也有限制，需要注意拼装sql的长度。

      六：test属性

     test属性用于条件判断语句中，在mybatis中广泛使用，前面用test判断了非空，判断字符串可以这么用

    <if test="type = 'Y' "></if>

    七：bind元素

     bind元素的作用是通过OGNL表达式去自定义一个上下文变量，这样更方便我们使用。在我们进行模糊查询的时候如果是mysql数据库，我们常常用到的是一个concat用"%"和参数相连接。然而在oracle数据库则用连接符号“||”，这样sql就需要提供两种形式去实现。但是有了bing元素，我们就完全不必使用数据库的语言，只要使用mybatis的语言即可与所需参数相连。

     比如我们按角色名称进行模糊查询，我们可以把映射文件写出这样：

<select id="findRoles" parameterType="role" resultMap="roleMap">
	<bind name="pattern"  value="'%'+_parameter+'%'"/>
	select id,role_name,note from role where 
	role_name like #{pattern}	
  </select>

     这里_parameter代表的是传进来的一个字符串参数，它和通配符连接以后赋值给pattern，这样我们就可以在select语句中使用这个变量进行模糊查询。不管是mysql或者oracle我们都可以使用这样的语句提供了可移植性。

    如果有多个参数可以这么用bind：

   

<select id="findRoles" parameterType="role" resultMap="roleMap">
	<bind name="pattern_roleName"  value="'%'+roleName+'%'"/>
	<bind name="pattern_note"  value="'%'+note+'%'"/>
	select id,role_name,note from role where 
	role_name like #{pattern_roleName}	
	and note like #{note}
  </select>

    

       这样传参数的时候传入roleName和role就行了。