Mybatis（四）—— Mybatis的动态Sql

Mybatis（四）—— Mybatis的动态Sql

1、主要内容

动态sql简介
if判断
where查询条件
trim自定义字符串截取
choose分支选择
set与if结合动态更新
foreach遍历集合
mysql下的批量保存
oracle下的批量保存
内置参数
参数绑定
sql抽取

2、动态sql的介绍

需求：查询员工信息，要求传入的参数为Emp对象，如果empName不为空，需要根据empName查询，如果empMail不为空，需要根据empMail查询，如果empGender不为空，要求根据empGender查询，要求如果deptId不为空，要求根据deptId查询。
类似于以上的需求，必须使用MyBatis的动态sql，动态sql是MyBatis强大的特性之一，能够极大的简化我们sql拼装的过程，动态sql使用标签完成动态sql，类似于之前用过的jstl，获取和其他的xml类似的语法。

3、动态sql中if判断

现在在满足每一个if条件的情况下，正常运行，但是如果将参数中的empName设置为空，那么拼装的sql语句肯定不能够正常执行。

<!-- 
		public List<Emp> selectEmpByEmp(Emp emp);
	 -->
	 <select id="selectEmpByEmp" resultType="com.wanbangee.entities.Emp">
	 
	 	select * from emp where 
	 	<if test="empName != null and empName != ''"> <!-- test="empName != null &amp;&amp; empName != ''"  -->
	 		emp_name = #{empName}
	 	</if>
	 	<if test="empMail != null and empMail != ''">
	 		and emp_mail = #{empMail}
	 	</if>
	 	<if test="empGender != null">
	 		and emp_gender = #{empGender}
	 	</if>
	 	<if test="deptId != null">
	 		and dept_id = #{deptId}
	 	</if>
	 </select>

4 、动态sql使用where查询条件

上面的程序，在指定的情况下，sql不能正常执行，要么前面多一个and，要么后面多一个and，我们有两种解决方案：
① where 1=1 …

select * from emp where 1=1
	 	<if test="empName != null and empName != ''"> <!-- test="empName != null &amp;&amp; empName != ''"  -->
	 		and emp_name = #{empName}
	 	</if>
	 	<if test="empMail != null and empMail != ''">
	 		and emp_mail = #{empMail}
	 	</if>
	 	<if test="empGender != null">
	 		and emp_gender = #{empGender}
	 	</if>
	 	<if test="deptId != null">
	 		and dept_id = #{deptId}
	 	</if>

②：MyBatis提倡的方式，就是使用where标签，将查询的判断放在where标签中

<!-- 
		public List<Emp> selectEmpByEmp(Emp emp);
	 -->
	 <select id="selectEmpByEmp" resultType="com.wanbangee.entities.Emp">
	 	<!-- 
	 	
	 	
	 	 -->
	 	select * from emp 
	 	<where>
		 	<if test="empName != null and empName != ''"> <!-- test="empName != null &amp;&amp; empName != ''"  -->
		 		 emp_name = #{empName} and
		 	</if>
		 	<if test="empMail != null and empMail != ''">
		 		 emp_mail = #{empMail} and
		 	</if>
		 	<if test="empGender != null">
		 		 emp_gender = #{empGender} and
		 	</if>
		 	<if test="deptId != null">
		 		dept_id = #{deptId}
		 	</if>
	 	</

以上的程序依然存在问题，在deptId为null情况下，会多出一个and，导致sql报错。

5、动态sql使用trim自定义字符串截取

Trim标签可以很好的解决where解决不了的问题。

 <select id="selectEmpByEmp" resultType="com.wanbangee.entities.Emp">
	 	<!-- 
	 	
	 	
	 	 -->
	 	select * from emp 
	 		<!-- 
	 		prefix : 添加前缀
	 		suffix ： 添加后缀
	 		prefixOverrides ： 前缀覆盖 
	 			prefixOverrides="abc" 表示如果trim标签中拼凑的sql语句 以 abc 结尾，则用空串将abc覆盖
	 		suffixOverrides ： 后缀覆盖
	 			suffixOverrides="and" 表示如果trim标签中拼凑的sql语句 最后以 and 结尾，则用空串将and覆盖
	 		 -->
	 		<trim prefix=" where " suffix="" prefixOverrides="abc" suffixOverrides="and" >
			 	<if test="empName != null and empName != ''"> <!-- test="empName != null &amp;&amp; empName != ''"  -->
			 		 emp_name = #{empName} and
			 	</if>
			 	<if test="empMail != null and empMail != ''">
			 		 emp_mail = #{empMail} and
			 	</if>
			 	<if test="empGender != null">
			 		 emp_gender = #{empGender} and
			 	</if>
			 	<if test="deptId != null">
			 		dept_id = #{deptId}
			 	</if>
		 	</trim>
	 </select>

6、动态sql使用choose分支选择

需求：查询员工信息，要求传入的参数为Emp对象，如果empName不为空，则根据empName查询，如果empMail不为空，则根据empMail查询，如果empGender不为空，则根据empGender查询，要求如果deptId不为空，则根据deptId查询。这个需求的要求表示查询条件只有一个，使用if是完成不了了，只能使用choose分支标签。

<!-- 
		public List<Emp> selectEmpByEmp2(Emp emp);
	 -->
	 <select id="selectEmpByEmp2" resultType="com.wanbangee.entities.Emp">
	 	select * from emp 
	 	<where>
	 		<choose>
	 			<when test="empName != null and empName != ''">
	 				emp_name = #{empName}
	 			</when>
	 			<when test="empMail != null and empMail != ''">
	 				emp_mail = #{empMail}
	 			</when>
	 			<when test="empGender != null">
	 				emp_gender = #{empGender}
	 			</when>
	 			<when test="deptId != null">
	 				dept_id = #{deptId}
	 			</when>
	 			<!-- <otherwise>
	 				1=1
	 			</otherwise> -->
	 		</choose>
	 	</where>
	 </select>

7 、动态sql使用set 标签与if结合进行更新

Set 标签用于修改。
需求：根据emp_id 修改emp数据，入参为Emp对象，对象中如果empName不为空，empName则需要修改，如果empMail不为空，则empMail需要修改…

<!-- 
		public int updateEmp(Emp emp);
	-->
	<update id="updateEmp">
		update emp  set
		<set>
			<if test="empName != null and empName != ''"> 
			 		 emp_name = #{empName} ,
			</if>
		 	<if test="empMail != null and empMail != ''">
			 		 emp_mail = #{empMail} ,
			</if>
			<if test="empGender != null">
			 		 emp_gender = #{empGender} ,
			</if>
			<if test="deptId != null">
			 		dept_id = #{deptId}
			</if>
		</set>
		<where>
			<if test="empId == null"> 
			 	1 = 2
			</if>
			<if test="empId != null"> 
			 	emp_id = #{empId}
			</if>
		</where>
	</update>

当然，以上代码我们也可以使用trim标签完成

<!-- 
		public int updateEmp(Emp emp);
	-->
	<update id="updateEmp">
		update emp  
		<trim prefix=" set " suffixOverrides=",">
			<if test="empName != null and empName != ''"> 
			 		 emp_name = #{empName} ,
			</if>
		 	<if test="empMail != null and empMail != ''">
			 		 emp_mail = #{empMail} ,
			</if>
			<if test="empGender != null">
			 		 emp_gender = #{empGender} ,
			</if>
			<if test="deptId != null">
			 		dept_id = #{deptId}
			</if>
		</trim>
		<where>
			<if test="empId == null"> 
			 	1 = 2
			</if>
			<if test="empId != null"> 
			 	emp_id = #{empId}
			</if>
		</where>
	</update>

8、动态sql使用foreach标签遍历集合

Foreach标签用来遍历集合的，如何使用呢？范例：传入一个Integer类型的List集合，要求emp_id in(集合中的数据)，这个时候我们可以使用foreach标签去循环遍历list集合。

<!-- 
		public List<Emp> selectEmpByEmpIds(List<Integer> ids);
	 -->
	 <select id="selectEmpByEmpIds" resultType="com.wanbangee.entities.Emp">
	 	select * from emp where emp_id in
	 	<!-- 
	 		collection : 表示要遍历的集合,
	 				如果传入的参数是List类型，那么可以使用list或者collection，如果传入是Set集合，只能使用collection 
	 				实际上我们在接口的方法中使用@Param指定collection属性使用的值 ,指定之后，能使用指定的内容和param1....
	 		open : 前缀添加
	 		close：后缀添加
	 		separator ： 每次遍历的分割符
	 		index:遍历次数
	 		item : 每次遍历的数据赋值的变量
	 	 -->
	 	<foreach collection="ids" open="(" close=")" separator="," item="id">
	 		#{id}
	 	</foreach>
	 </select>

9、 mysql环境下使用foreach进行批量插入操作

Mysql进行批量新增数据的两种sql语法：

# 第一种
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id)
VALUES('AAA','aaa@163.com',1,1),
('BBB','aaa@163.com',1,1),
('CCC','aaa@163.com',1,1),
('DDD','aaa@163.com',1,1)

# 第二种
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id)VALUES('aaa','aaa@163.com',1,1);
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id)VALUES('bbb','aaa@163.com',1,1);
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id)VALUES('ccc','aaa@163.com',1,1);
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id)VALUES('ddd','aaa@163.com',1,1);

使用foreach进行批量新增：

	<!-- 
		public int insertEmps(List<Emp> emps);
	 -->
	<insert id="insertEmps">
		<!--第一种sql-->
		INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id) VALUES
		<foreach collection="list" separator="," item="emp">
			(#{emp.empName},#{emp.empMail},#{emp.empGender},#{emp.deptId})
		</foreach> 

	</insert>

	<insert id="insertEmps">

		<!-- 第二种sql ： 
			出现了问题，因为默认情况下，JDBC允许一次执行多条sql，而且使用；隔开就是多条sql，为了能让数据库一次性执行多条SQL，我们需要修改连接的参数allowMultiQueries=true
			jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf-8&useUnicode=true&allowMultiQueries=true
		-->
		<foreach collection="list" separator=";" item="emp">
			INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id) VALUES (#{emp.empName},#{emp.empMail},#{emp.empGender},#{emp.deptId})
		</foreach>
	</insert>

10 、 Oracle环境下使用foreach进行批量插入操作

Oracle数据库和mysql数据库的批量新增的语法，完全不同

-- 语法1 
begin 
  INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id)VALUES(seq_2020721.nextval,'aaa','aaa@163.com',1,1);
  INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id)VALUES(seq_2020721.nextval,'bbb','aaa@163.com',1,1);
  INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id)VALUES(seq_2020721.nextval,'ccc','aaa@163.com',1,1);
  INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id)VALUES(seq_2020721.nextval,'ddd','aaa@163.com',1,1);
end ;

-- 语法2
INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id) select seq_2020721.nextval,emp_name,emp_mail,emp_gender,dept_id from(
select 'AAA' emp_name,'aaa@qq.com' emp_mail,1 emp_gender,1 dept_id from dual
union all
select 'BBB' emp_name,'aaa@qq.com' emp_mail,1 emp_gender,1 dept_id from dual
union all
select 'CCC' emp_name,'aaa@qq.com' emp_mail,1 emp_gender,1 dept_id from dual
union all
select 'DDD' emp_name,'aaa@qq.com' emp_mail,1 emp_gender,1 dept_id from dual
)

Foreach完成批量新增

	<!-- 
		public int insertEmpsOracle(List<Emp> emps);
	 -->
	<insert id="insertEmpsOracle">
		<!-- 第一种语法 -->
		 begin
			<foreach collection="list" item="emp">
				INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id)VALUES(seq_2020721.nextval,#{emp.empName},#{emp.empMail},#{emp.empGender},#{emp.deptId});
			</foreach>
		end;

	</insert>

	<insert id="insertEmpsOracle">
		<!-- 第二种语法 -->
		INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id) select seq_2020721.nextval,emp_name,emp_mail,emp_gender,dept_id from
		<foreach collection="list" close=")" open="(" separator=" union all " item="emp">
			select #{emp.empName} emp_name,#{emp.empMail} emp_mail,#{emp.empGender} emp_gender,#{emp.deptId} dept_id from dual
		</foreach>
	</insert>

11 、内置参数与databaseId

我们之前进行模糊查询，根据姓名模糊查询，在拼装sql的时候，如果参数没有传递%%，那么我们如何拼装sql呢？

<select id="selectEmpByEmpNameLike" resultType="com.wanbangee.entities.Emp">
		<!-- 
			在sql映射文件中，存在隐式参数
				- 对于单个参数来说，这个隐式参数名称就是_parameter
				- 对于多个参数来说，我们知道参数会封装成一个map，_parameter 就代表了这个map
			参数绑定:bind标签完成
				name ： 指定新的参数名
				value ： 指定参数绑定规则
				
		 -->
		 <bind name="_empName" value="'%'+_parameter+'%'"/>
		select * from emp_721 where emp_name like #{_empName}
	</select>

我们在开发的时候，可以在insert，update，delete和select标签中，指定databaseid属性，表示不同的数据执行的不同的sql，实际上sql映射文件中还存在另外一个隐式参数，_databaseId,我们可以通过此隐式参数来执行不同的SQL。

	<insert id="insertEmpsOracle" >
		<if test="_databaseId=='oracle'">
			INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id) select seq_2020721.nextval,emp_name,emp_mail,emp_gender,dept_id from
			<foreach collection="list" close=")" open="(" separator=" union all " item="emp">
				select #{emp.empName} emp_name,#{emp.empMail} emp_mail,#{emp.empGender} emp_gender,#{emp.deptId} dept_id from dual
			</foreach>
		</if>
		<if test="_databaseId == 'mysql'">
			<foreach collection="list" separator=";" item="emp">
				INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id) VALUES (#{emp.empName},#{emp.empMail},#{emp.empGender},#{emp.deptId})
			</foreach>
		</if>
	</insert>

12 、抽取可重用的sql片段

有些sql是重复的，那么我们可以抽取出来，使用sql标签定义，在使用的时候include引用抽取的sql片段：

<!-- 抽取重用的sql片段 -->
	<sql id="emp_column">
		emp_id,emp_name,emp_mail,emp_gender,dept_id
	</sql>

使用抽取的sql片段

<insert id="insertEmpsOracle" >
	<if test="_databaseId=='oracle'">
			INSERT INTO emp_721(<include refid="emp_column"></include>) select seq_2020721.nextval,<include refid="emp_column"></include> from
			<foreach collection="list" close=")" open="(" separator=" union all " item="emp">
				select #{emp.empName} emp_name,#{emp.empMail} emp_mail,#{emp.empGender} emp_gender,#{emp.deptId} dept_id from dual
			</foreach>
		</if>
		<if test="_databaseId == 'mysql'">
			<foreach collection="list" separator=";" item="emp">
				INSERT INTO emp(<include refid="emp_column"></include>) VALUES (#{emp.empName},#{emp.empMail},#{emp.empGender},#{emp.deptId})
			</foreach>
		</if>
	</insert>