动态 SQL

动态 SQL

我们都知道，有时候需要根据业务逻辑去拼装 SQL 语句是一件很麻烦的事，因为除了拼装以为，还需要判断最后是否有多余的 ，and，or 等导致语法错误的字符，而且得保证空格才不会导致 SQL 语句挤到一起。利用 MyBatis 的动态 SQL 就能摆脱苦恼。

if

如果传入的参数是一个实体类，并且当实体类的某个属性不为 null 的时候就加入 where 条件。

 	<select id="selectEmpByEmp" resultType="com.nhky.entities.Emp">
	 	select * from emp where 
	 	<if test="empName != null and empName != ''">
	 		emp_name = #{empName}
	 	</if>
	 	<if test="empMail != null and empMail != ''">
	 		and emp_mail = #{empMail}
	 	</if>
	 	<if test="deptId != null">
	 		and dept_id = #{deptId}
	 	</if>
	 </select>

上面的代码的意思是，如果 empName 不为 null 或空串，就设定 emp_name 的查询条件为指定的值。其他 if 标签也是类似的意思。

where

 	<select id="selectEmpByEmp" resultType="com.nhky.entities.Emp">
	 
	 	select * from emp 
        <where>
            <if test="empName != null and empName != ''">
                emp_name = #{empName}
            </if>
            <if test="empMail != null and empMail != ''">
                and emp_mail = #{empMail}
            </if>
            <if test="deptId != null">
                and dept_id = #{deptId}
            </if>
        </where>
	 </select>

where 标签就代替了上面例子我们 sql 的 where 关键字，并且在有些时候，比较智能。例如我们的 where 标签没有内容，那么 where 的关键字是不会添加进去导致语句出错，省去了我们的判断。

trim

trim 用于字符串截取，上面的程序有问题，如果说，我们的 empName 为 null，而后面 empMail 不为 null，那么 sql 语句就会变成这样

• select * from emp and emp_mail = ?

sql 语句就会报错。那么如何解决？采用 trim 标签字符串截取

	<select id="selectEmpByEmp" resultType="com.nhky.entities.Emp">
	 
	 	select * from emp 
        <where>
            <trim prefix="" suffix="" prefixOverrides="and" suffixOverrides="">
                <if test="empName != null and empName != ''">
                    emp_name = #{empName}
                </if>
                <if test="empMail != null and empMail != ''">
                    and emp_mail = #{empMail}
                </if>
                <if test="deptId != null">
                    and dept_id = #{deptId}
                </if>
            </trim>
        </where>
	 </select>

• prefix : 添加前缀
• suffix ： 添加后缀
• prefixOverrides ： 前缀覆盖
  • prefixOverrides=“abc” 表示如果 trim 标签中拼凑的 sql 语句以 abc 开头，则用空串将 abc 覆盖
• suffixOverrides ： 后缀覆盖
  • suffixOverrides=“and” 表示如果 trim 标签中拼凑的 sql 语句 最后以 and 结尾，则用空串将 and 覆盖

choose

假如传入的参数还是一个实体类 emp，choose 标签用于分支选择，类似于 if-else if-else if - else… 语句。

	 <select id="selectEmpByEmp2" resultType="com.nhky.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>
	 			<otherwise>
	 				1=1
	 			</otherwise>
	 		</choose>
	 	</where>
	 </select>

查询员工信息，要求传入的参数为 Emp 对象，如果 empName 不为空，则根据empName 查询，并且其他条件不再判断。如果empMail不为空，则根据 empMail 查询，其他条件不再判断，以此类推。这个需求的要求表示查询条件只有一个，我们使用 if 是完成不了了，只能使用choose分支标签。

otherwise 标签用于在其他 when 标签不能的时候，垫底的。整个 choose 类似于 if-else if - else if- else 一样。

set

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

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

并且 set 标签很智能，在上面的程序中，如果 deptId 为 null，那么会多余一个 ，，但是 set 标签会自动去除，和 trim 很像（这里也可以用 trim 实现）。

foreach

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

	 <select id="selectEmpByEmpIds" resultType="com.wanbangee.entities.Emp">
	 	select * from emp where emp_id in
	 	
	 	<foreach collection="ids" open="(" close=")" separator="," item="id">
	 		#{id}
	 	</foreach>
	 </select>

• collection : 表示要遍历的集合,
  • 如果传入的参数是List类型，那么可以使用list或者collection，如果传入是Set集合，只能使用collection
  • 实际上我们在接口的方法中使用@Param指定collection属性使用的值 ,指定之后，能使用指定的内容和param1…
  • open : 前缀添加
  • close：后缀添加
  • separator ： 每次遍历的分割符
  • index：遍历次数
  • item : 每次遍历的数据赋值的变量

foreach 批量新增

第一种方法

	<insert id="insertEmp" >
		insert into emp values 
			<foreach collection="list" open="" close="" separator="," item="emp">
				(null,#{emp.empName},#{emp.empMail},#{emp.empGender},#{emp.deptId})
			</foreach>
	</insert>

第二种方法

	<insert id="insertEmp2" >
			<foreach collection="list" open="" close="" separator="," item="emp">
				insert into emp values (null,#{emp.empName},#{emp.empMail},#{emp.empGender},#{emp.deptId})
			</foreach>
	</insert>	

内置参数（多数据库支持）

我们在开发的时候，可以在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>

抽取重用 sql 片段

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

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

使用抽取的 sql 片段，用 include 标签 <include refid="emp_column"></include>

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