mybatis动态sql

最新推荐文章于 2024-07-23 14:00:25 发布

挥发的石灰

最新推荐文章于 2024-07-23 14:00:25 发布

阅读量363

点赞数 5

分类专栏： spring 文章标签： mybatis sql java

本文链接：https://blog.csdn.net/SJH184554558/article/details/136489422

版权

spring 专栏收录该内容

6 篇文章 7 订阅

订阅专栏

🌽trim标签

🌽where和if标签

🌽set标签

🌽choose-when-otherwise

🌽sql-include标签，bind标签

🍆sql和include标签

🍆bind标签

🌽foreach标签

trim标签

给person表添加数据

<!--    动态sql-->
    <insert id="insertPer" parameterType="com.soft.entity.Person">
        insert into person
        <trim prefix="(" suffix=")" suffixOverrides="," >
            <if test="id != null and id != ''">id,</if>
            <if test="name != null and name != ''">name,</if>
            <if test="age != null and age != ''">age,</if>
            <if test="idcard_id != null and idcard_id != ''">idcard_id,</if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">#{id},</if>
            <if test="name != null and name != ''">#{name},</if>
            <if test="age != null and age != ''">#{age},</if>
            <if test="idcard_id != null and idcard_id != ''">#{idcard_id},</if>
        </trim>
    </insert>

where和if标签

 <select id="selectDStu" resultType="com.soft.entity.Student">
        select * from student
        <where>
            <if test="sid != null and sid != ''"> and sid = #{sid}</if>
            <if test="sname != null and sname != ''">and sname = #{sname}</if>
            <if test="ssex != null and ssex != ''">and ssex = #{ssex}</if>
            <if test="sbirth != null and sbirth != ''">and sbirth = #{sbirth}</if>
            <if test="sclass != null and sclass != ''">and sclass = #{sclass}</if>
        </where>
    </select>

set标签

与where标签类似，当需要set的字段时，回给sql提供set关键字。

 <update id="updateDStu" parameterType="com.soft.entity.Student">
        update student
        <set>
            <if test="sid != null and sid != ''">sid = #{sid},</if>
            <if test="sname != null and sid != ''">sname = #{sname},</if>
            <if test="ssex != null and sid != ''">ssex = #{ssex},</if>
            <if test="sbirth != null and sid != ''">sbirth = #{sbirth}},</if>
            <if test="sclass != null and sid != ''">sclass = #{sclass},</if>
        </set>
        <where>
            <if test="sid != null and sid != ''"> and sid = #{sid}</if>
        </where>
    </update>

choose-when-otherwise

<!--    flg为0：给传过来的id的person修改姓名  flg为1：给传过来的id的person修改年龄 其他修改姓名和年龄-->
    <update id="updatePer" >
        update person
        <set>
            <choose>
                <when test="flg == 0">
                    name = #{person.name}
                </when>
                <when test="flg == 1">
                    age = #{person.age},
                </when>
                <otherwise>
                    name = #{person.name},age = #{person.age},
                </otherwise>
            </choose>
        </set>
        <where>
             <!-- 这里注意传过来的时person对象，test里获取id也需要使用person.id-->
            <if test="person.id != null and person.id != ''">id = #{person.id}</if>
        </where>
    </update>

sql-include标签，bind标签

sql和include标签

将<sql />标签配置为全局作用域

bind标签

模糊匹配，传过来一个字符串 s ，bind标签就会生成一个 %王%

<select id="selectLikePer" resultType="com.soft.entity.Person">
      <bind name="binds" value="'%'+s+'%'"/>
        select <include refid="sql.fs"/>
                           <!-- 注意binds也要用#{}-->
            from person where name like #{binds}
    </select>

foreach标签

 <select id="selectAllPer"  resultType="com.soft.entity.Person">
        select <include refid="sql.fs"/>
            from person where id in
        <foreach collection="array" item="i" open="(" close=")" separator=",">
            #{i}
        </foreach>
    </select>