Mybatis动态SQL详解

原创已于 2022-06-09 09:44:15 修改 · 1.6k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#sql #java #linq

于 2022-05-26 15:31:21 首次发布

Mybatis 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了MyBatis框架中动态SQL的使用方法，包括where、if、choose、when、otherwise、set、foreach和sql标签的应用场景及技巧。并通过具体实例展示了如何利用这些标签实现更为灵活的SQL语句生成，特别适用于需要根据不同条件动态调整SQL查询或更新需求的场景。

一、SQL详解

1. where和if标签

where标签和if标签都可以单独使用，我这里为了方便演示，在示例中就放在一起了。

<select id="queryBlogIf" parameterType="map" resultType="Blog">
    select * from blog
    <!-- where标签会自动添加where语句，并将后面满足条件的if标签中的内容进行拼接，
     并且如果拼接之后检查到where之后紧接着的开始语句是and或or的的话，也会自动删除单词and或or，
     当然如果所有if都不满足，where语句也不会拼接到SQL里。
     例如：如果下面第一个if没有满足，第二if满足了，直接拼接的话就会变成where and，这样的SQL是有问题的，
     所以where标签会自动将这个and给删除掉 ，当然如果两个条件都不满足，
     where语句也不会拼接到最终的SQL里 -->
    <where>
        <!-- test中是对传入参数的判断，这里的title != null表示是map中有key为title且不为null时才满足条件，
         通常没有传入该参数的话就会是null -->
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </where>
</select>

2. choose，when和otherwise标签

choose，when和otherwise标签类似于java的switch，case和default语句。

<select id="queryBlogWhen" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <!-- choose标签类似于java中的switch case语句，从上往下顺序判断，
         只要遇到满足条件的when标签，就会拼接对应的SQL，其他的when标签就不会再去判断了，
         如果所有when标签都不满足的话，则会拼接最后的otherwise标签的SQL -->
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

3. set标签

set标签用于拼接update更新操作的SQL，注意示例中使用的标签是update，不再是select查询标签了。

<update id="updateBlogSet" parameterType="map">
    update blog
    <!-- set标签用于拼接update操作的set语句，会在之前的update语句之后自动添加set语句，
     同时还会自动处理拼接后SQL中的逗号。例如：如果下面的if判断只有第一个if满足条件，
     拼接之后在末尾就会多一个逗号，此时set标签会自动删除这个逗号，保证SQL语句的合法性 -->
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    <!-- 这里的where语句也可以使用where标签进行更复杂的查询 -->
    where id = #{id}
</update>

4. foreach标签

foreach标签用于在参数是集合类型的场景下，我们可以循环遍历集合中的元素并拼接到SQL中。

<select id="queryBlogForeach" parameterType="map" resultType="Blog">
    select * from blog where id in
    <!-- foreach标签会根据指定规则拼接SQL，比如下面的配置：collection="ids"表示
     从参数map中根据key="ids"拿到对应的集合，然后遍历集合中的元素，item="id"表示遍历时元素对应的变量为id，
     open="("表示拼接时开始的语句（可以是多个字符），open="("表示结束的语句（可以是多个字符），separator=","表示每个元素在拼接时使用的
     连接符或分隔符（可以是多个字符），最终会生成的SQL为“ ( xxx , xxx , xxx ) ”，对应的空格（缺少的或多余的）会自动进行处理 -->
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

5. sql标签

sql标签用于提取公共的SQL配置，在需要使用的地方直接通过include标签进行引用即可。

<!-- 可以使用sql标签将常用的SQL抽取出来，在需要的地方使用include标签进行引用即可 -->
<sql id="blogIf">
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>
<select id="queryBlogIf2" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <!-- 使用include标签的refid引用对应的sql标签内容 -->
        <include refid="blogIf"/>
    </where>
</select>

二、CRUD使用动态语句

（1）添加

<insert id="AddUser">
        insert into smbms_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="usercode != null">
                userCode,
            </if>
            <if test="username != null">
                userName,
            </if>
            <if test="userpassword != null">
                userPassword
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="usercode != null">
                #{usercode},
            </if>
            <if test="username != null">
                #{username},
            </if>
            <if test="userpassword != null">
                #{userpassword}
            </if>
        </trim>
    </insert>

（2）修改

方法1：（注意最后<if></if>不能有，否则会报错）

  <update id="updateById" parameterType="com.zx.smbms_smp.pojo.SmbmsUser" >
        update user
        <set>
            id = #{id},
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="password != null and password != ''">
                password = #{password},
            </if>
            <if test="phone != null and phone != ''">
                phone = #{phone},
            </if>
            <if test="state != null and state != ''">
                state = #{state}
            </if>
        </set>
        where id = #{id}
    </update>

方法2：（推荐使用）

<update id="updateBill">
        update  smbms_bill
        <trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
            <if test="billcode!=null and billcode!=''">
                billcode=#{billcode},
            </if>
            <if test="productname!=null and productname!=''">
                productname=#{productname},
            </if>
            <if test="productdesc!=null and productdesc!=''">
                productdesc=#{productdesc},
            </if>
        </trim>
    </update>

（3）删除

<delete id="delUser">
        delete from smbms_user where id=#{id}
    </delete>

（4）查询

方法1：

<select id="login" resultType="com.zx.smbms_smp.pojo.SmbmsUser">
        select * from smbms_user
        <where>
            <if test="code!=null">
                and usercode=#{code}
            </if>
            <if test="password!=null">
                and userpassword=#{password}
            </if>
            and deleteflag=0
        </where>
    </select>

方法2：（推荐使用）

<!--把实体类中所有属性，通过${bils}.XXX 命名-->
    <sql id="bill">
        ${bils}.id,${bils}.billCode,${bils}.productName,
        ${bils}.productDesc,${bils}.productUnit,${bils}.productCount,
        ${bils}.totalPrice,${bils}.isPayment,${bils}.createdBy,
        ${bils}.creationDate,${bils}.modifyBy,${bils}.modifyDate,
        ${bils}.providerId,${bils}.deleteflag
        </sql>

<!--    通过 include 引用上面定义的sql  并重命名为b -->
    <select id="findAll" resultType="SmbmsBill">
        select
        <include refid="bill">
            <property name="bils" value="b"/>
        </include>
        from smbms_bill b where deleteflag=0
    </select>