项目需要配置读写分离，分库分表，选了shardingJDBC，但是shardingJDBC又许多不支持的sql，综合了一下，选择mybaitis-plus做读写分离，遇到分库分表的时候切换sharding数据源，也就是一般情况下使用的还是jdbc，有分表的时候才会用shardingJDBC
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.24</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
<version>2.4.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<!-- mybatis plus start -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<!-- mybatis plus end -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
</dependency>
</dependencies>
dynamic-datasource-spring-boot-starter采用3.3以上的好像就切不过来数据源，具体没找到原因
spring:
datasource:
dynamic:
primary: master
datasource:
master:
url: jdbc:mysql://localhost:3306/test_0?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
slave:
url: jdbc:mysql://localhost:3306/test_s_0?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
shardingsphere:
datasource:
names: salve-sharding,slave0
salve_sharding:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&useTimezone=true
username: root
password: root
slave0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test_s_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&useTimezone=true
username: root
password: root
props:
sql.show: true
masterslave:
load-balance-algorithm-type: round_robin
master-data-source-name: ms
sharding:
master-slave-rules:
salve-sharding:
masterDataSourceName: salve-sharding
slaveDataSourceNames:
- slave0
tables:
tab_survey:
table-strategy:
standard:
sharding-column: customer_id
preciseAlgorithmClassName: com.ls.demoread.config.MyPreciseShardingAlgorithm
rangeAlgorithmClassName: com.ls.demoread.config.MyPreciseShardingAlgorithm
actual-data-nodes: salve-sharding.tab_user_$->{0..1}
# table-strategy:
# inline:
# sharding-column: customer_id
# algorithm-expression: tab_survey_$->{customer_id % 2}
# actual-data-nodes: salve-sharding.tab_user_$->{0..1}
logging:
level:
com.baomidou.dynamic: debug
配置mybatis-plus读写分离
@Configuration
public class DynamicDatasourceConfig {
@Bean
public MasterSlaveAutoRoutingPlugin masterSlaveAutoRoutingPlugin() {
return new MasterSlaveAutoRoutingPlugin();
}
}
数据源配置
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
SpringBootConfiguration.class})
public class DataSourceConfiguration {
/**
* 分表数据源名称
*/
private static final String SHARDING_DATA_SOURCE_NAME = "salve-sharding";
/**
* 动态数据源配置项
*/
@Autowired
private DynamicDataSourceProperties properties;
/**
* shardingjdbc有四种数据源，需要根据业务注入不同的数据源
*
* <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
* <p>2. 主从数据源: masterSlaveDataSource;
* <p>3. 脱敏数据源：encryptDataSource;
* <p>4. 影子数据源：shadowDataSource
*
*/
@Lazy
@Resource(name = "shardingDataSource")
AbstractDataSourceAdapter shardingDataSource;
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
Map<String, DataSourceProperty> datasourceMap = properties.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(properties.getPrimary());
dataSource.setStrict(properties.getStrict());
dataSource.setStrategy(properties.getStrategy());
dataSource.setProvider(dynamicDataSourceProvider);
dataSource.setP6spy(properties.getP6spy());
dataSource.setSeata(properties.getSeata());
return dataSource;
}
}
自定义分片
@Slf4j
@Component
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm, RangeShardingAlgorithm {
@Override
public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
//collection:[user_0, user_1]
// preciseShardingValue:PreciseShardingValue(logicTableName=user, columnName=age, value=20)
log.info("collection:{}",collection);
log.info("preciseShardingValue:{}",preciseShardingValue);
int customId = (int) preciseShardingValue.getValue();
String tableReal;
if (String.valueOf(customId).equals("1") || String.valueOf(customId).equals("10") ||String.valueOf(customId).equals("70")){
tableReal = preciseShardingValue.getLogicTableName().concat("_").concat("0");
}else {
tableReal = preciseShardingValue.getLogicTableName().concat("_").concat("1");
}
return tableReal;
}
@Override
public Collection<String> doSharding(Collection collection, RangeShardingValue rangeShardingValue) {
return null;
}
}
数据源切换
方法或者类上加上注解 @DS("salve-sharding")
配置主从的时候，有时候插入数据，马上查询，可能主从还没同步，这时候可以加上事务的注解，这样子就都会走主库了
