Usage of tags such as prefix and suffix in trim under xml framework of mybatis

Usage of tags such as prefix and suffix in trim

1. prefix Content added by prefix

2. suffix Content added by suffix

3. prefixOverrides The prefix needs to cover the content, 1 is usually the redundant structure before the first judgment condition, such as: the first judgment condition is preceded by 'and'

select * from User where name='zhangsan' and age='20';

<select id='queryUser'>
        select * from User
        <trim prefix='where' prefixOverrides='and'>
            <if test="name != null and name != ''">
                name = #{name}
            </if>
            <if test="age !=null and age !=''">
                and age = #{age}
            </if>
        </trim>
<select>

There is no sign before the first condition, and and should be added to the second condition, otherwise the sql statement will report an error. Ideally, both the first and second have values, but since the judgment and explanation may not have values, when the first name has no value, the sql statement will be select * from User where and age = '', which obviously has problems with the syntax of this sql statement.

Here the tag attribute prefixOverrides comes into play, which causes the prefix where to overwrite the first and. After the coverage is: select * from User where age= '';

4. suffixOverrides The content that suffix needs to override, 1 is generally the symbol after the last 1 data, for example, when set value, there is one comma after the last 1 value ','

 <insert id="insertSelective" parameterType="org.javaboy.vhr.model.Salary">
        insert into salary
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="basicSalary != null">
                basicSalary,
            </if>
            <if test="bonus != null">
                bonus,
            </if>
            <if test="lunchSalary != null">
                lunchSalary,
            </if>
            <if test="trafficSalary != null">
                trafficSalary,
            <if test="name != null">
                name,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=INTEGER},
            </if>
            <if test="basicSalary != null">
                #{basicSalary,jdbcType=INTEGER},
            </if>
            <if test="bonus != null">
                #{bonus,jdbcType=INTEGER},
            </if>
            <if test="lunchSalary != null">
                #{lunchSalary,jdbcType=INTEGER},
            </if>
            <if test="trafficSalary != null">
                #{trafficSalary,jdbcType=INTEGER},
            </if>
    		 <if test="name != null">
                #{name,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>

Added suffixOverrides= ","

Results:

insert into salary (id,basicSalary,bonus,lunchSalary,trafficSalary,name) values (#{id},#{basicSalary},#{bonus},#{lunchSalary},#{trafficSalary},#{name})

Without suffixOverrides= ","

Results:

insert into salary (id,basicSalary,bonus,lunchSalary,trafficSalary,name , ) values (#{id},#{basicSalary},#{bonus},#{lunchSalary},#{trafficSalary},#{name} , )

If suffixOverrides= "," is added, the comma "," in the last condition in this example will be overwritten by the suffix

trim prefix= "(" suffix= ")" of mybatis

1. As follows

<trim prefix="" suffix="" suffixOverrides="" prefixOverrides=""></trim>

prefix Prefix the sql statement within the trim tag.

suffix Suffix the sql statement within the trim tag.

suffixOverrides : Specifies the removal of redundant suffix content, such as: suffixOverrides= ",", and the removal of redundant suffix "," of sql statement in trim tag.

prefixOverrides Specifies removing redundant prefix content

2. Here is an mybatis statement to insert data into the shopping cart table

<insert id="insert" parameterType="com.tortuousroad.groupon.cart.entity.Cart">
        insert into cart
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="userId != null">
                user_id,
            </if>
            <if test="dealId != null">
                deal_id,
            </if>
            <if test="dealSkuId != null">
                deal_sku_id,
            </if>
            <if test="count != null">
                count,
            </if>
            <if test="createTime != null">
                create_time,
            </if>
            <if test="updateTime != null">
                update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=BIGINT},
            </if>
            <if test="userId != null">
                #{userId,jdbcType=BIGINT},
            </if>
            <if test="dealId != null">
                #{dealId,jdbcType=BIGINT},
            </if>
            <if test="dealSkuId != null">
                #{dealSkuId,jdbcType=BIGINT},
            </if>
            <if test="count != null">
                #{count,jdbcType=INTEGER},
            </if>
            <if test="createTime != null">
                #{createTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updateTime != null">
                #{updateTime,jdbcType=TIMESTAMP},
            </if>
        </trim>
    </insert>

suffixOverrides=","

The executed sql statement may look like this: insert into cart (id, user_id, deal_id,) values (1, 2, 1,); It is obviously wrong

When specified, the statement becomes insert into cart (id, user_id, deal_id) values (1, 2, 1); In this way, "," is removed.

Prefix is also a reason, so I won't say it here.