【Mybatis】 Mybatis在xml文件中处理大于号小于号的方法【问题】

处理大于小于号的方法：

https://www.cnblogs.com/winner-0715/p/6132755.html

第一种方法：
用转义字符把">"和"<"替换掉，就没有问题了。

<if test="startTime != null ">
    AND order_date &gt;= #{startTime,jdbcType=DATE} 
</if>
<if test="endTime != null ">
    AND order_date &lt;= #{endTime,jdbcType=DATE} 
</if>

注意下，这里的startTime，endTime都是Date类型的~

附：XML转义字符

&lt;	<	小于号
&gt;	>	大于号
&amp;	&	和
&apos;	’	单引号
&quot;	"	双引号

第二种方法：
因为这个是xml格式的，所以不允许出现类似">"这样的字符，但是可以使用<![CDATA[ ]]>符号进行说明，将此类符号不进行解析 
mapper文件示例代码

<if test="startTime != null ">
    AND <![CDATA[ order_date >= #{startTime,jdbcType=DATE}  ]]>
</if>
<if test="endTime != null ">
    AND <![CDATA[ order_date <= #{endTime,jdbcType=DATE}  ]]>
</if>

 

 

====================================

附带问题：

使用情况：mybatis  xml中写的mapper  对接的是postgresql数据库

问题：在同一个项目中不同的mapper.xml文件中，分别出现了>= 和<=的比较运算符，但是在一个xml中需要额外处理才能使用，一个xml文件中不需要额外处理>或者<符号可以直接使用

下面附上两个xml文件代码和截图，

1.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.rollong.chinatower.server.persistence.mapper.FinanceReportMapper">
    <resultMap id="financeBriefDataResultMap"
               type="com.rollong.chinatower.server.api.payload.finance.FinanceBriefData">
        <id property="id" column="id"/>
    </resultMap>
    <select id="search" resultMap="financeBriefDataResultMap">
        SELECT
        *
        FROM
        (
        SELECT
        ( CAST ( partner.id AS VARCHAR ) || '@partner' ) AS ID,
        partner.id AS partnerId,
        project.id AS projectId,
        construction.id AS constructionId,
        NULL AS takerId,
        NULL AS maintenanceId,
        count(report.id) as reportsCount,
        sum(report.amount) as totalAmount,
        max(report.updated_at) as reportLatestUpdatedAt
        FROM
        "project"."project_construction_partners" AS partner
        LEFT JOIN "project"."project_construction" AS construction ON partner.construction_id = construction.id
        LEFT JOIN "project"."projects" AS project ON construction.project_id = project.id
        LEFT JOIN "finance"."finance_reports" as report on partner.id = report.partner_id
        WHERE
        #{isConstruction} = TRUE
        AND construction.deleted_at IS NULL
        <if test="null != partnerType">
            AND partner.name = '${partnerType}'
        </if>
        <if test="null != provinceId">
            AND project.province_id = #{provinceId}
        </if>
        <if test="null != cityId">
            AND project.city_id = #{cityId}
        </if>
        <if test="null != districtId">
            AND project.district_id = #{districtId}
        </if>
        <if test="null != subCompanyId">
            AND project.sub_company_id = #{subCompanyId}
        </if>
        <if test="null != thirdPartCompanyId">
            AND partner.third_part_id = #{thirdPartCompanyId}
        </if>
        <if test="null != leaderEmployeeId">
            AND partner.leader_employee_id = #{leaderEmployeeId}
        </if>
        <if test="null != workerEmployeeId">
            AND exists(
            select 1 from "third_part"."worker_involved_constructions" as involved
            where involved.worker_id = #{workerEmployeeId} AND involved.project_partner_id = partner.id
            )
        </if>
        <if test="null != reportStatus">
            AND exists(
            select 1 from "finance"."finance_reports" as r
            where r.partner_id = partner.id
            <choose>
                <when test="'Submit' == reportStatus">
                    and r.approved_at is null and r.rejected_at is null and r.paid_at is null
                </when>
                <when test="'Approved' == reportStatus">
                    and r.approved_at is not null and r.rejected_at is null and r.paid_at is null
                </when>
                <when test="'Rejected' == reportStatus">
                    and r.approved_at is null and r.rejected_at is not null and r.paid_at is null
                </when>
                <when test="'Paid' == reportStatus">
                    and r.paid_at is not null
                </when>
            </choose>
            )
        </if>
        <if test="null != keyword">
            AND project.name like #{keyword}
        </if>
        GROUP BY partner.id,project.id,construction.id
        UNION
        SELECT
        ( CAST ( taker.ID AS VARCHAR ) || '@maintenance' ) AS ID,
        NULL AS partnerId,
        project.ID AS projectId,
        NULL AS constructionId,
        taker.ID AS takerId,
        maintenance.ID AS maintenanceId,
        count(report.id) as reportsCount,
        sum(report.amount) as totalAmount,
        max(report.updated_at) as reportLatestUpdatedAt
        FROM
        "project"."project_maintenance_takers" AS taker
        LEFT JOIN "project"."project_maintenance" AS maintenance ON taker.maintenance_id = maintenance.id
        LEFT JOIN "project"."projects" AS project ON maintenance.project_id = project.id
        LEFT JOIN "finance"."finance_reports" as report on taker.id = report.taker_id
        WHERE
        #{isMaintenance} = TRUE
        AND maintenance.deleted_at IS NULL
        AND taker.deleted_at IS NULL
        <if test="null != provinceId">
            AND project.province_id = #{provinceId}
        </if>
        <if test="null != cityId">
            AND project.city_id = #{cityId}
        </if>
        <if test="null != districtId">
            AND project.district_id = #{districtId}
        </if>
        <if test="null != subCompanyId">
            AND project.sub_company_id = #{subCompanyId}
        </if>
        <if test="null != thirdPartCompanyId">
            AND taker.third_part_id = #{thirdPartCompanyId}
        </if>
        <if test="null != leaderEmployeeId">
            AND taker.leader_employee_id = #{leaderEmployeeId}
        </if>
        <if test="null != workerEmployeeId">
            AND exists(
            select 1 from "third_part"."worker_involved_maintenance" as involved
            where involved.worker_id = #{workerEmployeeId} AND involved.taker_id = taker.id
            )
        </if>
        <if test="null != reportStatus">
            AND exists(
            select 1 from "finance"."finance_reports" as r
            where r.taker_id = taker.id
            <choose>
                <when test="'Submit' == reportStatus">
                    and r.approved_at is null and r.rejected_at is null and r.paid_at is null
                </when>
                <when test="'Approved' == reportStatus">
                    and r.approved_at is not null and r.rejected_at is null and r.paid_at is null
                </when>
                <when test="'Rejected' == reportStatus">
                    and r.approved_at is null and r.rejected_at is not null and r.paid_at is null
                </when>
                <when test="'Paid' == reportStatus">
                    and r.paid_at is not null
                </when>
            </choose>
            )
        </if>
        <if test="null != keyword">
            AND project.name like #{keyword}
        </if>
        GROUP BY maintenance.id,project.id,taker.id
        ) AS table_all
        WHERE TRUE
        <if test="minReportsCount != null">
            AND table_all.reportsCount &gt;= #{minReportsCount}
        </if>
        <if test="maxReportsCount != null">
            AND table_all.reportsCount &lt;= #{maxReportsCount}
        </if>
        <choose>
            <when test="sort == 'Latest'">
                ORDER BY table_all.reportLatestUpdatedAt DESC
            </when>
            <when test="sort == 'AmountAsc'">
                ORDER BY table_all.totalAmount ASC
            </when>
            <when test="sort == 'AmountDesc'">
                ORDER BY table_all.totalAmount DESC
            </when>
        </choose>
    </select>
</mapper>

View Code

2.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.rollong.chinatower.server.persistence.mapper.OrderMealTimeMapper">
    <resultMap id="OrderMealResult"
               type="com.rollong.chinatower.server.api.payload.canteen.OrderMealTime" autoMapping="true">
        <result property="years" column="years"/>
        <result property="months" column="months"/>
        <result property="days" column="days"/>
        <result property="counts" column="counts"/>
        <result property="checkOut" column="checkOut"/>
    </resultMap>
    <select id="search" resultMap="OrderMealResult">
        SELECT
        ors.year_meal AS years,
        ors.month_meal AS months,
        ors.day_meal AS days,
        SUM (ors.counts) AS counts,
        SUM (ors.checkd_out) AS checkOut
        FROM canteen.order_meal  ors
        WHERE TRUE
        <if test="null != canteenId">
            AND ors.canteen_id = #{canteenId}
        </if>
        <if test=" null != startY and null != startM and null != startD" >
            AND <![CDATA[ (ors.year_meal *10000 + ors.month_meal *100 + ors.day_meal) >= (#{startY}*10000+#{startM}*100+#{startD}) ]]>
        </if>
        <if test=" null != endY and null != endM and null != endD" >
            AND <![CDATA[ (ors.year_meal *10000 + ors.month_meal *100 + ors.day_meal) <= (#{endY}*10000+#{endM}*100+#{endD}) ]]>
        </if>
          GROUP BY ors.year_meal,ors.month_meal,ors.day_meal
    </select>
</mapper>

View Code

 

 

【究竟是xml的问题/还是对接的数据库的问题/还是数据库中对于某些类型字段处理不一样】

如果有兴趣或者刚好知道，遇到过这种情况的 希望大家能给个反馈，多多交流！！