Mybatis中trim的使用

骑着蜗牛打天下

于 2023-12-14 19:15:40 发布

阅读量225

点赞数 2

文章标签： mybatis

本文链接：https://blog.csdn.net/weixin_45326523/article/details/135002218

版权

trim属性

属性	描述
prefix	使用trim语句块部分拼接前缀
suffix	使用trim语句块部分拼接后缀
prefixOverrides	去除拼接sql的第一个关键字或字符比如where后第一个and
suffixOverrides	去除拼接sql的最后一个关键字或字符比如插入语句最后一个，

select查询

<select id="testTrimPage" resultType="com.chensir.system.domain.vo.UserVo">
        select *
        from user
        <trim prefix="where" prefixOverrides="and|AND">
            is_del = 0
            <if test="query.name != null and query.name != ''">
                and name like concat('%', #{query.name},'%')
            </if>
            <if test="query.age != null">
                and age = #{query.age}
            </if>
            <if test="query.sex != null and query.sex != ''">
                and sex = #{query.sex}
            </if>
            <if test="query.beginCreateTime != null and query.beginCreateTime != ''">
                and create_time >= #{query.beginCreateTime}
            </if>
            <if test="query.endCreateTime != null and query.endCreateTime != ''">
                and create_time &lt;= #{query.endCreateTime}
            </if>
        </trim>
    </select>

insert新增

<insert id="testTrimAdd">
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="query.name != null and query.name !=''">name,</if>
            <if test="query.age != null">age,</if>
            <if test="query.sex != null and query.sex !=''">sex,</if>
            <if test="query.sort != null and query.sort !=''">sort,</if>
            <if test="query.createName != null and query.createName !=''">create_name,</if>
            <if test="query.createTime != null">create_time,</if>
            <if test="query.updateName != null and query.updateName !=''">update_name,</if>
            <if test="query.updateTime != null">update_time,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="query.name != null and query.name !=''">#{query.name},</if>
            <if test="query.age != null">#{query.age},</if>
            <if test="query.sex != null and query.sex !=''">#{query.sex},</if>
            <if test="query.sort != null and query.sort !=''">#{query.sort},</if>
            <if test="query.createName != null and query.createName !=''">#{query.createName},</if>
            <if test="query.createTime != null">#{query.createTime},</if>
            <if test="query.updateName != null and query.updateName !=''">#{query.updateName},</if>
            <if test="query.updateTime != null">#{query.updateTime},</if>
        </trim>
    </insert>

update修改

    
   <!-- =================== where写在最后 =================== -->

   <update id="testTrimPut">
        update user
        <trim prefix="SET" suffixOverrides=",">
            <if test="query.name != null and query.name !=''">name = #{query.name},</if>
            <if test="query.age != null">age = #{query.age},</if>
            <if test="query.sex != null and query.sex !=''">sex = #{query.sex},</if>
            <if test="query.sort != null and query.sort !=''">sort = #{query.sort},</if>
            <if test="query.updateName != null and query.updateName !=''">update_name = #{query.updateName},</if>
            <if test="query.updateTime != null">update_time = #{query.updateTime},</if>
        </trim>
        where id=#{query.id}
    </update>

    <!-- =================== where使用suffix =================== -->

    <update id="testTrimPut">
        update user
        <trim prefix="SET" suffixOverrides="," suffix="where id = #{query.id}">
            <if test="query.name != null and query.name !=''">name = #{query.name},</if>
            <if test="query.age != null">age = #{query.age},</if>
            <if test="query.sex != null and query.sex !=''">sex = #{query.sex},</if>
            <if test="query.sort != null and query.sort !=''">sort = #{query.sort},</if>
            <if test="query.updateName != null and query.updateName !=''">update_name = #{query.updateName},</if>
            <if test="query.updateTime != null">update_time = #{query.updateTime},</if>
        </trim>
    </update>