动态SQL

动态SQL

在项目开发中，动态SQL可以解决很多不确定因素导致的SQL语句不同的问题。动态SQL可以简单高效的进行编码。在接下来的案例中进行认识和学习动态SQL。
动态SQL只是在原有的SQL语句中进行细微修改。案例贴合实际，编码简单易懂

一、引入动态SQL

如下图片中的功能：

我们经常会遇到如上图所示的多条件查询，将多条件查询的结果展示在下方的数据列表中。而我们做这个功能需要分析最终的SQL语句应该是什么样，思考两个问题

• 条件表达式
• 如何连接

条件字段 企业名称 和 品牌名称* 需要进行模糊查询，所以条件应该是：

简单的分析后，我们来看功能实现的步骤：
编写接口方法
参数：所有查询条件
结果：List
在映射配置文件中编写SQL语句

二、 接口方法的参数定义

在 BrandMapper 接口中定义多条件查询的方法。而该功能有三个参数，我们就需要考虑定义接口时，参数应该如何定义。Mybatis针对多参数有多种实现

1.使用 @Param(“参数名称”) 标记每一个参数，在映射配置文件中就需要使用 #{参数名称} 进行占位

List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName,@Param("brandName") String brandName);

2.将多个参数封装成一个 实体对象 ，将该实体对象作为接口的方法参数。该方式要求在映射配置文件的SQL中使用 #{内容} 时，里面的内容必须和实体类属性名保持一致。

List<Brand> selectByCondition(Brand brand);

3.将多个参数封装到map集合中，将map集合作为接口的方法参数。该方式要求在映射配置文件的SQL中使用#{内容}时，里面的内容必须和map集合中键的名称一致。

List<Brand> selectByCondition(Map map);

编写SQL语句，在 BrandMapper.xml 映射配置文件中编写 SQL语句 ，使用 resultMap 而不是使用 resultType

<select id="selectByCondition" resultMap="brandResultMap">
	 select * from tb_brand
	 where status = #{status}
	 and company_name like #{companyName}
	 and brand_name like #{brandName}
</select>

问题分析：
上述功能实现存在很大的问题。用户在输入条件时，肯定不会所有的条件都填写，这个时候我们的SQL语句就不能那样写的。如下：

• 用户只输入 当前状态 时，SQL语句就是

select * from tb_brand where status = #{status}

• 而用户如果只输入企业名称时，SQL语句就是

select * from tb_brand where company_name like #{companName}

• 而用户如果输入了 当前状态 和 企业名称 时，SQL语句又不一样

select * from tb_brand where status = #{status} and company_name like #{companName}

三、动态SQL标签元素

针对上述的需要，Mybatis对动态SQL有很强大的支撑——动态SQL的标签元素：

if
choose (when, otherwise)
where
set
foreach
trim

在接下来的四个案例中进行学习动态SQl标签的用法

3.1 不确定多条件查询—— if、where 标签

对于以上案例，进行的解决方案: 在 BrandMapper.xml 映射配置文件中编写条件查询数据的SQl语句:

<select id="selectByCondition" resultMap="brandResultMap">
	 select *
	 from tb_brand
	  <where>
	    <if test="status != null">
	     and status = #{status}
	    </if>
	    <if test="companyName != null and companyName != '' ">
	     and company_name like "%"#{companyName}"%"
	    </if>
	    <if test="brandName != null and brandName != '' ">
	     and brand_name like "%"#{brandName}"%"
	    </if>
	  </where>
</select>

• if 标签：条件判断
  • test 属性：逻辑表达式
• where 标签
  • 作用：
    1. 替换where关键字
    2. 会动态的去掉第一个条件前的 and
    3. 如果所有的参数没有值则不加where关键字
    4. 注意：需要给每个条件前都加上 and 关键字。

3.2 不确定单个条件查询——choose (when, otherwise) 标签

如上图所示，在查询时只能选择 品牌名称 、 当前状态 、 企业名称 这三个条件中的一个，但是用户到底选择哪儿一个，我们并不能确定。这种就属于单个条件的动态SQL语句。
这种需求需要使用到 choose（when，otherwise）标签实现， 而 choose 标签类似于Java 中的switch语句。

针对于上述案例的解决方法：在 BrandMapper 接口中定义单条件查询的方法：

/**
 * 单条件动态查询
 * @param brand
 * @return
 */
List<Brand> selectByConditionSingle(Brand brand);

在 BrandMapper.xml 映射配置文件中编写 SQL，使用 resultMap 而不是使用 resultType

<select id="selectByConditionSingle" resultMap="brandResultMap">
 select *
 from tb_brand
  <where>
    <choose><!--相当于switch-->
      <when test="status != null"><!--相当于case-->
       status = #{status}
      </when>
      <when test="companyName != null and companyName != '' "><!--相当于case-->
       company_name like #{companyName}
      </when>
      <when test="brandName != null and brandName != ''"><!--相当于case-->
       brand_name like #{brandName}
      </when>
    </choose>
  </where>
</select>

3.3 修改数据——set、if 标签

在 BrandMapper.xml 映射配置文件中编写修改数据的SQl语句:

<update id="update">
	 update tb_brand
	  <set>
	    <if test="brandName != null and brandName != ''">
	     	brand_name = #{brandName},
	    </if>
	    <if test="companyName != null and companyName != ''">
	     	company_name = #{companyName},
	    </if>
	    <if test="ordered != null">
	     	ordered = #{ordered},
	    </if>
	    <if test="description != null and description != ''">
	     	description = #{description},
	    </if>
	    <if test="status != null">
	     	status = #{status}
	    </if>
	  </set>
	 where id = #{id};
</update>

set 标签可以用于动态包含需要更新的列，忽略其它不更新的列。

3.4 批量删除——foreach 标签

在 BrandMapper 接口中定义删除多行数据的方法：

/**
 * 批量删除
 */
void deleteByIds(int[] ids);

在 BrandMapper.xml 映射配置文件中编写批量删除的SQl语句:

<delete id="deleteByIds">
	 delete from tb_brand where id
	 in
	  <foreach collection="array" item="id" separator="," open="(" close=")">
	   #{id}
	  </foreach>
	 ;
</delete>

假如数组中的id数据是{1,2,3}，那么拼接后的sql语句就是：

delete from tb_brand where id in (1,2,3);

• foreach 标签：用来迭代任何可迭代的对象（如数组，集合）。

属性	含义
collection	mybatis会将数组参数，封装为一个Map集合。默认：array = 数组；使用@Param注解改变map集合的默认key的名称
item	本次迭代获取到的元素
separator	集合项迭代之间的分隔符。 foreach 标签不会错误地添加多余的分隔符。也就是最后一次迭代不会加分隔符。
open	该属性值是在拼接SQL语句之前拼接的语句，只会拼接一次
close	该属性值是在拼接SQL语句拼接后拼接的语句，只会拼接一次

小贴士： 上述动态SQL中的标签元素最为常用，但并不只有这些。理解这些标签的含义，在实际的功能中：具体情况具体分析，灵活编码。有些标签是可以嵌套使用的。