Mybatis——动态sql+字符串匹配导致的判断问题

在mybatis的学习中,狂神建议字符串匹配直接将模糊匹配的符号放在字符串中,如:匹配'keWord',那么实际所使用的参数应该为'%keyWord%'

map.put("keyWord","%" + keyWord + "%");

<select id="getUserLikeGaojianList" parameterType="map" resultMap="userLikeGaojianEntity">

    SELECT *

    FROM news_base_gaojian g LEFT JOIN news_base_gaojian_like l ON l.gid=g.gj_id and l.uid = #{uId} and type=2

    <where>

        is_delete = 0 AND gj_user_type = #{userType} AND gj_hash_p = #{gjHashP}

        <if test="tougaoState != null">

            AND gj_tougao_pingtai_num >= #{tougaoState}

        </if>

        <if test="gjType != null">

            AND gj_type = #{gjType}

        </if>

        <if test="keyWord != null ">

            AND ( gj_title LIKE #{keyWord} OR gj_title2 LIKE #{keyWord} OR gj_bianji LIKE #{keyWord} OR gj_laiyuan LIKE #{keyWord} OR gj_excerpt LIKE #{keyWord})

        </if>

        <if test="date1 != null and date2 != null">

            AND gj_lasttime &lt;= #{date1} AND  &gt;= #{date2}

        </if>

    </where>

    LIMIT 0,20

</select>

注意:此时就不能用keyWord != null做为动态sql中test的参数,因为当关键字keyWord为空时,keyWord="%null%"

解决方案:

修改.xml文件

    <select id="getUserLikeGaojianList" parameterType="map" resultMap="userLikeGaojianEntity">

        SELECT *

        FROM news_base_gaojian g LEFT JOIN news_base_gaojian_like l ON l.gid=g.gj_id and l.uid = #{uId} and type=2

        <where>

            is_delete = 0 AND gj_user_type = #{userType} AND gj_hash_p = #{gjHashP}

            <if test="tougaoState != null">

                AND gj_tougao_pingtai_num >= #{tougaoState}

            </if>

            <if test="gjType != null">

                AND gj_type = #{gjType}

            </if>

            <if test="keyWord != '%null%' ">

                AND ( gj_title LIKE #{keyWord} OR gj_title2 LIKE #{keyWord} OR gj_bianji LIKE #{keyWord} OR gj_laiyuan LIKE #{keyWord} OR gj_excerpt LIKE #{keyWord})

            </if>

            <if test="date1 != null and date2 != null">

                AND gj_lasttime &lt;= #{date1} AND  &gt;= #{date2}

            </if>

        </where>

        LIMIT 0,20

    </select>

修改service层

<if test="keyWord != null ">

    AND ( gj_title LIKE #{keyWord} OR gj_title2 LIKE #{keyWord} OR gj_bianji LIKE #{keyWord} OR gj_laiyuan LIKE #{keyWord} OR gj_excerpt LIKE #{keyWord})

    </if>

巴特西

Mybatis——动态sql+字符串匹配导致的判断问题

最新文章

热门文章