背景
- 按查询时间来走分表，分表根据年份
依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
application.yml
#######################################dynamic配置###########################################
spring:
datasource:
dynamic:
primary: db1 #设置默认的数据源,默认值为master
datasource:
db1: #数据源db1
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/dev?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=CTT
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
druid:
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
#######################################ShardingSphere配置###########################################
shardingsphere:
datasource:
names: campaign-db
campaign-db:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: ${spring.datasource.dynamic.datasource.db1.url}
username: ${spring.datasource.dynamic.datasource.db1.username}
password: ${spring.datasource.dynamic.datasource.db1.password}
props:
sql:
show: true
sharding:
props:
sql.show: true
tables:
tdata_business_monitor_indicator_summary: #必须要写表名
actual-data-nodes: campaign-db.tdata_business_monitor_indicator_summary_$->{2022..2023} #分表后缀
table-strategy:
standard:
shardingColumn: date_time #分表字段
preciseAlgorithmClassName: com.wf.dev.config.DatePreciseShardingAlgorithm # 自定义in和=的精准匹配算法
rangeAlgorithmClassName: com.wf.dev.config.DatePreciseShardingAlgorithm # 自定义between and、>=、<=、>、<等范围匹配算法
自定义算法
- 自定义in和=的精准匹配算法
- 自定义between and、>=、<=、>、<等范围匹配算法
package com.wf.dev.config;
import com.google.common.collect.Sets;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.springframework.util.CollectionUtils;
import java.util.Collection;
/**
* @author wf
* @date 2022年12月01日 15:07
* @description
*/
@Slf4j
public class DatePreciseShardingAlgorithm implements PreciseShardingAlgorithm<String>, RangeShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> preciseShardingValue) {
String dateTime = preciseShardingValue.getValue();
log.info("Sharding input:" + preciseShardingValue.getValue());
String suffix = getSuffixByYearMonthDay(dateTime);
for (String tableName : availableTargetNames) {
log.info("suffix:" + suffix + ", 表明:{}" + tableName);
if (tableName.endsWith(suffix)) {
return tableName;
}
}
throw new IllegalArgumentException("未找到匹配的数据表");
}
private static String getSuffixByYearMonthDay(String dateTime) {
return StringUtils.substring(dateTime, 0, 4);
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
String startTime = rangeShardingValue.getValueRange().lowerEndpoint();
String endTime = rangeShardingValue.getValueRange().upperEndpoint();
String startTimeSuffix = getSuffixByYearMonthDay(startTime);
String endTimeSuffix = getSuffixByYearMonthDay(endTime);
Collection<String> collect = Sets.newHashSet();
for (String tableName : collection) {
if (tableName.endsWith(startTimeSuffix)) {
collect.add(tableName);
continue;
}
if (tableName.endsWith(endTimeSuffix)) {
collect.add(tableName);
continue;
}
}
if (CollectionUtils.isEmpty(collect)) {
throw new IllegalArgumentException("未找到匹配的数据表");
}
return collect;
}
}
数据源合并
- 将ShardingSphere数据源加入到dynamic数据源中
package com.wf.dev.config;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;
/**
* @author wf
* @date 2022年12月01日 13:54
* @description
*/
@Configuration
@AutoConfigureAfter({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class DataSourceConfiguration {
/**
* 分表数据源名称
*/
public static final String SHARDING_DATA_SOURCE_NAME = "sharding";
/**
* 动态数据源配置项
*/
@Autowired
private DynamicDataSourceProperties dynamicDataSourceProperties;
/**
* shardingjdbc有四种数据源，需要根据业务注入不同的数据源
*
* <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
* <p>2. 主从数据源: masterSlaveDataSource;
* <p>3. 脱敏数据源：encryptDataSource;
* <p>4. 影子数据源：shadowDataSource
* <p>
* shardingjdbc默认就是shardingDataSource
* 如果需要设置其他的可以使用
*
* @Resource(value="") 设置
*/
@Lazy
@Resource
DataSource shardingDataSource;
/**
* 将shardingDataSource放到了多数据源（dataSourceMap）中
* 注意有个版本的bug，3.1.1版本 不会进入loadDataSources 方法，这样就一直造成数据源注册失败
*/
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
Map<String, DataSourceProperty> datasourceMap = dynamicDataSourceProperties.getDatasource();
return new AbstractDataSourceProvider() {
@Override
public Map<String, DataSource> loadDataSources() {
Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
// 将 shardingjdbc 管理的数据源也交给动态数据源管理
dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
return dataSourceMap;
}
};
}
/**
* 将动态数据源设置为首选的
* 当spring存在多个数据源时, 自动注入的是首选的对象
* 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
*
* @return
*/
@Primary
@Bean
public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
dataSource.setPrimary(dynamicDataSourceProperties.getPrimary());
dataSource.setStrict(dynamicDataSourceProperties.getStrict());
dataSource.setStrategy(dynamicDataSourceProperties.getStrategy());
dataSource.setProvider(dynamicDataSourceProvider);
dataSource.setP6spy(dynamicDataSourceProperties.getP6spy());
dataSource.setSeata(dynamicDataSourceProperties.getSeata());
return dataSource;
}
}
Mapper
- 使用注解
@DS("sharding")，选择ShardingSphere数据源
package com.wf.dev.mapper.monitor.business;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.wf.dev.model.monitor.business.BusinessMonitorReq;
import com.wf.dev.model.monitor.business.BusinessMonitorVO;
import java.util.List;
/**
*
* @author wf
* @date 2022年11月28日
*/
@DS("sharding")
public interface BusinessMonitorMapper {
List<BusinessMonitorVO> list(BusinessMonitorReq req);
}
参考
Shardingsphere与dynamic-datasource配合实现多数据源切换
SpringBoot整合ShardingSphere 4.x单库 分表
dynamic + shardingsphere(4.1.1) 实现动态分库分表
shardingsphere-jdbc 之 Mybatis Interval 分片算法
标签：shardingSphere,org,dynamic,datasource,整合,数据源,import,com From： https://www.cnblogs.com/wftop1/p/16942889.html