MaBatis八：动态SQL

动态SQL

引入

1. 现实中的某种情况

   

2. 存在问题

   select * from t_user where name = ? and age = ? and email = ? and gender = ?
   

   根据多个特定条件拼装SQL语句，每个条件之间需要有and字符或者逗号，如上图，四个条件有的可能是空着的，那么这个条件前或者后的and如何自动屏蔽掉

3. 解决问题

   MyBatis使用了一系列标签解决上述SQL拼接问题

一、if标签

1. 特性

   根据标签中的test属性所对应的表达式决定标签中的内容是否需要拼接到SQL语句中

2. 核心点 where 1=1

   可以保证某个if语句不插入时，则不会引发由and造成SQL语法错误

3. mapper接口

   public interface DynamicMapper {
       List<Emp> getEmpCondition(Emp emp);
   }
   

4. mapper映射文件

   <mapper namespace="com.atguigu.mybatis.mapper.DynamicMapper">
   <!--    List<Emp> getEmpCondition();-->
       <!-- 在if标签中，可以直接使用属性名 -->
       <select id="getEmpCondition" resultType="Emp">
           select * from t_emp where 1=1
           <if test="empName != null and empName != ''">
               and emp_name = #{empName}
           </if>
           <if test="age != null and age != ''">
               and age = #{age}
           </if>
           <if test="sex != null and sex != ''">
               and sex = #{sex}
           </if>
           <if test="email != null and email != ''">
               and email = #{email}
           </if>
       </select>
   </mapper>
   

5. 测试

   @Test
   public void denamicTest() {
       SqlSession sqlSession = SqlSessionUtil.getSqlSession();
       DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class);
       List<Emp> list = mapper.getEmpCondition(new Emp(null,"张三",23,'男',"zs@163.com"));
       System.out.println(list);
   }
   }
   

二、where标签

1. 特性

   当where标签中有内容时，会自动生成where关键字，并且将内容前多余的and或or去掉

   当where标签中没有内容时，此时where标签不会生成where关键字

   注：

   ​ where标签不能将内容后多余的and或or去掉

2. mapper映射文件

   <mapper namespace="com.atguigu.mybatis.mapper.DynamicMapper">
   <!--    List<Emp> getEmpCondition();-->
       <select id="getEmpCondition" resultType="Emp">
           select * from t_emp
           <where>
               <if test="empName != null and empName != ''">
                    and emp_name = #{empName} 
               </if>
               <if test="age != null and age != ''">
                    and age = #{age} 
               </if>
               <if test="sex != null and sex != ''">
                    and sex = #{sex} 
               </if>
               <if test="email != null and email != ''">
                    and email = #{email} 
               </if>
           </where>
       </select>
   </mapper>
   

三、trim

1. 属性

   prefix：在trim标签中的内容之前加上指定内容

   suffix：在trim标签中的内容之后加上指定内容

   prefixOverrides：将trim标签中的内容之前指定的内容去掉

   suffixOverrides：将trim标签中的内容之后指定的内容去掉

2. mapper映射文件

   <!--    List<Emp> getEmpCondition();-->
   <select id="getEmpCondition" resultType="Emp">
       select * from t_emp
       <trim prefix="where" suffixOverrides="and|or">
           <if test="empName != null and empName != ''">
               emp_name = #{empName} and
           </if>
           <if test="age != null and age != ''">
               age = #{age} and
           </if>
           <if test="sex != null and sex != ''">
               sex = #{sex} and
           </if>
           <if test="email != null and email != ''">
               email = #{email} and
           </if>
       </trim>
   </select>
   

四、choose 、when 、otherwise

1. 特性

   整体效果相当于if …elseif…else，其中when是if、elseif；otherwise是else

2. 要求

   when至少要有一个，otherwise最多只能有一个

3. mapper映射文件

   <select id="getEmpByChoose" resultType="emp">
       select * from t_emp
       <where>
       <choose>
       <when test="empName != null and empName != ''">
       emp_name = #{empName}
       </when>
       <when test="age != null and age != ''">
       age = #{age}
       </when>
       <when test="sex != null and sex != ''">
       sex = #{sex}
       </when>
       <when test="email != null and email != ''">
       email = #{email}
       </when>
       <otherwise>
       eid = 6
       </otherwise>
       </choose>
       </where>
   </select>
   

五、foreach - - 批量删除

1. 特性

   collection：表示要循环的集合或数组

   item：表示数组中的每一个元素

   separator：循环体之间的分隔符

   open/close：标签所循环的所有内容的开始符/结束符

2. mapper映射文件

   <!-- 方式一 -->
   <delete id="deleteByForeach">
       delete from t_emp where eid in
       <foreach collection="eids" item="eid" separator="," open="(" close=")">
           #{eid}
       </foreach>
   </delete>
   
   <!-- 方式二 -->
   <delete id="deleteByForeach">
       delete from t_emp where
       <foreach collection="eids" item="eid" separator="or" >
           eid = #{eid}
       </foreach>
   </delete>
   

3. 测试

   @Test
   public void deleteTest() {
       SqlSession sqlSession = SqlSessionUtil.getSqlSession();
       DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class);
       int result = mapper.deleteByForeach(new Integer[]{6, 7, 8, 9});
       System.out.println("result = " + result);
   
   }
   

六、foreach - - 批量添加

1. mapper映射文件

   <!--    int insertByForeach(@Param("emps") List<Emp> emps);-->
   <insert id="insertByForeach">
       insert into t_emp values
       <foreach collection="emps" item="emp" separator=",">
           (null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
       </foreach>
   </insert>
   

2. 测试

   @Test
       public void insertTest() {
           SqlSession sqlSession = SqlSessionUtil.getSqlSession();
           DynamicMapper mapper = sqlSession.getMapper(DynamicMapper.class);
           Emp emp1 = new Emp(null, "a", 23, '女', "a@qq.com");
           Emp emp2 = new Emp(null, "a", 23, '女', "a@qq.com");
           Emp emp3 = new Emp(null, "a", 23, '女', "a@qq.com");
           Emp emp4 = new Emp(null, "a", 23, '女', "a@qq.com");
           List<Emp> emps = Arrays.asList(emp1, emp2, emp3, emp4);
           int result = mapper.insertByForeach(emps);
           System.out.println("result = " + result);
       }
   

七、sql标签

1. 设置sql片段

   <sql id="empColumns">eid,emp_name,age,sex,email</sql>
   

2. 引用sql片段

   <include refid="empColumns"></include>
   // 使用举例
   <select id = "getEmpCondition" resultType="Emp">
   	select <include refid="empColumns"></include> from t_emp
   </select>