MyBatis 实现动态 SQL

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:  MyBatis 中的动态 SQL 就是SQL语句可以根据不同的情况情况来拼接不同的sql。本文会介绍 xml 和 注解 两种方式的动态SQL实现方式。

XML的实现方式

先创建一个数据表,SQL代码如下:

DROPTABLE IF EXISTS `userinfo`;CREATETABLE `userinfo`  (  `id` int(11)NULL DEFAULT NULL,  `username` varchar(127) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `gender` tinyint(4)NULL DEFAULT NULL COMMENT '1-男 2-⼥ ',  `delete_flag` tinyint(4)NULL DEFAULT 0 COMMENT '0-正常, 1-删除',  `create_time` datetimeNULL DEFAULT CURRENT_TIMESTAMP,  `update_time` datetimeNULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB CHARACTER SET= utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS =1;

image.gif

数据库表和JAVA对象的对应如下:

image.gif

平时在注册账号时会有一些非必填项,而我们就可以使用 <if> 标签来跟据条件进行动态的SQL语句的添加。

接口定义:

@MapperpublicinterfaceUserinfo {
IntegeraddUserinfo(Useruser);
}

image.gif

<if>标签

语法:

<if test = "条件"> 语句块 </if>

如果 test 后面的条件判断结果为 true,那么就将后面的语句块拼接到最终的 SQL 语句中。

XML的实现代码如下:

<insertid="addUserinfo">    insert into userinfo(id, username,
<iftest="gender != null">                    gender,
</if><iftest="deleteFlag != null">                    delete_flag
</if>    ) values (
                #{id},#{username},
<iftest="gender != null">                  #{gender},
</if><iftest="deleteFlag != null">                    #{deleteFlag}
</if>    );
</insert>

image.gif

注意:test中的gender和deleteFlag,是传入对象中的属性,不是数据库字段。

执行以下测试代码:

@TestvoidaddUserinfo() {
Useruser=newUser();
user.setId(2);
user.setUsername("zhangsan");
user.setDeleteFlag(0);
//此时gender为空userinfo.addUserinfo(user);
}

image.gif

运行之后我们从日志中可以看出最终执行的SQL语句中并没有 gender

image.gif

而当我们令gender不为空:

user.setGender(1);

image.gif

image.gif

但是此时我们的代码还有一个隐藏BUG,比如当deleteFlag为空时:

@TestvoidaddUserinfo() {
Useruser=newUser();
user.setId(2);
user.setUsername("zhangsan");
//user.setDeleteFlag(0);user.setGender(1);
//此时deleteFlag为空userinfo.addUserinfo(user);
}

image.gif

此时代码报错了,我们可以从MyBatis打印的日志中看出SQL语句出现了多余的逗号。

image.gif

此时就需要使用<trim>标签

<trim>标签

<trim>标签中有如下属性:

    • prefix:表示在整个语句块起始位置加上prefix的值作为前缀
    • suffix:表示在整个语句块结尾加上suffix的值作为后缀
    • prefixOverrides:如果整个语句块的前缀等于prefixOverrides的值,去掉prefixOverrides的值;
    • suffixOverrides:如果整个语句块的后缀等于suffixOverrides的值,去掉suffixOverrides的值。

    我们利用<trim>标签来将上述SQL中结尾的 ‘,’ 去除

    <insertid="addUserinfo">    insert into userinfo 
    <trimprefix="("suffix=")"suffixOverrides=",">        id, username,
    <iftest="gender != null">            gender,
    </if><iftest="deleteFlag != null">            delete_flag
    </if></trim>    values
    <trimprefix="("suffix=");"suffixOverrides=",">        #{id},#{username},
    <iftest="gender != null">          #{gender},
    </if><iftest="deleteFlag != null">            #{deleteFlag}
    </if></trim></insert>

    image.gif

    image.gif

    此时程序就可以正常执行了。

    <where>标签

    <where>标签一般应用于需要动态组装where条件的地方。

    <where> 只会在子元素有内容的情况下才插入where子句,而且会自动去除子句的开头的AND或
    OR。

    例如:我们此时根据 id 和 gender 来查找数据。

    数据库中的数据如下:

    image.gif

    接口定义:

    UserselectUser(Integerid, Integergender);

    image.gif

    xml实现:

    <selectid="selectUser"resultType="com.example.Spring_demo.mySQL.User">    select * from userinfo
    <where><iftest="id != null">            id=#{id}
    </if><iftest="gender != null">            and gender=#{gender}
    </if></where>    ;
    </select>

    image.gif

    JAVA测试代码

    @TestvoidselectUser() {
    System.out.println(userinfo.selectUser(1, 1));
    }

    image.gif

    image.gif

    从打印的结果和日志中我们可以看出结果正确。

    如果 id 和 gender 都为 null

    @TestvoidselectUser() {
    System.out.println(userinfo.selectUser(null, null));
    }

    image.gif

    虽然程序报错了,可是并不是因为SQL错了而是因为接收的参数报错。

    此时可以看出 where 被去掉了。

    image.gif

    此时如果 gender 为空

    @TestvoidselectUser() {
    System.out.println(userinfo.selectUser(1, null));
    }

    image.gif

    <where>标签并不能去除句末的 and

    image.gif

    <set>标签

    <set>标签用于动态更新数据

    用于 update 语句中动态的在SQL语句中插入 set 关键字,并会删掉额外的逗号。

    例如:根据传入的id属性,修改 username 和 gender 中不为null的属性。

    接口定义:

    IntegerupData(Integerid, Stringusername, Integergender);

    image.gif

    xml代码实现:

    <updateid="upData">    update userinfo  
    <set><iftest="username != null">            username = #{username},
    </if><iftest="gender != null">            gender = #{gender}
    </if></set>    where id = #{id};
    </update>

    image.gif

    JAVA测试代码

    voidupData() {
    //gender为空userinfo.upData(2, "xiaohong", null);
    }

    image.gif

    image.gif

    可以看出删掉了额外的逗号(前后都会删掉)。

    <foreach>标签

    该标签可以在对集合进行遍历时使用。

    标签有如下属性:

      • collection:绑定方法参数中的集合,如List,Set,Map或数组对象;
      • item:遍历时对象中的每个元素;
      • open:语句块开头的字符串;
      • close:语句块结束的字符串;
      • separator:每次遍历之间间隔的字符串。

      例如:批量删除数据

      接口定义:

      IntegerdeleteUsers(List<Integer>ids);

      image.gif

      xml代码实现:

      <deleteid="deleteUsers">    delete from userinfo where id in
      <foreachcollection="ids"item="id"open="("close=");"separator=",">        #{id}
      </foreach></delete>

      image.gif

      注意:这两个地方的名称必须相同。

      image.gif

      JAVA测试代码

      @TestvoiddeleteUsers() {
      List<Integer>list=newArrayList<>();
      list.add(1);
      list.add(2);
      userinfo.deleteUsers(list);
      }

      image.gif

      image.gif

      <include>和<SQL>标签

      这两个标签配合使用可以实现对重复的代码片段进行抽取,将其通过 <sql> 标签封装到一个SQL片段,然后再通过<include> 标签进行引用。用来降低代码的冗余度。

        • <sql> :定义可重用的SQL片段
        • <include> :通过属性refid,指定包含的SQL片段

        例如:我们可以抽取下面xml中的部分代码。

        <deleteid="deleteUsers">    delete from userinfo where id in
        <foreachcollection="ids"item="id"open="("close=");"separator=",">        #{id}
        </foreach></delete>

        image.gif

        <sqlid="aaa">    delete from userinfo where id in
        </sql><deleteid="deleteUsers"><includerefid="aaa"></include><foreachcollection="ids"item="id"open="("close=");"separator=",">        #{id}
        </foreach></delete>

        image.gif

        JAVA测试代码

        @TestvoiddeleteUsers() {
        List<Integer>list=newArrayList<>();
        list.add(1);
        list.add(2);
        userinfo.deleteUsers(list);
        }

        image.gif

        image.gif

        @注解的实现方式

        注解的实现其实非常简单只需把xml标签中的SQL(包括标签),使用<script></script> 标签括起来就可以了。

        例如下面的xml代码

        <insertid="insertUserByCondition">INSERT INTO userinfo (
        username,
        `password`,
        age,
        <iftest="gender != null">gender,
        </if>phone)
        VALUES (
        #{username},
        #{age},
        <iftest="gender != null">#{gender},
        </if>#{phone})
        </insert>

        image.gif

        注解的实现方法:

        @Insert("<script>"+"INSERT INTO userinfo (username,`password`,age,"+"<if test='gender!=null'>gender,</if>"+"phone)"+"VALUES(#{username},#{age},"+"<if test='gender!=null'>#{gender},</if>"+"#{phone})"+"</script>")
        IntegerinsertUserByCondition(UserInfouserInfo);

        image.gif

        目录
        相关文章
        |
        19天前
        Mybatis+mysql动态分页查询数据案例——测试类HouseDaoMybatisImplTest)
        Mybatis+mysql动态分页查询数据案例——测试类HouseDaoMybatisImplTest)
        19 1
        |
        19天前
        |
        Java 关系型数据库 数据库连接
        Mybatis+MySQL动态分页查询数据经典案例(含代码以及测试)
        Mybatis+MySQL动态分页查询数据经典案例(含代码以及测试)
        21 1
        |
        19天前
        Mybatis+mysql动态分页查询数据案例——条件类(HouseCondition)
        Mybatis+mysql动态分页查询数据案例——条件类(HouseCondition)
        13 1
        |
        19天前
        Mybatis+mysql动态分页查询数据案例——分页工具类(Page.java)
        Mybatis+mysql动态分页查询数据案例——分页工具类(Page.java)
        18 1
        |
        19天前
        Mybatis+mysql动态分页查询数据案例——房屋信息的实现类(HouseDaoMybatisImpl)
        Mybatis+mysql动态分页查询数据案例——房屋信息的实现类(HouseDaoMybatisImpl)
        17 2
        |
        19天前
        Mybatis+mysql动态分页查询数据案例——工具类(MybatisUtil.java)
        Mybatis+mysql动态分页查询数据案例——工具类(MybatisUtil.java)
        14 1
        |
        19天前
        |
        Java 数据库连接 mybatis
        Mybatis+mysql动态分页查询数据案例——Mybatis的配置文件(mybatis-config.xml)
        Mybatis+mysql动态分页查询数据案例——Mybatis的配置文件(mybatis-config.xml)
        14 1
        |
        19天前
        Mybatis+mysql动态分页查询数据案例——配置映射文件(HouseDaoMapper.xml)
        Mybatis+mysql动态分页查询数据案例——配置映射文件(HouseDaoMapper.xml)
        14 1
        |
        19天前
        Mybatis+mysql动态分页查询数据案例——房屋信息的接口(IHouseDao)
        Mybatis+mysql动态分页查询数据案例——房屋信息的接口(IHouseDao)
        12 1
        |
        24天前
        |
        Java 关系型数据库 MySQL
        Mybatis+MySQL动态分页查询数据经典案例
        Mybatis+MySQL动态分页查询数据经典案例
        12 0
        Mybatis+MySQL动态分页查询数据经典案例