7.动态Sql语句

1、动态SQL:if 语句

我们可以发现，如果 #{name} 为空，那么查询结果也是空，如何解决这个问题呢？使用 if 来判断

 1 <select id="selectByWhere1" resultType="com.zhiyou100.wc.bean.Users">
 2           select 
 3           <include refid="usercolumns"></include>
 4           from users 
 5           <if test="name!=null ">
 6                where name=#{name}
 7           </if>
 8           <if test="sex!=null and sex!=''">
 9               and sex=#{sex}
10           </if>   
11     </select>

　　这样写我们可以看到，如果 sex 等于 null，那么查询语句为 select * from users where name=#{name},但是如果name 为空呢？那么查询语句为 select * from users where and sex=#{sex}，这是错误的 SQL 语句，如何解决呢？请看下面的 where 语句

2、动态SQL:if+where 语句

 1 <!--解决：如果第二个条件满足，第一个条件不满足。那么就是导致sql语句出错。 
 2         if+where：如果第一个条件中 where 并且去掉and
 3      -->
 4     <select id="selectByWhere2" resultType="com.zhiyou100.wc.bean.Users">
 5           select 
 6           <include refid="usercolumns"></include>
 7           from users 
 8           <where>
 9               <if test="name!=null ">
10                    and name=#{name}
11               </if>
12               <if test="sex!=null and sex!=''">
13                   and sex=#{sex}
14               </if>   
15           </where>
16     </select>

这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉

3、动态SQL:if+set 语句

同理，上面的对于查询 SQL 语句包含 where 关键字，如果在进行更新操作的时候，含有 set 关键词，我们怎么处理呢？

 1 <!-- if+set
 2         如果传来的字段为null，那么保留原来的内容
 3      -->
 4     <update id="updateUser" parameterType="com.zhiyou100.wc.bean.Users">
 5         update users 
 6         <set>
 7             <if test="name!=null">
 8             name=#{name},
 9             </if>
10             <if test="sex!=null">
11             sex=#{sex},
12             </if>
13             <if test="age!=0">
14             age=#{age},
15             </if>
16         </set>
17         where id=#{id}
18     </update>

4、动态SQL:choose(when,otherwise) 语句

有时候，我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch 语句

 1 <!-- choose+when+otherwise -->
 2     <select id="selectByWhere3" resultType="com.zhiyou100.wc.bean.Users">
 3           select 
 4           <include refid="usercolumns"/>
 5           from users 
 6           <where>
 7               <choose>
 8                   <when test="name!=null ">
 9                         name like concat('%',#{name},'%')
10                   </when>
11                   <when test="sex!=null and sex!=''">
12                       sex=#{sex}
13                   </when>
14                   <otherwise>
15                       age>=#{age}
16                   </otherwise>
17              </choose>    
18           </where>
19     </select>

也就是说，这里我们有三个条件，id,name,sex，只能选择一个作为查询条件

　　　　如果 id 不为空，那么查询语句为：select * from users where id=?

　　　　如果 id 为空，那么看name 是否为空，如果不为空，那么语句为 select * from users where name=?;

　　　　　　　　　　如果 name 为空，那么查询语句为 select * from users where sex=?

5、动态SQL:trim 语句

　　trim标记是一个格式化的标记，可以完成set或者是where标记的功能

　　①、用 trim 改写上面第二点的 if+where 语句

 1 <!-- trim -->
 2     <select id="selectByWhere4" resultType="com.zhiyou100.wc.bean.Users">
 3           select 
 4             <include refid="usercolumns"/>
 5           from users 
 6           <!-- 
 7             prefix:把trim中返回的字符串前添加一个set
 8             prefixOverrides：覆盖trim 中返回的字符串的前缀为and | or
 9             suffix:把trim中返回的字符串后添加一个set
10             suffixOverrides:覆盖trim 中返回的字符串的后缀
11          -->
12           <trim prefix="where" prefixOverrides="and / or">
13               <if test="name!=null ">
14                     name=#{name}
15               </if>
16               <if test="sex!=null and sex!=''">
17                   and sex=#{sex}
18               </if>   
19           </trim>
20     </select>

②、用 trim 改写上面第三点的 if+set 语句

 1 <!-- trim -->
 2     <update id="updateUser2" parameterType="com.zhiyou100.wc.bean.Users">
 3         update users 
 4         <!-- 
 5             prefix:把trim中返回的字符串前添加一个set
 6             prefixOverrides：覆盖trim 中返回的字符串的前缀为and | or
 7             suffix:把trim中返回的字符串后添加一个set
 8             suffixOverrides:覆盖trim 中返回的字符串的后缀
 9          -->
10         <trim prefix="set" suffixOverrides=",">
11             <if test="name!=null">
12             name=#{name},
13             </if>
14             <if test="sex!=null">
15             sex=#{sex},
16             </if>
17             <if test="age!=0">
18             age=#{age},
19             </if>
20         </trim>
21         where id=#{id}
22     </update>

6、动态SQL: SQL 片段

有时候可能某个 sql 语句我们用的特别多，为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用

1     <sql id="usercolumns">
2         id,name,age,sex
3     </sql>

引用 sql 片段

 1 <select id="selectByWhere1" resultType="com.zhiyou100.wc.bean.Users">
 2           select 
 3           <include refid="usercolumns"></include>
 4           from users 
 5           <if test="name!=null ">
 6                where name=#{name}
 7           </if>
 8           <if test="sex!=null and sex!=''">
 9               and sex=#{sex}
10           </if>   
11     </select>

注意：①、最好基于单表来定义 sql 片段，提高片段的可重用性

　　　　　②、在 sql 片段中不要包括 where

7、动态SQL: foreach 语句

　需求：我们需要查询 user 表中 id 分别为1,2,3的用户

　　sql语句：delete from users where id=1 or id=2 or id=3

　　　　　　 delete from users where id in (1,2,3)

1、我们用 foreach 来改写 delete from users where id in (1,2,3)

 1 <!-- delete通过foreach -->
 2      <delete id="deleteByIds">
 3         delete from users where id in
 4         <!--
 5                 collection:指定输入对象中的集合属性
 6                 item:每次遍历生成的对象
 7                 open:开始遍历时的拼接字符串
 8                 close:结束时拼接的字符串
 9                 separator:遍历对象之间需要拼接的字符串
10               -->
11         <foreach collection="ids" open="(" close=")" separator="," item="id">
12             #{id}
13         </foreach>
14         
15     </delete>

2、我们用 foreach 来改写 delete from users where id=1 or id=2 or id=3

 1 <delete id="deleteByIds2">
 2         delete from users  
 3         <!--
 4             collection:指定输入对象中的集合属性
 5             item:每次遍历生成的对象
 6             open:开始遍历时的拼接字符串
 7             close:结束时拼接的字符串
 8             separator:遍历对象之间需要拼接的字符串
 9             delete from users where id=1 or id=2 or id=3
10           -->
11         <foreach collection="ids2" open="where" separator="or" item="id">
12             id=#{id}
13         </foreach>
14         
15     </delete>

测试：

 1 /**
 2      * delete in()
 3      */
 4     @Test
 5     void testdeleteByIds() {
 6         List<Integer> ids=new ArrayList<>();
 7         ids.add(7);
 8         ids.add(8);
 9         usersDao.deleteByIds(ids);
10     }
11     /**
12      * delete or
13      */
14     @Test
15     void testdeleteByIds2() {
16         List<Integer> ids2=new ArrayList<>();
17         ids2.add(6);
18         ids2.add(9);
19         usersDao.deleteByIds2(ids2);
20     }

3.模糊查询

1.UsersMapper.xml

 1 <!-- choose+when+otherwise  模糊查询 -->
 2     <select id="selectByWhere3" resultType="com.zhiyou100.wc.bean.Users">
 3           select 
 4           <include refid="usercolumns"/>
 5           from users 
 6           <where>
 7               <choose>
 8                   <when test="name!=null ">
 9                         name like concat('%',#{name},'%')
10                   </when>
11                   <when test="sex!=null and sex!=''">
12                       sex=#{sex}
13                   </when>
14                   <otherwise>
15                       age>=#{age}
16                   </otherwise>
17              </choose>    
18           </where>
19     </select>

2.Usersdao

1 public List<Users> selectByWhere3(Users users);

3.测试

 1     /**
 2      * 模糊查询
 3      */
 4     @Test
 5     void testSelectByWhere3() {
 6         Users users= new Users();
 7         users.setName("晨");
 8         users.setSex("男");
 9         List<Users> list=usersDao.selectByWhere3(users);
10         System.out.println(list);
11     }