mybatis mapper.xml 区间查询条件详解

<select id="selectByExample" resultMap="BaseResultMap" parameterType="com.test.entity.TestQuery">
SELECT 
    *
    FROM
    test s
    WHERE 1=1

    <!-- 创建时间 -->
    <if test="createTime!=null and createTime!=''">
        AND s.create_time = #{createTime,jdbcType=TIMESTAMP}
    </if>
    <!-- 开始创建时间，结束创建时间都不为空 -->
    <if test="beginCreateTime!=null and beginCreateTime!='' and endCreateTime!=null and endCreateTime!=''">
        AND s.create_time &gt;= #{beginCreateTime,jdbcType=TIMESTAMP}
        AND s.create_time &lt;= #{endCreateTime,jdbcType=TIMESTAMP}
    </if>
    <!-- 开始不为空，结束为空 null -->
    <if test="beginCreateTime!=null and beginCreateTime!='' and endCreateTime==null">
        AND s.create_time &gt;= #{beginCreateTime,jdbcType=TIMESTAMP}
    </if>
    <!-- 开始不为空，结束为空 '' -->
    <if test="beginCreateTime!=null and beginCreateTime!='' and endCreateTime==''">
        AND s.create_time &gt;= #{beginCreateTime,jdbcType=TIMESTAMP}
    </if>
    <!-- 开始为空 null，结束不为空 -->
    <if test="beginCreateTime==null  and endCreateTime!=null and endCreateTime!=''">
        AND s.create_time &lt;= #{endCreateTime,jdbcType=TIMESTAMP}
    </if>
    <!-- 开始为空 ''，结束不为空 -->
    <if test="beginCreateTime==''  and endCreateTime!=null and endCreateTime!=''">
        AND s.create_time &lt;= #{endCreateTime,jdbcType=TIMESTAMP}
    </if>

</select>

　　　　 Weekend<User> weekend = new Weekend<>(User.class);
        //关键字查询部分
        String keyWord = pageReq.geTKEyword();
        WeekendCriteria<User, Object> keywordCriteria = weekend.weekendCriteria();
        if (StringUtils.isNotEmpty(keyword)) {
            keywordCriteria.orLike(User::getUserName, keyword).orLike(User::getPoliceNo, keyword).orLike(User::getRealName, keyword);
　　　　　　  //此处不需要再用下面这一句了,不然上面这个条件组合会重复一次
            //weekend.and(keywordCriteria)
        }
        //部门查询部分
        Example example = new Example(User.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("departmentId", departmentId);
        weekend.and(criteria);

        PageHelper.startPage(pageReq.getPageIndex(), pageReq.getPageSize());
        List<User> users = userMapper.selectByExample(weekend);

        Example e = new Example(User.class);
        Example.Criteria c = e.createCriteria();

        //关键字查询部分
        String keyword = pageReq.getKeyword();
        if (StringUtils.isNotEmpty(keyword)) {
            c.orEqualTo("userName", keyword).orEqualTo("policeNo",keyword).orEqualTo("realName",keyword);
        }
        //部门查询部门
        Example.Criteria criteria = e.createCriteria();
        criteria.andEqualTo("departmentId", departmentId);
        e.and(criteria);

        PageHelper.startPage(pageReq.getPageIndex(), pageReq.getPageSize());
        List<User> users = userMapper.selectByExample(e);

WHERE (
  user_name = ? 
  OR police_no = ? 
  OR real_name = ?
) 
AND (department_id = ?)