根据数据库表字段进行字符串分割筛选过滤数据

需求描述:

	需要根据数据库表字段进行字符串分割筛选过滤数据

数据库字段结构

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