mybatis

1.id in ids (数组)

SELECT
   *
  FROM
   `cw_alarm_info`
  <where>
   <if test="beginTime != null and beginTime!= '' and endTime != null and endTime != ''">
    createTime BETWEEN #{beginTime} AND #{endTime}
   </if>
   <if test="minSim != null and minSim != '' and maxSim != null and maxSim != ''">
    AND similiar BETWEEN #{minSim} AND #{maxSim}
   </if>
   <if test="cameraId != null and cameraId != ''">
    AND cameraId = #{cameraId}
   </if>
   <if test="libraryIds != null and libraryIds.size > 0 ">
    AND libraryId in
    <foreach collection="libraryIds" index="index" item="item" open="(" separator="," close=")">
                #{item}
             </foreach>
            </if>
   <if test="controllerNO != null and controllerNO != ''">
    AND controllerNO = #{controllerNO}
   </if>
  </where>

  

2.特殊字符  >=  <=

>=      >=
<=      &lt;=

3.批量新增 

<!-- schedule 车辆感知离开 批量新增 -->
    <insert id ="insertsLeave" parameterType="java.util.List" >
            <selectKey resultType ="java.lang.Integer" keyProperty= "id"
                 order= "AFTER">
                SELECT LAST_INSERT_ID()
            </selectKey>
           INSERT INTO cw_vehicle_leave (
    tollgateID,
    cameraId,
    villageCode,
    plateNumber,
    inOutTime,
    platePicUrl,
    plateNoPicUrl,
    channelName,
    days,
    status
   )
   VALUES
            <foreach collection ="list" item="item" index= "index" separator =",">
                (
                #{item.tollgateID},
                #{item.cameraId},
                #{item.villageCode},
                #{item.plateNumber},
                #{item.inOutTime},
                #{item.platePicUrl},
                #{item.plateNoPicUrl},
                #{item.channelName},
                1,
                0
                )
            </foreach>
     </insert>

 批量修改

     <update id="discoveryUpdate" parameterType="java.util.List">
         <foreach collection="list" item="item" separator=";">
             UPDATE cw_vehicle_discovery set
                updateTime = NOW(),
                 days = 0,
                inOutTime = #{item.inOutTime}
            WHERE id = #{item.id}
            </foreach>    
     </update>

4.模糊查询

<select id="pages"
        parameterType="cn.cloudwalk.isc.data.resource.common.model.Unit"
        resultType="cn.cloudwalk.isc.data.resource.common.model.Unit">
        SELECT
            *
        FROM
            cw_base_unit
        <where>
            <if test="buildingNo != null and buildingNo != ''">
                buildingNo = #{buildingNo}
            </if>
            <if test="unitName != null and unitName != ''">
                AND unitName LIKE CONCAT('%',#{unitName},'%')
            </if>
        </where>
    </select>

 

5.今天在写mapper文件时遇到integer类型参数时判断不为空

如下

当topSeq=0时，if语句没有执行。究其原因，原来mybatis默认将integer=0的参数等于‘’空串。

6.int参数 String参数  list参数 判断不为空

 7.单个update

注：一个更简单方法 <set></set> 标签可以过滤","

进行动态SQL拼接，如下，使用trim就是为了删掉最后字段的“,”。
主要不用单独写SET了，因为set被包含在trim中了：

<update id="updateOne"  parameterType="com.inspur.search.data.EntityRelation">
 UPDATE ENTITY_RELATION
 <trim prefix="set" suffixOverrides=",">
  <if test="srcId!=null">SRC_ID=#{srcId},</if>
  <if test="srcType!=null">SRC_TYPE=#{srcType},</if>
  <if test="destId!=null">DEST_ID=#{destId},</if>
  <if test="destType!=null">DEST_TYPE=#{destType},</if>
  <if test="relType!=null">REL_TYPE=#{relType},</if>
  <if test="status!=null">STATUS=#{status},</if>
  <if test="snId!=null">SN_ID=#{snId},</if>
 </trim>
 WHERE id=#{id}
</update>

 7.Date 空判定

mybatis 3.0  Date只能判断为 ！= null，    != '' 空字符串 会报错