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 = ?)