想要多个条件来查询对应的记录 起始时间 结束时间 用户名 状态

Question

 SELECT
            u.id,u.username,u.password,u.phone,u.state,u.regdate,
            r.id rid,r.rolename,r.code
        FROM
            USER u,role r,user_role ur
        WHERE
            ur.uid=u.id
        AND
            ur.rid=r.id
        AND
         1=1
        and(username like #{info} or phone like #{info})
        or
            state=#{state}
        or
            regdate
        BETWEEN
            #{startdate}
        AND
            #{enddate}

发现出了问题 用的是mybatis 主要是连接时or和and该怎么用不是很清楚
且当某项为空时 就忽略

SELECT
        u.id,u.username,u.password,u.phone,u.state,u.regdate,
        r.id rid,r.rolename,r.code
        FROM
        USER u,role r,user_role ur
        <where>
            ur.uid=u.id
            AND
            ur.rid=r.id
            AND
            1=1
            <if test="info!=null and info !=&quot;&quot;">
                and (
                username like #{info}
                or phone like #{info}
                )
            </if>
            <if test="state!=null and state !=&quot;&quot;">
                and state=#{state}
            </if>
            <if test="startdate!=null and startdate !=&quot;&quot;">
                and regdate>=#{startdate}
            </if>
            <if test="enddate!=null and enddate !=&quot;&quot;">
                and #{enddate}>regdate
            </if>

        </where>

补充了 看下

已经解决啦 现把没问题的代码放上 有需要可以参考下

<select id="getPageInfo" resultMap="userRoleMap">
        SELECT
        u.id,u.username,u.password,u.phone,u.state,u.regdate,
        r.id,r.rolename,r.code
        FROM
        USER u,role r,user_role ur
        <where>
            ur.uid=u.id
            AND
            ur.rid=r.id
            and
            1=1
            <if test="info!='%%'">
                and (
                u.username like #{info}
                or u.phone like #{info}
                )
            </if>

            <if test="state!=null and state!=&quot;&quot;">
                and u.state=#{state}
            </if>
            <if test="startdate!=null and startdate !=&quot;&quot;">
                and u.regdate &gt;=#{startdate}
            </if>
            <if test="enddate!=null and enddate !=&quot;&quot;">
                and #{enddate} &gt;u.regdate
            </if>

        </where>
    </select>
    <resultMap id="userRoleMap" type="cn.bb.bbtravel.model.User">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="phone" property="phone"></result>
        <result column="state" property="state"></result>
        <result column="regdate" property="regdate"></result>
        <collection property="roles" ofType="cn.bb.bbtravel.model.Role">
            <id column="rid" property="id"></id>
            <result column="rolename" property="rolename"></result>
            <result column="code" property="code"></result>
        </collection>
    </resultMap>

Answer 1

and是与运算，or是或运算，or的运算级最低，判空的话可以用

<if test="state != null and state != ''" >
                    and state =#{state}
</if>