MyBatis（三）——动态SQL

最新推荐文章于 2023-12-08 18:45:18 发布

ewenll

最新推荐文章于 2023-12-08 18:45:18 发布

阅读量260

点赞数

分类专栏： mybatis 文章标签： mybatis 动态sql 学习笔记

本文链接：https://blog.csdn.net/weixin_43691723/article/details/108058201

版权

mybatis 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

1. 动态SQL 简介

通过mybatis提供的各种标签方法实现动态拼接sql，极大的简化我们拼装SQL的操作。动态SQL元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。MyBatis采用功能强大的基于 OGNL 的表达式来简化操作。

元素种类

if
choose (when, otherwise)
trim (where, set)
foreach

2. 使用动态SQL

2.1 if

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

	 <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
	 <select id="getEmpsByConditionIf" resultType="Employee">
	 	select * from employee where
	 	<!-- test：判断表达式（OGNL）
	 	OGNL参照PPT或者官方文档。
	 	  	 c:if  test
	 	从参数中取值进行判断，遇见特殊符号应该去写转义字符
	 	-->
	 	<if test="id!=null">
	 		id=#{id}
	 	</if>
	 	<if test="lastName!=null and lastName!=''">
	 		and last_name like #{lastName}
	 	</if>
	 	<if test="email!=null and email.trim()!=''">
	 		and email=#{email}
	 	</if> 
	 	<!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
	 	<if test="gender==0 or gender==1">
	 	 	and gender=#{gender}
	 	</if>
	 </select>

2.2 where

上面的写法有个问题，就是，当查询的条件中，id为空时，sql语句中，会多个and

解决这个问题的方法有两种

在where后加一个1=1（只要结果是true就行），再在id 前加一个and（不推荐）
使用where标签来将所有的查询条件包括在内（推荐）

where 标签会自动加上 where 并且处理开头多余的 and 或 or，如果结尾有多余的and 或 or 不会去掉

<select id="getEmpsByConditionIf" resultType="Employee">
	 	select * from employee where
	 	<!-- test：判断表达式（OGNL）
	 	OGNL参照PPT或者官方文档。
	 	  	 c:if  test
	 	从参数中取值进行判断，遇见特殊符号应该去写转义字符
	 	-->
	 	<if test="id!=null">
	 		id=#{id}
	 	</if>
	 	<if test="lastName!=null &amp;&amp; lastName!=''">
	 		and last_name like #{lastName}
	 	</if>
	 	<if test="email!=null and email.trim()!=''">
	 		and email=#{email}
	 	</if> 
	 	<!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
	 	<if test="gender==0 or gender==1">
	 	 	and gender=#{gender}
	 	</if>
	 </select>

2.3 trim

如果语句后面多出and或者or，where标签不能解决，可以使用trim标签

trim 标签中的属性

prefix ：前缀，trim标签体中是整个字符串拼串后的结果，prefix给拼串后的整个字符串加一个前缀
prefixOverrides ：前缀覆盖，去掉整个字符串前面多余的字符（prefixOverrides 指定的字符）
suffix ：后缀，suffix给拼串后的整个字符串加一个后缀
suffixOverrides：后缀覆盖，去掉整个字符串后面多余的字符（suffixOverrides 指定的字符）

 <select id="getEmpsByConditionTrim" resultType="Employee">
	 	select * from employee
	 	<!-- 自定义字符串的截取规则 -->
	 	<trim prefix="where" suffixOverrides="and">
	 		<if test="id!=null">
		 		id=#{id} and
		 	</if>
		 	<if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
		 		last_name like #{lastName} and
		 	</if>
		 	<if test="email!=null and email.trim()!=&quot;&quot;">
		 		email=#{email} and
		 	</if> 
		 	<!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
		 	<if test="gender==0 or gender==1">
		 	 	gender=#{gender}
		 	</if>
		 </trim>
	 </select>

2.4 choose

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose元素，它有点像 Java 中的 switch语句，只有一个条件会被使用。

 <!-- public List<Employee> getEmpsByConditionChoose(Employee employee); -->
<select id="getEmpsByConditionChoose" resultType="ean.Employee">
	select * from employee 
	<where>
		<!-- 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 -->
		<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>

2.5 set

set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

<!--public void updateEmp(Employee employee);  -->
	 <update id="updateEmp">
	 	<!-- Set标签的使用 -->
	 	update 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}

动态更新操作，也可以使用trim标签实现

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}

2.6 foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）

foreach 中的属性

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

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

<!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids);  -->
<select id="getEmpsByConditionForeach" resultType="com.lun.c01.helloworld.bean.Employee">
	select * from employee
	<foreach collection="ids" item="item_id" separator=","
		open="where id in(" close=")">
		#{item_id}
	</foreach>
</select>

2.7 MySQL 批量插入

可以利用foreach标签进行批量插入，有两种方式

第一种：利用MySQL的插入支持values(),(),()…

 <!-- 批量保存 -->
<!--public void addEmps(@Param("emps")List<Employee> emps);  -->
<!--MySQL下批量保存：可以foreach遍历   mysql支持values(),(),()语法-->
<insert id="addEmps">
	insert into employee(last_name,email,gender,department_id) 
	values
	<foreach collection="emps" item="emp" separator=",">
		(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
	</foreach>
</insert>

第二种方式：

 
 <!-- 这种方式需要数据库连接属性allowMultiQueries=true；
 	这种分号分隔多个sql可以用于其他的批量操作（删除，修改） -->
 <insert id="addEmps2">
 	<foreach collection="emps" item="emp" separator=";">
 		insert into employee(last_name,email,gender,department_id)
 		values(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
 	</foreach>
 </insert>

注意，MySQL数据库连接属性allowMultiQueries=true，才能批量删除，修改数据。（在连接MySQL的url后添加参数）。

3. 内置参数

不只是方法传递过来的参数可以被用来判断，mybatis默认还有两个内置参数：

_parameter：代表整个参数，如果是单个参数，_parameter就是这个参数。多个参数，参数会被封装为一个map，_parameter就是代表这个map
_databaseId：如果配置了databaseIdProvider标签。_databaseId就是代表当前数据库的别名oracle

根据配置的环境，选择执行sql语句

<!--public List<Employee> getEmpsTestInnerParameter(Employee employee);  -->
<select id="getEmpsTestInnerParameter" resultType="com.lun.c01.helloworld.bean.Employee">

	<if test="_databaseId=='mysql'">
		select * from tbl_employee
		<if test="_parameter!=null">
			where last_name like #{_parameter.lastName}
		</if>
	</if>

	<if test="_databaseId=='oracle'">
		select * from employees
		<if test="_parameter!=null">
			where last_name like #{_parameter.lastName}
		</if>
	</if>
</select>

4. 绑定（bind）

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

<!--public List<Employee> getEmpsTestInnerParameter(Employee employee);  -->
<select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">

		<!-- bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->
		<bind name="lastName" value="'%'+lastName+'%'"/>

		<if test="_databaseId=='mysql'">
			select * from tbl_employee
			<if test="_parameter!=null">
				where last_name like #{lastName}<!-- 这里使用到bind中lastName， -->
			</if>
		</if>
		<if test="_databaseId=='oracle'">
			select * from employees
			<if test="_parameter!=null">
				where last_name like #{_parameter.lastName}
			</if>
		</if>
</select>