CheerwayRen opened a new issue #16326:
URL: https://github.com/apache/shardingsphere/issues/16326
At the beginning, I add day table sharding rule through SPI. When I added
another rule, the table was not divided successfully. Is there a problem with
my addition method?
using
shardingsphere-jdbc : 5.0.0-beta
dynamic-datasource-spring-boot-starter:3.3.1
```
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
SpringBootConfiguration.class})
public class DataSourceConfig {
/**
* 分表数据源名称
*/
private static final String SHARDING_DATA_SOURCE_NAME = "sharding";
/**
* 动态数据源配置项
*/
@Autowired
private DynamicDataSourceProperties properties;
/**
* 代理DynamicDataSource创建过程，对sharding进行包装，包装成shardingDataSource再放回去
*/
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
Map<String, DataSourceProperty> datasourceMap =
properties.getDatasource();
return new AbstractDataSourceProvider() {
@SneakyThrows
@Override
public Map<String, DataSource> loadDataSources() {
Map<String, DataSource> dataSourceMap =
createDataSourceMap(datasourceMap);
DataSource sharding =
dataSourceMap.get(SHARDING_DATA_SOURCE_NAME);
Map<String, DataSource> targetDataSources = new HashMap<>(1);
targetDataSources.put(SHARDING_DATA_SOURCE_NAME, sharding);
Date now = new Date();
String shardingStart =
DateUtils.format(DateUtils.beginOfYear(DateUtils.offsetYear(now, 0 -
SalesPropertiesConstant.SALE_MODE_DEFALUT_TABLE_BACKUP_YEAR)),
SalesPropertiesConstant.SALE_YM_FORMAT);
String shardingEnd =
DateUtils.format(DateUtils.endOfYear(DateUtils.offsetYear(now, 1)),
SalesPropertiesConstant.SALE_YM_FORMAT);
System.setProperty("backupEnd", shardingStart);
ShardingRuleConfiguration shardingRuleConfiguration = new
ShardingRuleConfiguration();
//day table sharding rule
String dayReportActualDataNodes =
"sharding.mng_sale_amount_day_$->{" +
shardingStart.substring(SpecialCharConstants.FINAL_INT_ZERO,
SpecialCharConstants.FINAL_INT_FOUR) + ".." +
shardingEnd.substring(SpecialCharConstants.FINAL_INT_ZERO,
SpecialCharConstants.FINAL_INT_FOUR) +
"}0$->{1..9},sharding.mng_sale_amount_day_$->{" +
shardingStart.substring(SpecialCharConstants.FINAL_INT_ZERO,
SpecialCharConstants.FINAL_INT_FOUR) + ".." +
shardingEnd.substring(SpecialCharConstants.FINAL_INT_ZERO,
SpecialCharConstants.FINAL_INT_FOUR) +
"}1$->{0..2},sharding.mng_sale_amount_day_backup";
ShardingTableRuleConfiguration dayReportRuleConfiguration =
new
ShardingTableRuleConfiguration(SalesPropertiesConstant.SALE_DAY_REPORT_TABLE_NAME,
dayReportActualDataNodes);
StandardShardingStrategyConfiguration
standardShardingStrategyConfiguration= new
StandardShardingStrategyConfiguration("SALE_YMD",
"dayReportTableShardingAlgorithm");
dayReportRuleConfiguration.setTableShardingStrategy(standardShardingStrategyConfiguration);
shardingRuleConfiguration.getTables().add(dayReportRuleConfiguration);
shardingRuleConfiguration.getShardingAlgorithms().put("dayReportTableShardingAlgorithm",
new ShardingSphereAlgorithmConfiguration("dayReportRule", new Properties()));
//brand table sharding rule
String brandDailyActualDataNodes =
"sharding.mng_sale_brand_daily_data_$->{" +
shardingStart.substring(SpecialCharConstants.FINAL_INT_ZERO,
SpecialCharConstants.FINAL_INT_FOUR) + ".." +
shardingEnd.substring(SpecialCharConstants.FINAL_INT_ZERO,
SpecialCharConstants.FINAL_INT_FOUR) +
"},sharding.mng_sale_brand_daily_data_backup";
ShardingTableRuleConfiguration brandDailyRuleConfiguration =
new
ShardingTableRuleConfiguration(SalesPropertiesConstant.SALE_BRAND_DAILY_TABLE_NAME,
brandDailyActualDataNodes);
brandDailyRuleConfiguration.setTableShardingStrategy(new
StandardShardingStrategyConfiguration("SALE_YMD",
"brandDailyPreciseShardingAlgorithm"));
shardingRuleConfiguration.getTables().add(brandDailyRuleConfiguration);
shardingRuleConfiguration.getShardingAlgorithms().put("brandDailyPreciseShardingAlgorithm",
new ShardingSphereAlgorithmConfiguration("brandDailyRule", new Properties()));
shardingRuleConfiguration.setDefaultTableShardingStrategy(standardShardingStrategyConfiguration);
Properties prop= new Properties();
prop.setProperty("sql-show","true");
DataSource dataSource =
ShardingSphereDataSourceFactory.createDataSource(targetDataSources,
Collections.singleton(shardingRuleConfiguration), prop);
dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, dataSource);
return dataSourceMap;
}
};
}
/**
* 将动态数据源设置为首选的
* 当spring存在多个数据源时, 自动注入的是首选的对象
* 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
*
* @return
*/
@Primary
@Bean
public DataSource dataSource(DynamicDataSourceProvider
dynamicDataSourceProvider) {
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
dataSource.setPrimary(properties.getPrimary());
dataSource.setStrict(properties.getStrict());
dataSource.setStrategy(properties.getStrategy());
dataSource.setProvider(dynamicDataSourceProvider);
dataSource.setP6spy(properties.getP6spy());
dataSource.setSeata(properties.getSeata());
return dataSource;
}
}
@Slf4j
public class BrandDailyPreciseShardingAlgorithm implements
StandardShardingAlgorithm<String> {
private Properties props = new Properties();
/**
* 精准匹配 in 和 =
*
* @param tableNames 表名
* @param shardingValue 分片值
* @return {@link String}
*/
@Override
public String doSharding(Collection<String> tableNames,
PreciseShardingValue<String> shardingValue) {
return
TableSuffixUtil.getBrandDailyTableNameOfDay(shardingValue.getValue());
}
/**
* 范围匹配
*
* @param collection 集合
* @param rangeShardingValue 分片值范围
* @return {@link Collection}<{@link String}>
*/
@Override
public Collection<String> doSharding(Collection<String> collection,
RangeShardingValue<String> rangeShardingValue) {
Range<String> range = rangeShardingValue.getValueRange();
return
TableSuffixUtil.getBrandDailyTableNameBetween(range.hasLowerBound() ?
range.lowerEndpoint() : SpecialCharConstants.EMPTY_STRING,
range.hasUpperBound() ? range.upperEndpoint() :
SpecialCharConstants.EMPTY_STRING);
}
@Override
public void init() {
log.info("<<< <<< init brand daily rule >>> >>>");
}
@Override
public String getType() {
return "brandDailyRule";
}
@Override
public Properties getProps() {
return props;
}
@Override
public void setProps(Properties props) {
this.props = props;
}
}
@Slf4j
public class DayReportPreciseShardingAlgorithm implements
StandardShardingAlgorithm<String> {
private Properties props = new Properties();
/**
* 精准匹配 in 和 =
*
* @param tableNames 表名
* @param shardingValue 分片值
* @return {@link String}
*/
@Override
public String doSharding(Collection<String> tableNames,
PreciseShardingValue<String> shardingValue) {
return
TableSuffixUtil.getDayReportTableNameOfDay(shardingValue.getValue());
}
/**
* 范围匹配 between
*
* @param collection 集合
* @param rangeShardingValue 分片值范围
* @return {@link Collection}<{@link String}>
*/
@Override
public Collection<String> doSharding(Collection<String> collection,
RangeShardingValue<String> rangeShardingValue) {
Range<String> range = rangeShardingValue.getValueRange();
return
TableSuffixUtil.getDayReportTableNameBetween(range.hasLowerBound() ?
range.lowerEndpoint() : SpecialCharConstants.EMPTY_STRING,
range.hasUpperBound() ? range.upperEndpoint() :
SpecialCharConstants.EMPTY_STRING);
}
@Override
public void init() {
log.info("<<< <<< init day report rule >>> >>>");
}
@Override
public String getType() {
return "dayReportRule";
}
@Override
public Properties getProps() {
return props;
}
@Override
public void setProps(Properties props) {
this.props = props;
}
}
#数据源
spring:
servlet:
multipart:
max-file-size: 50MB
max-request-size: 100MB
datasource:
druid:
stat-view-servlet: #monintor,druid login user config
enabled: true
loginUsername: admin
loginPassword: 123456
dynamic:
p6spy: true # 默认false,建议线上关闭
primary: sharding #设置默认的数据源或者数据源组,默认值为master
strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候回抛出异常,不启动会使用默认数据源
druid: #以下是支持的全局默认值
minIdle: 5
maxActive: 20
filters: stat # 注意这个值和druid原生不一致，默认启动了stat,wall
stat:
merge-sql: true
log-slow-sql: true
datasource:
base:
url:
jdbc:mysql://127.0.0.1:3306/amp_sale_db?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&preferredTestQuery=select
now()
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
sharding:
url:
jdbc:mysql://127.0.0.1:3306/amp_sale_db?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&autoReconnect=true&preferredTestQuery=select
now()
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
