QueryWrapper.in几种查询方式QueryWrapper.in查询不准优化结果，如何使用QueryWrapper.in的SQL查询几种方式讲解

冷不防 2023-01-03 14:06 267阅读 0赞

#  QueryWrapper查询方式案例 #

##        in参考下面红色标注字段，其他查询参考下面 ##

public List<String> getNextNewsIdList(NewsSearchVo searchVo) throws ParseException \{  
    QueryWrapper<TbmNewsProcessing> qw = new QueryWrapper<>();

if (searchVo.getCreateDate() != null) {
        qw.ge("create_time", searchVo.getCreateDate());
    }
    if (searchVo.getCreateDateEnd() != null) {
        qw.le("create_time", DateUtil.parse(searchVo.getCreateDateEnd()).plusSeconds(24 * 60 * 60 - 1));
    }
    if (StringUtils.isNotBlank(searchVo.getNewsType()) && searchVo.getNewsType().contains(",")) {
        //此处最好是List入参，in才更为精准
       List<String> newsTypes=Arrays.asList(searchVo.getNewsType().split(",")); qw.in("news_type", newsTypes);
    } else if (StringUtils.isNotBlank(searchVo.getNewsType()) && !searchVo.getNewsType().contains(",")) {
        qw.eq("news_type", searchVo.getNewsType());
    }
    if (StringUtils.isNotBlank(searchVo.getMediaSource())) {
        qw.eq("media_source_code", searchVo.getMediaSource());
    }
    if (StringUtils.isNotBlank(searchVo.getAuthor())) {
        qw.eq("author", searchVo.getAuthor());
    }
    if (CollectionUtils.isNotEmpty(searchVo.getTagIds()) && searchVo.getTagIds().size() == 2) {
        if (StringUtils.isNotBlank(searchVo.getTagIds().get(0)) || StringUtils.isNotBlank(searchVo.getTagIds().get(1))) {
            
        qw.inSql("news_id", "SELECT DISTINCT(news_id) FROM tbm_news_tags WHERE register_tag_id in (" + registerTagIds + ")");
        }
    }
    if (searchVo.getAreaCode() != null) {
        qw.inSql("news_id", "SELECT DISTINCT(news_id) FROM tbm_news_area_tags WHERE business_id = " + searchVo.getAreaCode());
    }
    
    if (searchVo.getAppIds() != null) {
        if (searchVo.getAppIds().equals("2")) {
            qw.orderByDesc("priority");
        } else if (searchVo.getAppIds().equals("3")) {
            qw.orderByDesc("xf_priority");
        } else if (searchVo.getAppIds().equals("5")) {
            qw.orderByDesc("yq_priority");
        } else if (searchVo.getAppIds().equals("6")) {
            qw.orderByDesc("jyp_priority");
        }
    }
    qw.orderByDesc("create_time")
            .eq("handle_status", 0)
           .notInSql("news_id", "select news_id from tbm_discard_task where discard_user_id=" + searchVo.getSessionUserId())
            .last("limit  100");
    qw.select("    news_id    ");
    List<TbmNewsProcessing> newsIdObjectList = processingMapper.selectList(qw);

public List<String> getNextNewsIdList(NewsSearchVo searchVo) throws ParseException {
            QueryWrapper<TbmNewsProcessing> qw = new QueryWrapper<>();
            if (StringUtils.isNotBlank(searchVo.getNewsId())) {
                qw.like("news_id", SqlUtil.escapeLike(searchVo.getNewsId().trim()));
            }
            if (StringUtils.isNotBlank(searchVo.getAppIds())) {
                qw.like("app_ids", searchVo.getAppIds());
            }
            if (StringUtils.isNotBlank(searchVo.getTitle())) {
                qw.like("title", SqlUtil.escapeLike(searchVo.getTitle().trim()));
            }
            if (StringUtils.isNotBlank(searchVo.getDsNewsColumns())) {
                qw.eq("ds_news_columns", SqlUtil.escapeLike(searchVo.getDsNewsColumns().trim()));
            }
            if (searchVo.getUpdateTime() != null) {
                qw.ge("update_time", searchVo.getUpdateTime());
            }
            if (searchVo.getUpdateTimeEnd() != null) {
                qw.le("update_time", DateUtil.parse(searchVo.getUpdateTimeEnd()).plusSeconds(24 * 60 * 60 - 1));
            }
            if (searchVo.getCreateDate() != null) {
                qw.ge("create_time", searchVo.getCreateDate());
            }
            if (searchVo.getCreateDateEnd() != null) {
                qw.le("create_time", DateUtil.parse(searchVo.getCreateDateEnd()).plusSeconds(24 * 60 * 60 - 1));
            }
            if (searchVo.getPublishDate() != null) {
                qw.ge("src_publish_date", searchVo.getPublishDate());
            }
            if (searchVo.getPublishDateEnd() != null) {
                qw.le("src_publish_date", DateUtil.parse(searchVo.getPublishDateEnd()).plusSeconds(24 * 60 * 60 - 1));
            }
            if (searchVo.getPublishStatus() != null) {
                qw.eq("publish_status", searchVo.getPublishStatus());
            }
            if (searchVo.getDsSourceType() != null) {
                qw.eq("ds_source_type", searchVo.getDsSourceType());
            }
            if (searchVo.getLevel() != null) {
                qw.eq("level", searchVo.getLevel());
            }
            if (StringUtils.isNotBlank(searchVo.getNewsUrl())) {
                qw.like("news_url", SqlUtil.escapeLike(searchVo.getNewsUrl().trim()));
            }
            if (StringUtils.isNotBlank(searchVo.getAuthorOrgCode())) {
                qw.eq("author_org_code", searchVo.getAuthorOrgCode());
            }
            if (searchVo.getRange() != null) {
                qw.eq("range_base", searchVo.getRange());
            }
            if (searchVo.getRangePlus() != null) {
                qw.eq("range_plus", searchVo.getRangePlus());
            }
            if (searchVo.getFinancial() != null) {
                qw.eq("financial", searchVo.getFinancial());
            }
            if (searchVo.getFinancialPlus() != null) {
                qw.eq("financial_plus", searchVo.getFinancialPlus());
            }
            if (StringUtils.isNotBlank(searchVo.getNewsType()) && searchVo.getNewsType().contains(",")) {
                List<String>  newsTypes=Arrays.asList(searchVo.getNewsType().split(","));
                qw.in("news_type", newsTypes);
            } else if (StringUtils.isNotBlank(searchVo.getNewsType()) && !searchVo.getNewsType().contains(",")) {
                qw.eq("news_type", searchVo.getNewsType());
            }
            if (StringUtils.isNotBlank(searchVo.getMediaSource())) {
                qw.eq("media_source_code", searchVo.getMediaSource());
            }
            if (StringUtils.isNotBlank(searchVo.getAuthor())) {
                qw.eq("author", searchVo.getAuthor());
            }
            if (CollectionUtils.isNotEmpty(searchVo.getTagIds()) && searchVo.getTagIds().size() == 2) {
                if (StringUtils.isNotBlank(searchVo.getTagIds().get(0)) || StringUtils.isNotBlank(searchVo.getTagIds().get(1))) {
                    String registerTagIds = "";
                    if (StringUtils.isNotBlank(searchVo.getTagIds().get(0))) {
                        registerTagIds = registerTagIds + "\"" + searchVo.getTagIds().get(0) + "\"";
                    }
                    if (StringUtils.isNotBlank(searchVo.getTagIds().get(0)) && StringUtils.isNotBlank(searchVo.getTagIds().get(1))) {
                        registerTagIds = registerTagIds + ",";
                    }
                    if (StringUtils.isNotBlank(searchVo.getTagIds().get(1))) {
                        registerTagIds = registerTagIds + "\"" + searchVo.getTagIds().get(1) + "\"";
                    }
                    qw.inSql("news_id", "SELECT DISTINCT(news_id) FROM tbm_news_tags WHERE register_tag_id in (" + registerTagIds + ")");
                }
            }
            if (searchVo.getAreaCode() != null) {
                qw.inSql("news_id", "SELECT DISTINCT(news_id) FROM tbm_news_area_tags WHERE business_id = " + searchVo.getAreaCode());
            }
    
    
            if (searchVo.getAppIds() != null) {
                if (searchVo.getAppIds().equals("2")) {
                    qw.orderByDesc("priority");
                } else if (searchVo.getAppIds().equals("3")) {
                    qw.orderByDesc("xf_priority");
                } else if (searchVo.getAppIds().equals("5")) {
                    qw.orderByDesc("yq_priority");
                } else if (searchVo.getAppIds().equals("6")) {
                    qw.orderByDesc("jyp_priority");
                }
            }
            qw.orderByDesc("create_time")
                    .eq("handle_status", 0)
                    .notInSql("news_id", "select news_id from tbm_discard_task where discard_user_id=" + searchVo.getSessionUserId())
                    .last("limit  100");
            qw.select("    news_id    ");
            List<TbmNewsProcessing> newsIdObjectList = processingMapper.selectList(qw);
    
            List<String> newsIdList = new ArrayList<String>();
            if (newsIdObjectList != null && !newsIdObjectList.isEmpty()) {
                newsIdObjectList.forEach(item -> {
                    newsIdList.add(String.valueOf(item.getNewsId()));
                });
            }
            return newsIdList;
        }