mybatis中的<if test=" ">不起作用

喜欢ヅ旅行 2021-09-16 15:34 655阅读 1赞

今天遇到一个问题，就是在mybatis传值的时候，不管有没有条件都会把我的<if>标签里面的条件给带上

<if test="condition.crossIds!= null">
    			and rdtl.CROSS_ID IN
    			<foreach item="item" index="index" collection="condition.crossIds"
    				open="(" separator="," close=")">
    				#{item}
    			</foreach>
    </if>

就是不管我的crossIds里面有没有值，它都会把我这个条件给加上导致查询报错，

全部条件语句

<sql id="whereVoSql">
    		where rdtl.IS_DEL = 0
    		<if test="condition.devName != null">
    			and rdtl.dev_name LIKE CONCAT('%',#{condition.devName},
    			'%')
    		</if>
    		<if test="condition.crossIds!= null">
    			and rdtl.CROSS_ID IN
    			<foreach item="item" index="index" collection="condition.crossIds"
    				open="(" separator="," close=")">
    				#{item}
    			</foreach>
    		</if>
    		<if test="condition.areaId != null">
    			and rdtl.AREA_ID = #{condition.areaId}
    		</if>
    		<if test="condition.createTimeStart != null">
    			and rdtl.CREATE_TIME >= #{condition.createTimeStart}
    		</if>
    		<if test="condition.createTimeEnd != null">
    			and rdtl.CREATE_TIME < #{condition.createTimeEnd}
    		</if>
    		<if test="condition.devType != null">
    			and rdtl.DEV_TYPE = #{condition.devType}
    		</if>
    		<if test="condition.isLinkTask != null">
    			and rdtl.IS_LINK_TASK = #{condition.isLinkTask}
    		</if>
    		<if test="condition.badType != null">
    			and rdtl.BAD_TYPE = #{condition.badType}
    		</if>
    		<if test="condition.CrossId != null">
    			and rdtl.CROSS_ID = #{condition.CrossId}
    		</if>
    		<if test="condition.isImportantRoad != null">
    			and rc.IS_IMPORTANT_ROAD = #{condition.isImportantRoad}
    		</if>
    		<if test="condition.isLinkDev != null">
    			and ( SELECT count(CAMERA_ID) FROM rg_light_camera_link
    			WHERE rdtl.ID=TRAFFIC_LIGHT_ID ) = #{condition.isLinkDev}
    		</if>
    	</sql>

完整的SQL语句

<select id="selectVoPage" resultMap="DevTrafficLightVo">
    		SELECT
    		rdtl.ID,
    		rdtl.DEV_CODE,
    		rdtl.DEV_NAME,
    		rdtl.CROSS_ID,
    		rdtl.IS_LINK_TASK,
    		ra.AREANAME AS areaName,
    		rdtl.DEV_ADDRESS,
    	
    		FROM
    		rg_dev_traffic_light rdtl
    		LEFT JOIN rg_task_devlight_link rtdl ON
    		rdtl.ID = rtdl.LIGHT_DEV_ID
    		LEFT JOIN rg_cross rc ON rdtl.CROSS_ID =
    		rc.id
    		LEFT JOIN rg_area ra ON rdtl.AREA_ID = ra.ID
    		LEFT JOIN
    		rg_light_camera_link rlcl ON rdtl.ID = rlcl.TRAFFIC_LIGHT_ID
    		LEFT JOIN
    		device_info di ON rlcl.CAMERA_ID = di.ID
    		LEFT JOIN rg_light_group_link
    		rlgl ON rdtl.ID = rlgl.TRAFFIC_LIGHT_ID
    		LEFT JOIN rg_rgl_group_info
    		rrgi ON rlgl.GROUP_ID = rrgi.ID
    
    		<include refid="whereVoSql" />
    		GROUP BY
    		rdtl.ID
    		ORDER BY
    		CREATE_TIME DESC
    
    		limit ${from}, ${size}
    	</select>

为了省点空间，查询的有些字段我省略了，

我的crossIds这个字段的controller层

List<Long> crossIds = new ArrayList<>();
    		for(int i=0;i<list.size();i++){
    			crossIds.add(list.get(i).getId());
    		}

以分页查询条件传递到后台。

Object[][] objects = { { "devName", devName }, { "areaId", areaId }, { "devType", devType },
    				{ "crossIds", crossIds }, { "createTimeStart", createTimeStart_ },
    				{ "createTimeEnd", createTimeEnd_ }, { "isLinkTask", isLinkTask },
    				{ "isImportantRoad", isImportantRoad }, { "isLinkDev", isLinkDev } };
    		Map condition = super.getCondition(objects);
    		pageInfo.setCondition(condition);
    		pageInfo = devTrafficLightService.selectVoPage(pageInfo);

当我没有传条件到后台时，会报错

nested exception is java.sql.SQLException: sql injection violation, syntax error: syntax error, error in :'ROUP BY
    		rdtl.ID
    		ORDER BY
    		CREA', expect BY, actual BY pos 2466, line 165, column 9, token BY : SELECT
    		rdtl.ID,
    		rdtl.DEV_CODE,
    		rdtl.DEV_NAME,
    		rdtl.CROSS_ID,
    		rdtl.IS_LINK_TASK,
    		ra.AREANAME AS areaName,
    		rdtl.DEV_ADDRESS,
    		
    		rdtl.BAD_TYPE,
    		rdtl.CREATE_TIME,
    
    		di.DEVICE_NAME AS deviceName,
    		di.IP AS ipAddress,
    		rrgi. NAME AS
    		
    		FROM
    		rg_dev_traffic_light rdtl
    		LEFT JOIN rg_task_devlight_link rtdl ON
    		rdtl.ID = rtdl.LIGHT_DEV_ID
    		LEFT JOIN rg_cross rc ON rdtl.CROSS_ID =
    		rc.id
    		LEFT JOIN rg_area ra ON rdtl.AREA_ID = ra.ID
    		LEFT JOIN
    		rg_light_camera_link rlcl ON rdtl.ID = rlcl.TRAFFIC_LIGHT_ID
    		LEFT JOIN
    		device_info di ON rlcl.CAMERA_ID = di.ID
    		LEFT JOIN rg_light_group_link
    		rlgl ON rdtl.ID = rlgl.TRAFFIC_LIGHT_ID
    		LEFT JOIN rg_rgl_group_info
    		rrgi ON rlgl.GROUP_ID = rrgi.ID
    
    		 
    		where rdtl.IS_DEL = 0	 
    			and rdtl.CROSS_ID IN
    			 
    		GROUP BY
    		rdtl.ID
    		ORDER BY
    		CREATE_TIME DESC
    
    		limit 0, 10

还是会把 where rdtl.IS\_DEL = 0       
            and rdtl.CROSS\_ID IN  这句打印出来。

后来突然才发现，我传的是集合，要做判断

参考：[https://blog.csdn.net/cloudzpc/article/details/78254479][https_blog.csdn.net_cloudzpc_article_details_78254479]

mybatis中判断两个集合是否为空

<if test="condition.crossIds!= null and condition.crossIds.size > 0">
    			and rdtl.CROSS_ID IN
    			<foreach item="item" index="index" collection="condition.crossIds"
    				open="(" separator="," close=")">
    				#{item}
    			</foreach>
    </if>

[https_blog.csdn.net_cloudzpc_article_details_78254479]: https://blog.csdn.net/cloudzpc/article/details/78254479