MyBatis--------动态SQL详解

一、
1.前提：为什么会出现动态SQL？
首先我们来看一下分别在JDBC、Hibernate和MyBatis中使用SQL语句的情况

注意：此例子满足经典数据库列名字A_COLUMN到java对象属性名aColumn的映射，省略了在mybatis-config.xml文件中自动映射的配置<setting name="mapUnderscoreToCamelCase" value="true"/>，只有当设置为true且满足驼峰命名规则时，才可以实现从数据库到POJO对象的自动映射，否则需要自定义映射规则，这里不做介绍，请看mybatis的其他总结！

（1）设计一张学生表如图：

​
（2）定义一个Student类：

package com.cyn.po;

/*
属性的命名符合驼峰命名规范
*/
public class Student {
	private int Sno;
	private String Sname;
	private String Ssex;
	private int Sage;
	private String Sdept;
	public int getSno() {
		return Sno;
	}
	public void setSno(int sno) {
		Sno = sno;
	}
	public String getSname() {
		return Sname;
	}
	public void setSname(String sname) {
		Sname = sname;
	}
	public String getSsex() {
		return Ssex;
	}
	public void setSsex(String ssex) {
		Ssex = ssex;
	}
	public int getSage() {
		return Sage;
	}
	public void setSage(int sage) {
		Sage = sage;
	}
	public String getSdept() {
		return Sdept;
	}
	public void setSdept(String sdept) {
		Sdept = sdept;
	}
	
}

（3）定义两个变量

    String Sname = "lisi";    
    String Sage = 16

情景：
    通过Sname和Sage在T_STUDENT表中查询该学生的所有信息，比较在JDBC、Hibernate(HQL)和MyBatis中使用SQL语句的不同

（1）JDBC：   
     "select * from T_STUDENT where S_NAME = '"+Sname+"' and S_AGE = '"+Sage+"'"
（2）Hibernate：    
    "from Student where S_NAME = '"+Sname+"' and S_AGE = '"+Sage+"'"
（3）MyBatis：    
    "select * from T_STUDENT where S_NAME = #{Sname} and S_AGE = #{Sage}"

2.结论：
（1）JDBC：SQL的拼接要特别注意引号和空格的使用，后期维护是个灾难
（2）Hibernate：SQL被进行了过度封装，而HQL不够灵活，特殊场景下不适用
（3）MyBatis：可以根据参数信息灵活多变，在下面的使用中可以深切体会它的强大之处-针对同一个表而言，多次不同条件的查询只需写一次select，多次不同字段的更新只需写一次update等

二、动态SQL的使用：

1.动态SQL-if语句：

（1）首先不使用动态SQL

<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
<!--student是com.cyn.po.Student的别名，我们省略了在mybatis-config.xml中的配置，以下情况中均使用student来代替com.cyn.po.Student-->
		select * from T_STUDENT where S_NAME = #{Sname} and S_AGE = #{Sage}
</select>

注意：这里和普通的sql查询语句差不多，对于只有一个参数，后面的#{Sname}表示占位符，里面不一定要写Sname,写啥都可以，但是不要空着，如果有多个参数则必须写pojo类里面的属性 

• ·id：和接口方法名保持一致

        parameterType：和接口方法中的参数类型保持一致

        resultType：和接口方法中的返回值类型保持一致

• ·#{**}和$(**)的作用？ 

从参数域中取出参数名为**的值，值为什么类型，默认#{}就是什么类型

例如：String username = "cyn"

          #{username} = "cyn" = 'cyn'

• ·为什么使用#{**}而不使用$(**)？

    使用#{**}可以防止SQL注入

（2）接上面的查询语句，我们发现如果#{Sname}为空，那么查询结果也为空，如何解决呢？

<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
    select * from T_STUDENT where
    <if test="Sname != null and Sname != '' ">
	S_NAME = #{Sname} 
    </if>
    <if test="Sage != null and Sage != '' ">
	and S_AGE = #{Sage}
    </if>	 
</select>

注意：这样写我们可以看到，如果Sage等于null那么查询语句为 select * from T_STUDENT where S_NAME = #{Sname},但是如果Sname为空呢？那么查询语句为 select * from T_STUDENT where and Sage = #{Sage}，这是错误的SQL语句，如何解决呢？请看下面的where语句

*2.动态SQL-if+where语句

<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
    select * from T_STUDENT
    <where>
        <if test="Sname != null and Sname != '' ">
	    and S_NAME = #{Sname} 
	</if>
			
	<if test="Sage != null and Sage != '' ">
	    and S_AGE = #{Sage}
	</if>
    </where> 
</select>

可能看到很多人只有在第一个判断执行语句中省略了and，为了防止记性混乱，我们统一方便都在判断执行语句前面添加and！你只需知道这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会自动剔除掉以此保证sql语句格式的正确性。
注意：当上述查询条件Sname和Sage都为空的时候，默认查询语句为：select * from T_STUDENT，符合某些当不输入查询条件时返回全部查询的需求，更为灵活！

3.动态SQL：if+set语句

（1）当不使用set标签而使用原生sql语句中的set：

<update id="updateStudentBySnameAndSage" parameterType="com.cyn.po.Student">
    update T_STUDENT set
    <if test="Sname != null and Sname != '' ">
        S_NAME = #{Sname}, 
    </if>
    <if test="Sage != null and Sage != '' ">
	S_AGE = #{Sage},
    </if>
    <if test="Sdept != null and Sdept != '' ">
	S_DEPT = #{Sdept}
    </if>
    where S_NO = #{Sno}
</update>

注意：当更新全部字段时候成立，但是当Sdept等于null，此时我们只想更新Sname和Sage字段时sql语句则变成了：update T_STUDENT set S_NAME = #{Sname},S_AGE = #{Sage}, where S_NO = #{S_no}。我们发现多了一个逗号，如何解决呢？

*（2）当在update更新语句中引入set标签时：

<update id="updateStudentBySnameAndSage" parameterType="com.cyn.po.Student">
    update T_STUDENT 
        <set>
	    <if test="Sname != null and Sname != '' ">
	        S_NAME = #{Sname}, 
	    </if>
	    <if test="Sage != null and Sage != '' ">
		S_AGE = #{Sage},
	    </if>
	    <if test="Sdept != null and Sdept != '' ">
		S_DEPT = #{Sdept},
	    </if>
	</set>
    where S_NO = #{Sno}
</update> 

在这种情况下，当我们只想更新Sname和Sage字段时sql语句格式正确：update T_STUDENT set S_NAME = #{Sname},S_AGE = #{Sage} where S_NO = #{S_no}
注意：可能看到很多人只有在最后一个判断执行语句中省略了逗号，为了防止记性混乱，我们统一方便都在判断执行语句后面添加逗号即可！你只需知道在动态生成的sql语句中，set标签会默认将sql语句中最后一个逗号去掉，即可满足所有的更新情况！

*4.动态SQL：choose(when，otherwise)语句

有时候，我们不想用到所有的查询条件，只想选着其中的一个，查询条件有一个满足即可，类似于java中的switch case default选择语句。在MyBatis中，我们使用choose标签可以解决此类问题

<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
    select * from T_STUDENT
    <where>
        <choose>
	    <when test="Sno != null and Sno != ''">
	        and S_NO = #{Sno}
	    </when>
	    <when test="Sname != null and Sname != ''">
		and S_NAME = #{Sname}
	    </when>
	    <otherwise>
		and S_AGE = #{Sage}
	    </otherwise>
	</choose>
    </where> 
</select> 

也就是说，这里我们列出的三个查询条件：Sno、Sname、Sage，我们只能选择一个作为查询条件
（1）如果Sno不为空，则SQL语句为：select * select * from T_STUDENT where S_NO = Sno
（2）如果Sno为空，那么接下来看Sname是否为空，如果不为空，则SQL语句为：select * select * from T_STUDENT where S_NAME = Sname
（3）如果Sname为空，那么查询语句为：select * select * from T_STUDENT where S_AGE = Sage

5.动态SQL：trim语句

trim标记是一个格式化的标记，可以完成set或者是where标记的功能

（1）用trim改写上面第二点的if+where语句

<!-- <select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
        select * from T_STUDENT
	<where>
	    <if test="Sname != null and Sname != '' ">
	        and S_NAME = #{Sname} 
	    </if>
	    <if test="Sage != null and Sage != '' ">
		and S_AGE = #{Sage}
	    </if>
	</where> 
</select> -->
	
<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
    select * from T_STUDENT
    <trim prefix="where" prefixOverrides="and || or">
        <if test="Sname != null and Sname != '' ">
	    and S_NAME = #{Sname} 
	</if>
	<if test="Sage != null and Sage != '' ">
	    and S_AGE = #{Sage}
	</if>
    </trim> 
</select> 

注意：
    prefix：前缀加一个where
    prefixoverride：去掉where后紧接着的第一个and或者是or
弊端：当Sname和Sage都为空的时候，使用trim就成了select * from T_STUDENT where，所有一般不建议使用，在特殊情况下才使用该标签
（2）用 trim 改写上面第三点的 if+set 语句

<!-- <update id="updateStudentBySnameAndSage" parameterType="com.cyn.po.Student">
         update T_STUDENT set
	 <if test="Sname != null and Sname != '' ">
	     S_NAME = #{Sname}, 
	 </if>
	 <if test="Sage != null and Sage != '' ">
	     S_AGE = #{Sage},
	 </if>
	 <if test="Sdept != null and Sdept != '' ">
	     S_DEPT = #{Sdept}
	 </if>
	 where S_NO = #{S_no}
</update> -->

<update id="updateStudentBySnameAndSage" parameterType="com.cyn.po.Student">
    update T_STUDENT
    <trim prefix="set" suffixOverrides=",">
        <if test="Sname != null and Sname != '' ">
	    S_NAME = #{Sname}, 
	</if>
	<if test="Sage != null and Sage != '' ">
	    S_AGE = #{Sage},
	</if>
	<if test="Sdept != null and Sdept != '' ">
	    S_DEPT = #{Sdept},
	</if>
    </trim> 
    where S_NO = #{Sno}
</update>

注意：
    prefix：前缀加一个set
    suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

6.动态SQL：foreach语句

当我们需要查询表中学号为：95001、92002、95003的学生信息，
SQL语句：select * from T_STUDENT where S_NO in (95001,95002,95003)
（1）设置传递的参数为List类型的snoList：

List<Integer> snoList = new ArrayList<>();
snoList.add(95001);
snoList.add(95002);
snoList.add(95003);

（2）使用foreach来改写select * from T_STUDENT  where S_NO in (95001,95002,95003)

<select id="selectStudentBySnameAndSage" parameterType="List" resultType="com.cyn.po.Student">
    select * from T_STUDENT
    <where>
	<foreach collection="list" item="Sno" open="and S_NO in (" close=")" separator=",">
	    #{Sno}
	</foreach>
    </where> 
</select>

注意：     foreach的作用是循环遍历，通常用来遍历集合比如数组、List和Set等。
                collection:指定输入对象中的集合属性，当遍历数组时候，只能写array！！！，参数类型为List！！！
                item:每次遍历生成的对象
                open:开始遍历时的拼接字符串
                close:结束时拼接的字符串
                separator:遍历对象之间需要拼接的字符串

7.动态SQL：include语句

有时候可能某个sql语句我们用的特别多，为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用。
比如：假如我们需要经常根据学生姓名和性别来进行联合查询，那么我们就把这个代码抽取出来，如下：
（1）首先定义一个复用的SQL片段

<sql id="selectStudentBySnameAndSageSQL">
    <if test="Sname != null and Sname != '' ">
        and S_NAME = #{Sname} 
    </if>
    <if test="Sage != null and Sage != '' ">
	and S_AGE = #{Sage}
    </if>
</sql>

（2）引用SQL片段

<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
    select * from T_STUDENT
    <where>
<!-- 引用sql片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace -->
        <include refid="selectStudentBySnameAndSageSQL"></include>
<!-- 在这里还可以引用其他的 sql 片段 -->
    </where> 
</select>

注意：最好基于单表来定义sql片段，提高片段的可重用性；在sql片段中不要包括where

8.动态SQL：bind语句

作用是通过OGNL表达式去自定义一个上下文变量，多次使用一次定义，方便开发效率
！

<select id="selectStudentBySnameAndSage" parameterType="student" resultType="com.cyn.po.Student">
    <bind name="pattern_name" value=" '%' + Sname + '%'"/>
    <bind name="pattern_age" value=" '%' + Sage + '%'"/>
    select * from T_STUDENT where S_NAME like #{pattern_name} and S_AGE like #{pattern_age}
</select>

注意：这里的Sname和Sage就是传递进来的参数，它和通配符连接后，赋值给了pattern_name和pattern_age，然后我们就可以在select语句中使用这个变量进行模糊查询，这个变量在上下文中均可使用。

三、心得

    为了防止SQL语句拼接出错，我们应该先写出原生SQL语句->然后再根据MyBatis的基本语法生成动态SQL，所以我们最好自己写一个MyBatis通用增删改查的动态SQL语法框架，能够让你很快去对照着改出来，这个通用格式可以根据上面的总结出来）  

 

GO