本次项目中涉及了三组数据源:系统数据的mysql、tdEngine、shardingsphere分库分表的mysql。具体数据源的yml配置如下:
一、数据源配置1——YAML
spring:
datasource:
dynamic:
primary: base
strict: true
datasource:
base:
username: ${MYSQL_USER}
password: ${MYSQL_PWD}
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://${MYSQL_HOST}:${MYSQL_PORT}/base?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8&enabledTLSProtocols=TLSv1.2&useSSL=false
name: HikariCP-1
hikari:
pool-name: HikaraPool-1
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
maximum-pool-size: 6
minimum-idle: 5
detect:
username: root
password: taosdata
driver-class-name: com.taosdata.jdbc.TSDBDriver
url: jdbc:TAOS://${TAOS_HOST}:${TAOS_PORT}/detect
name: HikariCP-2
hikari:
pool-name: HikaraPool-2
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
maximum-pool-size: 15
minimum-idle: 5
shardingsphere:
# 打印sql
props:
sql-show: true
datasource:
dta_data:
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql://${MYSQL_HOST}:${MYSQL_PORT}/dta_data?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8&enabledTLSProtocols=TLSv1.2&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
username: ${MYSQL_USER}
password: ${MYSQL_PWD}
rules:
table1: dta_data
table2: dta_data
二、数据源配置2——DynamicDataSourceProvider
使用DynamicDataSourceProvider后,可以用mybatis的@DS注解很方便地使用数据源了。
配置类如下:
private static final String SHARDING_DATA_SOURCE_NAME = "sharding";
/**
* 动态数据源配置项
*/
@Autowired
private DynamicDataSourceProperties dynamicDataSourceProperties;
@Lazy
@Resource
private ShardingSphereDataSource shardingSphereDataSource;
@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, shardingSphereDataSource);
return dataSourceMap;
}
};
}
/**
* 将动态数据源设置为首选的
* 当spring存在多个数据源时, 自动注入的是首选的对象
* 设置为主要的数据源之后,就可以支持shardingjdbc原生的配置方式了
*/
@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()); // 如果报错,可能是Spring版本问题,注释即可。
return dataSource;
}
三、数据源使用
- 注解方式使用:
在mapper接口上或者具体的使用mapper的接口或者继承myBatis IService的接口上用@DS(“数据源名称”),即可使用对应的数据源。
示例如下:
// 用法1:在接口上面
@DS("sharding")
public interface MyServerI extends IService<MyPo> {
boolean refreshMyPo(MyPo mypo);
}
// 在具体方法上
@DS("sharding")
@Override
public boolean saveMyPo(MyPo myPo) {
return myServerI.save(myPo);
}
-
从DynamicDataSourceProvider中获取数据源
示例如下
@Autowired
private DynamicRoutingDataSource routingDataSource;
public boolean insertDetectResult(DetectionItemResult detectionItemResult) {
// 1、查询
String qry = "xxxxx";
try (
Connection connection = routingDataSource.getDataSource("detect").getConnection();
Statement stmt = connection.createStatement()
) {
ResultSet resultSet = stmt.executeQuery(qry);
// .....
} catch (Exception e) {
log.error("异常:", e);
}
return true;
}
但是上面的配置对分库分表不灵活,当产生新的分表时,无法自动创建实际使用的数据表。可以通过手动配置分库分表的数据源。示例如下:
彩蛋:手动配置分库分表数据源
- 继承MybatisPlusAutoConfiguration,获取yml中配置的数据源信息。
- 用
ShardingSphereDataSourceFactory.createDataSource()
创建分库分表数据源。
具体代码示例如下:
//-----shardingsphere数据源配置yml解析
@Data
@Component
@ConfigurationProperties(prefix = "spring.shardingsphere")
public class MultipleDbConfiguration {
private Map<String, Datasource> datasource;
private Map<String, String> rules;
@Data
public static class Datasource {
private String url;
private String driverClassName;
private String username;
private String password;
}
}
// --------shardingsphere数据源创建、并加入到动态数据源map
@Slf4j
@Configuration
public class ShardingMybatisConfig extends MybatisPlusAutoConfiguration {
private static final String SHARDING_DATA_SOURCE_NAME = "sharding";
private final MultipleDbConfiguration multipleDbConfig;
/**
* 动态数据源配置项
*/
@Autowired
private DynamicDataSourceProperties dynamicDataSourceProperties;
public ShardingMybatisConfig(MybatisPlusProperties properties,
ObjectProvider<Interceptor[]> interceptorsProvider,
ObjectProvider<TypeHandler[]> typeHandlersProvider,
ObjectProvider<LanguageDriver[]> languageDriversProvider,
ResourceLoader resourceLoader,
ObjectProvider<DatabaseIdProvider> databaseIdProvider,
ObjectProvider<List<ConfigurationCustomizer>> configurationCustomizersProvider,
ObjectProvider<List<MybatisPlusPropertiesCustomizer>> mybatisPlusPropertiesCustomizerProvider,
ApplicationContext applicationContext,
MultipleDbConfiguration multipleDbConfig) {
super(properties, interceptorsProvider, typeHandlersProvider, languageDriversProvider, resourceLoader, databaseIdProvider,
configurationCustomizersProvider, mybatisPlusPropertiesCustomizerProvider,
applicationContext);
this.multipleDbConfig = multipleDbConfig;
}
@Bean
public DataSource sharding() throws SQLException {
// 配置真实数据源
Map<String, MultipleDbConfiguration.Datasource> dbs = multipleDbConfig.getDatasource();
Map<String, DataSource> dataSourceMap = new HashMap<>(dbs.size());
for (String dbName : dbs.keySet()) {
MultipleDbConfiguration.Datasource dbConfig = dbs.get(dbName);
HikariDataSource dataSource = new HikariDataSource();
dataSource.setDriverClassName(dbConfig.getDriverClassName());
dataSource.setJdbcUrl(dbConfig.getUrl());
dataSource.setUsername(dbConfig.getUsername());
dataSource.setDriverClassName(dbConfig.getDriverClassName());
dataSource.setPassword(dbConfig.getPassword());
dataSourceMap.put(dbName, dataSource);
}
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
Map<String, String> rules = multipleDbConfig.getRules();
for (final String table : rules.keySet()) {
final String actualDataNodes = String.join(".", rules.get(table), table);
final ShardingTableRuleConfiguration shardingTableRuleConfiguration = new ShardingTableRuleConfiguration(table, actualDataNodes);
shardingRuleConfig.getTables().add(shardingTableRuleConfiguration);
}
shardingRuleConfig.setDefaultTableShardingStrategy(
new StandardShardingStrategyConfiguration("分库或分表字段", "自定义的分库或分表算法类实例名称"));
// 配置分表算法
Properties tableShardingAlgorithmProps = new Properties();
tableShardingAlgorithmProps.setProperty("strategy", "standard");
tableShardingAlgorithmProps.setProperty("algorithmClassName", "自定义的分库或分表算法类实全路径");
shardingRuleConfig.getShardingAlgorithms().put("myShardingAlgorithm",
new ShardingSphereAlgorithmConfiguration("CLASS_BASED", tableShardingAlgorithmProps));
// 开启Sql日志
final Properties properties = new Properties();
properties.setProperty("sql-show", "true");
// 创建 ShardingSphereDataSource
DataSource dataSource = ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, Collections.singleton(shardingRuleConfig), properties);
return dataSource;
}
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
Map<String, DataSourceProperty> datasourceMap = dynamicDataSourceProperties.getDatasource();
return new AbstractDataSourceProvider() {
@SneakyThrows
@Override
public Map<String, DataSource> loadDataSources() {
Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
// 将 shardingjdbc 管理的数据源也交给动态数据源管理
dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, sharding());
return dataSourceMap;
}
};
}
/**
* 将动态数据源设置为首选的
* 当spring存在多个数据源时, 自动注入的是首选的对象
* 设置为主要的数据源之后,就可以支持shardingjdbc原生的配置方式了
*/
@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());
return dataSource;
}
}
// ------------我的分库分表算法
/**
* Description:分表策略
*
* @author LiuLei
* @version 1.0
* @date 2023/9/13 18:02
*/
@Slf4j
public class MyShardingAlgorithm implements StandardShardingAlgorithm<Integer> {
private static final String TABLE_SPLIT_SYMBOL = "_";
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
String logicTableName = shardingValue.getLogicTableName();
ShardingTablesCacheEnum logicTable = ShardingTablesCacheEnum.of(logicTableName);
if (logicTable == null) {
log.error("逻辑表名未配置!logicTableNames:{},logicTableName:{}",
ShardingTablesCacheEnum.logicTableNames(), logicTableName);
throw new IllegalArgumentException("逻辑表名未配置!");
}
Integer value = shardingValue.getValue();
String resultTableName = logicTableName + TABLE_SPLIT_SYMBOL + value;
// 检查分表获取的表名是否存在,不存在则自动建表
if (!availableTargetNames.contains(resultTableName)) {
availableTargetNames.add(resultTableName);
}
return ShardingAlgorithmsTool.getShardingTableAndCreate(logicTable, resultTableName);
}
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Integer> shardingValue) {
log.error("进入了范围分表!");
return null;
}
@Override
public void init() {
}
@Override
public String getType() {
return null;
}
}
// -----------服务启动时,自动刷新分库分表的表和自动根据sql请求,创建实际表
@Slf4j
public class ShardingAlgorithmsTool {
/**
* 表分片符号,例:system_msg_202201 中,分片符号为 "_"
*/
private static final String TABLE_SPLIT_SYMBOL = "_";
/**
* 数据库配置
*/
private static final Environment ENV = SpringUtil.getApplicationContext().getEnvironment();
private static final String DATASOURCE_URL = ENV.getProperty("spring.shardingsphere.datasource." + ShardingTableEnum.DB_NAME.getValue() + ".url");
private static final String DATASOURCE_USERNAME = ENV.getProperty("spring.shardingsphere.datasource." + ShardingTableEnum.DB_NAME.getValue() + ".username");
private static final String DATASOURCE_PASSWORD = ENV.getProperty("spring.shardingsphere.datasource." + ShardingTableEnum.DB_NAME.getValue() + ".password");
/**
* 检查分表获取的表名是否存在,不存在则自动建表
*
* @param logicTable 逻辑表
* @param resultTableName 真实表名
* @return 确认存在于数据库中的真实表名
*/
public static String getShardingTableAndCreate(ShardingTablesCacheEnum logicTable, String resultTableName) {
// 缓存中有此表则返回,没有则判断创建
if (logicTable.resultTableNamesCache().contains(resultTableName)) {
return resultTableName;
} else {
// 未创建的表返回逻辑空表
boolean isSuccess = createShardingTable(logicTable, resultTableName);
return isSuccess ? resultTableName : logicTable.logicTableName();
}
}
/**
* 重载全部缓存
*/
public static void tableNameCacheReloadAll() {
Arrays.stream(ShardingTablesCacheEnum.values()).forEach(ShardingAlgorithmsTool::tableNameCacheReload);
}
/**
* 重载指定分表缓存
*
* @param logicTable 逻辑表
*/
public static void tableNameCacheReload(ShardingTablesCacheEnum logicTable) {
// 读取数据库中所有表名
List<String> tableNameList = getAllTableNameBySchema(logicTable);
// 删除旧的缓存(如果存在)
logicTable.resultTableNamesCache().clear();
// 写入新的缓存
logicTable.resultTableNamesCache().addAll(tableNameList);
// 动态更新配置 actualDataNodes
actualDataNodesRefresh(logicTable);
}
/**
* 获取所有表名
*
* @param logicTable 逻辑表
* @return 表名集合
*/
public static List<String> getAllTableNameBySchema(ShardingTablesCacheEnum logicTable) {
List<String> tableNames = new ArrayList<>();
if (StringUtils.isEmpty(DATASOURCE_URL) || StringUtils.isEmpty(DATASOURCE_USERNAME) || StringUtils.isEmpty(DATASOURCE_PASSWORD)) {
log.error(">>>>>>>>>> 【ERROR】数据库连接配置有误,请稍后重试,URL:{}, username:{}, password:{}", DATASOURCE_URL, DATASOURCE_USERNAME, DATASOURCE_PASSWORD);
throw new IllegalArgumentException("数据库连接配置有误,请稍后重试");
}
try (Connection conn = DriverManager.getConnection(DATASOURCE_URL, DATASOURCE_USERNAME, DATASOURCE_PASSWORD);
Statement st = conn.createStatement()) {
String logicTableName = logicTable.logicTableName();
try (ResultSet rs = st.executeQuery("show TABLES like '" + logicTableName + TABLE_SPLIT_SYMBOL + "%'")) {
while (rs.next()) {
tableNames.add(rs.getString(1));
}
}
} catch (SQLException e) {
log.error(">>>>>>>>>> 【ERROR】数据库连接失败,请稍后重试,原因:{}", e.getMessage(), e);
throw new IllegalArgumentException("数据库连接失败,请稍后重试");
}
return tableNames;
}
/**
* 动态更新配置 actualDataNodes
*
* @param logicTable ShardingTablesCacheEnum
*/
public static void actualDataNodesRefresh(ShardingTablesCacheEnum logicTable) {
try {
// 获取数据分片节点
String dbName = ShardingTableEnum.DB_NAME.getValue();
String logicTableName = logicTable.logicTableName();
Set<String> tableNamesCache = logicTable.resultTableNamesCache();
log.info(">>>>>>>>>> 【INFO】更新分表配置,logicTableName:{},tableNamesCache:{}", logicTableName, tableNamesCache);
String newActualDataNodes = tableNamesCache.stream().map(o -> String.format("%s.%s", dbName, o)).collect(Collectors.joining(","));
ShardingSphereDataSource shardingSphereDataSource = SpringUtil.getBean("sharding", ShardingSphereDataSource.class);
updateShardRuleActualDataNodes(shardingSphereDataSource, logicTableName, newActualDataNodes);
} catch (Exception e) {
log.error("初始化 动态表单失败,原因:{}", e.getMessage(), e);
}
}
// --------------------------------------------------------------------------------------------------------------
// 私有方法
// --------------------------------------------------------------------------------------------------------------
/**
* 刷新ActualDataNodes
*/
private static void updateShardRuleActualDataNodes(ShardingSphereDataSource dataSource, String logicTableName, String newActualDataNodes) {
ContextManager contextManager = dataSource.getContextManager();
String schemaName = "logic_db";
Collection<RuleConfiguration> newRuleConfigList = new LinkedList<>();
Collection<RuleConfiguration> oldRuleConfigList = dataSource.getContextManager()
.getMetaDataContexts()
.getMetaData(schemaName)
.getRuleMetaData()
.getConfigurations();
for (RuleConfiguration oldRuleConfig : oldRuleConfigList) {
if (oldRuleConfig instanceof ShardingRuleConfiguration) {
// Algorithm provided sharding rule configuration
ShardingRuleConfiguration oldAlgorithmConfig = (ShardingRuleConfiguration) oldRuleConfig;
ShardingRuleConfiguration newAlgorithmConfig = new ShardingRuleConfiguration();
// Sharding table rule configuration Collection
Collection<ShardingTableRuleConfiguration> newTableRuleConfigList = new LinkedList<>();
Collection<ShardingTableRuleConfiguration> oldTableRuleConfigList = oldAlgorithmConfig.getTables();
oldTableRuleConfigList.forEach(oldTableRuleConfig -> {
if (logicTableName.equals(oldTableRuleConfig.getLogicTable())) {
ShardingTableRuleConfiguration newTableRuleConfig = new ShardingTableRuleConfiguration(oldTableRuleConfig.getLogicTable(), newActualDataNodes);
newTableRuleConfig.setTableShardingStrategy(oldTableRuleConfig.getTableShardingStrategy());
newTableRuleConfig.setDatabaseShardingStrategy(oldTableRuleConfig.getDatabaseShardingStrategy());
newTableRuleConfig.setKeyGenerateStrategy(oldTableRuleConfig.getKeyGenerateStrategy());
newTableRuleConfigList.add(newTableRuleConfig);
} else {
newTableRuleConfigList.add(oldTableRuleConfig);
}
});
newAlgorithmConfig.setTables(newTableRuleConfigList);
newAlgorithmConfig.setAutoTables(oldAlgorithmConfig.getAutoTables());
newAlgorithmConfig.setBindingTableGroups(oldAlgorithmConfig.getBindingTableGroups());
newAlgorithmConfig.setBroadcastTables(oldAlgorithmConfig.getBroadcastTables());
newAlgorithmConfig.setDefaultDatabaseShardingStrategy(oldAlgorithmConfig.getDefaultDatabaseShardingStrategy());
newAlgorithmConfig.setDefaultTableShardingStrategy(oldAlgorithmConfig.getDefaultTableShardingStrategy());
newAlgorithmConfig.setDefaultKeyGenerateStrategy(oldAlgorithmConfig.getDefaultKeyGenerateStrategy());
newAlgorithmConfig.setDefaultShardingColumn(oldAlgorithmConfig.getDefaultShardingColumn());
newAlgorithmConfig.setShardingAlgorithms(oldAlgorithmConfig.getShardingAlgorithms());
newAlgorithmConfig.setKeyGenerators(oldAlgorithmConfig.getKeyGenerators());
newRuleConfigList.add(newAlgorithmConfig);
}
}
contextManager.alterRuleConfiguration(schemaName, newRuleConfigList);
}
/**
* 创建分表
*
* @param logicTable 逻辑表
* @param resultTableName 真实表名,例:logicTableName_xxx
* @return 创建结果(true创建成功,false未创建)
*/
private static boolean createShardingTable(ShardingTablesCacheEnum logicTable, String resultTableName) {
synchronized (logicTable.logicTableName().intern()) {
// 缓存中有此表 返回
if (logicTable.resultTableNamesCache().contains(resultTableName)) {
return false;
}
// 缓存中无此表,则建表并添加缓存
executeSql(Collections.singletonList("CREATE TABLE IF NOT EXISTS `" + resultTableName + "` LIKE `" + logicTable.logicTableName() + "`;"));
// 缓存重载
tableNameCacheReload(logicTable);
}
return true;
}
/**
* 执行SQL
*
* @param sqlList SQL集合
*/
private static void executeSql(List<String> sqlList) {
if (StringUtils.isEmpty(DATASOURCE_URL) || StringUtils.isEmpty(DATASOURCE_USERNAME) || StringUtils.isEmpty(DATASOURCE_PASSWORD)) {
log.error(">>>>>>>>>> 【ERROR】数据库连接配置有误,请稍后重试,URL:{}, username:{}, password:{}", DATASOURCE_URL, DATASOURCE_USERNAME, DATASOURCE_PASSWORD);
throw new IllegalArgumentException("数据库连接配置有误,请稍后重试");
}
try (Connection conn = DriverManager.getConnection(DATASOURCE_URL, DATASOURCE_USERNAME, DATASOURCE_PASSWORD)) {
try (Statement st = conn.createStatement()) {
conn.setAutoCommit(false);
for (String sql : sqlList) {
st.execute(sql);
}
} catch (Exception e) {
conn.rollback();
log.error(">>>>>>>>>> 【ERROR】数据表创建执行失败,请稍后重试,原因:{}", e.getMessage(), e);
throw new IllegalArgumentException("数据表创建执行失败,请稍后重试");
}
} catch (SQLException e) {
log.error(">>>>>>>>>> 【ERROR】数据库连接失败,请稍后重试,原因:{}", e.getMessage(), e);
throw new IllegalArgumentException("数据库连接失败,请稍后重试");
}
}
}
public enum ShardingTablesCacheEnum {
MY_TB("my_tb", new HashSet<>());
private static Map<String, ShardingTablesCacheEnum> valueMap = new HashMap<>();
static {
Arrays.stream(ShardingTablesCacheEnum.values()).forEach(o -> valueMap.put(o.logicTableName, o));
}
/**
* 逻辑表名
*/
private final String logicTableName;
/**
* 实际表名
*/
private final Set<String> resultTableNamesCache;
ShardingTablesCacheEnum(String logicTableName, Set<String> resultTableNamesCache) {
this.logicTableName = logicTableName;
this.resultTableNamesCache = resultTableNamesCache;
}
public static ShardingTablesCacheEnum of(String value) {
return valueMap.get(value);
}
public static Set<String> logicTableNames() {
return valueMap.keySet();
}
public String logicTableName() {
return logicTableName;
}
public Set<String> resultTableNamesCache() {
return resultTableNamesCache;
}
@Override
public String toString() {
return "ShardingTableCacheEnum{" +
"logicTableName='" + logicTableName + '\'' +
", resultTableNamesCache=" + resultTableNamesCache +
'}';
}
}
// --------------启动服务调用
@Order(value = 1) // 数字越小,越先执行
@Component
public class ShardingTableLoadRunner implements CommandLineRunner {
@Override
public void run(String... args) {
// 读取已有分表,进行缓存
ShardingAlgorithmsTool.tableNameCacheReloadAll();
}
}
通过上面的配置后,就可以在新增分表时,自动根据逻辑表(结构和分表一样的表)创建分表了。
《完》