动态 SQL(1)
阅读原文时间:2023年07月10日阅读:3

使用动态 SQL 完成多条件查询

  动态 SQL 是 MyBatis 的一个强大的特性。在使用 JDBC 操作数据时,如果查询条件特别多,将条件串联成 SQL 字符串是一件痛苦的事情,通常的解决方法是写很多的 if-else 条件语句对字符串进行拼接,并确保不能忘了空格或在字段的最后省略逗号。 MyBatis 使用一种强大的动态 SQL 语言来改善这种情形。动态 SQL 基于 OGNL 的表达式,可使我们方便地在 SQL 语句中实现某些逻辑。

  用于实现动态 SQL 的元素如下:
      ➣ if:利用 if 实现简单的条件选择。

      ➣ choose(when,otherwise):相当于 Java 中的 switch 语句,通常与 when 和 otherwise 搭配。

      ➣ where:简化 SQL 语句中 where 的条件判断。

      ➣ set:解决动态更新语句。

      ➣ trim:可以灵活地去除多余的关键字。

      ➣ foreach:迭代一个集合,通常用于 in 条件。

  说明:由于在进行多条件查询的时候,用户并不一定会完整地输入所有的查询条件,因此对于此类情况,要使用 MyBatis 的动态 SQL 来实现多条件查询。

if 元素

 


  说明:仅仅使用一个 if 元素,当查询条件 where 后面没有固定条件,无法处理多余的 and ,or 等关键字。且当不输入查询条件时,多余的 where 关键字也无法处理。为了智能处理 and、or、where 等关键字,if 元素应配合 where 元素一起使用。

使用  if+where 实现多条件查询

where 元素

    where 元素主要用来简化 SQL 语句中的 where 条件判断,并能智能地处理 and 和 or,不必担心多余关键字导致的语法错误。
    where 元素标签会自动识别其标签内是否有返回值,若有返回值,就插入一个 where 。此外,若该标签返回的内容是以 and或者or 开头的,会自动剔除。

//案例:根据用户名称(模糊查询)和用户 id 查询用户列表,且用户列表不需要显示角色名称,显示角色 id 即可,即不用进行连表查询。

/\*\*  
 \* 根据用户名称(模糊查询)和用户角色查询用户列表  
 \* @param userName 用户名称  
 \* @param userRole 用户角色  
 \* @return  
 \*/  
public List<User> getUserList(@Param("uName")String userName,@Param("uRole")Integer userRole); 


@Test  //测试根据用户名称(模糊查询)和用户角色查询用户列表  
public void testGetUserList() {  
    SqlSession session=null;  
    List<User> userList = new ArrayList<User>();  
    try {  
        session=MyBatisUtil.getSqlSession();  
        String userName = null;  
        Integer userRole =null;  
        userList = session.getMapper(UserMapper.class).getUserList(userName, userRole);  
    } catch (Exception e) {  
        e.printStackTrace();  
    }finally{  
        MyBatisUtil.close(session);  
    }

    System.out.println("userlist.size ----> " + userList.size());  
    for (User user : userList) {  
        System.out.println(user);  
    }  
}

  说明:查询条件不是很多(一般不超过四个)且较为固定的情况下,最好采用多参数直接入参的方式(即 @Param 实现多参数入参),这样代码比较清晰,可读性强。

使用 if+trim 实现多条件查询

trim 元素

在 MyBatis 中处了使用 if+where 实现多条件查询,还有一个更为灵活的元素 trim 可以替代之前的做法。

      trim 元素也会自动识别其标签内是否有返回值,若有返回值,会在自己包含的内容前加上某些前缀,也可在其后加上某些后缀,与之对应的属性是 prefix 和 suffix;也可把包含内容的首部某些内容覆盖(即忽略),或者把尾部的某些内容覆盖,与之对应的属性是 prefixOverrides 和 suffixOverrides;正因为 trim 有这样强大的功能,我们可以利用 trim 来替代 where 元素,并实现与 where 元素相同的效果。

  trim 的属性

    prefⅸ:前缀,作用是通过自动识别是否有返回值后, 在 trim 包含的内容上加上前缀。

    suffix:后缀,作用是在 trim 包含的内容上加上后缀。

    prefixOverrides:对于 trim 包含内容的首部(前缀)进行指定内容的忽略/删除。

    suffixOverrides:对于 trim 包含内容的尾部(后缀)进行指定内容的忽略/删除。

//修改上面示例中 UserMapper.xml 中的代码,使用 if+trim 达到相同的效果。

<!-- 根据用户名称(模糊查询)和用户角色查询用户列表 -->  
<select id="getUserList" resultType="user">  
    SELECT \* FROM \`smbms\_user\`  
    <trim prefix="where" prefixOverrides="and | or">  
        <if test="uRole!=null">  
            AND userRole=#{uRole}  
        </if>  
        <if test="uName!=null and uName!=''">  
            AND userName LIKE CONCAT('%',#{uName},'%')  
        </if>  
    </trim>  
</select>

使用动态 SQL 实现更新操作

  修改用户信息操作,在实际项目中,用户在进行信息的更新操作时,并不一定所有的数据都会进行修改,对于用户没有修改的数据,数据库不需要进行相应的更新操作。即更新用户表数据时,若某个参数传入值为 null 时,不需要 set 该字段。若要实现此需求,使用动态 SQL 中的 set 元素来处理。

  set 元素

   set 元素主要用于更新操作,它的主要功能和 where 元素差不多,主要是在包含的语句前输出一个 set ,若包含的语句是以逗号结束的,会自动把该逗号忽略掉,再配合 if 元素就可以动态地更新需要修改的字段;而不需修改的字段,则可以不再被更新。

   使用 set 标签不仅可以动态地配置 set 关键字,还可剔除追加到条件末尾的任何不相关的逗号。

//案例:实现根据用户 id 修改用户信息的操作,要求不需要修改的字段,则可以不进行更新。

/**
* 修改操作:实现根据用户 id 修改用户信息的操作
* @param user 对象入参
* @return
*/
public int update(User user);

<!--修改操作:实现根据用户 id 修改用户信息的操作 -->  
<update id="update" parameterType="user">  
    UPDATE \`smbms\_user\`  
    <set>  
        <if test="userCode != null">userCode=#{userCode},</if>  
        <if test="userName != null">userName=#{userName},</if>  
        <if test="userPassword != null">userPassword=#{userPassword},</if>  
        <if test="gender != null">gender=#{gender},</if>  
        <if test="birthday != null">birthday=#{birthday},</if>  
        <if test="phone != null">phone=#{phone},</if>  
        <if test="address != null">address=#{address},</if>  
        <if test="userRole != null">userRole=#{userRole},</if>  
        <if test="modifyBy != null">modifyBy=#{modifyBy},</if>  
        <if test="modifyDate != null">modifyDate=#{modifyDate},</if>  
    </set>  
    where id = #{id}  
</update>

@Test // 测试修改操作:实现根据用户 id 修改用户信息的操作
public void testUpdate() {
SqlSession session=null;
int count = 0;// 返回执行 SQL 影响的行数
try {
session=MyBatisUtil.getSqlSession();
User user = new User();
user.setId(16);
user.setUserName("测试用户修改");
user.setAddress("地址测试修改");
user.setModifyBy(1);
user.setModifyDate(new Date());
count = session.getMapper(UserMapper.class).update(user);
session.commit(); // 提交事务(MyBatisUtil 中设置了不自动提交事务,因此手动提交)
} catch (Exception e) {
e.printStackTrace();
session.rollback();// 发生异常就回滚
count = 0;
}finally{
MyBatisUtil.close(session);
}
System.out.println("执行 update 影响行数:" + count);
}

使用 if+trim 改造修改操作

使用 trim 元素来替代 set 元素,并实现与 set 一样的效果。

<!--修改操作:实现根据用户 id 修改用户信息的操作 -->  
<update id="update" parameterType="user">  
    UPDATE \`smbms\_user\`  
    <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">  
        <if test="userCode != null">userCode=#{userCode},</if>  
        <if test="userName != null">userName=#{userName},</if>  
        <if test="userPassword != null">userPassword=#{userPassword},</if>  
        <if test="gender != null">gender=#{gender},</if>  
        <if test="birthday != null">birthday=#{birthday},</if>  
        <if test="phone != null">phone=#{phone},</if>  
        <if test="address != null">address=#{address},</if>  
        <if test="userRole != null">userRole=#{userRole},</if>  
        <if test="modifyBy != null">modifyBy=#{modifyBy},</if>  
        <if test="modifyDate != null">modifyDate=#{modifyDate},</if>  
    </trim>  
</update>