mybatis中LIKE模糊查询

mybatis中LIKE模糊查询

mybatis 中模糊查询有几种方式的，我主要用了下面的方式

1. 使用关键字CONCAT拼接，如下

<select id="getByTitleAndCorpAccId" parameterType="java.util.Map" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from UMP_MSG_TEMPLATE
        where CORP_ACC_ID = #{corpAccId,jdbcType=INTEGER}
        <if test="title != null">
            AND TITLE like CONCAT('%', #{title,jdbcType=VARCHAR}, '%')
        </if>
    </select>

2. 使用locate()函数

locate()函数： 判断字符串（string）中是否包含另一个字符串（subStr）
locate(subStr,string) ：函数返回subStr在string中出现的位置

<select id="searchApiAccountList" parameterType="java.util.Map"
            resultType="com.pccwhkt.ump.portal.umportal.sys.user.bean.UmgLoginAcc">
        select <include refid="Base_Column_List" />
        from UMG_LOGIN_ACC
        <if test="name != null and name != ''">
            where LOCATE(#{corp_id}, CORP_ID)>0
        </if>

        <if test="login_id != null and login_id != ''">
            <choose>
                <when test="corp_id != null and corp_id != ''">
                    and LOCATE(#{corp_name}, CORP_ACC_NAME)>0
                </when>
                <otherwise>
                    where LOCATE(#{corp_name}, CORP_ACC_NAME)>0
                </otherwise>
            </choose>
        </if>

        <if test="status != null and status != ''">
            <choose>
                <when test="(corp_id != null and corp_id != '') or (corp_name != null and corp_name != '')">
                    and LOCATE(#{status}, STATUS)>0
                </when>
                <otherwise>
                    where LOCATE(#{status}, STATUS)>0
                </otherwise>
            </choose>
        </if>

        <if test="msisdn != null and msisdn != ''">
            <choose>
                <when test="(corp_id != null and corp_id != '') or (corp_name != null and corp_name != '') or (status != null and status != '')">
                    and LOCATE(#{type}, TYPE)>0
                </when>
                <otherwise>
                    where LOCATE(#{type}, TYPE)>0
                </otherwise>
            </choose>
        </if>
    </select>

其他方式请参考这篇文章：https://blog.csdn.net/zhenwei1994/article/details/81876278