[Java知识库]MyBatis xml文件的占位符

MyBatis xml文件的占位符

一、大于 大于等于 小于 小于等于 写法

（一）原始sql语句：

select sc_grade,  case
					 when sc_grade >0   and sc_grade <60   then '不及格'
					 when sc_grade >=60 and sc_grade <=70  then '及格'
					 when sc_grade >70  and sc_grade <=80  then '良好'
					 when sc_grade >80  and sc_grade <=90  then '中等'
					 when sc_grade >90  and sc_grade <=100 then '优秀'
					 else '其他' end as grade_rank 
from t_student_course where sc_date='2019-06-06' and course_id='2'

（二）Mybatis xml中的写法

写法一：

原符号： < <= > >= & ' "
替换符号： < <= > >= & ' "
注意： &lt 和 ";" 是一起的，不能分开

替换后：

select sc_grade, case
		when sc_grade >= 0  and sc_grade <  60 then '不及格'
		when sc_grade >= 60 and sc_grade <= 70 then '及格'
		when sc_grade >= 70 and sc_grade <= 80 then '良好'
		when sc_grade >= 80 and sc_grade <= 90 then '中等'
		when sc_grade >= 90 and sc_grade <= 100 then '优秀'
		else '其他' end as grade_rank 
from t_student_course where sc_date=#{sc_date} and course_id=#{course_id}

写法二：

>= 的写法      <![CDATA[ >= ]]>

<= 的写法      <![CDATA[ <= ]]>

替换后：

select sc_grade, case
	 when sc_grade <![CDATA[ >= ]]> 0 and sc_grade <![CDATA[ < ]]>   60 then '不及格'
	 when sc_grade <![CDATA[ >= ]]> 60 and sc_grade <![CDATA[ <= ]]> 70 then '及格'
	 when sc_grade <![CDATA[ > ]]>  70 and sc_grade <![CDATA[ <= ]]> 80 then '良好'
	 when sc_grade <![CDATA[ > ]]>  80 and sc_grade <![CDATA[ <= ]]> 90 then '中等'
	 when sc_grade <![CDATA[ > ]]>  90 and sc_grade <![CDATA[ <= ]]> 100 then '优秀'
	 else '其他' end as grade_rank 
from t_student_course where sc_date=#{sc_date} and course_id=#{course_id}

（三）项目实战经典写法

mapper 接口：

 /**
     * 根数是否预期，查询养护设施记录
     *
     * @param page
     * @param isWarning
     * @return
     */
    IPage<FacilityMaintenanceVO> getMaintenanceListByWarning(Page<FacilityMaintenanceVO> page, @Param("isWarning")Boolean isWarning);

mybatis 的xml 文件：

 <select id="getMaintenanceListByWarning" resultType="com.vortex.cloud.jcss.reborn.vo.FacilityMaintenanceVO"
            parameterType="java.lang.Boolean">

        SELECT jf.id as facility_Id,jf.`name` AS facility_name,jft.name AS
        facility_class_name,jf.`type_id` as facility_class_id,jf.`division_id`,jf.`manage_unit_id`,
        jf.`recent_maintain_date`,jf.`default_maintain_date`,jft.is_regular_maintenance,jft.maintain_period,
        jft.is_regular_maintenance,jf.`recent_maintain_date`,jf.`default_maintain_date`,jf.address,jf.geo_location
        FROM `jcss_facility` jf
        LEFT JOIN `jcss_facility_type` jft ON jf.`type_id`=jft.`id`
        <where>
            jft.`is_regular_maintenance` = TRUE AND jf.`deleted`=FALSE AND jft.`deleted`= FALSE
            <if test="isWarning">
                and UNIX_TIMESTAMP(
                CASE WHEN jft.`maintain_period` = 'WEEK' THEN DATE_ADD(`recent_maintain_date`,INTERVAL 1 WEEK)
                WHEN jft.`maintain_period` = 'MONTH' THEN DATE_ADD(`recent_maintain_date`,INTERVAL 1 MONTH)
                WHEN jft.`maintain_period` = 'QUARTER' THEN DATE_ADD(`recent_maintain_date`,INTERVAL 3 MONTH)
                WHEN jft.`maintain_period` = 'HALF_YEAR' THEN DATE_ADD(`recent_maintain_date`,INTERVAL 6 WEEK)
                WHEN jft.`maintain_period` = 'YEAR' THEN DATE_ADD(`recent_maintain_date`,INTERVAL 1 YEAR)
                END) &gt; UNIX_TIMESTAMP(`recent_maintain_date`)
            </if>

            <if test="!isWarning">
                and UNIX_TIMESTAMP(
                CASE WHEN jft.`maintain_period` = 'WEEK' THEN DATE_ADD(`recent_maintain_date`,INTERVAL 1 WEEK)
                WHEN jft.`maintain_period` = 'MONTH' THEN DATE_ADD(`recent_maintain_date`,INTERVAL 1 MONTH)
                WHEN jft.`maintain_period` = 'QUARTER' THEN DATE_ADD(`recent_maintain_date`,INTERVAL 3 MONTH)
                WHEN jft.`maintain_period` = 'HALF_YEAR' THEN DATE_ADD(`recent_maintain_date`,INTERVAL 6 WEEK)
                WHEN jft.`maintain_period` = 'YEAR' THEN DATE_ADD(`recent_maintain_date`,INTERVAL 1 YEAR)
                END) &lt; UNIX_TIMESTAMP(recent_maintain_date)
            </if>
        </where>
    </select>

关注林哥，持续更新哦！！！★,°:.☆(￣▽￣)/$:.°★ 。