mybatis学习笔记(8):动态sql

刺骨的言语ヽ痛彻心扉 2022-05-21 06:52 235阅读 0赞

MyBatis 的强大特性之一便是它的动态 SQL。  
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。

--------------------

我们以 tbl\_employee表为例来说明：  
![这里写图片描述][70]

--------------------

## 1. 动态SQL:if 语句 ##

根据 lastName 和 gender 来查询数据。如果 lastName 为空，那么将不使用lastName 过滤查询；如果 gender 为空，那么将不使用gender 过滤查询；  
**首先不使用 动态SQL 来书写**

<select id="getEmpsByConditionIf"
        resultType="com.mybatis.domain.Employee">
    
        select * from tbl_employee
        where last_name like #{lastName} and gender = #{gender} 
    </select>

上面的查询语句，我们可以发现，如果 \#\{lastName\} 为空，那么过滤条件为last\_name like null,还是会使用使用lastName作为过滤条件，如何解决这个问题呢？使用 if 来判断：

<select id="getEmpsByConditionIf" resultType="com.mybatis.domain.Employee">
        select * from tbl_employee
        where 1=1
        <if test="lastName!=null">
            and last_name like #{lastName}
        </if>
        <if test="gender!=null">
            and gender = #{gender}
        </if>
    </select>

这样写我们可以看到，如果lastName等于null，那么查询将不使用last\_name 作为过滤条件。  
注意：上面where后面的 1=1 是用来统一拼接过滤条件的，因为可能会产生sql拼接语法错误的情况，比如 where and gender = xx 的情况发生。  
当然，mybatis提供了另一种解决方案，看下面。

--------------------

## 2. 动态SQL:if+where 语句 ##

<select id="getEmpsByConditionIf" resultType="com.mybatis.domain.Employee">
        select * from tbl_employee
        <where>
            <if test="lastName!=null">
                last_name like #{lastName}
            </if>
            <if test="gender!=null">
                and gender = #{gender}
            </if>
        </where>
    </select>

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

--------------------

## 3. 动态SQL:if+set 语句 ##

同理，上面的对于查询 SQL 语句使用`<where>`标签配合if标签处理查询过滤条件。那么对于更新操作，可以使用`<set>`标签配合if标签进行更新操作。

<update id="updateEmpById" parameterType="com.mybatis.domain.Employee">
        update tbl_employee
        <set>
            <if test="lastName!=null and lastName!=''">
                last_name = #{lastName},
            </if>
            <if test="gender!=null and gender!=''">
                gender = #{gender}
            </if>
        </set>
        where id=#{id}
    </update>

和`<where>`标签相似，如果`<set>`标签中有返回值的话，它就插入一个set关键字。此外，如果标签返回的内容最后是以的逗号结尾，则它会将之剔除掉。

--------------------

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

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

<select id="getEmpsByConditionChoose" resultType="com.mybatis.domain.Employee">
        select * from tbl_employee
        <where>
            <choose>
                <when test="lastName!=null">
                    last_name like #{lastName}
                </when>
                <when test="gender!=null">
                    gender = #{gender}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>
    </select>

这里有三个条件，但是只能选择最先满足的一个作为查询过滤条件。

--------------------

## 5. 动态SQL:trim 语句 ##

trim标记是一个格式化的标记，可以完成set或者是where标记的功能  
①、用 trim 改写上面第二点的 if+where 语句

<select id="getEmpsByConditionIf" resultType="com.mybatis.domain.Employee">
        select * from tbl_employee
         <trim prefix="where" prefixOverrides="and|or">
            <if test="lastName!=null">
                last_name like #{lastName}
            </if>
            <if test="gender!=null">
                and gender = #{gender}
            </if>
         </trim>
    </select>

prefix：前缀　　　　　　  
prefixoverride：去掉第一个and或者是or

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

<update id="updateEmpById" parameterType="com.mybatis.domain.Employee">
        update tbl_employee
        <trim prefix="set" suffixOverrides=",">
            <if test="lastName!=null and lastName!=''">
                last_name = #{lastName},
            </if>
            <if test="gender!=null and gender!=''">
                gender = #{gender}
            </if>
        </trim>
        where id=#{id}
    </update>

suffixoverride：去掉最后一个逗号（也可以是其他的标记）

--------------------

## 6、动态SQL: SQL 片段 ##

有时候可能某个 sql 语句我们用的特别多，为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用。  
比如：假如可以把查询过滤条件抽取出来，如下：

<sql id="selectCondition">
        <if test="lastName!=null">
            last_name like #{lastName}
        </if>
        <if test="gender!=null">
            and gender = #{gender}
        </if>
    </sql>

引用 sql 片段：

<select id="getEmpsByConditionIf" resultType="com.mybatis.domain.Employee">
        select * from tbl_employee
        <trim prefix="where" prefixOverrides="and|or">
            <include refid="selectCondition"></include>
        </trim>
    </select>

注意：  
①、最好基于 单表来定义 sql 片段，提高片段的可重用性  
②、在 sql 片段中不要包括 where

--------------------

## 7、动态SQL: foreach 语句 ##

需求：我们需要查询 user 表中 id 分别为1,2,3的用户  
sql语句：  
select \* from tbl\_employeewhere id=1 or id=2 or id=3  
或  
select \* from tbl\_employeewhere id in (1,2,3)

①、用 foreach 改写拼接 select \* from tbl\_employeewhere id=1 or id=2 or id=3  
创建接口:

public interface EmployeeMapper {
        public List<Employee> getEmpsByConditionForeach (@Param("ids")List<Integer> ids);
    }

Mapper XML文件：

<select id="getEmpsByConditionForeach" resultType="com.mybatis.domain.Employee">
        select * from tbl_employee
        <where>
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

**`<foreach>`标签属性说明：**

*  collection:指定传递过来的集合参数
 *  item:每次遍历生成的变量
 *  open:遍历开始时的拼接字符串
 *  close:遍历结束时拼接的字符串
 *  separator:遍历生成的变量之间需要拼接的字符串

测试：

@Test
    public void getEmpsByConditionForeach(){
        SqlSessionFactory factory = MyBatisUtil.getFactory();
        SqlSession session = factory.openSession();
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        //传递集合参数
        List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1,2,3));
        for (Employee emp : list) {
            System.out.println(emp);
        }
        session.commit();
        session.close();
    }

结果：  
![这里写图片描述][70 1]

执行的sql为：  
select \* from tbl\_employee where (id=1 or id=2 or id=3)

②、用 foreach 来改写 select \* from tbl\_employee where id in (1,2,3)

<select id="getEmpsByConditionForeach" resultType="com.mybatis.domain.Employee">
        select * from tbl_employee
        <where>
            <foreach collection="ids" item="id" open="id in(" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

--------------------

## 8. 动态SQL: bind语句 ##

bind 标签的作用是可以创建一个变量并将其绑定到上下文。比如：

<select id="getEmpsByConditionIf" resultType="com.mybatis.domain.Employee">
        select * from tbl_employee
        <where>
            <if test="lastName!=null and lastName!=''">
                <bind name="_lastName" value="'%'+lastName+'%'"/>
                last_name like #{_lastName}
            </if>
        </where>
    </select>

上面定义的是将参数lastName值两边拼接上%然后赋值给\_lastName

--------------------

## 9. mybatis动态sql中的两个内置参数（\_parameter和\_databaseId） ##

不只是方法传递过来的参数可以被用来判断，取值  
mybatis默认还有两个内置参数**\_parameter**和**\_databaseId**  
**\_parameter**保存了传递过来的所有参数，分两种情况：  
单个参数：\_parameter就是这个参数的值  
多个参数：参数会被封装为一个map:\_parameter就是代表这个map  
**\_databaseId**如果配置了databaseIdProvider标签，\_databaseId 就是代表当前数据库的别名Oracle

<select id="getEmpsTestInnerParameter" resultType="com.mybatis.domain.Employee">
         SELECT * FROM tbl_employee
         
         <if test="_parameter!=null">     
          where id=#{_parameter.id}   
         </if>
     </select>

--------------------

[70]: /images/20220521/534ac539f42443b08f091d258157b92f.png
[70 1]: /images/20220521/42cb2e3c1e6b4bce9f0597afc08f47cf.png