背景
有一个业务目前是单库单表的,随着业务的发展需要拓展为多库多表的形式,而且还需要将数据从原先的实例转移到分库分表后的DB中
CREATE TABLE `t_topic_2` (
`id` bigint(20) DEFAULT NULL,
`topic` varchar(20) DEFAULT NULL,
`state` int(11) DEFAULT NULL,
`connect` int(11) DEFAULT NULL,
`is_print_log` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
本地测试过后的配置如下
spring:
application:
name: test
shardingsphere:
mode:
type: Memory
props:
sql-show: true
datasource:
names: ds0, ds1
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/test
username: root
password: 12345678
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/test2
username: root
password: 12345678
rules:
sharding:
tables:
t_topic:
actualDataNodes: ds${0..1}.t_topic_${0..2}
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: table-inline
default-database-strategy:
standard:
sharding-column: id
sharding-algorithm-name: id-inline-database-strategy
sharding-algorithms:
# id-inline-database-strategy是自定义的名称,
# !!!最好别以下划线分隔,会有问题
id-inline-database-strategy:
type: INLINE
props:
# 该表达式是指把 id 值是偶数路由到 ds0,奇数路由到 ds1
algorithm-expression: ds$->{id % 2}
table-inline:
type: INLINE
props:
algorithm-expression: t_topic_$->{id % 3}
#
debug: true
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
type-aliases-package: com.dym.entity
mapper-locations: classpath*:/mapper/*.xml
动态数据源
spring:
datasource:
dynamic:
allowed-public-only: true
strict: true
primary: first
datasource:
first:
driverClassName: com.mysql.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql://localhost:3306/test
username: root
password: chenshun
shardingsphere:
mode:
type: Memory
props:
sql-show: true
datasource:
//动态数据源,参与下边的分库
names: ds0, ds1
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/test1
username: root
password: chenshun
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/test2
username: root
password: chenshun
rules:
//分片规则
sharding:
tables:
t_topic:
actualDataNodes: ds${0..1}.t_topic_${0..2}
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: table-inline
default-database-strategy:
standard:
sharding-column: id
sharding-algorithm-name: id-inline-database-strategy
sharding-algorithms:
id-inline-database-strategy:
type: INLINE
props:
algorithm-expression: ds$->{id % 2}
table-inline:
type: INLINE
props:
algorithm-expression: t_topic_$->{id % 3}
package io.github.chenshun00.springcloud.provider.config;
/**
* @author chenshun00@gmail.com
* @since 2022/5/22 2:40 PM
*/
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.springframework.beans.factory.annotation.Autowired;
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;
/**
* 动态数据源配置:
*
* 使用{@link com.baomidou.dynamic.datasource.annotation.DS}注解,切换数据源
*
* <code>@DS(DataSourceConfiguration.SHARDING_DATA_SOURCE_NAME)</code>
*
* @author fanqie
* @date 2021/8/16 下午12:36
*/
@Configuration
@AutoConfigureBefore({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
*
* 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;
}
}