mybatis

1.id in ids (数组)

 1 SELECT
 2    *
 3   FROM
 4    `cw_alarm_info`
 5   <where>
 6    <if test="beginTime != null and beginTime!= '' and endTime != null and endTime != ''">
 7     createTime BETWEEN #{beginTime} AND #{endTime}
 8    </if>
 9    <if test="minSim != null and minSim != '' and maxSim != null and maxSim != ''">
10     AND similiar BETWEEN #{minSim} AND #{maxSim}
11    </if>
12    <if test="cameraId != null and cameraId != ''">
13     AND cameraId = #{cameraId}
14    </if>
15    <if test="libraryIds != null and libraryIds.size > 0 ">
16     AND libraryId in
17     <foreach collection="libraryIds" index="index" item="item" open="(" separator="," close=")">
18                 #{item}
19              </foreach>
20             </if>
21    <if test="controllerNO != null and controllerNO != ''">
22     AND controllerNO = #{controllerNO}
23    </if>
24   </where>
View Code

  

2.特殊字符  >=  <=

1 >=      &gt;=
2 <=      &lt;=
View Code

 

3.批量新增 

 1 <!-- schedule 车辆感知离开 批量新增 -->
 2     <insert id ="insertsLeave" parameterType="java.util.List" >
 3             <selectKey resultType ="java.lang.Integer" keyProperty= "id"
 4                  order= "AFTER">
 5                 SELECT LAST_INSERT_ID()
 6             </selectKey>
 7            INSERT INTO cw_vehicle_leave (
 8     tollgateID,
 9     cameraId,
10     villageCode,
11     plateNumber,
12     inOutTime,
13     platePicUrl,
14     plateNoPicUrl,
15     channelName,
16     days,
17     status
18    )
19    VALUES
20             <foreach collection ="list" item="item" index= "index" separator =",">
21                 (
22                 #{item.tollgateID},
23                 #{item.cameraId},
24                 #{item.villageCode},
25                 #{item.plateNumber},
26                 #{item.inOutTime},
27                 #{item.platePicUrl},
28                 #{item.plateNoPicUrl},
29                 #{item.channelName},
30                 1,
31                 0
32                 )
33             </foreach>
34      </insert>
View Code

 批量修改

     <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>
View Code

 

4.模糊查询

 1 <select id="pages"
 2         parameterType="cn.cloudwalk.isc.data.resource.common.model.Unit"
 3         resultType="cn.cloudwalk.isc.data.resource.common.model.Unit">
 4         SELECT
 5             *
 6         FROM
 7             cw_base_unit
 8         <where>
 9             <if test="buildingNo != null and buildingNo != ''">
10                 buildingNo = #{buildingNo}
11             </if>
12             <if test="unitName != null and unitName != ''">
13                 AND unitName LIKE CONCAT('%',#{unitName},'%')
14             </if>
15         </where>
16     </select>
View Code

 

 

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

如下

View Code

 

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

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

 

View Code

 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>
View Code

 

 

 7.Date 空判定

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

posted @ 2019-02-15 17:27  手中天  阅读(194)  评论(0)  编辑  收藏  举报