【Mybatis】

查询条件

查询值在List中的数据

-- 查询值在List中的数据-输入为List
 <if test="factoryList != null and factoryList.size != 0">
     and factory_code in
     <foreach collection="factoryList" item="factoryCode" open="(" separator="," close=")">
         #{factoryCode}
     </foreach>
 </if>
-- 查询值在List中的数据-输入为用‘,’连接的字符串
<if test="vehicleModelCode != null  and vehicleModelCode != ''">
    and FIND_IN_SET(#{vehicleModelCode,jdbcType=LONGVARCHAR}, vehicle_model_code)
</if>
-- 查询值在List中的数据-输入为用‘,’连接的字符串
 <if test="isColor != null  and isColor != ''">
    and is_color in
    <foreach item="item" collection="isColor.split(',')" open="(" separator="," close=")">
        #{item,jdbcType=VARCHAR}
    </foreach>
 </if>
-- INSTR 函数返回一个整数，表示子字符串在原字符串中的起始位置。如果子字符串不存在，则返回 0
<if test="factoryType != null and factoryType !=''">
    and ( instr(#{factoryType,jdbcType=VARCHAR},bd_factory_type) <![CDATA[  >  ]]> 0
    <if test="factoryType.contains('0'.toString())">
        or bd_factory_type is null
    </if>)
</if>

涉及更新时间/操作时间的查询

-- 更新时间大于当前时间
<if test="isLatest != null and isLatest ">
    and update_time > CONCAT(DATE(NOW()), ' 00:00:00')
</if>
-- 操作时间-入参为Date类型
<if test="operateTimeStart != null and operateTimeEnd != null">
    and IFNULL(update_time, create_time) between #{operateTimeStart,jdbcType=TIMESTAMP} and #{operateTimeEnd,jdbcType=TIMESTAMP}
</if>
-- 操作时间-入参为String类型
<if test="startTime != null and startTime != ''">
    and date_format(create_time,'%Y-%m-%d') &gt;= date_format(#{startTime},'%Y-%m-%d')
</if>
<if test="endTime != null and endTime != ''">
    and date_format(create_time,'%Y-%m-%d') &lt;= date_format(#{endTime},'%Y-%m-%d')
</if>

模糊查询

<if test="createBy != null  and createBy != ''">
    and create_by like concat('%', #{createBy,jdbcType=VARCHAR}, '%')
</if>
-- 或者
<if test="createBy != null  and createBy != ''">
    <bind name="createByLike" value="'%' + createBy + '%'"/>
    and create_by like #{createByLike,jdbcType=VARCHAR}
</if>