wangwangwangBoy opened a new issue #12703:
URL: https://github.com/apache/shardingsphere/issues/12703
##### As the title, the Spring boot project uses Java for sharding-jdbc
configuration, and encountered this error when starting.
##### Springboot main class:
```java
import com.netflix.zuul.ZuulFilter;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import
org.springframework.boot.autoconfigure.transaction.jta.JtaAutoConfiguration;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.cloud.netflix.eureka.EnableEurekaClient;
import org.springframework.cloud.netflix.zuul.EnableZuulProxy;
import
org.springframework.cloud.netflix.zuul.filters.discovery.PatternServiceRouteMapper;
import org.springframework.cloud.openfeign.EnableFeignClients;
import org.springframework.context.annotation.Bean;
import
org.springframework.session.data.redis.config.annotation.web.http.EnableRedisHttpSession;
@EnableZuulProxy
@SpringBootApplication( scanBasePackageClasses = {GateWayApplication.class,
ApplicationContextProvider.class})
@EnableEurekaClient
@ServletComponentScan
@EnableRedisHttpSession
public class GateWayApplication {
public static void main(String[] args) {
SpringApplication.run(GateWayApplication.class, args);
}
@Bean
public PatternServiceRouteMapper serviceRouteMapper() {
return new
PatternServiceRouteMapper("(?<name>^.+)-(?<version>.+$)", "${name}/${version}");
}
@Bean
public ZuulFilter ProvideFilter(){
return new ProvideFilter();
}
@Bean
public RedisService redisService(){
return new RedisService();
}
@Bean
public FeignInterceptor feignInterceptor(){
return new FeignInterceptor();
}
}
```
##### My main configuration is as follows:
```java
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import
org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
import
org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import
org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import
org.apache.shardingsphere.sharding.api.config.strategy.keygen.KeyGenerateStrategyConfiguration;
import
org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
@Configuration
public class ShardingConfig {
private final String DEVICE_LOGICAL_TABLE =
"environment_supervise_device_data";
private final String AI_LOGICAL_TABLE = "ai_supervise_device_data";
private static final String LARGE_PROJECT_DEPARTMENT =
"large_project_department";
private static final String TABLE_RULE = "table_rule";
private static final String DATABASE_RULE = "database_rule";
public static final String DATABASE_SHARDING_ALGORITHM_NAME =
"dataBaseShardingAlgorithmNameCreateDate";
public static final String TABLE_SHARDING_ALGORITHM_NAME =
"tableShardingAlgorithmNameCreateDate";
private final String TABLE_SHARDING_COLUMN = "create_date";
@Bean
@Primary
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource getDruidDataSource() {
DruidDataSource druidDataSource = new DruidDataSource();
List<Filter> filterList = new ArrayList<>();
filterList.add(wallFilter());
druidDataSource.setProxyFilters(filterList);
return druidDataSource;
}
@Bean()
public DataSource dataSource() throws SQLException {
Map<String, DataSource> dataSourceMap = new HashMap<>(0);
dataSourceMap.put(LARGE_PROJECT_DEPARTMENT, createDataSource());
ShardingTableRuleConfiguration deviceDataRuleConfiguration =
getTableRuleConfiguration(DEVICE_LOGICAL_TABLE);
ShardingTableRuleConfiguration aiDataRuleConfiguration =
getTableRuleConfiguration(AI_LOGICAL_TABLE);
Properties properties = new Properties();
properties.setProperty("sql.show", "true");
ShardingRuleConfiguration shardingRuleConfiguration = new
ShardingRuleConfiguration();
shardingRuleConfiguration.getTables().add(deviceDataRuleConfiguration);
shardingRuleConfiguration.getTables().add(aiDataRuleConfiguration);
shardingRuleConfiguration.setDefaultDatabaseShardingStrategy(
new
StandardShardingStrategyConfiguration(TABLE_SHARDING_COLUMN,
DATABASE_SHARDING_ALGORITHM_NAME));
shardingRuleConfiguration.setDefaultTableShardingStrategy(
new
StandardShardingStrategyConfiguration(TABLE_SHARDING_COLUMN,
TABLE_SHARDING_ALGORITHM_NAME));
shardingRuleConfiguration.getShardingAlgorithms()
.put(DATABASE_SHARDING_ALGORITHM_NAME,
new
ShardingSphereAlgorithmConfiguration(DatabaseShardingAlgorithm.DATABASE_SHARDING_ALGORITHM,
properties));
shardingRuleConfiguration.getShardingAlgorithms()
.put(TABLE_SHARDING_ALGORITHM_NAME,
new
ShardingSphereAlgorithmConfiguration(TableShardingAlgorithm.TABLE_SHARDING_ALGORITHM,
properties));
shardingRuleConfiguration.getKeyGenerators()
.put("snowflake", new
ShardingSphereAlgorithmConfiguration("SNOWFLAKE", properties));
return ShardingSphereDataSourceFactory
.createDataSource(dataSourceMap,
Collections.singleton(shardingRuleConfiguration), properties);
}
@Bean
public DataSourceTransactionManager dataSourceTransactionManager() {
return new DataSourceTransactionManager(getDruidDataSource());
}
@Bean
public JdbcTemplate jdbcTemplate() {
return new JdbcTemplate(getDruidDataSource());
}
private DataSource createDataSource() {
DruidDataSource dataSource = (DruidDataSource) getDruidDataSource();
return dataSource;
}
private ShardingTableRuleConfiguration getTableRuleConfiguration(String
tableName) {
KeyGenerateStrategyConfiguration keyGeneratorConfiguration = new
KeyGenerateStrategyConfiguration("id",
"SNOWFLAKE");
ShardingTableRuleConfiguration deviceDataRuleConfiguration = new
ShardingTableRuleConfiguration(tableName,
LARGE_PROJECT_DEPARTMENT + "." + tableName +
"_20$->{21..22}0$->{1..9}" + "," +
LARGE_PROJECT_DEPARTMENT + "." + tableName +
"_20$->{21..22}$->{11..12}");
deviceDataRuleConfiguration.setDatabaseShardingStrategy(
new
StandardShardingStrategyConfiguration(TABLE_SHARDING_COLUMN, DATABASE_RULE));
deviceDataRuleConfiguration.setTableShardingStrategy(
new
StandardShardingStrategyConfiguration(TABLE_SHARDING_COLUMN, TABLE_RULE));
deviceDataRuleConfiguration.setKeyGenerateStrategy(keyGeneratorConfiguration);
return deviceDataRuleConfiguration;
}
@Bean
public WallFilter wallFilter() {
WallFilter wallFilter = new WallFilter();
wallFilter.setConfig(wallConfig());
return wallFilter;
}
@Bean
public WallConfig wallConfig() {
WallConfig config = new WallConfig();
config.setMultiStatementAllow(true);
config.setNoneBaseStatementAllow(true);
return config;
}
}
```
##### The table fragmentation algorithm is configured as follows:
```java
import com.google.common.collect.Range;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Optional;
import javax.sql.DataSource;
import org.apache.commons.collections4.CollectionUtils;
import
org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import
org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import
org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
public class TableShardingAlgorithm implements
StandardShardingAlgorithm<Date> {
public static final String TABLE_SHARDING_ALGORITHM =
"tableShardingAlgorithm";
private AutoCreateTableConfig autoCreateTableConfig = new
AutoCreateTableConfig();
@Override
public void init() {
}
@Override
public String getType() {
return TABLE_SHARDING_ALGORITHM;
}
@Override
public String doSharding(Collection<String> availableTargetNames,
PreciseShardingValue<Date> shardingValue) {
long value = shardingValue.getValue().getTime();
if (value <= 0) {
throw new UnsupportedOperationException("preciseShardingValue is
null");
}
String yearJoinMonthStr =
DateUtil.getYearJoinMonthByMillisecond(value);
Optional<String> optional = availableTargetNames.parallelStream()
.filter(item -> item.endsWith(yearJoinMonthStr))
.findAny();
if (optional.isPresent()) {
judgeTable(optional.get());
return optional.get();
}
throw new UnsupportedOperationException("No available tables
according input time:" + yearJoinMonthStr);
}
@Override
public Collection<String> doSharding(Collection<String>
availableTargetNames,
RangeShardingValue<Date> rangeShardingValue) {
Range<Date> range = rangeShardingValue.getValueRange();
long startMillisecond = range.lowerEndpoint().getTime();
long endMillisecond = range.upperEndpoint().getTime();
// 起始年和结束年
int startYear =
Integer.parseInt(DateUtil.getYearByMillisecond(startMillisecond));
int endYear =
Integer.parseInt(DateUtil.getYearByMillisecond(endMillisecond));
// 起始月和结束月
int startMonth =
Integer.parseInt(DateUtil.getMonthByMillisecond(startMillisecond));
int endMonth =
Integer.parseInt(DateUtil.getMonthByMillisecond(endMillisecond));
int startYearJoinMonth =
Integer.parseInt(DateUtil.getYearJoinMonthByMillisecond(startMillisecond));
int endYearJoinMonth =
Integer.parseInt(DateUtil.getYearJoinMonthByMillisecond(endMillisecond));
List<String> result = startYear == endYear ? theSameYear(startYear,
startMonth, endMonth, availableTargetNames)
: differentYear(startYear, endYear, startMonth, endMonth,
startYearJoinMonth, endYearJoinMonth,
availableTargetNames);
return result;
}
private List<String> theSameYear(int startYear, int startMonth, int
endMonth, Collection<String> availableTargetNames) {
return startMonth == endMonth ? theSameMonth(startYear, startMonth,
availableTargetNames)
: differentMonth(startYear, startMonth, endMonth,
availableTargetNames);
}
private List<String> theSameMonth(int startYear,int startMonth,
Collection<String> availableTargetNames) {
List<String> result = new ArrayList<>();
String startMonthStr = String.valueOf(startMonth);
if (startMonthStr.length() == 1) {
startMonthStr = "0" + startMonthStr;
}
String condition = startYear + startMonthStr;
for (String availableTargetName : availableTargetNames) {
if (availableTargetName.endsWith(condition)) {
result.add(availableTargetName);
}
}
return result;
}
private List<String> differentMonth(int startYear,int startMonth, int
endMonth, Collection<String> availableTargetNames) {
List<String> result = new ArrayList<>();
for (String availableTargetName : availableTargetNames) {
for (int i = startMonth; i <= endMonth; i++) {
String monthStr = String.valueOf(i);
if (monthStr.length() == 1) {
monthStr = "0" + monthStr;
}
if (availableTargetName.endsWith(startYear + monthStr)) {
result.add(availableTargetName);
}
}
}
return result;
}
private List<String> differentYear(int startYear, int endYear, int
startMonth, int endMonth,
int startYearJoinMonth, int endYearJoinMonth, Collection<String>
availableTargetNames) {
return endYear - startYear == 1 ? twoYears(startYear, endYear,
startMonth, endMonth, startYearJoinMonth,
endYearJoinMonth, availableTargetNames)
: moreThanTwoYears(startYear, endYear, startMonth, endMonth,
availableTargetNames);
}
private List<String> twoYears(int startYear, int endYear, int
startMonth, int endMonth,
int startYearJoinMonth, int endYearJoinMonth, Collection<String>
availableTargetNames
) {
List<String> result = new ArrayList<>();
int endCondition;
endCondition = Integer.parseInt(startYear + "12");
for (int i = startYearJoinMonth; i <= endCondition; i++) {
for (String availableTargetName : availableTargetNames) {
if (availableTargetName.endsWith(String.valueOf(i))) {
result.add(availableTargetName);
}
}
}
endCondition = Integer.parseInt(endYear + "01");
for (int i = endYearJoinMonth; i >= endCondition; i--) {
for (String availableTargetName : availableTargetNames) {
if (availableTargetName.endsWith(String.valueOf(i))) {
result.add(availableTargetName);
}
}
}
return result;
}
private List<String> moreThanTwoYears(int startYear, int endYear, int
startMonth, int endMonth,
Collection<String> availableTargetNames) {
throw new UnsupportedOperationException("Unsupported more than two
years.");
}
}
```
##### The database fragmentation algorithm is configured as follows:
```java
import com.google.common.collect.Range;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import
org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import
org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import
org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
public class DatabaseShardingAlgorithm implements
StandardShardingAlgorithm<Date> {
public static final String DATABASE_SHARDING_ALGORITHM =
"databaseShardingAlgorithm";
@Override
public String doSharding(Collection<String> availableTargetNames,
PreciseShardingValue<Date> shardingValue) {
Long value = shardingValue.getValue().getTime();
if (value <= 0) {
throw new UnsupportedOperationException("preciseShardingValue is
null");
}
for (String availableTargetName : availableTargetNames) {
return availableTargetName;
}
return null;
}
@Override
public Collection<String> doSharding(Collection<String>
availableTargetNames, RangeShardingValue<Date> rangeShardingValue) {
List<String> result = new ArrayList<>();
Range<Date> range = rangeShardingValue.getValueRange();
long startMillisecond = range.lowerEndpoint().getTime();
// 起始年和结束年
int startYear =
Integer.parseInt(DateUtil.getYearByMillisecond(startMillisecond));
return theSameYear(String.valueOf(startYear), availableTargetNames,
result);
}
// 同一年，说明只需要一个库
private Collection<String> theSameYear(String startTime,
Collection<String> availableTargetNames, List<String> result) {
for (String availableTargetName : availableTargetNames) {
result.add(availableTargetName);
break;
}
return result;
}
private Collection<String> differentYear(int startYear, int endYear,
Collection<String> availableTargetNames, List<String> result) {
for (String availableTargetName : availableTargetNames) {
for (int i = startYear; i <= endYear; i++) {
if (availableTargetName.endsWith(String.valueOf(i)))
result.add(availableTargetName);
}
}
return result;
}
@Override
public void init() {
}
@Override
public String getType() {
return DATABASE_SHARDING_ALGORITHM;
}
}
```
--
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]
