查询或者关系的两个条件用一个参数接受，且不区分大小写查询。mybatis中SQL语句的处理方式

迷南。 2021-10-09 10:07 436阅读 0赞

<select id="selectYgList" resultType="com.hot.analysis.bean.common.TDeviceYg">
    		SELECT y.* 
    		FROM 
        
    		<if test="moduleid == 1">t_device_dc</if>
    		<if test="moduleid == 2">t_device_yg</if>
    		<if test="moduleid == 3">t_device_jg</if>
    		<if test="moduleid == 8">t_device_hw</if>
    		<if test="moduleid == 9">t_device_ljt</if>
    		<if test="moduleid == 10">t_device_sj</if>
    		<if test="moduleid == 11">t_device_mc</if>
    		<if test="moduleid == 12">t_device_krq</if>
    		<if test="moduleid == 13">t_device_ywj</if>
    		 y
    		LEFT JOIN t_device_group_relation r ON y.id=r.deviceid
    		RIGHT JOIN t_user_dgroup_relation u ON r.groupid=u.devicegroupid
    		WHERE u.userid=#{userid}
    		AND u.isdelete=0 
    		AND r.isdelete=0 
    		AND r.moduleid=#{moduleid}
            
                     <if test="code != null and code != ''">
    		AND (y.code	LIKE CONCAT('%', #{code}, '%')
    		or
    		y.devnum LIKE CONCAT('%', #{code}, '%'))</if>
    	</select>

页面：![70][]

两个字段都要是一种数据类型才行

controller层：将数据都装在code

// 地图标注所有的设备
    	@PostMapping("/somke/selectYgList")
    	public List<TDeviceYg> selectYgList(@RequestParam("userid") Integer userid, @RequestParam("moduleid") Integer moduleid,  @RequestParam("code") String code) {
    		return somkeDataAnalysisService.selectYgList(userid,moduleid,code);
    	}

改进：不区分大小写查询

<if test="code != null and code != ''">
    			AND ( dev.`code`    LIKE UPPER(CONCAT('%',#{code},'%')) OR  dev.`code` LIKE LOWER(CONCAT('%',#{code},'%')) 
    			OR
    			dev.devnum   LIKE UPPER(CONCAT('%',#{code},'%')) OR  dev.devnum  LIKE LOWER(CONCAT('%',#{code},'%'))  )
    		</if>

[70]: /images/20211009/f0dcf9bcc24f4cfd94d75eddd5b09002.png