SpringBoot单种类多筛选实现

2022-07-05

说明

示例：现有多个系列，每个系列下有多个商品，现要筛选多个系列下的多个商品。

关键问题：把JAVA程序的系列List映射为Mybatis下SQL语句的IN元组。

下文仅包含关键代码，参考XML的foreach用法为主要。

请求体

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import javax.validation.constraints.Max;
import javax.validation.constraints.Min;
import java.util.List;

/**
 * @author 曳戈泰尔
 * @description 市场NFT请求实体
 * @since 2022-05-06
 */
@Data
public class GetNewMarketNftReq {

    @Min(value = 1, message = "页数从1开始")
    Integer pageNum;

    @Min(value = 1, message = "每页最少包含1项")
    @Max(value = 100, message = "超出每页最大限制")
    Integer pageSize;

    @Schema(description = "系列编号；系列为空查全部，否则查特定系列")
    List<Integer> seriesIdList;
}

控制层

@ApiOperation(
        value = "查询市场NFT——通过系列列表",
        notes = "默认1页，20页大小，查全部系列，全部NFT。")
@PostMapping(value = "/nft/bySeriesIdList")
public Result selectNewMarketNft(@Valid @RequestBody GetNewMarketNftReq getNewMarketNftReq) {
    return marketNftService.selectNewMarketNft(getNewMarketNftReq);
}

实现层

@Override
public Result selectNewMarketNft(GetNewMarketNftReq getNewMarketNftReq) {

    // 页码，默认为1
    Integer pageNum;
    if (null == getNewMarketNftReq.getPageNum()) {
        pageNum = PageConfig.pageNum;
    } else {
        pageNum = getNewMarketNftReq.getPageNum();
    }

    // 页大小，默认为20
    Integer pageSize;
    if (null == getNewMarketNftReq.getPageSize()) {
        pageSize = PageConfig.pageSize;
    } else {
        pageSize = getNewMarketNftReq.getPageSize();
    }

    // 系列ID
    List<Integer> seriesIdList = new ArrayList<>();
    if (0 == getNewMarketNftReq.getSeriesIdList().size()) {
        seriesIdList.add(-1);
    } else {
        seriesIdList = getNewMarketNftReq.getSeriesIdList();
    }

    // 查缓存
    if (StringUtils.isNotBlank(
            menuSeriesNftGoodsCache.getNewMarketNftCache(seriesIdList, pageNum, pageSize))) {
        return Result.success(
                JSON.parse(
                        menuSeriesNftGoodsCache.getNewMarketNftCache(
                                seriesIdList, pageNum, pageSize)));
    }

    // return 数据
    List<GetMarketNftResp> getMarketNftRespList = new ArrayList<>();

    // SQL查询返回数据
    List<String> stringList = null;

    // 数据总数
    Integer total;

    // 系列为空查全部
    if (0 == getNewMarketNftReq.getSeriesIdList().size()) {

        // 查询总数
        total = marketNftBizMapper.selectMarketNftAllSeriesTotal();

        // 分页
        PageHelper.startPage(pageNum, pageSize);
        stringList = marketNftBizMapper.selectMarketNftAllSeriesOrderByBuyNumDesc();

    } else {
        // 系列不为空查特定系列

        // 查询总数
        total =
                marketNftBizMapper.selectNewMarketNftTotalBySeriesId(
                        getNewMarketNftReq.getSeriesIdList());

        // 有可购买Goods在前
        // 分页
        // 多系列多商品
        PageHelper.startPage(pageNum, pageSize);
        stringList =
                marketNftBizMapper.selectNewMarketNftBySeriesIdOrderByBuyNumDesc(
                        getNewMarketNftReq.getSeriesIdList());
    }

    // 查询参数非法
    if (stringList == null) {
        return Result.fail("查询参数非法");
    }

    for (String nftTemplateId : stringList) {

        // 初始化返回实体
        GetMarketNftResp getMarketNftResp = GetMarketNftResp.builder().build();

        // 返回体增加NFT编号信息
        getMarketNftResp.setNftTemplateId(nftTemplateId);

        // 查询NFT基本信息
        QueryWrapper<MarketNftBaseEntity> nftBaseEntityQueryWrapper = new QueryWrapper<>();
        nftBaseEntityQueryWrapper.eq("nft_template_id", nftTemplateId);
        MarketNftBaseEntity nftBaseEntity =
                marketNftBaseMapper.selectOne(nftBaseEntityQueryWrapper);
        // 返回体增加NFT信息
        if (nftBaseEntity != null) {
            getMarketNftResp.setNftName(nftBaseEntity.getNftName());
            getMarketNftResp.setNftCover(nftBaseEntity.getNftCover());
        }

        // 查询NFT业务信息
        QueryWrapper<MarketNftBizEntity> nftBizEntityQueryWrapper = new QueryWrapper<>();
        nftBizEntityQueryWrapper.eq("nft_template_id", nftTemplateId);
        MarketNftBizEntity nftBizEntity =
                marketNftBizMapper.selectOne(nftBizEntityQueryWrapper);
        // 返回体增加NFT业务信息
        if (nftBizEntity != null) {
            getMarketNftResp.setCircuitBreakerTime(
                    nftBizEntity
                            .getCircuitBreakerTime()
                            .format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
            getMarketNftResp.setNftStatus(nftBizEntity.getNftStatus());
        }

        // 查询NFT对应的系列信息
        QueryWrapper<MarketSeriesEntity> seriesEntityQueryWrapper = new QueryWrapper<>();
        seriesEntityQueryWrapper.eq("id", nftBaseEntity.getSeriesId());
        MarketSeriesEntity marketSeriesEntity =
                marketSeriesMapper.selectOne(seriesEntityQueryWrapper);
        // 返回体增加系列信息
        if (marketSeriesEntity != null) {
            getMarketNftResp.setSeriesName(marketSeriesEntity.getSeriesName());
            getMarketNftResp.setSeriesAvatar(marketSeriesEntity.getCoverImg());
        }

        // 查询NFT指导价格信息——前一天价格price
        QueryWrapper<MarketNftPriceHistoryEntity> priceHistoryEntityQueryWrapper =
                new QueryWrapper<>();
        priceHistoryEntityQueryWrapper.eq(
                "nft_template_id", getMarketNftResp.getNftTemplateId());
        priceHistoryEntityQueryWrapper.orderByDesc("id");
        priceHistoryEntityQueryWrapper.last("LIMIT 1");
        MarketNftPriceHistoryEntity priceHistoryEntity =
                marketNftPriceHistoryMapper.selectOne(priceHistoryEntityQueryWrapper);
        // 返回体增加指导价格信息
        if (priceHistoryEntity != null) {
            getMarketNftResp.setGuidingPrice(priceHistoryEntity.getPrice());
        }

        // 查询NFT熔断级别
        QueryWrapper<NftCircuitBreakerEntity> nftCircuitBreakerEntityQueryWrapper =
                new QueryWrapper<>();
        nftCircuitBreakerEntityQueryWrapper.eq("data_id", getMarketNftResp.getNftTemplateId());
        nftCircuitBreakerEntityQueryWrapper.orderByDesc("id");
        nftCircuitBreakerEntityQueryWrapper.last("LIMIT 1");
        NftCircuitBreakerEntity nftCircuitBreakerEntity =
                nftCircuitBreakerMapper.selectOne(nftCircuitBreakerEntityQueryWrapper);
        // 返回体增加熔断级别信息
        if (nftCircuitBreakerEntity != null) {
            getMarketNftResp.setCircuitLevel(nftCircuitBreakerEntity.getLevel());
        } else {
            getMarketNftResp.setCircuitLevel(0);
        }

        getMarketNftRespList.add(getMarketNftResp);
    }

    // 设置缓存
    if (getMarketNftRespList.size() != 0) {
        menuSeriesNftGoodsCache.cacheNewMarketNft(
                seriesIdList,
                pageNum,
                pageSize,
                JSON.toJSONString(
                        GetPageResp.getInstance()
                                .pageNum(pageNum)
                                .pageSize(pageSize)
                                .total(total)
                                .data(getMarketNftRespList)));
    }

    // return Result.success(getMarketNftRespList);
    return Result.success(
            GetPageResp.getInstance()
                    .pageNum(pageNum)
                    .pageSize(pageSize)
                    .total(total)
                    .data(getMarketNftRespList));
}

Mapper层

@Mapper
public interface MarketNftBizMapper extends BaseMapper<MarketNftBizEntity> {

		Integer selectNewMarketNftTotalBySeriesId(@Param("seriesIdList") List<Integer> seriesIdList);

    List<String> selectNewMarketNftBySeriesIdOrderByBuyNumDesc(
            @Param("seriesIdList") List<Integer> seriesIdList);
}

XML层

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.folksapce.demo.api.mapper.MarketNftBizMapper">

    <select id="selectNewMarketNftTotalBySeriesId" resultType="java.lang.Integer">
        SELECT COUNT(DISTINCT (nb.nft_template_id))
        FROM s_nft_base AS nb
        INNER JOIN s_nft_biz AS nz
        ON nb.nft_template_id = nz.nft_template_id
        WHERE nb.series_id IN
        <foreach collection="seriesIdList" item="seriesId" open="(" close=")" separator=",">
            #{seriesId}
        </foreach>
        AND nz.sec_market_sale = 1
    </select>

    <select id="selectNewMarketNftBySeriesIdOrderByBuyNumDesc" resultType="java.lang.String">
        SELECT t.`nft_template_id`,
        (SELECT COUNT(*)
        FROM `s_nft_goods` b
        WHERE t.`nft_template_id` = b.`nft_template_id`
        AND `goods_status` IN (1, 2)
        GROUP BY b.`nft_template_id`) good_count
        FROM `s_nft_base` t,
        `s_nft_biz` a
        WHERE t.`nft_template_id` = a.`nft_template_id`
        AND t.series_id IN
        <foreach collection="seriesIdList" item="seriesId" open="(" close=")" separator=",">
            #{seriesId}
        </foreach>
        AND a.`sec_market_sale` = 1
        ORDER BY good_count DESC, t.id ASC
    </select>

</mapper>