Mybatis动态sql语句foreach中拼接正则表达式字符串注意事项

xiaomifeng1010

已于 2023-12-12 13:45:32 修改

阅读量1.6k

点赞数 1

分类专栏： ORM框架 SSM框架 mysql 文章标签： sql 数据库

于 2023-03-04 13:59:50 首次发布

本文链接：https://blog.csdn.net/u011174699/article/details/129333021

版权

mysql 同时被 3 个专栏收录

20 篇文章 2 订阅

订阅专栏

SSM框架

12 篇文章 0 订阅

订阅专栏

ORM框架

10 篇文章 0 订阅

订阅专栏

今天要说到的查询情况，平时项目里边其实用到的并不是很多，使用正则表达式无非是为了匹配结果比较灵活，最常见的，我们的查询条件一般一个参数仅仅只是一种情况的筛选，对于如何选择查询方式，主要还是要看前端传递的查询条件格式，比如单参数只表示单个值，而且数据表对应也是单个值，这种最简单，精确匹配的话，就直接where 字段=参数值，如果模糊查询，就使用like匹配，如果前端传递的某个参数，值是用逗号分割的一个字符串，实际上可以看做是多个匹配条件，而数据表中，对应的字段值是单个值的话，可以where find_in_set(字段,字符串参数值)或者将分割的字符串转换成List，然后用in匹配

还有一种就是前端传递的参数值是逗号分割的字符串，对应要查询匹配的字段，数据表中存的也是以逗号分割的字符串，查询的时候，一般是或关系，就需要从表中查询出只要包含查询条件中的其中一个值的匹配结果

举例来说吧，比如昨天一个需求是需要根据标签id来查询只要含有这些标签中其中给一个标签的企业。

先看swagger文档:

这是一个get请求方式的分页查询，重点就是下边那个tagIdList参数，前端传递的是一个逗号分割的id字符串，是两个标签，而企业也是多标签的，现在从企业中查询出有这两个标签的企业(其实就是或的关系）

接下来看mybatis xml中的sql写法吧

    <select id="pageList" resultType="com.xiaomifeng1010.response.vo.JobSearchVO">
        select
        job.id,
        SUBSTRING_INDEX(job.state_record,',',-1) as state,
        job.name,
        (select name from dictionary where code = job.industry) as industry,
        job.uscc,
        group_concat(distinct tag.tag_name) as tag_names
        from
        job_search job
        join enterprise_info info
        on job.uscc=info.uscc
        left join enterprise_tag_mid mid on info.id=mid.enterprise_id
        left join enterprise_tag tag on mid.tag_id=tag.id
        where job.del_flag = 0

        <if test="param.name != null and param.name != ''">
            and job.name like concat('%',#{param.name},'%')
        </if>
   
        <if test="param.state!=null">
            and SUBSTRING_INDEX(job.state_record,',',-1)=#{param.state,jdbcType=INTEGER}
        </if>
        group by job.name,job.uscc
        <if test="param.tagIdList != null and param.tagIdList.size() != 0">
            HAVING group_concat(distinct tag.id) REGEXP
            replace(<foreach collection="param.tagIdList" item="tagId" separator="|" open="concat("
                             close=")">
            #{tagId,jdbcType=BIGINT}
        </foreach>,' ','')
        </if>
        <if test="param.sort != '' and param.sort != null">
            order by ${param.sort}
        </if>
        <if test="param.sort == '' or param.sort == null">
            order by job.create_time desc
        </if>


    </select>

要匹配标签或关系：

我一开始是这样写的，为什么这样写的，因为以前我使用Integer类型的枚举值List时候这样用是可以正常匹配的，所以Id值是Long类型的依然这样用，但是结果查询结果出乎我的预料，当我选择单个标签或者不选标签的时候，是可以正常查询结果的，但是当我选择的是两个标签时候，奇怪的事情就发生了，居然一条结果都没有了！

我选单个标签的时候，是可以查询出231条结果的

但是当我选择两个标签的时候，居然一条结果都没有：

正常情况下，foreach拼接的tagId，拼接好，完整的sql可以通过打印的日志查看到我在navicat中直接执行这个语句是可以正常查询到结果的，但是在程序中就是没有查询结果，所以我又去试了一下测试环境，并查看日志，并且我把#{tagId}换成了${tagId}方便直接打印sql的时候直接带上参数，而不是占位符，看到的日志信息就是这样的：

可以看到使用concat拼接的正则表达式每个id以及‘|’符号之间都加上了空格，应该是mybatis加上的，所以为了去掉空格，所以我又加上了replace函数，然后在本地测试，结果本地可以查询出来一些结果了，但是结果却是只有匹配第二个标签的结果，好家伙，replace没有替换掉‘|’符号前边那个id的空格？然后发到测试环境，结果测试环境，还是查不到结果，就非常诡异，正常情况下replace是可以替换掉字符串中所有空格的，在Navicat中运行也是正常的：

既然mybatis会改变最终的拼接结果，那么索性不用mybatis来拼接了，解决方案是使用ognl表达式，直接用java的api就可以了，所以最终改写成了：

        group by job.name,job.uscc
        <if test="param.tagIdList != null and param.tagIdList.size() != 0">
            <bind name="joiner" value="@com.google.common.base.Joiner@on('|')"/>
            <bind name="tagIds" value="joiner.join(param.tagIdList)"/>
            HAVING group_concat(distinct tag.id) REGEXP
            #{tagIds}
        </if>

然后再到swagger文档中测试,就可以正常查询到准确的结果条数了（即满足所选多个标签的至少任一项都应该被查出来，也就是或关系）：

但是如果是Integer类型的枚举值列表，使用foreach来拼接正则表达式，目前测试是可以正常查询的，比如另外一个查询场景：之前的一个业务，在新增金融政策的时候，政策类型下拉选只能单选，所以设计的数据表字段直接是枚举int类型，1,2,3来存储的，后来要求这个字段可以下拉多选，所以就改成了字符串类型，多选的时候，传递过来的参数值就类似‘1,2,3’这样逗号拼接的字符串存储到该字段中，同时查询也由单选改成了多选：

所以查询也要改写成这样：


        <if test="@org.apache.commons.collections4.CollectionUtils@isNotEmpty(param.typeList)">
            and type REGEXP
            <foreach collection="param.typeList" open="concat(" item="type" close=")" separator="|">
                #{type,jdbcType=INTEGER}
            </foreach>

        </if>

没有出现查询不到结果的情况，当前端选择两种政策类型时，只要包含这两种类型的一种都会查询出来：