sharding分库分表，使用插件进行sql查询

ゝ一纸荒年。 2023-07-18 05:49 6阅读 0赞

<sharding:data-source id="shardingDataSource">
            <sharding:sharding-rule data-source-names="writeDataSource">
             <sharding:table-rules>
             	 <sharding:table-rule logic-table="t_debitcore_repay_order_flow_his" table-strategy-ref="repayHistroyStrategy" actual-data-nodes="writeDataSource.t_debitcore_repay_order_flow_his_$->{0..1}$->{0..9}" />
              </sharding:table-rules>
      <sharding:standard-strategy id="repayHistroyStrategy" sharding-column="created_at" precise-algorithm-ref="repayHistroyAlgorithm" range-algorithm-ref="repayHistroyAlgorithmRange"/>
    
    <bean id="repayHistroyAlgorithmRange" class=“RepayHistroyTableStrategyRange”/>
    <bean id="repayHistroyAlgorithm" class="RepayHistroyTableStrategy"/>

java bean 查询转换类

@Slf4j
    public class RepayHistroyTableStrategyRange implements RangeShardingAlgorithm<Date> { 
    
        @Override
        public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> shardingValue) { 
            Range<Date> created_at = shardingValue.getValueRange();
    
            // 表名后缀
            log.info("created_at {} " ,created_at);
            Integer startMonth= null;
            Integer endMonth= null;
            List<String> tableNames = Lists.newArrayList();
            try { 
                Date startDate = created_at.lowerEndpoint();
                Date endDate = created_at.upperEndpoint();
                startMonth = startDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDate().getMonth().getValue();
                endMonth = endDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDate().getMonth().getValue();
            }catch (Exception e){ 
                log.error("获得分表月出错 {}",e);
                startMonth = new Date().toInstant().atZone(ZoneId.systemDefault()).toLocalDate().getMonth().getValue();
                endMonth = new Date().toInstant().atZone(ZoneId.systemDefault()).toLocalDate().getMonth().getValue();
            }
    
            while(startMonth<=endMonth){ 
                for(String tableName : availableTargetNames){ 
                    if(tableName.endsWith(startMonth.toString())){ 
                        tableNames.add(tableName);
                    }
                }
                startMonth++;
            }
            return tableNames;
        }
    }

@Slf4j
    public class RepayHistroyTableStrategy implements PreciseShardingAlgorithm<Date> { 
        @Override
        public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) { 
            Date created_at = shardingValue.getValue();
            // 表名后缀
            log.debug("created_at {} " ,created_at);
            Month month= null;
            try { 
    
                  month = created_at.toInstant().atZone(ZoneId.systemDefault()).toLocalDate().getMonth();
            }catch (Exception e){ 
                log.error("获得分表月出错 {}",e);
                 month = new Date().toInstant().atZone(ZoneId.systemDefault()).toLocalDate().getMonth();
            }
            String suffix =   Integer.toString(month.getValue());
            if (StringUtils.isNotEmpty(suffix)) { 
                for (String tableName : availableTargetNames ) { 
                    if(tableName.endsWith(suffix)) { 
                        return tableName;
                    }
                }
            }
            log.error("t_debitcore_repay_detail分表参数无效：suffix={}", suffix);
            throw  new IllegalArgumentException("t_debitcore_repay_detail无分表参数,无法定位具体数据表");
        }