MyBatis（5）——Mapper映射文件_动态SQL

目录

 

一、if、where、trim、choose、set

1、if标签与OGNL表达式

（1）if标签：

（2）OGNL表达式：

2、if、where结合

3、choose、when、otherwise

4、trim

5、set

二、foreach

1、对集合进行遍历

2、遍历批量保存

（1）MySQL数据库批量保存：

（2）Oracle数据库批量保存

3、真正的批量处理

三、其他

1、内置参数

（1）_parameter

（2）_databaseId

2、bind标签

3、sql标签

---

一、if、where、trim、choose、set

1、if标签与OGNL表达式

（1）if标签：

查询方法：public List<Employee> getEmpsByConditionIf(Employee employee);

 <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 &amp;&amp; lastName!=&quot;&quot;">
			and last_name like #{lastName}
		</if>
		<if test="email!=null and email.trim()!=&quot;&quot;">
			and email=#{email}
		</if>
		<if test="gender==0 or gender==1">
			and gender=#{gender}
		</if>
 </select>

（2）OGNL表达式：

OGNL从参数中取值进行判断，遇见特殊符号应该去写转义字符

*在进行字符串判空的时候，空字符串==0：解决方案：

字符串判空：https://www.jianshu.com/p/732839a2f532

Mybatis判断int类型是否为空：https://blog.csdn.net/gui66497/article/details/50166433

• 访问对象属性： person.name
• 调用方法： person.getName()
• 调用静态属性/方法： @java.lang.Math@PI、 @java.util.UUID@randomUUID()
• 调用构造方法： new com.atguigu.bean.Person(‘admin’).name
• 运算符： +,-*,/,%
• 逻辑运算符： in,not in,>,>=,<,<=,==,!=
• 注意：xml中特殊符号如”,>,<等这些都需要使用转义字符

2、if、where结合

if标签的缺陷：第一个if标签没有生效的时候，生成的sql会多出一个and

改写查询方法：public List<Employee> getEmpsByConditionIf(Employee employee);

where 标签和if标签结合可以解决前面多出and的问题，where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。

 <select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">
	select * from tbl_employee
	<!-- where -->
	<where>
		<if test="id!=null">
			id=#{id}
		</if>
		<if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
			and last_name like #{lastName}
		</if>
		<if test="email!=null and email.trim()!=&quot;&quot;">
			and email=#{email}
		</if> 
		<!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
		<if test="gender==0 or gender==1">
			and gender=#{gender}
		</if>
	</where>
 </select>

测试：

3、choose、when、otherwise

有时我们不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

 <select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee">
	select * from tbl_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>

4、trim

自定义字符串的截取规则。

后面多出的and或者or where标签不能解决 

• prefix：给拼串后的整个字符串加一个前缀 
• prefixOverrides：去掉整个字符串前面多余的字符串
• 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 &amp;&amp; lastName!=&quot;&quot;">
			last_name like #{lastName} and
		</if>
		<if test="email!=null and email.trim()!=&quot;&quot;">
			email=#{email} and
		</if> 
		<if test="gender==0 or gender==1">
			gender=#{gender}
		</if>
	 </trim>
 </select>

5、set

set 用于动态更新语句,set 元素可以用于动态包含需要更新的列，而舍去其它的。

 <update id="updateEmp">
	<!-- Set标签的使用 -->
	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>

set 元素会动态前置 SET 关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。因为用的是“if”元素，若最后一个“if”没有匹配上而前面的匹配上，SQL 语句的最后就会有一个逗号遗留。

使用trim代替set:

 <update id="updateEmp">
	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} 
 </update>

二、foreach

1、对集合进行遍历

动态 SQL 的另外一个常用的操作需求是对一个集合进行遍历，通常是在构建 IN 条件语句的时候。

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

• collection：指定要遍历的集合
• item：将当前遍历出的元素赋值给指定的变量
• separator：每个元素之间的分隔符
• open：遍历出所有结果拼接一个开始的字符
• close：遍历出所有结果拼接一个结束的字符
• index：索引。
• 当使用可迭代对象（list、set）或者数组时，index 是当前迭代的次数，item 的值是本次迭代获取的元素。
• 当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

2、遍历批量保存

保存方法：public void addEmps(@Param("emps")List<Employee> emps);

（1）MySQL数据库批量保存：

写法一：遍历insert语句的values

使用insert into table(colum1,colum2...) 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；

即：jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true

弊端：会出现sql语句过长，报异常；

解决：https://blog.csdn.net/dqsweet/article/details/54584470

（2）Oracle数据库批量保存

写法一：多个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;

示例：注意end后面有；

 <insert id="addEmps" databaseId="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>
 </insert>

写法二：利用中间表

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
   )	

示例：

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

3、真正的批量处理

上述2充其量为foreach遍历执行sql，而不是真正的批处理SQL。

在MyBatis全局配置文件中有一个配置：

全局配置了defaultExecutorType为BATCH ，MyBatis才会启用批量操作。但是默认的是SIMPLE，没必要进行批量的全局配置。

在特定的需求下，可以单独的设置一个SqlSession，其参数executorType设置为BATCH，然后使用即可（整合的框架里可以单独的注册一个SqlSessionTemplate）：

/**
 * 测试批量插入
 * 
 * @throws IOException
 */
@Test
public void testBatch() throws IOException {
	String resource = "mybatis-config.xml";
	InputStream inputStream = Resources.getResourceAsStream(resource);
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
	try {
		EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
		for (int i = 0; i < 50; i++) {
			Employee employee = new Employee(null, "test" + i, "testLast" + i + "", "test" + i + "@email");
			mapper.insertSelective(employee);
		}
		session.commit();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		session.close();
	}
}

注意获取SqlSession的方式：SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);

输出的日志：

日志中只发了一条插入的SQL，其他的作为参数设置进去。最后进行一次总的提交commit.

批量提交只能应用于 insert, update, delete。并且在批量提交使用时，如果在操作同一SQL时中间插入了其他数据库操作，就会让批量提交方式变成普通的执行方式，所以在使用批量提交时，要控制好 SQL 执行顺序。

数据库的连接URL中需要加入参数：rewriteBatchedStatements=true

mysql.url=jdbc:mysql://localhost:3306/mybatis_test?characterEncoding=utf8&rewriteBatchedStatements=true

MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。

参考：

https://blog.csdn.net/isea533/article/details/80922305

https://blog.csdn.net/qq_15003505/article/details/80428249

三、其他

1、内置参数

mybatis默认还有两个内置参数：

（1）_parameter

_parameter:代表整个参数

• 单个参数：_parameter就是这个参数
• 多个参数：参数会被封装为一个map；_parameter就是代表这个map

简单数据类型

如果需要if test则一定使用<if test="_parameter != null">,此处一定使用_parameter != null而不是id != null

<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="Java.lang.Integer" >
	select 
		*
	from base.tb_user
	<if test="_parameter != null">
		where id = #{id,jdbcType=INTEGER}
	</if>
</select>

一个对象数据类型

public List<Employee> getEmpsTestInnerParameter(Employee employee);

if test里面使用_parameter判空，取参数的时候_parameter代表这个对象，既可以使用对象的属性名：#{lastName}、也可以：#{_parameter.lastName}

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

多个对象数据类型

List<User> select(User user,Page page)

此时if test一定要<if test='_parameter.get("0").name != null'>(通过parameter.get(0)得到第一个参数即user);

where语句where name = #{0.name,jdbcType=CHAR}(通过0.name确保第一个参数user的name属性值)

不用0,1也可以取名List<User> select(@param(user)User user,@param(page)Page page)

（2）_databaseId

如果在全局配置文件中配置了databaseIdProvider标签，_databaseId就是代表当前数据库的别名oracle、mysql。

此时进行一条sql语句的不同数据库的切换，不需要再写两个select标签了。：

<!--public List<Employee> getEmpsTestInnerParameter(Employee employee);  -->
<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>

2、bind标签

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

示例：可以在SQL语句中对like的参数进行处理，添加%：name属性为下方sql中引用的参数，value为对应的参数值。

<!--public List<Employee> getEmpsTestInnerParameter(Employee employee);  -->
<select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">
	<!-- bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->
	<bind name="_lastName" value="'%'+lastName+'%'"/>
	select * from tbl_employee
	<if test="_parameter!=null">
		where last_name like #{_lastName}
	</if>
</select>

3、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>

引用处：property

 <insert id="addEmps" databaseId="oracle">
	insert into employees(
		<!-- 引用外部定义的sql -->
		<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>

上一章：MyBatis（4）——Mapper映射文件_SELECT

下一章：