【MybatisPlus】Java代码定制化SQL与示例(持续更新...)

前言

一切为了提高生产力。

MybatisPlus几个重要API介绍

select：指定‘查询’出的字段数量、字段名称；

group by：分组；

last：在sql语句后面追加；

MybatisPlus-API示例

distinct示例

   public Set<String> selectIndustryNameList () {
        QueryWrapper<AiEmployeeStatisticDO> queryWrapper = new QueryWrapper<AiEmployeeStatisticDO>();
        queryWrapper.select("distinct (industry_name)");
        queryWrapper.eq("deleted", 0);
        List<AiEmployeeStatisticDO> statisticDOS = employeeStatisticDAO.selectList(queryWrapper);
        return statisticDOS.stream().map(AiEmployeeStatisticDO::getIndustryName).collect(Collectors.toSet());
    }

group by结合selectList示例

SQL：SELECT  esp_flag, ip  FROM atta_mail_event_record  WHERE  deleted = ? GROUP BY esp_flag,ip ；

List<MailEventRecordDO>结构如下：

group by结合selectMaps示例

default List<String> queryServiceProvideList(@NotEmpty Mid mid,
                                             @NotEmpty String messageSendRequestId) {
        QueryWrapper<MessageSendDetailDO> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("service_provider_type, count(0) as service_provider_type_count");
        queryWrapper.eq(MID, mid);
        queryWrapper.eq(MESSAGE_SEND_REQUEST_ID, messageSendRequestId);
        queryWrapper.groupBy(SERVICE_PROVIDER_TYPE); // 支持可变参数
        List<Map<String, Object>> list = this.selectMaps(queryWrapper) == null ? new ArrayList<>() : this.selectMaps(queryWrapper);
        ArrayList<String> resultList   = new ArrayList<>();
        list.forEach(sqlMap -> {
            String serviceProviderType = (String)sqlMap.get("service_provider_type");
            Long providerTypeCount     = (Long)sqlMap.get("service_provider_type_count");
            resultList.add(serviceProviderType + ":" + providerTypeCount);
        });
        return resultList;
    }

groupby函数的参数支持【可变参数】：queryWrapper.groupBy(ESP, IP)。

List<Map<String, Object>> list中存储的数据结构如下：

last示例

default List<MessageSendDetailDO> rollSelectBackDetail(Mid mid, Long id, Date beginTime, Date endTime) {

        QueryWrapper<MessageSendDetailDO> queryWrapper = new QueryWrapper<>();

        if (mid != null) {
            queryWrapper.eq(MID, mid);
        }
        queryWrapper.eq(STATUS, MessageSendDetailStatus.SENT);
        queryWrapper.in(DELIVERY_STATUS, Arrays.asList(MessageSendDeliveryStatus.HARDBACK, MessageSendDeliveryStatus.SOFTBACK));
        queryWrapper.ge(MODIFIED_TIME, beginTime);
        queryWrapper.le(MODIFIED_TIME, endTime);
        queryWrapper.gt("id", id);
        queryWrapper.orderByAsc("id");
        queryWrapper.last("limit 100");
        return this.selectList(queryWrapper);

    }