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

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

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

    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
    
        is_delete = 0 AND gj_user_type = #{userType} AND gj_hash_p = #{gjHashP}
        
            AND gj_tougao_pingtai_num >= #{tougaoState}
        
        
            AND gj_type = #{gjType}
        
        
            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})
        
        
            AND gj_lasttime <= #{date1} AND  >= #{date2}
        
    
    LIMIT 0,20

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

解决方案:

1. 修改.xml文件

       
           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
           
               is_delete = 0 AND gj_user_type = #{userType} AND gj_hash_p = #{gjHashP}
               
                   AND gj_tougao_pingtai_num >= #{tougaoState}
               
               
                   AND gj_type = #{gjType}
               
               
                   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})
               
               
                   AND gj_lasttime <= #{date1} AND  >= #{date2}
               
           
           LIMIT 0,20
       
   

2. 修改service层

   
       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})
       
   

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

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