1. Scene restore

In the actual project, there are many needs to search or filter the required data by setting a specific time period. Today, the author gives a detailed explanation on the application method of greater than and less than signs involved in time comparison in mybatis.

2. Implementation plan

Here are two possible methods:

① Escape method

Greater than: >

Less than:<

Greater than or equal to: >=

Up to:<=

Author's case:

<select id="view" parameterType="map" resultMap="BaseResultMap">
    SELECT * FROM task t,staff s,product p WHERE t.staff_id = s.id AND t.product_id = p.id
    <if test="companyId != null ">
        AND  t.company_id = #{companyId}
    </if>
    <if test="workshopId != null">
        AND  t.workshop_id = #{workshopId}
    </if>
    <if test="opunitId != null">
        AND  t.opunit_id = #{opunitshopId}
    </if>
    <if test="processId != null">
        AND  t.process_id = #{processId}
    </if>
    <if test="@Ognl@isNotEmpty(equipmentId)">
        AND  t.equipment_id = #{equipmentId}
    </if>
    <if test="dateStart != null and dateStart !='' ">
        AND UNIX_TIMESTAMP(t.date_work)  &gt;= UNIX_TIMESTAMP(#{dateStart})
    </if>
    <if test="dateEnd != null and dateEnd !='' ">
        AND UNIX_TIMESTAMP(t.date_work)  &lt;= UNIX_TIMESTAMP(#{dateEnd})
    </if>
    GROUP BY t.order_no
    ORDER BY t.date_work
</select>

Operation effect:

Note: the date input parameter type here is String

②<! [CDATA [SQL statement]] >

<! <!! in [CDATA [SQL statement]] >! Auto comment in CDATA [] >

Author's case:

<select id="selectByTime" resultType="Date" parameterType="map">
    SELECT
    r.stop_time
    FROM
    rtg r   <![CDATA[
    WHERE  UNIX_TIMESTAMP(r.stop_time) >= UNIX_TIMESTAMP(#{startTime})
    AND UNIX_TIMESTAMP(r.stop_time) <= UNIX_TIMESTAMP(#{endTime}) ]]>
</select>

Operation effect:

1, Brief overview

1. Usually, when writing sql in the mapping file of mybatis, you need to write some special characters. For example: "<" character ">" character "> =" character "< =" character, but the above listed characters cannot be written directly in the xml file, otherwise an error will be reported.
2. Because when parsing xml files, if we write special characters, without special processing. These characters will be escaped, but we don't want it to be escaped, so we want to use <! [CDATA []] >.
3. Then why write like this? <! [CDATA []] >, it goes without saying: This is XML syntax. Everything inside CDATA is ignored by the parser.
4. Therefore, when we include a lot of "<" characters "< =" and "&" characters in xml text - just like program code, it's best to put them in CDATA parts.

2, Actual writing specification

1. One thing to note is that in our mybatis mapping file, the following tags will not be parsed, so we only put the statements with special characters in <! [CDATA []] >, minimize <! [CDATA []] >.
2. case

 SELECT * FROM (SELECT t.*, rownum FROM bst_busi_msg t
            <where>
                <if test="targetId != null">
                    and (t.busi_sys_order = #{targetId,jdbcType=VARCHAR}
                        or t.busi_intf_seq = #{targetId,jdbcType=VARCHAR}
                    )
                </if>
                <if test="targetId == null and shardingTotal > 0">
                    and (t.task_status = '0'
                        OR (t.task_status = '3'
                            AND t.task_count <![CDATA[ < ]]> ${@com.asiainfo.bst.common.Constant@max_handle_count()}
                        )
                    )
                    and MOD(t.msg_id,#{shardingTotal,jdbcType=NUMERIC}) = #{shardingIndex,jdbcType=NUMERIC}
                </if>
            </where>
          ORDER BY t.msg_id ASC
        ) WHERE rownum <![CDATA[ <= ]]> #{rownum,jdbcType=NUMERIC}

[description]
Because there are ">" < = "special characters here, we need to use them to comment, but there are labels, so put them outside

ok, the above are all the experience refined by the author's actual needs. I hope I can help more partners