mybatis-plus条件构造器整理及使用示例时间查询

1、mybatis-plus条件构造器整理及使例时间查询、条件构造器览构造器数据载官集中整理1、常法例条件构造器构造法构造例eq等于 =eq(“name”, “王”)name = 王ne不等于 ne(“name”, “王”)name 王gt于 gt(“age”, 18)age 18ge于等于 =ge(“age”, 18)age = 18lt于 age 18le于等于 age age between 18 and 30likeLIKE %值%like(“name”, “王”)name like %王%in字段 IN (value.get(0), value.get(1), )in(“age”,1,2

2、,3)age in (1,2,3)orderByAsc排序：ORDER BY 字段, ASCorderByAsc(“id”, “name”)order by id ASC,name ASCorderByDesc排序：ORDER BY 字段, DESCorderByDesc(“id”, “name”)order by id DESC,name DESCorderBy排序：ORDER BY 字段, orderBy(true, true, “id”, “name”)order by id ASC,name ASCgroupBy分组：GROUP BY 字段groupBy(“id”, “name”)gr

3、oup by id,nameor拼接 OReq(“id”,1).or().eq(“name”,“王”)id = 1 or name = 王andAND 嵌套and(i - i.eq(“name”, “李”).ne(“status”, “活着”)and (name = 李 and status 活着)2、剩余法例条件构造器构造法构造例allEq全部eq例1: allEq(id:1,name:“王”,age:null)id = 1 and name = 王 and age is nullnotBetweenNOT BETWEEN 值1 AND 值2notBetween(“age”, 18, 30)

4、age not between 18 and 30notLikeNOT LIKE %值%notLike(“name”, “王”)name not like %王%likeLeftLIKE %值likeLeft(“name”, “王”)name like %王likeRightLIKE 值%likeRight(“name”, “王”)name like 王%isNull字段 IS NULLisNull(“name”)name is nullisNotNull字段 IS NOT NULLisNotNull(“name”)name is not nullnotIn字段 IN (value.get(0

5、), value.get(1), )notIn(“age”,1,2,3)age not in (1,2,3)nested正常嵌套 不带 AND 或者 ORnested(i - i.eq(“name”, “李”).ne(“status”, “活着”)(name = 李 and status 活着)3、sql 拼接法例条件构造器构造法构造例apply拼接 sqlapply(“id = 1”)id = 1last视优化规则直接拼接到 sql 的最后last(“limit 1”)exists拼接 EXISTS ( sql语句 )exists(“select id from table where ag

6、e = 1”)exists (select id from table where age = 1)notExists拼接 NOT EXISTS ( sql语句 )notExists(“select id from table where age = 1”)not exists (select id from table where age = 1)inSql字段 IN ( sql语句 )inSql(“age”, “1,2,3,4,5,6”)age in (1,2,3,4,5,6)notInSql字段 NOT IN ( sql语句 )notInSql(“age”, “1,2,3,4,5,6”)

7、age not in (1,2,3,4,5,6)havingHAVING ( sql语句 )having(“sum(age) 10”)having sum(age) 10、只返回部分字段例1、只返回 product_id 和 agent_info_id 字段.select(ActivityCouponSpu.class, info - info.getColumn().equals(product_id) | info.getColumn().equals(agent_info_id)2、不返回 product_id 和 agent_info_id 字段，其余全返回.select(Activi

8、tyCouponSpu.class, info - !info.getColumn().equals(product_id) & !info.getColumn().equals(agent_info_id)3、完整例（只返回 product_id 和 agent_info_id 字段）List activityCouponSpus = activityCouponSpuMapper.selectList(new LambdaQueryWrapper().select(ActivityCouponSpu.class, info - info.getColumn().equals(product

9、_id) | info.getColumn().equals(agent_info_id).eq(ActivityCouponSpu:getCouponId, ActivityCouponVo.getId().in(ActivityCouponSpu:getProductId, Arrays.asList(goodsIds.split(,);三、时间处理相关写法1、mybatis-plus 使 Lambda 查询例LambdaQueryWrapper：查询LambdaUpdateWrapper：修改List activityInfos = activityInfoMapper.selectLi

10、st(new LambdaQueryWrapper().orderByDesc(ActivityInfo:getStartTime).le(ActivityInfo:getShowTime, date).ge(ActivityInfo:getNoShowTime, date).eq(ActivityInfo:getStatus, StatusEnum.STATUS_YES).eq(ActivityInfo:getType, ActivityTypeEnum.IS_BACK_SPIKE);2、双字段时间段查询 le / ge (startTime + endTime )getShowTime：

11、活动开始展时间 getNoShowTime： 活动结束展时间 date: 当前时间查询当前正在处于活动期间的数据.ge(StringUtils.isNotBlank(starTime), ActivityCoupon:getStarTime, starTime).le(StringUtils.isNotBlank(endTime), ActivityCoupon:getEndTime, endTime)db(2020-2-21) = ?(2020-2-20) and (2020-3-20) = ?2020-3-213、双字段时间段查询 between ( Time )between（判空, 时间字段，开始时间，结束时间） time : 时间字段查询 time 在st