MyBatis之动态sql详细分类说明

男娘i 2022-04-16 06:16 251阅读 0赞

**本文主要内容：**

*  1、if\_判断&OGNL
 *  2、where\_查询条件
 *  3、trim\_自定义字符串截取，用来处理if连接的问题
 *  4、choose\_分支选择
 *  5、set\_与if结合的动态更新
 *  6、foreach
 *       6.1  遍历集合
 *       6.2  mysql下foreach批量插入的两种方式
 *       6.3  oracle下批量插入的两种方式
 *       6.4  oracle下foreach批量保存两种方式
 *  7、内置参数\_parameter&\_databaseId
 *  8、bind\_绑定：即在xml中动态的给这个值添加规则
 *  9、sql\_抽取可重用的sql片段

--------------------

**1、if\_判断&OGNL**

场景：查询员工，要求，携带了哪个字段查询条件就带上这个字段的值

注意：if中test的写法，写and而不是&&，写or而不是||，或者写&&，||的转义字符也可；

如：&&的转义字符：&    ""的转义字符："

and最好写在sql语句的前面，写<select>标签中，当id为null时，mybatis会自动去掉第一个and；如果写后面则不会去掉；

*ONGL规则官方文档：*[http://commons.apache.org/proper/commons-ognl/language-guide.html][http_commons.apache.org_proper_commons-ognl_language-guide.html]

*转义字符对应官方文档：*[http://www.w3school.com.cn/tags/html\_ref\_entities.html][http_www.w3school.com.cn_tags_html_ref_entities.html]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmhhaWppbmc_size_16_color_FFFFFF_t_70][]

<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">
     	select * from tbl_employee
     	
     	<where>
    	 	
    	 	<if test="id!=null">
    	 		id=#{id}
    	 	</if>
    	 	<if test="lastName!=null && lastName!=""">
    	 		and last_name like #{lastName}
    	 	</if>
    	 	<if test="email!=null and email.trim()!=""">
    	 		and email=#{email}
    	 	</if> 
    	 	
    	 	<if test="gender==0 or gender==1">
    	 	 	and gender=#{gender}
    	 	</if>
     	</where>
     </select>

**2、where\_查询条件**

优点：and最好写在sql语句的前面，写<select>标签中，当id为null时，mybatis会自动去掉第一个and；如果写后面则不会去掉；

**3、trim\_自定义字符串截取，用来处理if连接的问题**

后面多出的and或者or where标签不能解决   
         prefix="":前缀：trim标签体中是整个字符串拼串 后的结果。  
                 prefix给拼串后的整个字符串加一个前缀   
         prefixOverrides="":  
                 前缀覆盖： 去掉整个字符串前面多余的字符  
         suffix="":后缀  
                 suffix给拼串后的整个字符串加一个后缀   
         suffixOverrides=""  
                 后缀覆盖：去掉整个字符串后面多余的字符

<select id="getEmpsByConditionTrim" resultType="com.atguigu.mybatis.bean.Employee">
     	select * from tbl_employee
     	
     	<trim prefix="where" suffixOverrides="and">
     		<if test="id!=null">
    	 		id=#{id} and
    	 	</if>
    	 	<if test="lastName!=null && lastName!=""">
    	 		last_name like #{lastName} and
    	 	</if>
    	 	<if test="email!=null and email.trim()!=""">
    	 		email=#{email} and
    	 	</if> 
    	 	
    	 	<if test="gender==0 or gender==1">
    	 	 	gender=#{gender}
    	 	</if>
    	 </trim>
     </select>

**4、choose\_分支选择**

相当于Java中的switch-case-break语句；

<select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee">
     	select * from tbl_employee 
     	<where>
     		
     		<choose>
     			<when test="id!=null">
     				id=#{id}
     			</when>
     			<when test="lastName!=null">
     				last_name like #{lastName}
     			</when>
     			<when test="email!=null">
     				email = #{email}
     			</when>
     			<otherwise>
     				gender = 0
     			</otherwise>
     		</choose>
     	</where>
     </select>

**5、set\_与if结合的动态更新**

<set>标签会默认去掉最后一个逗号

<update id="updateEmp">
      
      update tbl_employee 
      <set>
    	<if test="lastName!=null">
    		last_name=#{lastName},
    	</if>
    	<if test="email!=null">
    		email=#{email},
    	</if>
    	<if test="gender!=null">
    		gender=#{gender}
    	</if>
      </set>
      where id=#{id} 
    </update>

<trim>结合set:

update tbl_employee 
      <trim prefix="set" suffixOverrides=",">
    	<if test="lastName!=null">
    		last_name=#{lastName},
    	</if>
    	<if test="email!=null">
    		email=#{email},
    	</if>
    	<if test="gender!=null">
    		gender=#{gender}
    	</if>
      </trim>
    where id=#{id}

**6、foreach**

** 6.1  遍历集合**

<foreach>中元素说明：

collection：指定要遍历的集合：  
        list类型的参数会特殊处理封装在map中，map的key就叫list  
    item：将当前遍历出的元素赋值给指定的变量  
    separator:每个元素之间的分隔符  
    open：遍历出所有结果拼接一个开始的字符  
    close:遍历出所有结果拼接一个结束的字符  
    index:索引。遍历list的时候是index就是索引，item就是当前值  
                        遍历map的时候index表示的就是map的key，item就是map的值

\#\{变量名\}就能取出变量的值也就是当前遍历出的元素

<select id="getEmpsByConditionForeach" resultType="com.atguigu.mybatis.bean.Employee">
    	select * from tbl_employee
    	<foreach collection="ids" item="item_id" separator=","
    		open="where id in(" close=")">
    		#{item_id}
    	</foreach>
    </select>

**6.2  mysql下foreach批量插入的两种方式**

第一种方式：

MySQL下批量保存：可以foreach遍历 ，mysql支持values(),(),()语法

<insert id="addEmps">
     	insert into tbl_employee(
     		last_name,email,gender,d_id
     	) 
    	values
    	<foreach collection="emps" item="emp" separator=",">
    		(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
    	</foreach>
     </insert>

第二种方式

执行多个insert语句：

<insert id="addEmps">
     	<foreach collection="emps" item="emp" separator=";">
     		insert into tbl_employee(last_name,email,gender,d_id)
     		values(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
     	</foreach>
     </insert>

这种方式需要数据库连接属性allowMultiQueries=true；这种分号分隔多个sql可以用于其他的批量操作（删除，修改）

jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
    jdbc.username=root
    jdbc.password=123456

**6.3  oracle下批量插入的两种方式**

Oracle数据库批量保存： Oracle不支持values(),(),()  
Oracle支持的批量方式  
1、多个insert放在begin - end里面  
    begin  
        insert into employees(employee\_id,last\_name,email)   
        values(employees\_seq.nextval,'test\_001','test\_001@atguigu.com');  
        insert into employees(employee\_id,last\_name,email)   
        values(employees\_seq.nextval,'test\_002','test\_002@atguigu.com');  
    end;  
2、利用中间表：  
    insert into employees(employee\_id,last\_name,email)  
       select employees\_seq.nextval,lastName,email from(  
              select 'test\_a\_01' lastName,'test\_a\_e01' email from dual  
              union  
              select 'test\_a\_02' lastName,'test\_a\_e02' email from dual  
              union  
              select 'test\_a\_03' lastName,'test\_a\_e03' email from dual  
       )      
**6.4  oracle下foreach批量保存两种方式**

oracle第一种批量方式：

<foreach collection="emps" item="emp" open="begin" close="end;">
    	insert into employees(employee_id,last_name,email) 
    	    values(employees_seq.nextval,#{emp.lastName},#{emp.email});
    </foreach>

oracle第二种批量方式

insert into employees(
    	employee_id,last_name,email
    )
    		<foreach collection="emps" item="emp" separator="union"
    			open="select employees_seq.nextval,lastName,email from("
    			close=")">
    			select #{emp.lastName} lastName,#{emp.email} email from dual
    		</foreach>
    </insert>

**7、内置参数\_parameter&\_databaseId**

mybatis默认还有两个内置参数：  
         \_parameter:代表整个参数  
             单个参数：\_parameter就是这个参数  
             多个参数：参数会被封装为一个map；\_parameter就是代表这个map  
         \_databaseId:如果配置了databaseIdProvider标签。  
             \_databaseId就是代表当前数据库的别名oracle

*注：\_databaseId可以在一个方法中来根据不同的数据库写不同的sql，方便调用*

<select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">
    		<if test="_databaseId=='mysql'">
    			select * from tbl_employee
    			<if test="_parameter!=null">
    				where last_name like #{lastName}
    			</if>
    		</if>
    		<if test="_databaseId=='oracle'">
    			select * from employees
    			<if test="_parameter!=null">
    				where last_name like #{_parameter.lastName}
    			</if>
    		</if>
    </select>

**8、bind\_绑定：即在xml中动态的给这个值添加规则**

bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值

<select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">
    		
    		<bind name="_lastName" value="'%'+lastName+'%'"/>
    		<if test="_databaseId=='mysql'">
    			select * from tbl_employee
    			<if test="_parameter!=null">
    				where last_name like #{_lastName}
    			</if>
    		</if>
    		<if test="_databaseId=='oracle'">
    			select * from employees
    			<if test="_parameter!=null">
    				where last_name like #{_parameter.lastName}
    			</if>
    		</if>
    </select>

**9、sql\_抽取可重用的sql片段**

抽取可重用的sql片段。方便后面引用   
          1、sql抽取：经常将要查询的列名，或者插入用的列名抽取出来方便引用  
          2、include来引用已经抽取的sql：  
          3、include还可以自定义一些property，sql标签内部就能使用自定义的属性  
                  include-property：取值的正确方式$\{prop\},  
                  \#\{不能使用这种方式\}

<sql id="insertColumn">
    		<if test="_databaseId=='oracle'">
    			employee_id,last_name,email
    		</if>
    		<if test="_databaseId=='mysql'">
    			last_name,email,gender,d_id
    		</if>
    </sql>

调用：

使用<include>标签进行调用，<include refid="insertColumn"></include>

oracle：

insert into employees(
    	
    	<include refid="insertColumn">
    		<property name="testColomn" value="abc"/>
    	</include>
    )
    		<foreach collection="emps" item="emp" separator="union"
    			open="select employees_seq.nextval,lastName,email from("
    			close=")">
    			select #{emp.lastName} lastName,#{emp.email} email from dual
    		</foreach>
    </insert>

mysql:

<insert id="addEmps">
     	insert into tbl_employee(
     		<include refid="insertColumn"></include>
     	) 
    	values
    	<foreach collection="emps" item="emp" separator=",">
    		(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
    	</foreach>
     </insert>

也可参考mybatis的jar包中的官方文档查看以上内容：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmhhaWppbmc_size_16_color_FFFFFF_t_70 1][]

[http_commons.apache.org_proper_commons-ognl_language-guide.html]: http://commons.apache.org/proper/commons-ognl/language-guide.html
[http_www.w3school.com.cn_tags_html_ref_entities.html]: http://www.w3school.com.cn/tags/html_ref_entities.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmhhaWppbmc_size_16_color_FFFFFF_t_70]: /images/20220416/197166e0e3934f3e85c9d2131ee8e868.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppbmhhaWppbmc_size_16_color_FFFFFF_t_70 1]: /images/20220416/7d62092330854d8db749eaa62daa3f02.png