mybatis sql语句格式化 trim prefix suffix

标题SQL语句格式化

trim标记：是格式化sql的标记
prefix：前缀
suffix：后缀
prefixOverrides：指定去除多余的前缀内容
suffixOverrides：指定去除多余的后缀内容

1. select语句

<select id="" parameterType="" resultType="">
	select * from tb_user
	<trim perfis="WHERE" prefixOverrides = "AND | OR">
		<if test="name != null"> AND name = #{name}</if>
		<if test="gender != null"> AND gender= #{gender}</if>
	</trim>
</select>

执行结果为：
select * from tb_user where and name = #{name} andgender = #{gender}

2. insert语句

<insert id="" parameterType="">
    insert into tb_user
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="name!= null">
        name,
      </if>
      <if test="gender!= null">
        gender,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=BIGINT},
      </if>
      <if test="name!= null">
        #{name,jdbcType=VARCHAR},
      </if>
     <if test="gender!= null">
        #{gender,jdbcType=BIGINT},
      </if>
    </trim>
  </insert>

执行结果为：
insert into tb_user (id,name,gender) values(1,“张三”,20)

3.update语句

  <update id="">
    update tb_user
    　<trim prefix="set" suffix=" where id = #{id}" suffixOverrides="," >
	　　　　<if test="name != null"> name = #{name} , </if>
	　　　　<if test="gender != null"> gender = #{gender} ,</if>
　　　　</trim>
  </update>

执行结果为：
update tb_user set name = “张三”，gender = 30 where id = 1