mybatis-plus 特殊用法小记_mybatis qw-优快云博客

本文链接：https://blog.youkuaiyun.com/tangfeng61/article/details/111570667

本文介绍了使用MyBatis Plus进行复杂查询的方法，包括通过Lambda表达式构建查询条件、连表查询以及对象作为查询参数等多种场景的应用实例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、指定字段或者函数和条件进行查询查询条件可以按照mybatis-plus 的 wrapper 进行封装，然后使用@select 写函数或者指定字段

LambdaQueryWrapper<CouponReceiver> qw = Wrappers.lambdaQuery(CouponReceiver.class);
qw.in(CouponReceiver::getCouponInfoId, couponIdList).groupBy(CouponUser::getCouponInfoId);
couponReceiverMapper.couponIdCount(qw);


//sql 查询， ${ew.customSqlSegment} 和 @Param(Constants.WRAPPER) Wrapper wrapper 是固定写法
@Select("select count(id) count,coupon_info_id couponInfoId from  coupon_receiver ${ew.customSqlSegment}")
List<CountDto> couponIdCount(@Param(Constants.WRAPPER) Wrapper wrapper);

2、查询条件为字段的连表查询

@Select("select t2.* from coupon_receiver t1 LEFT JOIN  coupon_info t2  " +
            "ON t1.coupon_info_id =t2.id " +
            "WHERE t1.user_id= #{userId} " +
            " and IF(#{couponType} IS NOT NULL,t2.coupon_type = #{couponType},1=1 ) " +
            " and IF(#{hasUsed} IS NOT NULL and #{hasUsed} = 1 and #{queryNotStart} = false,t1.has_used = 1 and use_start_time <= now() and use_end_time >= now(),1=1 )" +

            " and t1.enable = 1 ")
    List<CouponInfo> couponInfoList(@Param("userId") Long userId,
                                    @Param("hasUsed") Integer hasUsed,
                                    @Param("couponType") Integer couponType,
                                    @Param("terminal") String terminal,
                                    @Param("queryNotStart") boolean queryNotStart);

3、查询条件为对象的查询

@Select("select count(o.id)  " +
            "from order_coupon o   left join coupon_user_receiver u on u.id = o.coupon_id  " +
            "left join coupon_info c  on c.id = u.coupon_info_id  "+
            "where 1=1" +
            " and IF(#{req.couponIds} IS NOT NULL,c.id in (#{req.couponIds}), 1=1) " +
            " and IF(#{req.couponUserIds} IS NOT NULL,u.id in (#{req.couponUserIds}), 1=1) " +
            " and IF(#{req.userId} IS NOT NULL,u.user_id = #{req.userId}, 1=1) " +

            " and u.has_used = 2 " +
            " and o.enable = 1 " +
            " and IF(#{req.couponType} IS NOT NULL,c.coupon_type = #{req.couponType}, 1=1) " +

            " and IF(#{req.usedStartTime} IS NOT NULL and #{req.usedEndTime} IS NOT NULL,u.used_time between  #{req.usedStartTime} and #{req.usedEndTime} , 1=1) "
    )
    Integer couponOrderDtoCount(@Param("req") OpCouponReq req);