起因
有一个label标签表t_project_template_task_label
,里面uuid都是32位,一个任务可以有多个标签,后台怕逗号分割存不下多个标签的uuid,所以任务表t_project_template_task
改用json存储
前端传入都转json格式
java在接受的时候却是String类型接收
结果示例:
问题来了
前端筛选传入label的uuid构成的数组,例如:[“157D55CD26D043DEBE0E2A336A6E588D”,“199B4DA365BD4125ABC43562C281F6C0”],
后端要在sql中遍历循环 json中判断任务表t_project_template_task
的label是否包含这些多选的标签,
如果包含就返回这个任务
了解一下json的方法
自己写死数据测试一下(方法好用)
我又开始写bug了
我后台mapper.xml代码(where后面只是单单加了labelList筛选处理)
select a.*,(CASE WHEN a.`is_finish` = 0 THEN TIMESTAMPDIFF(DAY, a.`create_time`,NOW()) WHEN a.`is_finish` = 1 THEN -1 END) AS delayDays,
c.card_name,p.plan_name,t.name project_name from t_project_template_task a
left join t_project_template_card c on a.card_id = c.uuid
left join t_project_template_plan p on c.plan_id = p.uuid
left join t_project_template t on p.project_template_id = t.uuid and t.type = 'project'
left join t_project_template_task_executor e on a.uuid = e.task_id
,sys_user s
<where>
and s.user_name_id = e.user_id
<if test="commonSubFilter.labelList != null and commonSubFilter.labelList.size() > 0">
<if test="commonSubFilter.labelListIsContain == 1">
and exists(select 1 from t_project_template_task task
</if>
<if test="commonSubFilter.labelListIsContain == 0">
and not exists(select 1 from t_project_template_task task
</if>
<where>
task.uuid = a.uuid and
<foreach collection="commonSubFilter.labelList" item="uuid" open="(" separator=" OR " close=")">
JSON_CONTAINS(task.label, #{uuid}, '$')
</foreach>
</where>
)
</if>
</where>
出现报错
Invalid JSON text in argument 2 to function json_contains: “The document root must not follow by other values.” at position 2.
依旧百度解决