mybatis中的不起作用

父母的榜样作用在亲子关系中起关键作用 #生活知识# #生活心理学# #亲子关系心理学#

    今天遇到一个问题，就是在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 &gt;= #{condition.createTimeStart}

</if>

<if test="condition.createTimeEnd != null">

and rdtl.CREATE_TIME &lt; #{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

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>

网址：mybatis中的不起作用 https://www.yuejiaxmz.com/news/view/343518

相关内容

Mybatis
MyBatis中的if判断的问题 ，存在误区
MySQL实现序列（Sequence）效果以及在Mybatis中如何使用这种策略
二手家电交易网站(JSP+java+springmvc+mysql+MyBatis)
美食推荐系统的设计与实现(JSP+java+springmvc+mysql+MyBatis)
校园生活信息服务平台的设计与实现(JSP+java+springmvc+mysql+MyBatis)
毕设项目：基于大数据的身体健康管理平台(JSP+java+springmvc+mysql+MyBatis)
[附源码]计算机毕业设计日程管理系统(JSP+java+springmvc+mysql+MyBatis)
SpringBoot项目中MybatisPlus的使用
【附源码】计算机毕业设计本地生活服务平台（java+springboot+mysql+mybatis+论文)

随便看看