Mybatisplus 分表查询记录

 

private List<String> getTableName(String tradeTimeBegin,String tradeTimeEnd){
        List<String> tableName = new ArrayList<>();
        if(StringUtils.isBlank(tradeTimeBegin)){
            tradeTimeBegin="2020-01-01";
        }
        if(StringUtils.isBlank(tradeTimeEnd)){
            tradeTimeEnd=TimeUtils.getDateStr(new Date());
        }
        List<String> postfixes= TimeUtils.getMonthBetween(tradeTimeBegin,tradeTimeEnd);
        postfixes.forEach(e->{
            StringBuilder str=new StringBuilder("merchant_transaction_").append(e);
            tableName.add(str.toString());
        });
        return this.baseMapper.getTableNameList(tableName);
    }

 

/**
     * 获取当前数据库表名
     *
     * @param tableNameList 表名列表
     * @return
     */
    List<String> getTableNameList(@Param("tableNameList") List<String> tableNameList);

<select id="getTableNameList" resultType="java.lang.String">
        select table_name from information_schema.tables
        <where>
            <if test="tableNameList !=null and tableNameList.size() > 0">
                and table_name in
                <foreach collection="tableNameList" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    IPage<MerchantTransaction> queryWebPage(Page<MerchantTransWebPageRes> page, @Param("req") MerchantTransWebPageReq req, @Param(Constants.WRAPPER) LambdaQueryWrapper<MerchantTransWebPageReq> wrapper, @Param("tableName")List<String> tableName);

<select id="queryWebPage" resultType="com.eg.cdt.saas.pay.dao.entity.MerchantTransaction">
        select * from (
        <foreach collection="tableName" item="item" separator="union all">
            SELECT uuid,trade_amount,order_id,trade_time,trade_type,trade_status
            FROM ${item}
            where delete_flag=1
            <if test="req.tradeTimeBegin !=null">
                <![CDATA[and to_char(trade_time, 'yyyy-MM-dd') >=  #{req.tradeTimeBegin}]]>
            </if>
            <if test="req.tradeTimeEnd !=null">
                <![CDATA[and to_char(trade_time, 'yyyy-MM-dd') <=  #{req.tradeTimeEnd}]]>
            </if>
        </foreach>
        ) alt ${ew.customSqlSegment}
        ORDER BY trade_time desc
</select>

@Override
    public IPage<MerchantTransaction> webPage(Integer pageNo, Integer pageSize, MerchantTransWebPageReq req) {
        List<String> lists=getTableName(req.getTradeTimeBegin(),req.getTradeTimeEnd());
        if(lists.size()>0){
            Page<MerchantTransWebPageRes> page = new Page<>(pageNo, pageSize);
            return this.baseMapper.queryWebPage(page, req,new LambdaQueryWrapper<MerchantTransWebPageReq>(), lists);
        }
        return new Page<>();
    }