66 动态SQL

66 动态SQL

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

动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。

1. 掌握if元素

此元素用途非常广泛，是我们应有最多的一个元素标签

需求：根据用户名和角色查询用户列表(都是非必填项)

<!-- 
  if标签：
   test属性：条件表达式计算的
   条件表达式的内容是由OGNL表达构成的，而这个条件变量它是直接由传递的参数的名字决定的
  -->
<select id="getListByConditions" parameterType="map" resultType="User">
    select
    	u.*,r.roleName as userRoleName
    from 
    	smbms_user u,smbms_role r
    where
    	u.userRole = r.id
        <if test="userName != null and userName != ''">
            and userName like CONCAT('%',#{userName},'%')
        </if>
        <if test="userRole != null and userRole > 0">
            and userRole = #{userRole}
        </if>
</select>

2. 掌握where元素

where元素虽然挺不错，但是实际使用比较少，因为之后我们有替代方案。

<!-- 
  where标签：能够给指定的语句前添加一个where关键字，并且可以自动去除【if标签内语句前缀中】多余的and 或 or
           只能去除，不能添加！(谁知道你要添加and还是or)
  -->
<select id="getListByConditions" parameterType="map" resultType="User">
    select
    	u.*,r.roleName as userRoleName
    from 
    	smbms_user u,smbms_role r
    <where>
        <if test="userName != null and userName != ''">
            and userName like CONCAT('%',#{userName},'%')
        </if>
        <if test="userRole != null and userRole > 0">
            and userRole = #{userRole}
        </if>
        and u.userRole = r.id
    </where>
</select>

3. 掌握set元素

set元素虽然挺不错的，但是实际使用比较少，因为之后我们有替代方案。

<!-- 
  set元素：可以给指定的语句添加set关键字，同样还可以去除【if语句内后缀中】多余的逗号
  -->
<update id="updateUser" parameterType="User">
    update
    smbms_user
    <set>
        <if test="userCode != null">
            userCode = #{userCode},
        </if>
        <if test="userName != null">
            userName = #{userName},
        </if>
        <if test="userPassword != null">
            userPassword = #{userPassword}
        </if>
    </set>
    where
    id = #{id}
</update>

4. 掌握trim元素

trim:去除空格，比较常用

<!--   trim标签：可以作为where和set标签的替代方案  
	prefix：给指定的语句添加前缀   
	prefixOverrides：去除多余的指定前缀  
	suffix：给指定的语句添加后缀   
	suffixOverrides：去除多余的指定后缀 
-->
<update id="updateUser" parameterType="User">   
	update        
		smbms_user  
       <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">       
       	<if test="userCode != null">            userCode = #{userCode},       
        </if>       
        <if test="userName != null">            userName = #{userName},      
        </if>        
        <if test="userPassword != null">            
        	userPassword = #{userPassword}      
           </if>   
          </trim>
        </update>

<select id="getListByConditions" parameterType="map" resultType="User">
		select
			u.*,r.roleName as userRoleName
		from 
			smbms_user u,smbms_role r
		<trim prefixOverrides="and | or" prefix="where">
			<if test="userName != null and userName != ''">
				and userName like CONCAT('%',#{userName},'%')
			</if>
			<if test="userRole != null and userRole > 0">
				and userRole = #{userRole}
			</if>
			and u.userRole = r.id
		</trim>
	</select>

5.【了解】choose元素

多重if一样

<!-- 
		choose元素的内容
			自上而下执行判断，只要有一个满足，其他的就不再执行
	 -->
<select id="getListByMap" parameterType="map" resultType="User">
    select
    	u.*,r.roleName as userRoleName
    from 
    	smbms_user u,smbms_role r
    where
    	u.userRole = r.id
    <choose>
        <when test="userName != null and userName != ''">
            and userName like CONCAT('%',#{userName},'%')
        </when>
        <when test="userRole != null and userRole > 0">
            and userRole = #{userRole}
        </when>
        <otherwise>
            and YEAR(u.creationDate) = YEAR(now())
        </otherwise>
    </choose>
</select>

6. 掌握foreach元素

在实现一些复杂功能时，foreach应用比较多。

<!--
	 collection：要遍历的集合  它不是写要遍历的集合名 而是写集合的别名形式
			数组：array
			list集合：list
			Map集合：map集合中存储的集合对应的键名
	item：遍历出来的每一项内容
	open：在整块遍历出来的内容前加上指定前缀
	close：在整块遍历出来的内容后加上指定后缀
	index：索引
	separator：分隔符  遍历出来的每一项内容之间添加的分隔符
-->
<foreach collection="" item="" close="" open="" index=""   separator="">

</foreach>

需求：要求查询角色1和角色为2的用户列表

 select * from smbms_user where userRole = 1 or userRole = 2
-- (推荐)
select * from smbms_user where userRole in (1,2);

7. Mybatis的应用（Super Market Bill Management System -> smbms)