mybatisFlex各种链式sql写法小结

1.关联left join

 public list selecttocagentuserrelationbyphones(list phones) {
        querywrapper querywrapper = querywrapper.create()
                .select(toc_agent_user_relation.all_columns)
                .select(toc_user_info.phone,toc_user_info.nick_name)
                .select(toc_group_patient_relations.doctor_id,toc_group_patient_relations.create_time.as("binddoctortime"))
                .from(toc_agent_user_relation)
                .leftjoin(toc_user_info).on(toc_user_info.id.eq(toc_agent_user_relation.user_id))
                .leftjoin(toc_group_patient_relations).on(toc_group_patient_relations.patient_id.eq(toc_agent_user_relation.user_id))
                .where(toc_user_info.phone.in(phones))
                .orderby(toc_agent_user_relation.create_time.desc());
        return tocagentuserrelationmapper.selectlistbyqueryas(querywrapper,tocagentuserrelationvo.class);
    }

2.find_in_set+left join+and (or)写法

 public  page dietitianlist(dietitianlistrequest request, class astype) {
        querywrapper querywrapper = querywrapper.create()
                .select(toc_user_info.id,toc_user_info.nick_name,toc_user_info.img_url,toc_user_info.tag,toc_user_info.good_at,toc_user_info.good_at_remark)
                .select(toc_nutritionist_order_num.consult_order_number)
                //计算好评率=好评数目/总订单数目
                .select(toc_nutritionist_order_num.good_reputation_number.divide(toc_nutritionist_order_num.consult_order_number).as("goodrate"))
                .from(toc_user_info)
                .leftjoin(toc_nutritionist_order_num).on(toc_nutritionist_order_num.user_id.eq(toc_user_info.id));
        querywrapper.where(toc_user_info.platform.eq(3));
        querywrapper.where(toc_user_info.user_type.eq(usertypeenum.toc_doctor));
        if(request.getillnessid() != null){
            querywrapper.and("find_in_set('" + request.getillnessid() + "',good_at)");
        }

        if(request.getillnessids() != null && !request.getillnessids().isempty()){
            querywrapper.and(q -> { for (long illnessid : request.getillnessids()) { q.or("find_in_set('" + illnessid + "',good_at)"); } });

        }
        querywrapper.orderby("goodrate", false);
        pagedomain pagedomain = tablesupport.buildpagerequest();
        return tocuserinfomapper.paginateas(pagedomain.getpagenum(), pagedomain.getpagesize(), querywrapper, astype);
    }

3.修改部分字段写法

   public void updatereservephone(nutritionistrequest request) {
        updatechain.of(tocuserinfo.class)
                .set(tocuserinfo::getreservephone, request.getreservephone())
                .where(tocuserinfo::getid).eq(request.getuserid())
                .update();
    }

4.sum+case_().when().then().else_().end()写法

    public statisticsreturnvo selectregistrationcodeincome(statisticsrequest request) {
        querywrapper wrapper = querywrapper.create()
                .select(sum(case_().when(sys_wx_pay_order.user_from.eq(4)).then(sys_wx_pay_order.pay_money).else_(0.00).end()).as("num1"))
                .select(sum(case_().when(sys_wx_pay_order.user_from.eq(5)).then(sys_wx_pay_order.pay_money).else_(0.00).end()).as("num2"))
                .select(sum(case_().when(sys_wx_pay_order.user_from.eq(2)).then(sys_wx_pay_order.pay_money).else_(0.00).end()).as("num3"))
                .from(sys_wx_pay_order)
                .where(sys_wx_pay_order.user_from.in(2,4,5));
        wrapper.eq(syswxpayorder::getagentuserid, request.getagentid());
        wrapper.between(syswxpayorder::getcreatetime, request.getbegintime(), request.getendtime());
        wrapper.eq(syswxpayorder::getpaystate,1);
        return syswxpayordermapper.selectonebyqueryas(wrapper, statisticsreturnvo.class);
    }

4.count+case_().when().then().else_().end()写法

public list getreservetimeslotrule(tocreservequery query) {
        querywrapper querywrapper = querywrapper.create()
                .select(toc_reserve_time_slot_rule.id.as("slotid"),
                        toc_reserve_time_slot_rule.time_slot,
                        toc_reserve_time_slot_rule.max_capacity.as("totalavailable"))
                .select(count(case_().when(toc_reserve_detail.status.eq(1)).then(toc_reserve_detail.id).else_(null).end()).as("reservedcount"))
                .select(toc_reserve_time_slot_rule.max_capacity.subtract(count(toc_reserve_detail.id)) .as("remainingslots"))
                .from(toc_reserve_time_slot_rule)
                .leftjoin(toc_reserve_detail).on(toc_reserve_detail.slot_id.eq(toc_reserve_time_slot_rule.id))
                .where(toc_reserve_time_slot_rule.date_rule_id.eq(query.getid()))
                .groupby(toc_reserve_time_slot_rule.id, toc_reserve_time_slot_rule.time_slot, toc_reserve_time_slot_rule.max_capacity);
        return tocreservetimeslotrulemapper.selectlistbyqueryas(querywrapper,tocreservetimevo.class);
    }

5.字段加减乘除写法

 public  page dietitianlist(dietitianlistrequest request, class astype) {
        querywrapper querywrapper = querywrapper.create()
                .select(toc_user_info.id,toc_user_info.nick_name,toc_user_info.img_url,toc_user_info.tag,toc_user_info.good_at,toc_user_info.good_at_remark)
                .select(toc_nutritionist_order_num.consult_order_number)
                //计算好评率=好评数目/总订单数目
                .select(toc_nutritionist_order_num.good_reputation_number.divide(toc_nutritionist_order_num.consult_order_number).as("goodrate1"))
                .select(toc_nutritionist_order_num.good_reputation_number.multiply(toc_nutritionist_order_num.consult_order_number).as("goodrate2"))
                .select(toc_nutritionist_order_num.good_reputation_number.subtract(toc_nutritionist_order_num.consult_order_number).as("goodrate3"))
                .select(toc_nutritionist_order_num.good_reputation_number.add(toc_nutritionist_order_num.consult_order_number).as("goodrate4"))
                .from(toc_user_info)
                .leftjoin(toc_nutritionist_order_num).on(toc_nutritionist_order_num.user_id.eq(toc_user_info.id));
        querywrapper.where(toc_user_info.platform.eq(3));
        querywrapper.where(toc_user_info.user_type.eq(usertypeenum.toc_doctor));
        pagedomain pagedomain = tablesupport.buildpagerequest();
        return tocuserinfomapper.paginateas(pagedomain.getpagenum(), pagedomain.getpagesize(), querywrapper, astype);
    }

6. and +(字段 or 字段写法)

  public  page dietitianlist(dietitianlistrequest request, class astype) {
        querywrapper querywrapper = querywrapper.create()
                .select(toc_user_info.id,toc_user_info.nick_name,toc_user_info.img_url,toc_user_info.tag,toc_user_info.good_at,toc_user_info.good_at_remark)
                .select(toc_nutritionist_order_num.consult_order_number)
                //计算好评率=好评数目/总订单数目
                .select(toc_nutritionist_order_num.good_reputation_number.divide(toc_nutritionist_order_num.consult_order_number).as("goodrate"))
                .from(toc_user_info)
                .leftjoin(toc_nutritionist_order_num).on(toc_nutritionist_order_num.user_id.eq(toc_user_info.id));
        querywrapper.and(toc_nutritionist_order_num.good_reputation_number.eq(1).or(toc_nutritionist_order_num.good_reputation_number.gt(1)));

        
        pagedomain pagedomain = tablesupport.buildpagerequest();
        return tocuserinfomapper.paginateas(pagedomain.getpagenum(), pagedomain.getpagesize(), querywrapper, astype);
    }

7.批量修改写法

  public int batchuntiegroup(list relationids) {
        //每次执行1000条
        db.executebatch(relationids, 1000, tocgrouppatientrelationsmapper.class
                , (mapper, relationid) -> {
                    //下面是具体的执行sql写法
                    updatechain.of(mapper)
                            .set(tocgrouppatientrelations::getgroupid, null)
                            .set(tocgrouppatientrelations::getgroupname, "")
                            .where(tocgrouppatientrelations::getrelationid).eq(relationid)
                            .update();
                });
        return constants.success_code;
    }

到此这篇关于mybatisflex各种链式sql写法小结的文章就介绍到这了,更多相关mybatisflex链式sql内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！