MybatisPlus 收集

拼接sql

code:

var page = leadsInfoService.lambdaQuery()
        .eq(param.getLeadsStatus() != null, LeadsInfoEntity::getLeadsStatus, param.getLeadsStatus())
        .like(StringUtils.isNotBlank(param.getAgentName()), LeadsInfoEntity::getAgentAliasName, param.getAgentName())
        .apply(StringUtils.isNotBlank(param.getCreateDate()), String.format("date_format(gmt_created,'%%Y-%%m-%%d') = '%s'", param.getCreateDate()))
        .apply(StringUtils.isNotBlank(param.getModifyDate()), String.format("date_format(gmt_modified,'%%Y-%%m-%%d') = '%s'", param.getModifyDate()))
        .apply(StringUtils.isNotBlank(param.getName()), String.format("CONCAT(ifnull(last_name,''),' ',ifnull(first_name,'')) like '%%%s%%'", param.getName()))
        .and(StringUtils.isNotBlank(param.getName()), x -> {
            x.like(LeadsInfoEntity::getFirstName, param.getName()).or()
                    .like(LeadsInfoEntity::getLastName, param.getName()).or()
                    .apply(String.format("CONCAT(ifnull(last_name,''),' ',ifnull(first_name,'')) like '%%%s%%'", param.getName()));
        })
        .orderByDesc(LeadsInfoEntity::getGmtModified)
        .page(new Page<>(pageParam.getPageNum(), pageParam.getPageSize()));

sql:

SELECT
    *** 
FROM
	leads_info 
WHERE
	is_deleted = 'N' 
	AND ( CONCAT(ifnull( last_name, '' ),' ',ifnull( first_name, '' )) LIKE '%hong%' 
	AND ( first_name LIKE '%hong%' OR last_name LIKE '%hong%' OR CONCAT( ifnull( last_name, '' ), ' ', ifnull( first_name, '' )) LIKE '%hong%' )) 
	AND tenant_id = 1 
ORDER BY
	gmt_modified DESC 
	LIMIT 10

使用apply拼接mysql语法

and,or嵌套使用

String.format 注意%，处理特殊字符

concat拼接字段，必须校验字段是否为null，不然整体就为null

And方式嵌套or

        var extendAttrInfoList = leadsExtendAttrInfoService.lambdaQuery()
                .and(x -> {
                    x.eq(LeadsExtendAttrInfoEntity::getAttrId, productAttrId)
                            .in(LeadsExtendAttrInfoEntity::getRelationId, page.getRecords().stream().map(o -> o.getId()).collect(Collectors.toList()));
                })
                .or(campaignNameAttrId > 0L && StringUtils.isNotBlank(param.getCampaignName()), x -> {
                    x.eq(LeadsExtendAttrInfoEntity::getAttrId, campaignNameAttrId)
                            .like(LeadsExtendAttrInfoEntity::getAttrValue, param.getCampaignName());
                })
                .list();

sql：

SELECT id, tenant_id, extend_type, relation_id, attr_group_id, attr_id, attr_value, sort, is_deleted, creator, modifier, gmt_created, gmt_modified, create_user_id, modify_user_id 
FROM leads_extend_attr_info WHERE is_deleted = 'N' 
AND (
(attr_id = 1 AND relation_id IN (68482, 68481, 68480, 68479, 68478, 68477, 68476, 68475, 68474, 68473)) 
OR (attr_id = 158 AND attr_value LIKE '%%')
) AND tenant_id = 1

QueryWapper嵌套循环条件筛选

   var queryWrapper = new QueryWrapper<LeadsExtendAttrInfoEntity>();
        queryWrapper.and(x -> {
            for (LeadsOptionDTO optionField : optionFields) {
                x.or(o -> o.eq("attr_value", optionField.getOptionValue()).eq("attr_id", optionField.getAttrId()));
            }
        });
        queryWrapper.and(x -> x.eq("extend_type", PersonTypeEnum.LINKMAN.getCode()));
        //联系人ids
        var extendList = leadsExtendAttrInfoService
                .list(queryWrapper).stream().map(x -> x.getRelationId()).collect(Collectors.toList());

循环构造筛选条件

下面是拆分筛选：

    /**
     * queryWapper筛选条件
     *
     * @param y
     * @return
     */
    @NotNull
    private Consumer<QueryWrapper<LeadsExtendAttrInfoEntity>> getQueryWrapperConsumer(LeadsRulePathAttrEntity y) {
        if (AttrOperatorEnum.equal.getCode().equals(y.getAttrOperator())) {
            return o -> o.eq("attr_id", y.getAttrId()).eq("attr_value", y.getAttrValue());
        } else if (AttrOperatorEnum.notEqualTo.getCode().equals(y.getAttrOperator())) {
            return o -> o.eq("attr_id", y.getAttrId()).ne("attr_value", y.getAttrValue());
        } else if (AttrOperatorEnum.LessThanEqual.getCode().equals(y.getAttrOperator())) {
            return o -> o.eq("attr_id", y.getAttrId()).le("attr_value", y.getAttrValue());
        } else if (AttrOperatorEnum.lessThan.getCode().equals(y.getAttrOperator())) {
            return o -> o.eq("attr_id", y.getAttrId()).lt("attr_value", y.getAttrValue());
        } else if (AttrOperatorEnum.moreThanEqual.getCode().equals(y.getAttrOperator())) {
            return o -> o.eq("attr_id", y.getAttrId()).ge("attr_value", y.getAttrValue());
        } else if (AttrOperatorEnum.moreThan.getCode().equals(y.getAttrOperator())) {
            return o -> o.eq("attr_id", y.getAttrId()).gt("attr_value", y.getAttrValue());
        } else if (AttrOperatorEnum.lessThan.getCode().equals(y.getAttrOperator())) {
            return o -> o.eq("attr_id", y.getAttrId()).lt("attr_value", y.getAttrValue());
        } else if (AttrOperatorEnum.between.getCode().equals(y.getAttrOperator())) {
            return o -> o.eq("attr_id", y.getAttrId()).between("attr_value", y.getLeftValue(),
                    y.getRightValue());
        } else if (AttrOperatorEnum.contain.getCode().equals(y.getAttrOperator())) {
            return o -> o.eq("attr_id", y.getAttrId()).like("attr_value", y.getAttrValue());
        } else if (AttrOperatorEnum.notContain.getCode().equals(y.getAttrOperator())) {
            return o -> o.eq("attr_id", y.getAttrId()).notLike("attr_value", y.getAttrValue());
        }
        throw new BizException(ErrorEnum.PARAMETER_NOT_EXISTS, MessageFormat.format("has no {0} " +
                "computer method", y.getAttrOperator()));
    }

利用方法：

                var queryWrapper = new QueryWrapper<LeadsExtendAttrInfoEntity>();
                queryWrapper.and(x -> {
                    v.forEach(y -> {
                        if (y.getRequiredType().equalsIgnoreCase("or")) {
                            x.or(getQueryWrapperConsumer(y));
                        } else if (y.getRequiredType().equalsIgnoreCase("and")) {
                            x.and(getQueryWrapperConsumer(y));
                        }
                    });
                });
                queryWrapper.and(x -> x.eq("extend_type", attrType));
                //人员列表
                var ids = leadsExtendAttrInfoService.list(queryWrapper)
                        .stream().map(x -> x.getRelationId()).collect(Collectors.toList());

UpdateWapper更新sql

 //修改待办事项状态为无效
        var updateWrapper = new UpdateWrapper<LeadsPendingItemEntity>();
        updateWrapper.and(x -> {
            for (LeadsInfoEntity info : leadsInfoList) {
                x.or(o -> o.eq("leads_id", info.getId()).eq("follow_agent_id", info.getFollowAgentId()));
            }
        });
        updateWrapper.lambda().set(LeadsPendingItemEntity::getPendingState, PendingStateEnum.INVALID.getState());
        leadsPendingItemService.update(updateWrapper);

实际解析出的sql为：

UPDATE leads_pending_item SET pending_state = 5 WHERE tenant_id = 1 AND is_deleted = 'N' AND ((( leads_id = 1 AND follow_agent_id = 28 ) 
		OR ( leads_id = 4 AND follow_agent_id = 31 ) 
    .....
OR ( leads_id = 299 AND follow_agent_id = 127 )))

聚合处理查询

        QueryWrapper<LeadsAttrInfoEntity> wrapper = new QueryWrapper<>();
        wrapper.groupBy(" attr_group_id ");
        wrapper.select(" count(1) as attr_count, attr_group_id as id ");
        List<Map<String, Object>> maps = leadsAttrInfoService.listMaps(wrapper);

参考：

Mybatis-Plus 官网

MybatisPlus经典示例：使用Wrapper查询指定字段并添加字段函数处理、过滤字段查询、自定义sql、单独set某个字段