动态SQL-CSDN博客

本文链接：https://blog.csdn.net/chenHaiJaheike/article/details/90754544

前言

动态SQL是MyBatis强大的特性，在使用JDBC操作数据的时候，如果查询的条件特别多，将SQL语句连成字符串是一件很痛苦的事情，通常我们用if-else进行拼接。MyBatis使用动态SQL语言来改善这种情形。

用于实现动态SQL元素如下

基于OGNL表达式
完成多条件查询等逻辑实现
用于实现动态SQL的元素主要有

if：实现简单是条件选择
trim：灵活的去除多余的关键字
where：简化SQL语句中的where条件判断
choose（when、otherwise）：相当于Java中的switch语句，通常when与otherwise搭配
foreach：迭代一个结合，通常用于 in条件
set：解决动态SQL语句。

使用if-where实现多条件查询

1. if

if就是简单的条件判断，利用if语句我们可以实现某些简单的条件选择。
当我们多条件查询的时候，多有的条件都不能为空，但是我们要查询的因素不固定，若果查询条件为空的话，查不出我们想要的结果。

<select id="getBiilList" resultType="Bill">
		SELECT b.*, p.proName as providerName FROM smbms_bill  b,smbms_provider p 0 
			WHERE b.id=p.id
			<if test="productName!=null and productName!=''">
				 AND b.productName LIKE  CONCAT ('%', #{productName}, '%') 	
			</if>
			<if test="providerId!=null">
				 AND b.providerId=#{providerId}	
			</if>
			<if test="isPayment!=null">
				 AND b.isPayment=#{isPayment}
			</if>
	</select>

2. where

where元素的作用是会在写入where元素的地方输出一个where，另外一个

好处是你不需要考虑where元素里面的条件输出是什么样子的，MyBatis会智

能的处理，如果所有的条件都不满足那么MyBatis就会查出所有的记录，如果

输出后是and 开头的，MyBatis会把第一个and忽略，当然如果是or开头的，

MyBatis也会把它忽略；此外，在where元素中你不需要考虑空格的问题，

MyBatis会智能的帮你加上。像上述例子中，如果title=null，而content != null，

那么输出的整个语句会是select * from t_blog where content = #{content}，而

不是select * from t_blog where and content = #{content}，因为MyBatis会智

能的把首个and 或 or 给忽略。

<select id="getProvider" resultType="Provider">
		SELECT * FROM smbms_provider 
		<where>
			<if test="proCode!=null and proCode!=''">
				and proCode like CONCAT ('%',#{proCode},'%') 
			</if>
			<if test="proName!=null and proName!=''">
				and proName like CONCAT ('%',#{proName},'%') 
			</if>
		</where>
	</select>

使用if-trim实现多条件查询

1. trim

属性

prefix：前缀，作用通过自动识别是否有返回值后，在trim包含的内容上前缀，如此处的where。
suffix：后缀，作用是在trim包含的内容上加上后缀。
prefixOverrides：对于trim包含内容的首部进行制定内容（如此处的and || or）的忽略。
suffixOverrides：对于trim包含内容的首尾部进行指定内容的忽略。

作用：我们可以用if-trim代替if-where

使用动态SQL实现更新操作

使用if+set改造跟新操作

问题：我们为什么要使用if+set完成更新操作哪？

当修改数据库的时候，执行操作的时候，每个字段都进行了赋值更新。在实际中我们不会每次把数据全部跟新一遍，只会修改用户要修改的字段，对于用户没修改的数据，数据库不会进行相应的修改操作。

set元素主要用于跟新操作，它的主要功能和where差不多，主要包含的语句首输出一个set，若包含语句中是以，(逗号) 结尾的，会自动把逗号忽略掉，在配合if元素就可以动态的跟新需要修改的字段，而不需要修改的字段，则可以不被更新。

<update id="updProvider" parameterType="Provider">
		update smbms_provider 
		<set>
			<if test="proCode!=null">proCode=#{proCode},</if>
			<if test="proName!=null">proName=#{proName},</if>
			<if test="proDesc!=null">proDesc=#{proDesc},</if>
			<if test="proContact!=null">proContact=#{proContact},</if>
			<if test="proPhone!=null">proPhone=#{proPhone},</if>
			<if test="proAddress!=null">proAddress=#{proAddress},</if>
			<if test="proFax!=null">proFax=#{proFax},</if>
			<if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
			<if test="modifyDate!=null">modifyDate=#{modifyDate}</if>
		
		</set>
		 where id = #{id}
	</update>

使用if+trim改造修改操作

我们可以使用trim元素代替set元素，并且可以实现相同的操作。

<update id="updProvider1" parameterType="Provider">
		update smbms_provider 
		<trim prefix="set" suffix="where id = #{id}" suffixOverrides=",">
			<if test="proCode!=null">proCode=#{proCode},</if>
			<if test="proName!=null">proName=#{proName},</if>
			<if test="proDesc!=null">proDesc=#{proDesc},</if>
			<if test="proContact!=null">proContact=#{proContact},</if>
			<if test="proPhone!=null">proPhone=#{proPhone},</if>
			<if test="proAddress!=null">proAddress=#{proAddress},</if>
			<if test="proFax!=null">proFax=#{proFax},</if>
			<if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
			<if test="modifyDate!=null">modifyDate=#{modifyDate}</if>
		
		</trim>
		 
	</update>

使用foreach完成复杂查询

foreach基本属性

item：表示集合中每一个元素进行迭代时的别名。
index：指定一个名称，用于表示在迭代过程中，每次迭代的位置。
open：表示该语句以什么开始（既然是 in 条件语句所以必然以(开始）
separator：每次迭代之间以什么符号作为分割符（既然是 in 条件语句，所以必然是以，作为分隔符。）
close：表示该语句以什么结尾。（既然是 in 必然是以）结尾）。
collection：最关键最容易出错的属性，需要格外注意，主要有三种情况。
（1）若入参为单参数且参数类型为List的时候，collection属性为list
（2）若入参为单参数且参数类型为数组的时候，collection属性为array
（3）若传入的参数为多参数，就需要把它封装成Map进行处理。

MyBatis入参为数组类型的foreach迭代

<select id="getBillByProviderIdArray" resultMap="billMapByProIds" >
		SELECT * FROM smbms_bill WHERE providerId IN
		<foreach collection="array" item="bill" open="(" separator="," close=")">
			#{bill}
		</foreach>
	
	</select>

MyBatis入参为List类型的foreach迭代

<select id="getBillByProviderIdList" resultMap="billMapByProIds" >
		SELECT * FROM smbms_bill WHERE providerId IN
		<foreach collection="list" item="bill" open="(" separator="," close=")">
			#{bill}
		</foreach>
	
	</select>

MyBatis入参为Map类型的foreach迭代

<select id="getBillByProviderIdMap" resultMap="billMapByProIds" >
		SELECT * FROM smbms_bill WHERE billCode like CONCAT ('%',#{billCode},'%') AND providerId IN
		<foreach collection="providerIds" item="bill" open="(" separator="," close=")">
			#{bill}
		</foreach>
	
	</select>

choose（when，otherwise）

相当于Java中的Switch

<select id="getProviderList" resultType="Provider">
		select * from smbms_provider where 1=1
			<choose>
				<when test="proCode!=null and proCode!=''">
					and proCode like CONCAT ('%',#{proCode},'%') 
				</when>
				<when test="proName!=null and proName!=''">
					and proName like CONCAT ('%',#{proName},'%') 
				</when>
				<when test="proContact!=null and proContact!=''">
					and proContact like CONCAT ('%',#{proContact},'%') 
				</when>
				<otherwise>
					and creationDate like CONCAT ('%',#{creationDate},'%') 
				</otherwise>
			</choose>
	</select>