为什么不适用druid、dynamic的多数据源方式呢,因为后面分库分表会使用shardingsphere 。
技术选型:
```
java = 8
mysql = 5.7.30
spring-boot.version = 2.3.4.RELEASE
mybatis plus = 3.4.0
shardingsphere = 4.1.1
```
官网地址:
```
https://shardingsphere.apache.org/
```
实现目标:
不同的服务插入到不同的数据源中,为什么不每个服务创建连接不同的数据源呢?因为那就没法测试shardingsphere的强制路由了,这样是最简单的测试方式。。
官网简介:
> ShardingSphere使用ThreadLocal管理分片键值进行Hint强制路由。可以通过编程的方式向HintManager中添加分片值,该分片值仅在当前线程内生效。 Hint方式主要使用场景:
1.分片字段不存在SQL中、数据库表结构中,而存在于外部业务逻辑。
2.强制在主库进行某些数据操作。
测试项目结构:

这里配置了三个数据源是为了方便演示,对应的数据库:

三个库中的表是相同的:

目标是插入数据到不同的库中:
```
user -> code_sharding1
order -> code_sharding2
member -> code_sharding3
```
项目pom:
```
<dependencies>
<dependency>
<groupId>com.alibaba.cloud</groupId>
<artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba.cloud</groupId>
<artifactId>spring-cloud-starter-alibaba-nacos-config</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-openfeign</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
```
user服务的配置:
```
spring:
shardingsphere:
props:
sql.show: true
datasource:
names: ds0,ds1,ds2
ds0:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://XXX:XXX/code_sharding1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
username: XXX
password: XXX
ds1:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://XXX:XXX/code_sharding2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
username: XXX
password: XXX
ds2:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://XXX:XXX/code_sharding3?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
username: XXX
password: XXX
sharding:
tables:
user:
actualDataNodes: ds$->{0..2}.user
databaseStrategy:
hint:
algorithm-class-name: cool.qyb.microservice.sharding.user.server.common.DatasourceRoutingAlgorithm
```
其他服务的配置类似,只是spring.shardingsphere.sharding.tables不同
spring.shardingsphere.sharding.tables :配置不同表的sharding规则。
spring.shardingsphere.sharding.tables.user.actualDataNodes : 真实数据节点。
spring.shardingsphere.sharding.tables.user.databaseStrategy : 策略,这里指定了hint策略,algorithm-class-name需指向实现了HintShardingAlgorithm接口的方法,这里只配置了database的切换,也可以加上table的切换,都是可以指定的。
DatasourceRoutingAlgorithm :
```
package cool.qyb.microservice.sharding.user.server.common;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;
import java.util.Collection;
import java.util.HashSet;
/**
* 强制分片策略
*/
@Slf4j
public class DatasourceRoutingAlgorithm implements HintShardingAlgorithm<String> {
@Override
public Collection<String> doSharding(Collection<String> collection, HintShardingValue<String> hintShardingValue) {
Collection<String> result = new HashSet<>();
for (String value : hintShardingValue.getValues()) {
if (DatasourceType.DATASOURCE_USER.getValue().equals(value)) {
if (collection.contains(DatasourceType.DATASOURCE_USER.getValue())) {
result.add(DatasourceType.DATASOURCE_USER.getValue());
}
} else if (DatasourceType.DATASOURCE_MEMBER.getValue().equals(value)) {
if (collection.contains(DatasourceType.DATASOURCE_MEMBER.getValue())) {
result.add(DatasourceType.DATASOURCE_MEMBER.getValue());
}
} else {
if (collection.contains(DatasourceType.DATASOURCE_ORDER.getValue())) {
result.add(DatasourceType.DATASOURCE_ORDER.getValue());
} else {
throw new RuntimeException("选择数据源错误!");
}
}
}
log.info("availableTargetNames : {}, shardingValue : {},返回的数据源 : {}", collection, hintShardingValue, result);
return result;
}
}
```
这里指定了hint sharding的database。
因为要告诉shardingsphere选择哪个database,所以需要通过HintManager指定database,这里选择使用spring aop实现:
```
package cool.qyb.microservice.sharding.user.server.common;
import cool.qyb.microservice.sharding.user.server.common.annotation.QDS;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.hint.HintManager;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
@Slf4j
@Aspect
@Component
public class DatasourceAOP {
@Pointcut("execution(public * cool.qyb.microservice.sharding.user.server.modules.service.impl.*Impl.*(..))")
public void pointcut() {
}
@Around("pointcut()")
public Object doAround(ProceedingJoinPoint point) throws Throwable {
log.info("--------------------切换数据源--------------------");
HintManager hintManager = HintManager.getInstance();
QDS annotation = point.getTarget().getClass().getAnnotation(QDS.class);
if (annotation != null) {
String value = annotation.value();
if (StringUtils.isNotBlank(value)) {
hintManager.setDatabaseShardingValue(value);
}
} else {
hintManager.setDatabaseShardingValue(DatasourceType.DATASOURCE_USER.getValue());
}
try {
return point.proceed();
} finally {
hintManager.close();
}
}
}
```
对应的datasource枚举类:
```
package cool.qyb.microservice.sharding.user.server.common;
/**
* 数据源
*/
public enum DatasourceType {
DATASOURCE_USER("ds0"),
DATASOURCE_ORDER("ds1"),
DATASOURCE_MEMBER("ds2");
public String getValue() {
return value;
}
private String value;
DatasourceType(String value){
this.value = value;
}
}
```
这样在需要切换数据源的serviceimpl上面添加@QDS("xx")就可以完成数据源的切换,这里仅是简单的修改database,更多可以自由发挥。
原理:
在ShardingStandardRoutingEngine的getDataNodes方法中,判断是否启用hint,在getDatabaseShardingValuesFromHint中,通过
```return getRouteValues(HintManager.isDatabaseShardingOnly() ? HintManager.getDatabaseShardingValues() : HintManager.getDatabaseShardingValues(logicTableName));```获取到aop中设置的database完成routeing。

shardingshpere 实现数据源切换(hint模式)