MyBatis 框架动态SQL 处理简单的多参数查询

最新推荐文章于 2024-07-08 17:54:51 发布

听封

最新推荐文章于 2024-07-08 17:54:51 发布

阅读量925

点赞数 33

文章标签： mybatis sql java 数据库 idea mysql

本文链接：https://blog.csdn.net/whs_8792/article/details/139932266

版权

一、动态SQL

1. 什么是动态SQL ？

sql的内容是变化的, 可以根据条件获取到
不同的sql语句.
主要是where条件部分发生变化。
动态sql的实现, 使用的是mybatis提供的标签来实现

2.为什么使用动态sql

使用动态sql可以解决某些功能的使用例如使用条件查询某个商品你输入价格，地区等等进行筛选，如果使用静态sql可能会查询出来的是一个空内容 但使用动态sql可以很好的解决这种问题

3.动态sql的标签

标签	说明
if	条件判断
choose(when 、otherwise)	条件判断，这是一个组合标签，需要与when、otherwise 标签搭配使用类似于java中的switch 循环
where	为SQL语句动态的添加where关键字
set	为SQL语句动态的添加set关键字,实现动态更新功能
trim	对SQL语句进行格式化处理，添加或移除前后缀
froeach	以遍历方式处理集合类型参数

3.1 if 标签

if 标签是 MyBatis 框架动态SQL技术中重要且常用的标签之一，他所实现的功能与java中的if基本相同，用法也很类似。

if 标签语法：

 <if test=" 条件判断 ， 返回true或false ">
     
    SQL语句...
   
 </if>


例如：
 <if test=" supplierId != null ">
     and supplierId = #{supplierId}
 </if>

test : 属性意为进入 if 标签需要满足的条件，当判断为 true 时，表示条件满足，进入标签实现动态拼接sql语句。

3.2 where 标签

where 标签的主要作用是对 SQL 语句中的 where 关键字进行简化处理，并可以智能得处理其内部 and 、or 等关键字 ，避免拼接多余字符所带来的语法错误。

where 标签语法：

<where>
      <if test=" 条件判断 ， 返回true或false ">
            
            SQL语句..

      </if>
</where>


例如：

 <where>
       <if test=" supCode != null">

           and supCode like CONCAT('%',#{supCode},'%')

       </if>
       <if test=" supName != null">

           and supName like CONCAT('%',#{supName},'%')

       </if>
 </where>

where 关键字的使用很简单，只要把SQL语句中的where关键字替换为<where></where>标签，然后将所有的过滤条件置于标签内部即可

3.3 choose(when、otherwise) 标签

choose标签是一个组合标签，通常与when、otherwise 标签配合使用，实现了类似于java中的switch语句的功能。

choose标签语法：

     <choose>
                <when test=" 条件判断 返回true 或 fales ">
                   SQL语句..
                </when>
                <when test=" 条件判断 返回true 或 fales  ">
                    SQL语句..
                </when>
                <otherwise>
                     SQL语句..
                </otherwise>
     </choose>


   例如：

     <choose>
                <when test="supCode != null and supCode != '' ">
                    and supCode like CONCAT('%',#{supCode},'%')
                </when>
                <when test="supName != null and supName != '' ">
                    and supName like CONCAT('%',#{supName},'%')
                </when>
                <when test="supContact != null and supContact != '' ">
                    and supContact like CONCAT('%',#{supContact},'%')
                </when>
                <otherwise>
                    and YEAR(createdTime)=YEAR(#{createdTime})
                </otherwise>
     </choose>

上述代码的 choose 标签中包含多个 when 标签和 一个 otherwise 标签 。当程序执行到 choose 标签时，只会进入 test 属性判断为 true 的第一个 when 标签，执行后便会跳出 choose 标签。如果所有 when 标签的 test 属性都为 false ，则进入 otherwise 标签。

注意：

1. 当有一个 when 标签符合条件后，其他的when 标签都不会执行了

2. 如果当所有条件都不满足时，才会执行otherwise 标签内的代码

二、MyBatis 框架动态 SQL 处理集合参数

4.1 foreach 标签处理数组类型参数

MyBatis 框架通过 foreach 标签 对这类参数进行循环处理，最终拼接成一个符合MySQL 语法的 in 语句来处理这些参数 。 foreach 标签处理 数组、list集合、Map对象

类型的语法如下：

 <foreach collection=" 参数名称 " item=" 元素别名 " open="(" separator="," close=")" index=" 
当前元素位置下标 ">
             #{ 元素别名 }
 </foreach>

foreach 标签中涉及到的属性：

item	遍历数组时，为数组或List集合中的元素起的别名
open	起始位置的拼接字符串，表示 in 语句以“（” 左括号开始
close	结束位置的拼接字符，表示表示 in 语句以“ )” 右括号结束
separator	参数名称。当参数为数组类型时，默认参数名为 array 。当参数类型为list时，默认为list。当参数类型为 Map时，参数名为Map 中集合元素所在键值对的 key.

 1.当参数为 array 数组 时
 <foreach collection="array" item="item" open="(" separator="," close=")">
             #{item}
 </foreach>

 2.当参数为 list 集合 时
 <foreach collection="list" item="item" open="(" separator="," close=")">
             #{item}
 </foreach>

 3.当参数为 Map 集合时
 <foreach collection="userlist" item="item" open="(" separator="," close=")">
             #{item}
 </foreach>



!--       使用 数组为参数 实现列表查询功能-->
    <select id="selectStorageSupplierIdSupplierIdArrayList" resultType="storage">
        SELECT *  FROM `t_storage_record`  where supplierId in
        <foreach collection="array" item="arrayList" open="(" separator="," close=")">
            #{arrayList}
        </foreach>
    </select>

<!--      使用 list集合 为参数 实现列表查询功能-->
    <select id="selectStorageSupplierIdSupplierIdList" resultType="storage">
        SELECT *  FROM `t_storage_record`  where supplierId in
         <foreach collection="list" item="storageList" open="(" separator="," close=")">
             #{storageList}
         </foreach>
    </select>

<!--     使用 Map对象 为参数 同时查询多个供货商关联的入库单列数据  -->
    <select id="selectStorageInSupplierIdAndLikeSrCodeMapList" resultType="storage">
        SELECT * FROM `t_storage_record` where supplierId in
              <foreach collection="supplierId" item="item" open="(" separator="," close=")">
                  #{item}
              </foreach>
        and srCode like  CONCAT('%',#{srCode},'%')
    </select>

三、MyBatis 框架动态 SQL 处理更新能力

MyBatis 框架动态 SQL 处理更新的功能主要通过set + 标签实现

<set>

    <if test="  条件判断 ， 返回 true或 false  ">
        SQL 语句...
    </if>
    ...
</set>


例如：

<update>
        update t_sys_user
        <set>
        <if test="account != null">account=#{account},</if>
        <if test="realName != null">realName=#{realName},</if>
        <if test="password != null">password=#{password},</if>
        <if test="sex != null">sex=#{sex},</if>
        <if test="phone != null">phone=#{phone},</if>
        <if test="address != null">address=#{address},</if>
        <if test="birthday != null">birthday=#{birthday},</if>
        <if test="roleId != null">roleId=#{roleId},</if>
        <if test="updatedUserId != null">updatedUserId=#{updatedUserId},</if>
        <if test="updatedTime != null">updatedTime=#{updatedTime},</if>
        </set>
        where id = #{id}
</update>

set 关键字可以在其包裹的语句前拼接一个set关键字，并能忽略更新语句尾部多余出来的逗号。配合if标签灵活使用 set 标签，可以在拼接 SQL 时忽略不需要的字段，从而实现数据部分更新的功能。

四、trim 标签

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

trim标签中涉及到的属性：

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

 <trim prefix="前缀"  suffix=" 后缀 " prefixOverrides=" 忽略前缀 " suffixOverrides=" 忽略后缀 ">
           ...
</trim>




例如：

 <update id="update" parameterType="sysUser">
        update t_sys_user
            <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
            <if test="account != null">account=#{account},</if>
            <if test="realName != null">realName=#{realName},</if>
            <if test="password != null">password=#{password},</if>
            <if test="sex != null">sex=#{sex},</if>
            <if test="phone != null">phone=#{phone},</if>
            <if test="address != null">address=#{address},</if>
            <if test="birthday != null">birthday=#{birthday},</if>
            <if test="roleId != null">roleId=#{roleId},</if>
            <if test="updatedUserId != null">updatedUserId=#{updatedUserId},</if>
            <if test="updatedTime != null">updatedTime=#{updatedTime},</if>
            </trim>
    </update>

五、MyBatis 框架的分页查询功能

MySQL数据库分页关键字为 limit 。

在接口中定义方法：

 /**
     *   使用 if + trim 实现根据 角色名称 模糊查询角色信息列表的操作，并进行 分页查询
     * @param pageBegin 分页起始页
     * @param pageSize  页数
     * @param roleName  角色名称
     * @return
     */
    List<SysRole> selectSysRolePageBeginRoleNameLimitList(@Param("pageBegin")Integer pageBegin , @Param("pageSize")Integer pageSize , @Param("roleName")String roleName);

在xml文件中编写映射文件：

<!--   使用 if + trim 实现根据 角色名称 模糊查询角色信息列表的操作，并进行 分页查询-->

    <select id="selectSysRolePageBeginRoleNameLimitList" resultType="sysRole">
        select * from t_sys_role 
        <trim prefix="where" prefixOverrides="and|or">
            <if test=" roleName != null and roleName != ''  ">
                and roleName like CONCAT('%',#{roleName},'%')
            </if>
        </trim>
        limit #{pageBegin},#{pageSize}

    </select>

在测试类中编写测试方法：

  /**
     *  使用 if + trim 实现根据 角色名称 模糊查询角色信息列表的操作，并进行 分页查询
     */
    @Test
    public void selectSysRolePageBeginRoleNameLimitList() {
        List<SysRole> sysRoleList = new ArrayList<>();
        try {
            String realName = "店长";
            Integer pageIndex = 1;
            Integer pageSize = 5;
            Integer pageBegin = ( pageIndex - 1 ) * pageSize ;
            sysRoleList = sqlSession.getMapper(SysRoleMapper.class).selectSysRolePageBeginRoleNameLimitList(pageBegin,pageSize,realName);
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
            sqlSession.rollback();
        }
        if (sysRoleList != null && sysRoleList.size() > 0) {
            for (SysRole sysRole: sysRoleList){
                logger.debug("使用 if + trim 实现根据 角色名称 模糊查询角色信息列表的操作，并进行 分页查询到的数据 ===》 " + sysRole);
            }
        }
    }

最后查询到的数据：

2024-06-25 17:25:45 [DEBUG] PooledDataSource forcefully closed/removed all connections.
2024-06-25 17:25:45 [DEBUG] PooledDataSource forcefully closed/removed all connections.
2024-06-25 17:25:45 [DEBUG] PooledDataSource forcefully closed/removed all connections.
2024-06-25 17:25:45 [DEBUG] PooledDataSource forcefully closed/removed all connections.
2024-06-25 17:25:45 [DEBUG] Opening JDBC Connection
2024-06-25 17:25:46 [DEBUG] Created connection 812446698.
2024-06-25 17:25:46 [DEBUG] Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@306cf3ea]
2024-06-25 17:25:46 [DEBUG] ==>  Preparing: select * from t_sys_role where roleName like CONCAT('%',?,'%') limit ?,? 
2024-06-25 17:25:46 [DEBUG] ==> Parameters: 店长(String), 0(Integer), 5(Integer)
2024-06-25 17:25:46 [DEBUG] <==      Total: 3
2024-06-25 17:25:46 [DEBUG] 使用 if + trim 实现根据 角色名称 模糊查询角色信息列表的操作，并进行 分页查询到的数据 ===》 SysRole{id=2, code='SMBMS_MANAGER', roleName='店长', createdUserId=1, createdTime='2019-04-13 00:00:00', updatedUserId=null, updatedTime='null'}
2024-06-25 17:25:46 [DEBUG] 使用 if + trim 实现根据 角色名称 模糊查询角色信息列表的操作，并进行 分页查询到的数据 ===》 SysRole{id=8, code='CSGO_LOVE', roleName='店长', createdUserId=71, createdTime='2024-05-14 10:25:41', updatedUserId=1, updatedTime='2024-06-20 16:32:30'}
2024-06-25 17:25:46 [DEBUG] 使用 if + trim 实现根据 角色名称 模糊查询角色信息列表的操作，并进行 分页查询到的数据 ===》 SysRole{id=16, code='HongShuai_Wang', roleName='店长', createdUserId=1, createdTime='2024-06-20 16:19:42', updatedUserId=1, updatedTime='2024-06-24 16:31:24'}
2024-06-25 17:25:46 [DEBUG] Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@306cf3ea]
2024-06-25 17:25:46 [DEBUG] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@306cf3ea]
2024-06-25 17:25:46 [DEBUG] Returned connection 812446698 to pool.

Process finished with exit code 0