mybatis动态sql

多抓鱼

已于 2023-04-15 15:02:45 修改

阅读量41

点赞数

文章标签： mybatis sql 数据库

于 2023-04-15 14:20:33 首次发布

本文链接：https://blog.csdn.net/m0_63002921/article/details/130169548

版权

本文介绍了MyBatis中用于构建动态SQL的几个关键标签，如if用于条件判断，where确保无多余where条件，set处理更新语句中的动态字段，foreach进行循环操作，以及sql和include实现代码复用。这些标签极大地提高了SQL语句的灵活性和代码的可维护性。

摘要由CSDN通过智能技术生成

随着用户输入或外部条件的变化而变化的sql语句,我们称为动态SQL;

<if>:用于判断条件是否成立,如果成立则拼接sql.

<select id="list" resultType="pojo.Emp">
        select *
        from emp
        where
        <if test="name != null">
            name like concat('%',#{name},'%')
        </if>
        <if test="gender != null">
            and gender =#{gender}</if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
        order by update_time desc
    </select>

<where>:替代where关键字,只会在元素有内容的情况下才插入where子句,而且会过滤子句开头的and或or;

<select id="list" resultType="pojo.Emp">
        select *
        from emp
        <where>
        <if test="name != null">
            name like concat('%',#{name},'%')
        </if>
        <if test="gender != null">
            and gender =#{gender}</if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
        </where>
        order by update_time desc
    </select>

<set>:替代set关键字,动态的在行首插入set关键字,并且会删掉额外的逗号.

<update id="updateById2">
        <!--动态更新员工-->
        update emp
        <set>
            <if test="username != null">username = #{username},</if>
            <if test="name != null">name = #{name},</if>
            <if test="gender != null">gender = #{gender},</if>
            <if test="image != null">image = #{image},</if>
            <if test="job != null">job=#{job},</if>
            <if test="entrydate != null">entrydate = #{entrydate},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="updateTime != null">update_time = #{updateTime} </if>
        </set>
        where id = #{id}

<foreach>:遍历循环.

 <!--批量删除员工(18,19,20)-->
    <!--
    collection:要遍历的集合,
    item:遍历出来的元素,
    separator:分隔符,
    open:遍历前拼接的sql片段,
    close:遍历后拼接的sql片段
    -->
    <delete id="deleteBuyIds">
        delete from emp where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

<sql>:定义可重用的sql片段.

<sql id="commonSelect">
        select id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_time
        from emp
    </sql>

<include>:通过属性refid,指定包含的sql片段.

<select>
<include refid="commonSelect"></include>
        <where>
        <if test="name != null">
            name like concat('%',#{name},'%')
        </if>
        <if test="gender != null">
            and gender =#{gender}</if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
        </where>
        order by update_time desc
</select>