mybatisFlex各種鏈?zhǔn)絪ql寫法小結(jié)
1.關(guān)聯(lián)LEFT JOIN
public List<TocAgentUserRelationVO> selectTocAgentUserRelationByPhones(List<String> 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 <R> Page<R> dietitianList(DietitianListRequest request, Class<R> 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) //計(jì)算好評(píng)率=好評(píng)數(shù)目/總訂單數(shù)目 .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<TocReserveTimeVo> 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 <R> Page<R> dietitianList(DietitianListRequest request, Class<R> 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) //計(jì)算好評(píng)率=好評(píng)數(shù)目/總訂單數(shù)目 .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 <R> Page<R> dietitianList(DietitianListRequest request, Class<R> 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) //計(jì)算好評(píng)率=好評(píng)數(shù)目/總訂單數(shù)目 .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<Long> relationIds) { //每次執(zhí)行1000條 Db.executeBatch(relationIds, 1000, TocGroupPatientRelationsMapper.class , (mapper, relationId) -> { //下面是具體的執(zhí)行sql寫法 UpdateChain.of(mapper) .set(TocGroupPatientRelations::getGroupId, null) .set(TocGroupPatientRelations::getGroupName, "") .where(TocGroupPatientRelations::getRelationId).eq(relationId) .update(); }); return Constants.SUCCESS_CODE; }
到此這篇關(guān)于mybatisFlex各種鏈?zhǔn)絪ql寫法小結(jié)的文章就介紹到這了,更多相關(guān)mybatisFlex鏈?zhǔn)絪ql內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
springmvc+ajax+formdata上傳圖片代碼實(shí)例
這篇文章主要介紹了springmvc+ajax+formdata上傳圖片代碼實(shí)例,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2019-09-09Java為什么使用補(bǔ)碼進(jìn)行計(jì)算的原因分析
這篇文章主要介紹了Java為什么使用補(bǔ)碼進(jìn)行計(jì)算的原因分析,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-08-08Java序列化問題:“Serialized class has not impl
在Java開發(fā)中,序列化(Serialization)是一個(gè)常見的操作,尤其是在分布式系統(tǒng)、網(wǎng)絡(luò)通信或數(shù)據(jù)持久化場景中,然而,序列化過程中可能會(huì)遇到各種問題,其中最常見的一個(gè)錯(cuò)誤是Serialized class has not implement Serializable interface,本文給大家介紹了相關(guān)的解決方法2025-02-02Spring框架實(shí)現(xiàn)AOP添加日志記錄功能過程詳解
這篇文章主要介紹了Spring框架實(shí)現(xiàn)AOP添加日志記錄功能過程詳解,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2019-12-12Java?Spring?Boot請(qǐng)求方式與請(qǐng)求映射過程分析
這篇文章主要介紹了Java?Spring?Boot請(qǐng)求方式與請(qǐng)求映射過程分析,Spring?Boot支持Rest風(fēng)格:使用HTTP請(qǐng)求方式的動(dòng)詞來表示對(duì)資源的操作2022-06-06Java RabbitMQ 中的消息長期不消費(fèi)會(huì)過期嗎
RabbitMQ支持消息的過期時(shí)間,在消息發(fā)送時(shí)可以進(jìn)行指定。 RabbitMQ支持隊(duì)列的過期時(shí)間,從消息入隊(duì)列開始計(jì)算,只要超過了隊(duì)列的超時(shí)時(shí)間配置,那么消息會(huì)自動(dòng)的清除2021-09-09