Mybatis----动态Sql

1.概述
One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities. If you have any experience with JDBC or any similar framework, you understand how painful it is to conditionally concatenate strings of SQL together, making sure not to forget spaces or to omit a comma at the end of a list of columns. Dynamic SQL can be downright painful to deal with.
MyBatis的一个强大的特性之一通常是它的动态SQL能力。如果你有使用JDBC或其他相似框架的经验，你就明白条件地串联SQL字符串在一起是多么的痛苦，确保不能忘了空格或在列表的最后省略逗号。动态SQL可以彻底处理这种痛苦。

2.if标签
①基本使用方法
[1]接口方法：List<Customer> getListByMinMaxAge(@Param("minAge") Integer minAge, @Param("maxAge") Integer maxAge);
[2]配置方式

<select id="getListByMinMaxAge" resultType="com.sirius.entities.Customer">
				select cust_id custId,cust_name custName,cust_age custAge
				from tbl_cust where 1=1
				<if test="minAge!=null">
					<![CDATA[and cust_age>#{minAge}]]>
				</if>
				<if test="maxAge!=null">
					<![CDATA[and cust_age<#{maxAge}]]>
				</if>
			</select> 


②引用单个参数
	[1]@Param注解
接口方法：List<Customer> getListBySingleAge(@Param("age") Integer age);
		配置中引用传入的参数
<select id="getListBySingleAge" parameterType="Integer" resultType="com.atguigu.entities.Customer">
					select cust_id custId,cust_name custName,cust_age custAge
					from tbl_cust where 1=1
					<if test="age!=null">
						<![CDATA[and cust_age>#{age}]]>
					</if>
				</select>
	[2]_parameter
		接口方法：`List<Customer> getListBySingleAge(Integer age);`
		配置中引用传入的参数
			

<select id="getListBySingleAge" parameterType="Integer" resultType="com.atguigu.entities.Customer">
					select cust_id custId,cust_name custName,cust_age custAge
					from tbl_cust where 1=1
					<if test="_parameter!=null">
						<![CDATA[and cust_age>#{age}]]>
					</if>
</select>

4.where标签
①说明
where子句以and 或者or开头，它会自动删除第一个and或者or
一般建议Where和if结合起来用
②接口方法：List<Customer> getListByMinMaxAge(@Param("minAge") Integer minAge, @Param("maxAge") Integer maxAge);
③配置方式

<select id="getListByMinMaxAge" resultType="com.atguigu.entities.Customer">
			select cust_id custId,cust_name custName,cust_age custAge
			from tbl_cust
			<where>
				<if test="minAge!=null">
					<![CDATA[and cust_age>#{minAge}]]>
				</if>
				<if test="maxAge!=null">
					<![CDATA[and cust_age<#{maxAge}]]>
				</if>
			</where>
		</select>

5.choose/when/otherwise
①结构

<choose>
			<when test="条件1">
				子句1
			</when>
			<when test="条件2">
				子句2
			</when>
			<when test="条件3">
				子句3
			</when>
			<when test="条件4">
				子句4
			</when>
			……
			<otherwise>
				最后一条子句
			</otherwise>
		</choose>
②执行方式：按照从上到下的顺序依次检查每一个when中的条件，一旦发现满足条件的when则采纳当前子句，并停止执行。如果所有的when都不满足条件，则执行otherwise
③接口方法：List<Customer> getListByMinMaxAge(@Param("minAge") Integer minAge, @Param("maxAge") Integer maxAge);
④配置方式
	<select id="getListByMinMaxAge" resultType="com.atguigu.entities.Customer">
		select cust_id custId,cust_name custName,cust_age custAge
		from tbl_cust
		<where>
			<choose>
				<when test="minAge!=null">
					<![CDATA[and cust_age>#{minAge}]]>
				</when>
				<when test="maxAge!=null">
					<![CDATA[and cust_age<#{maxAge}]]>
				</when>
				<otherwise>
					and cust_name like '%a%'
				</otherwise>
			</choose>
		</where>
	</select>

6.foreach标签
①作用：组装有一定规律的字符串，例如in语句。
②接口方法：List getListBySeveralIds(@Param("custIdList") List<Integer> custIdList);
③配置方式

<select id="getLIstBySeveralIds" resultType="com.atguigu.entities.Customer">
			select cust_id custId, cust_name custName, cust_age custAge
			from tbl_cust where cust_id in 
			<foreach collection="custIdList" item="custId" open="(" close=")" separator=",">
				#{custId}
			</foreach>
		</select>

7.trim标签
①作用：拼字符串
②接口方法：List<Customer> getListByMinMaxAge(@Param("minAge") Integer minAge, @Param("maxAge") Integer maxAge);
③配置方式

<select id="getListByMinMaxAge" resultType="com.atguigu.entities.Customer">
			select cust_id custId,cust_name custName,cust_age custAge
			from tbl_cust
			<trim prefix="where" prefixOverrides="and|or">
				<if test="minAge!=null">
					<![CDATA[and cust_age>#{minAge}]]>
				</if>
				<if test="maxAge!=null">
					<![CDATA[and cust_age<#{maxAge}]]>
				</if>
			</trim>
		</select>
④使用trim标签生成动态insert语句
	[1]接口方法：void saveCustomerClever(Customer customer);
	[2]配置方式
		<insert id="saveCustomerClever" parameterType="com.atguigu.entities.Customer">
			INSERT INTO tbl_cust
			<trim prefix="(" suffix=")" suffixOverrides=",">
				<if test="custName!=null">cust_name,</if>
				<if test="custAge!=null">cust_age,</if>
			</trim>
			<trim prefix="VALUES(" suffix=")" suffixOverrides=",">
				<if test="custName!=null">#{custName},</if>
				<if test="custAge!=null">#{custAge},</if>
			</trim>
		</insert>

8.set标签
①作用：生成动态更新语句
②接口方法：void updateCustomer(Customer customer);
③配置方式

<update id="updateCustomer" parameterType="com.atguigu.entities.Customer">
			update tbl_cust
			<set>
				<if test="custName!=null">cust_name=#{custName},</if>
				<if test="custAge!=null">cust_age=#{custAge},</if>
			</set>
			where cust_id=#{custId}
		</update>