mybaitsplus mapper层

mybatis-plus 中的<if test=“”>test中多条件

 

 

<if test="name != null and name != '测试'"> 
      AND name like '%' #{name} '%' 
</if>

----------------------------------------------------------------
用where和if进行组合，当条件不成立时，if条件后的内容包括and会存在，因此会对整个sql语句产生影响
写法一：
每个if判断前添加and时，在if前添加1 = 1

    <select id="select" resultType="com.tzh.bean.Entity">
        select * from department
        where 1=1
            <if test="id!=null">
                and id=#{id}
            </if>
            <if test="name!=null and name!=''">
                and t_name like #{name}
            </if>
            <if test="email!=null and email.trim()!=''">
                and email=#{email}
            </if> 
     </select>

 

----------------------------------------------------------------

二 :使用转义符

    <select id="select" resultType="com.tzh.bean.Entity">
        select * from department
        where 
     <if test="id!=null">
            id=#{id}
        </if>
        <if test="name!=null &amp;&amp; name!=&quot;&quot;">
            and t_name like #{name}
        </if>
        <if test="email!=null and email.trim()!=&quot;&quot;">
            and email=#{email}
        </if> 
    </where>

-------------------------------------------------------------------------------------------------------

    /**
     * 分页查询用户
     * @param page
     * @param reqDto
     * @return
     */
    Page<User> PageUser(IPage<User> page, @Param("query") UserSearchReqDto reqDto);

 

<select id="PageUser" resultType="com.chancein.cloud.config.domain.user.User">
        SELECT
        t1.*
        FROM
        cfg_user t1
        LEFT JOIN cfg_role_user t2 ON t2.user_id = t1.id
        LEFT JOIN cfg_role t3 ON t3.id = t2.role_id
        LEFT JOIN cfg_dept_user t4 ON t4.user_id = t1.id
        LEFT JOIN cfg_dept t5 ON t5.id = t4.dept_id
        WHERE
        t1.del_flag = 0
        <if test="query.realName != null">
            AND t1.real_name like CONCAT('%',CONCAT(#{query.realName},'%'))
        </if>
        <if test="query.roleId != null">
            AND t3.id = #{query.roleId}
        </if>
        <if test="query.deptId != null">
            AND t5.id = #{query.deptId}
        </if>
    </select>