Mybatis动态sql拼接多个like模糊查询

1. 需求描述

keywords是一个ArrayList集合,其中包含了若干String类型关键字,需要根据不同的关键字进行模糊查询匹配

2.mapper接口

public interface ServeMapper extends BaseMapper<Serve> {

    // 本平台发送需求名称, 根据名称匹配服务信息
    List<ToThirdServeVo> matchServesByReqName(@Param("keys") List<String> keys);
}

3.xml文件编写

 <select id="matchServesByReqName" resultType="com.kjfw.shannxi.entity.responseVo.ToThirdServeVo" parameterType="list">
        select s.id as serveId, s.name , s.category , s.content as serviceDescription ,
               s.imageUrl as image , s.score as star , i.id as userId , i.contract as contact, i.phone as telephone,
               i.email , i.name as company , i.address
        from serve s
                 left join institution i on s.institutionId = i.id
        <if test="keys != null">
            <where>
                <foreach collection="keys" item="item" index="index" separator=" or ">
                    s.name like concat('%', #{item}, '%')
                </foreach>
            </where>
        </if>
    </select>

4.分析

foreach中collection的值"keys"是接口中的list参数名,用@Params(“keys”)指定名称

模糊查询拼接使用concat(‘%’,#{item},‘%’),而不使用’%${item}%', 防止sql注入