springboot-mybatis常用方法整理（like+转义+批量新增sql组装）

1.like

 <select id="selectByCondition"  resultMap="BaseResultMap">
    <if test=" supplierName != null "><bind name="namecnd" value="'%' + _parameter.supplierName + '%'"  /></if>
    <if test=" address != null "><bind name="addresscnd" value="'%' + _parameter.address + '%'"  /></if>
    select
    <include refid="Base_Column_List" />
    from onecity_supplier
    where isdelete =0
    <if test=" supplierName != null "> and supplier_name like #{namecnd} </if>
    <if test=" address != null "> and address like #{addresscnd} </if>
    <if test=" creator != null "> and creator = #{creator} </if>
    <if test=" phone != null "> and phone = #{phone} </if>
  </select>

2.符号转义

&lt;	<
&gt;	>
&amp;	&
&apos;	'
&quot;	"

3. 示例：转义+ <where>+  foreach

 <select id="selectByCondition"  resultType="hashmap" >
    select
   updatetime
    from devices.device_state_newest 
    <where>
      <if test="deviceStatus !=null">  and  device_status = #{deviceStatus} </if>
      <if test="starttime !=null">   AND createtime &gt;= #{starttime} </if>
      and  org_id IN
      <foreach item="item" collection="orgids" separator="," open="(" close=")" index="index">
      #{item}
      </foreach>
    </where>
  </select>

4. 示例：批量添加sql组装

 </insert>
    <insert id="batchinsert" >
    insert into onecity_user_org ( org_id, user_id,
      relation, enable, username
      )
    values
        <foreach item="item" collection="users" separator="," open="" close="" index="index">
            ( #{item.orgId,jdbcType=VARCHAR}, #{item.userId,jdbcType=VARCHAR},
            #{item.relation,jdbcType=INTEGER}, #{item.enable,jdbcType=INTEGER},  #{item.username,jdbcType=VARCHAR}
            )
        </foreach>

  </insert>

5. 字段值为null的属性正常返回

properties添加配置

mybatis.configuration.call-setters-on-nulls=true