关于mybaties通过一个入参参数模糊查询此表多个字段

谁践踏了优雅 2023-07-10 15:40 9阅读 0赞

1. 遇到某种需求,例如输入框里只用输入 姓名/邮箱/登录名 任意一个信息,就可以模糊查询该表的相关信息,例如

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xldHR1Y2Vf_size_16_color_FFFFFF_t_70][]

2. 解决方案:

UserMapper.xml

<select id="getListPageByOneParameter"
                parameterType="com.abc.po.UserPO"
                resultType="com.abc.bo.UserRspBO">
    
            SELECT   
                *
    
            FROM   base_user  AS  t  
            <where>
    
    
                <if test="userName != null and userName != ''">/* 利用userName 字段代替3个*/
                  1=1  AND ( user_name LIKE CONCAT('%',#{userName},'%') OR email LIKE CONCAT('%',#{userName},'%') OR  real_name LIKE CONCAT('%',#{userName},'%') )
                </if>
    
                <if test="roleId != null">
                    AND  role_id = #{roleId} 
                </if>
    
                <if test="roleName != null and roleName != ''">
                    AND  role_name = #{roleName} 
                </if>
    
            </where>
    
            
            <if test="orderBy != null and orderBy != ''">
                ORDER BY   ${orderBy}  /* 排序 */
            </if>
    
        </select>

注意其中关键的这部分

1=1  AND ( user\_name LIKE CONCAT('%',\#\{userName\},'%') OR email LIKE CONCAT('%',\#\{userName\},'%') OR  real\_name LIKE CONCAT('%',\#\{userName\},'%') )

1. 将查询的多个字段放在一个if 判断条件里,并使用 OR 连接起来

2. 模糊查询使用 LIKE CONCAT('%',\#\{userName\},'%')  //userName为入参po对象中的属性名,user\_name为user表中的字段

3. 此次一个参数查询3个字段,需要在AND 后用( ) 将 条件括起来

4. 注意在AND 前面加 1=1 ,否则会报错

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xldHR1Y2Vf_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xldHR1Y2Vf_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200303155050229.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xldHR1Y2Vf,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xldHR1Y2Vf_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200303160646522.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xldHR1Y2Vf,size_16,color_FFFFFF,t_70