dynamic-datasource + sharding 动态刷新表

原创已于 2024-09-03 09:30:58 修改 · 置顶 · 2.9k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#java #sharding 动态创建表 #spring boot

于 2022-02-21 09:36:26 首次发布

sharding 同时被 2 个专栏收录

4 篇文章

订阅专栏

动态数据源

3 篇文章

订阅专栏

本文介绍如何结合dynamic-datasource和Sharding-JDBC实现动态数据源切换及按年分表策略。通过Spring Boot整合，实现多数据源管理和动态刷新表结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

需求

在系统中可能会遇到动态数据源以及分表分库，动态分表

的情况，动态数据源切换采用的

<dependency>
   <groupId>com.baomidou</groupId>
   <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
   <version>3.2.1</version>
</dependency>

分表分库采用

<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
   <version>4.1.1</version>
</dependency>
<!-- for spring namespace -->
<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>sharding-jdbc-spring-namespace</artifactId>
   <version>4.1.1</version>
</dependency>

将表shardingsphere 交给DynamicDataSource管理

package com.iot.cloud.iotdevice.config;

/**
* @author lj
* @title: DataSourceConfiguration
* @projectName iot-platform-cloud
* @description: TODO 管理多数据源 shardingsphere的数据源交给dynamic-datasource去维护。
* @date 2022/2/17 001713:52
*/

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.jdbc.core.datasource.ShardingDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
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;

@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class DataSourceConfiguration {

/**
* 动态数据源配置项
*/
@Autowired
private DynamicDataSourceProperties dynamicDataSourceProperties;

@Value("${spring.shardingsphere.datasource.names:}")
private String SHARDING_DATA_SOURCE_NAME;

@Lazy
@Resource
ShardingDataSource shardingDataSource;

@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;
}
};
}
@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;
}
}

特别注意这里的DataSource 指定 ShardingDataSource 不然后面刷新Sharding动态表时会转换失败。

动态刷新表

package com.iot.cloud.iotdevice.init;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.iot.cloud.iotdevice.config.TableNamesConfig;
import com.iot.cloud.iotdevice.constant.DataSourceConstant;
import lombok.extern.log4j.Log4j2;
import org.apache.ibatis.javassist.Modifier;
import org.apache.shardingsphere.core.rule.TableRule;
import org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource;
import org.apache.shardingsphere.shardingjdbc.jdbc.unsupported.AbstractUnsupportedOperationDataSource;
import org.apache.shardingsphere.underlying.common.config.exception.ShardingSphereConfigurationException;
import org.apache.shardingsphere.underlying.common.rule.DataNode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
* @author Administrator
* @title: lj
* @projectName citymanagementplatform
* @description: TODO
* @date 2021/1/12 00129:41
*/
@Log4j2
@Component
@EnableConfigurationProperties(TableNamesConfig.class)
public class DeviceCreateTableHandler {

@Autowired
private TableNamesConfig tableNamesConfig;

@Value("${spring.shardingsphere.datasource.names:}")
private String SHARDING_DATA_SOURCE_NAME;

@Autowired
private DataSource dataSource;

/***
* 启动的时候初始化一下动态表单的配置初化当前年
*/
@PostConstruct
private void intData() {
Date date = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyy");
String s = format.format(date);
actualTablesRefresh(Integer.parseInt(s));
}

/**
* 初始化下一天的
*/
@Scheduled(cron = "0 0 20 31 12 ?")
public void nextInit() {
Date date = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyy");
String s = format.format(date);
actualTablesRefresh(Integer.parseInt(s) + 1);
}

/**
* 动态更新处理化的表配置
*
* @param data 表名称
*/
public void actualTablesRefresh(Integer data) {
try {
DynamicRoutingDataSource dd =(DynamicRoutingDataSource) this.dataSource;
Map<String, DataSource> map = dd.getCurrentDataSources();
if (tableNamesConfig.getNames() == null || tableNamesConfig.getNames().length == 0) {
log.info("dynamic.table.names为空");
return;
}
for (int i = 0; i < tableNamesConfig.getNames().length; i++) {
// 这里前缀为数据库简称后面为表名称
String dbName = tableNamesConfig.getNames()[i];
String [] dbNames = dbName.split("-");
DataSource dsource = map.get(dbNames[0]);
ShardingDataSource dataSource = (ShardingDataSource) dsource;
TableRule tableRule = null;
try {
tableRule = dataSource.getRuntimeContext().getRule().getTableRule(dbNames[1]);
} catch (ShardingSphereConfigurationException e) {
log.error("逻辑表：{},不存在配置！", dbNames[1]);
}
// 动态刷新 actualDataNodes
List<DataNode> dataNodes = tableRule.getActualDataNodes();
Field actualDataNodesField = TableRule.class.getDeclaredField("actualDataNodes");
Field modifiersField = Field.class.getDeclaredField("modifiers");
modifiersField.setAccessible(true);
modifiersField.setInt(actualDataNodesField, actualDataNodesField.getModifiers() & ~Modifier.FINAL);
actualDataNodesField.setAccessible(true);
List<DataNode> newDataNodes = new ArrayList<>();
int time = tableNamesConfig.getStartYear();
String dataSourceName = dataNodes.get(0).getDataSourceName();
while (true) {
DataNode dataNode = new DataNode(dataSourceName + "." + dbNames[1] + "_" + time);
newDataNodes.add(dataNode);
time = time + 1;
if (time > data.intValue()) {
break;
}
}
actualDataNodesField.set(tableRule, newDataNodes);
Set<String> actualTables = Sets.newHashSet();
Map<DataNode, Integer> dataNodeIndexMap = Maps.newHashMap();
AtomicInteger index = new AtomicInteger(0);
newDataNodes.forEach(dataNode -> {
actualTables.add(dataNode.getTableName());
if (index.intValue() == 0) {
dataNodeIndexMap.put(dataNode, 0);
} else {
dataNodeIndexMap.put(dataNode, index.intValue());
}
index.incrementAndGet();
});
// 动态刷新 actualTablesField
Field actualTablesField = TableRule.class.getDeclaredField("actualTables");
actualTablesField.setAccessible(true);
actualTablesField.set(tableRule, actualTables);
// 动态刷新 dataNodeIndexMapField
Field dataNodeIndexMapField = TableRule.class.getDeclaredField("dataNodeIndexMap");
dataNodeIndexMapField.setAccessible(true);
dataNodeIndexMapField.set(tableRule, dataNodeIndexMap);
// 动态刷新 datasourceToTablesMapField
Map<String, Collection<String>> datasourceToTablesMap = Maps.newHashMap();
datasourceToTablesMap.put(dataSourceName, actualTables);
Field datasourceToTablesMapField = TableRule.class.getDeclaredField("datasourceToTablesMap");
datasourceToTablesMapField.setAccessible(true);
datasourceToTablesMapField.set(tableRule, datasourceToTablesMap);
}
} catch (Exception e) {
e.printStackTrace();
log.info("初始化动态表单失败" + e.getMessage());
}
}
}

特别注意我这里是按年分表，具体的分表策略你们自己安需求弄。

配置 yml

spring:
  main:
    allow-bean-definition-overriding: true
  ######## 配置多数据源 ########
  datasource:
    dynamic:
      #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      strict: true
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:${DATASOURCE_DBTYPE:mysql}://${DATASOURCE_HOST:192.168.0.252}:${DATASOURCE_PORT:3306}/iot_platform?characterEncoding=UTF-8&useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
          username: ${DATASOURCE_USERNAME:root}
          password: ${DATASOURCE_PASSWORD:123456}
  ###   这里采用的按年分表策略  对拉取的公厕硬件设备数据进行分表 暂时不分库 #####
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: toilet
      ###  主数据库 ###
      toilet:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:${DATASOURCE_DBTYPE:mysql}://${DATASOURCE_HOST:192.168.0.252}:${DATASOURCE_PORT:3306}/iot_toilet?characterEncoding=UTF-8&useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: ${DATASOURCE_USERNAME:root}
        password: ${DATASOURCE_PASSWORD:123456}
    sharding:
      tables:
        wc_passenger:
          ### 数据表 按年分表 ####   流量
          actual-data-nodes: toilet.wc_passenger
          table-strategy:
            standard:
              sharding-column: created_time
              precise-algorithm-class-name: xxx
              range-algorithm-class-name: xxxx