Mybatis-Plus 收集
拼接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);
参考：
MybatisPlus经典示例：使用Wrapper查询指定字段并添加字段函数处理、过滤字段查询、自定义sql、单独set某个字段
