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

小小码 • 2023-04-03 17:30 • 代码基础 • 阅读 148

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说Mybatis——动态sql+字符串匹配导致的判断问题,希望您对编程的造诣更进一步.

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+字符串匹配导致的判断问题

原文地址：https://www.cnblogs.com/Arno-vc/p/13461619.html

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/6795.html

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

相关推荐

发表回复