Mybatis 动态SQL

MyBatis动态sql

目录

• 什么是动态sql
• IF 条件判断  ( where 标签)
• Trim 字符串拼接   (set 标签)
• choose (选择分支)
• foreach 集合遍历

什么是动态sql

MyBatis 的强大特性之一便是它的动态 SQL，它极大的简化了我们拼接SQL的操作。
动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。
MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素：

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

if 条件判断

<!-- public abstract LinkedList<Employee> findEmpByEmp(Employee emp);
     通过Employee对象查询员工，查询条件中携带那个字段，就把字段拼接上
  	       
     使用if标签，判断参数 Employee中有那个字段存在
-->
 <select id="findEmpByEmp" resultType="com.mybatis.bean.Employee">
  select * 
  from Employee  
  where
  <!--
  	test :判断条件表达式 (条件表达式为真，就会加上标签里的表达式)
  -->
  <if test="id!=null">  <!-- 判断id字段是否为null -->
  	id= #{id}
  </if>
  <if test="name!=null and name!=''">
   	and name = #{name}
  </if>
  <if test="sex!=null">
   	and sex = #{sex}
  </if>
  <if test="money!=null">
   	and money = #{money}
  </if>
 </select>

如上所述，可以使用if标签判断，符合条件的拼接上，但还是会遇到问题，如果第一个字段为null导致字符串没有拼接上

select * from Employee where and sex = ? 
jdbc4.MySQLSyntaxErrorException：缺失表达式

问题解决方式

• 解决方式一：给where添加永真条件 1 = 1 ，if里边所有条件开头都添加 and 或 or

<select id="findEmpByEmp" resultType="com.mybatis.bean.Employee">
  select * 
  from Employee  
  where 1=1
  <!--
  	test :判断条件表达式 (条件表达式为真，就会加上标签里的表达式)
  -->
  <if test="id!=null">  <!-- 判断id字段是否为null -->
  	and id= #{id}
  </if>
  <if test="name!=null and name!=''">
   	and name = #{name}
  </if>
  <if test="sex!=null">
   	and sex = #{sex}
  </if>
  <if test="money!=null">
   	and money = #{money}
  </if>
 </select>

• 解决方式二：mybatis中添加<where>标签 将所有查询条件包括在内

<!-- public abstract LinkedList<Employee> findEmpByEmp(Employee emp);
  	  通过Employee对象查询员工，查询条件中携带那个字段，就把字段拼接上
  	       
  	使用if标签，判断参数 Employee中有那个字段存在
-->
<select id="findEmpByEmp" resultType="com.mybatis.bean.Employee">
  select * 
  from Employee  
   
  <!-- 使用where标签 （使用了where标签，SQL语句中就不能用where啦）-->
  <where>
	<!--
	 test :判断条件表达式 (条件表达式为真，就会加上标签里的表达式)
	 -->
	<if test="id!=null">  <!-- 判断id字段是否为null -->
	   	and id= #{id}
	</if>
	<if test="name!=null and name!=''">
		and name = #{name}
	</if>
	<if test="sex!=null">
		and sex = #{sex}
	</if>
	<if test="money!=null">
		and money = #{money}
	</if>
  </where>
</select>

PS:Mybatis会动态的配置WHERE关键字。
只有在一个以上if 条件满足的情况下才去插入"WHERE"子句，且将where标签中拼接的SQL开头多余的AND / OR 去掉。

trim标签

mybatis的trim标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。

属性	描述
prefix	给sql语句拼接的前缀
suffix	给sql语句拼接的后缀
prefixOverrides	去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
suffixOverrides	去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

<!--
   更新方法，参数Employee中有哪些字段，就修改哪些字段
   public abstract void updateEmp(Employee emp);
-->
<update id="updateEmp">
  update employee set 
  		
  <if test="name!=null and name!='' ">
  	name = #{name},
  </if>
  <if test="money!=null">
  	money = #{money},
  </if>
  <if test="sex!=null">
  	sex = #{sex}
  </if>
  	
  where id = #{id}
</update>

如上，可以使用if标签判断哪些字段存在，将SQL拼接上，如果所有的if标签没有全部满足则会产生多余的逗号，从而发生语法错误

update employee set money = ?, where id = ? 
MySQLSyntaxErrorException：语法错误: ,

• 第一种解决方法：使用trim标签去除多余的逗号

<!--
 更新方法，参数Employee中有哪些字段，就修改哪些字段
 public abstract void updateEmp(Employee emp);
-->
<update id="updateEmp">
 update employee set 
  	
   <!--
        prefix:在sql语句中添加前缀
        suffix:在sql语句中添加后缀
        suffixOverrides:后缀重写 (将多余的逗号去除)
    -->
   <trim prefix="" suffix="" prefixOverrides="" suffixOverrides=",">
  	 <if test="name!=null and name!='' ">
  	   name = #{name},
  	 </if>
  	 <if test="money!=null">
  	   money = #{money},
  	 </if>
  	 <if test="sex!=null">
  	   sex = #{sex}
  	 </if>
   </trim>
  	 where id = #{id} 
  </update>

• 第二种解决方法：使用set标签 (动态的配置SET 关键字，和剔除追加到条件末尾的任何不相关的逗号。)

<update id="updateEmp">
 update employee 
   <set>
  	 <if test="name!=null and name!='' ">
  	   name = #{name},
  	 </if>
  	 <if test="money!=null">
  	   money = #{money},
  	 </if>
  	 <if test="sex!=null">
  	   sex = #{sex}
  	 </if>
   </set>
  	 where id = #{id} 
  </update>

PS:使用了set标签，sql中不能在用 where了

choose分支结构

<!-- 
  通过Employee对象查询员工，Employee中有那个字段，就用那个字段进行查询
  public abstract LinkedList<Employee> findEmpbyChoose(Employee emp);
-->
<select id="findEmpbyChoose" resultType="com.mybatis.bean.Employee">
  select * from employee where
   		
  <!-- choose 分支结构 
   只会进入一个when条件中 
  -->
  <choose>
   	<when test="id!=null">
   		id = #{id}
   	</when>
   	<when test="name!=null">
   		name = #{name}
   	</when>
   			
   	<!-- 以上都不满足，则执行，代表默认 -->
   	<otherwise>
   		sex = 'nv'
   	</otherwise>
  </choose>
</select>

foreach 循环

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

foreach标签的属性

属性	描述
collection	表示迭代集合的名称 （通常集合参数会被mybatis封装成map集合，指定该集合的key）
item	表示集合中每一个元素进行迭代时的别名
index	用于表示在迭代过程中，每次迭代到的位置
separator	表示在每次进行迭代之间以什么符号作为分隔符
open	表示该语句以什么开始，最常用的是左括弧’(’，注意:mybatis会将该字符拼接到整体的sql语句之前，并且只拼接一次
close	表示该语句以什么结束，最常用的是右括弧’)’，注意:mybatis会将该字符拼接到整体的sql语句之后

<!-- 
 根据id批量删除员工
 public abstract void deleteEmps(List<Integer> list);
 	
 参数是集合，mybatis会将参数做特殊处理，封装成一个map集合
 	参数是list集合，默认key是 list
 	参数是set集合，默认key是set
 	参数是数组，默认key是array
-->
<delete id="deleteEmps">
  <!-- delete from employee where id in(1,2,3) 
  	删除employee表中id为 1,2,3 的员工
  -->
  delete from employee where id in
  	
  <!-- 
  	因为参数是一个list集合，所以使用foreach 循环标签，将集合中的元素取出来
  	collection:指定遍历的集合
  	item: 每次遍历的元素
  	open: 在开始执行前加上前缀  ( 
        close: 循环结束后加上后缀 )
  	separator:指定每次循环后的分隔符(最后一次循环不会加上分隔符)
  -->
  <foreach collection="list" item="ids" open="(" close=")" separator=",">
  		#{ids}
  </foreach>	
  
</delete>