前端传递数组,mybatis 遍历后根据数组中的内容进行条件判断
sql如下:
SELECT
*
FROM
t_frs_contrast_record
WHERE
1 = 1
AND (
t_frs_contrast_record.user_id IS NULL
OR EXISTS ( SELECT id FROM t_frs_user tfu WHERE tfu.validity_type = '0' AND t_frs_contrast_record.user_id = tfu.id )
OR EXISTS ( SELECT id FROM t_frs_user tfu WHERE tfu.validity_type = '1' AND t_frs_contrast_record.user_id = tfu.id )
)
业务情境是当传递[1,2,3],后台根据1 2 3分别进行不同的查询,要满足前端多选查询的需求,使用上面的sql进行处理
mybaits写法如下
<if test="person_role != null ">
AND (
<foreach collection="person_role" item="item" index="index" >
<if test="index != 0">
or
</if>
<if test="item == 1">
t_frs_contrast_record.user_id IS NULL
</if>
<if test="item == 2">
EXISTS ( SELECT id FROM t_frs_user tfu WHERE tfu.validity_type = '0' AND t_frs_contrast_record.user_id = tfu.id )
</if>
<if test="item == 3">
EXISTS ( SELECT id FROM t_frs_user tfu WHERE tfu.validity_type = '1' AND t_frs_contrast_record.user_id = tfu.id )
</if>
</foreach>
)
</if>
关键点在于or 和 括号 (),不加括号会导致or和其他条件平级,导致查询出现错误。
通过index判断确定是否拼接时加or