Mybatis的灵活运用及注意事项

Mabatis,参考手册：
https://www.kancloud.cn/digest/andyalien-mybatis/190190

1 Mybatis 不能直接写SQL的小/大于号<　> ,不然会报错：The content of elements must consist of well-formed character data or markup
要变成

 &            &

 <            &lt;

 >            &gt;

 "             &quot;

 '              &apos;
 例如
 a小于等等b(a<=b) 在xml文件就要：  a &lt;= b 

注意不等于的写法：
a <>b 在XML中就要：a <![CDATA[ <> ]]>b

2 # 和$的区别

解析出来会带“”，而$解析出来不带

所以Limit 不能用 LIMIT #{topValue}，要LIMIT $topValue才可以。参数传的是String，不能用$，不然会报错，如我修改的这个BUG

注意事项
1、能使用#{}的地方应尽量使用#{}
2、#{}可以有效防止sql注入，像PreparedStatement ，，${}则可能导致sql注入成功。
例如用户输入用户名admin和密码123456’ or ‘1’ = ‘1’,那么拼接出来的语句就为
select * from user where name=’ admin ’ and password=‘123456’ or ‘1’= ‘1’;
这能够查询所有，这就出问题了。

3 mybatis返回集与传参，resultMap resultType parameterType
resultMap 和resultType 的区别，resultMap 定义好的实体类映射，resultType 表示返回的数据类型，常用的如Map ,Integer等等。
resultMap ：

<resultMap type="EqptData" id="EqptDataResult">
        <result property="dataId"    column="data_id"    />
        <result property="equipmentId"    column="equipment_id"    />
        <result property="equipmentDate"    column="equipment_date"    />
        <result property="status"    column="status"    />
        <result property="collectDate"    column="collect_date"    />
        <result property="remark"    column="remark"    />
        <result property="statusStr"    column="status_str"    />
        <result property="employeeNumber"    column="employee_number"    />
    </resultMap>
	
	<sql id="selectEqptDataVo">
        SELECT
			data_id,
			equipment_id,
			equipment_date,
			STATUS,
			collect_date,
			remark,
			status_str,
			employee_number
		FROM
			eqpt_data
    </sql>
	
    <select id="selectEqptDataList" parameterType="EqptData" resultMap="EqptDataResult">
        <include refid="selectEqptDataVo"/>
        <where>  
            <if test="dataId != null "> and data_id = #{dataId}</if>
             <if test="equipmentId != null "> and equipment_id = #{equipmentId}</if>
             <if test="equipmentDate != null "> and equipment_date = #{equipmentDate}</if>
             <if test="status != null  and status != '' "> and status = #{status}</if>
             <if test="collectDate != null "> and collect_date = #{collectDate}</if>
             <if test="remark != null  and remark != '' "> and remark = #{remark}</if>
             <if test="statusStr != null  and statusStr != '' "> and status_str = #{statusStr}</if>
             <if test="employeeNumber != null  and employeeNumber != '' "> and employee_number = #{employeeNumber}</if>
         </where>
         <if test="counts != null"><![CDATA[   LIMIT ${counts}]]></if>
    </select>

resultType ：

 <select id="selectEqptCount" resultType="Integer">
		SELECT  count(1) 
		 from
		  eqpt_data  <if test="partitionTime != null">partition (${partitionTime})</if> a 
		LEFT JOIN eqpt_equipment b on  a.equipment_id = b.equipment_id
		LEFT JOIN eqpt_equipment_type c on b.equipment_type_id = c.equipment_type_id
		<where>
		 	b.del_flag = 0
			<if test="variety != null"> and c.variety = #{variety}</if>
			<if test="deptId != null and deptId != ''">and (b.dept_id = #{deptId} or  b.dept_id IN ( SELECT	t.dept_id FROM sys_dept t WHERE FIND_IN_SET(#{deptId},ancestors))) </if> 
			 <choose>
			        <when test='status == "0"'>
			          and a.status  = 151
			        </when>
			         <when test='status == "00"'>
			          and a.status = 100
			        </when>
			 </choose>
			 <if test="equipmentTypeValue != null "> and c.equipment_type_value = #{equipmentTypeValue}</if>
			 <if test='attributeValue == "0"'>and a.collect_date &gt;= (NOW() - interval 12 hour)</if>
			<if test='attributeValue == "1"'>and a.collect_date &gt;= (NOW() - interval 24 hour)</if>
			<if test='attributeValue == "2"'>and a.collect_date &gt;= DATE_SUB(CURDATE(), INTERVAL 6 DAY)</if>
			<if test='attributeValue == "3"'>and a.collect_date &gt; DATE_SUB(CURDATE(), INTERVAL 30 DAY)</if>
			<if test='attributeValue == "4"'>and a.collect_date &gt; DATE_SUB(CURDATE(), INTERVAL 90 DAY)</if>
			<if test='attributeValue == "5"'>and a.collect_date &gt; DATE_SUB(CURDATE(), INTERVAL 180 DAY)</if>
			<if test='attributeValue == "6"'>and a.collect_date &gt; DATE_SUB(CURDATE(), INTERVAL 365 DAY)</if>
			<if test='time == "taday"'>and TO_DAYS(a.collect_date)=TO_DAYS(NOW())</if>
		</where>
	</select>

parameterType 代表传参的数据类型，如map
从controller到xml

/**
	 * 测试查询数据是否正常
	 */
	@PostMapping("/newEvnAllData")
	@ResponseBody
	public TableDataInfo newEvnAllData(@RequestParam Map<String, Object> paramMap) {
		paramMap.put("topValue", 20);
		paramMap.put("status", Global.getEquipmentOffLine());//不查离线的数据
		List<?> simpleSelectEvnAllData = iEnvService.simpleSelectEvnAllData(paramMap);
		logger.info("simpleSelectEvnAllData == " + JSON.toJSONString(simpleSelectEvnAllData));
		return getDataTable(simpleSelectEvnAllData);
	}

传参和返回值都是Map,这样的可以不用定义实体类，这样的用法比较灵活

 <select id="simpleSelectEvnAllData" parameterType="Map"  resultType="Map">
      SELECT equipmentEncod,equipmentName,equipmentTypeCode,aisle,measureType,measureData,equipmentLocation,g.status,
      h.status_name statusType,remark,g.data_id dataId,collectDate 
 		FROM(  SELECT 
 		a.data_id,c.equipment_encod equipmentEncod,c.equipment_name equipmentName,
 		CASE d.equipment_type_id	WHEN '84' THEN e.measure_type ELSE b.measure_type	END  measureType,
		CASE d.equipment_type_id	WHEN '84' THEN e.aisle ELSE b.aisle	END aisle,
 		concat(CASE d.equipment_type_id	WHEN '84' THEN e.particles ELSE b.measure_data	END,ifNULL(i.css_class,' ')) measureData,
 		a.collect_date collectDate,d.equipment_type_value equipmentTypeCode,a.status,
		c.equipment_location equipmentLocation,f.status_name remark
	FROM (SELECT collect_date ,STATUS,equipment_id,data_id FROM eqpt_data ORDER BY data_id DESC  LIMIT 100) a
	LEFT JOIN eqpt_environment_data b ON a.data_id= b.data_id 
	LEFT JOIN eqpt_equipment c ON a.equipment_id = c.equipment_id
	LEFT JOIN eqpt_equipment_type d ON c.equipment_type_id = d.equipment_type_id
	LEFT JOIN eqpt_particles_data e ON e.data_id = a.data_id
	LEFT JOIN state_dictionary f ON f.id=e.state_dictionary_id OR f.id=b.state_dictionary_id
	LEFT JOIN sys_dict_data i on b.measure_type = i.dict_value or e.measure_type= i.dict_value AND i.dict_type = 'eqpt_measure_type' 
	WHERE  d.variety = 1 
	AND  c.del_flag = 0 AND d.del_flag = 0  
	<if test="status != null and status != ''">and a.STATUS <![CDATA[ <> ]]> #{status} 
	 and f.status_switching_type not in (SELECT status_key FROM eqpt_status_switching WHERE (status_switching_id = #{status} or  status_switching_id  IN ( SELECT	t.status_switching_id FROM eqpt_status_switching t WHERE FIND_IN_SET(#{status},ancestors))))
	</if>
	<if test="deptId != null and deptId != ''">and (c.dept_id = #{deptId} or  c.dept_id IN ( SELECT	t.dept_id FROM sys_dept t WHERE FIND_IN_SET(#{deptId},ancestors))) </if>
	ORDER BY data_id DESC
	LIMIT ${topValue}
	)g  LEFT JOIN eqpt_status_switching h ON g.status=h.status_switching_id
    </select>

5 动态SQL，加LIMIT的方式

<![CDATA[   LIMIT  ${topValue}]]>