需求描述:
需要根据数据库表字段进行字符串分割筛选过滤数据
数据库字段结构
Xml 文件
<select id="selectCelebrationList" resultMap="BaseResultMap" parameterType="map">
SELECT
DISTINCT a.*
FROM
mst_celebration a
left join tran_company_celebration tcc on tcc.celebration_id = a.id
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="keyWord != null and keyWord !=''">
and a.name like concat(concat('%',#{keyWord}),'%')
</if>
<if test="colorId != null or styleId != null">
and(
<trim prefixOverrides="AND|OR">
<if test="colorId != null ">
(
<trim prefixOverrides="AND|OR">
<foreach collection="colorId" item="id">
or (locate(concat(#{id},','),a.color_ids) > 0 )
</foreach>
</trim>
)
</if>
<if test="styleId != null ">
and
(
<trim prefixOverrides="AND|OR">
<foreach collection="styleId" item="id">
or (locate(concat(#{id},','),a.style_ids) > 0 )
</foreach>
</trim>
)
</if>
</trim>
)
</if>
<if test="companyId != null and companyId !=''">
and a.company_id = #{companyId}
</if>
</trim>
order by a.update_time desc
SQL生成效果:
select a.* from mst_planning_scheme_templet a WHERE ( ( (locate(concat(?,’,’),a.color_ids) > 0 ) or (locate(concat(?,’,’),a.color_ids) > 0 ) or (locate(concat(?,’,’),a.color_ids) > 0 ) ) and ( (locate(concat(?,’,’),a.style_ids) > 0 ) or (locate(concat(?,’,’),a.style_ids) > 0 ) or (locate(concat(?,’,’),a.style_ids) > 0 ) ) ) and a.company_id = ? order by a.update_time desc LIMIT 0,12