shardingsphere-jdbc 按照租户(强制路由)分库+ springboot +MySQL
pom.xml
<?xml version="1.0"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<!--<version>2.2.5.RELEASE</version>-->
<version>1.5.5.RELEASE</version>
<relativePath/>
</parent>
<groupId>com.example</groupId>
<artifactId>test-shardingsphere</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<description>Demo project for Spring Boot</description>
<properties>
</properties>
<dependencies>
<!--swagger ui-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<!--springboot-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
</dependency>
<!--sharding-jdbc-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
<!--mybatis starter-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.41</version>
</dependency>
<!--alibaba数据源-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.12</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.10</version>
</dependency>
<!--aop-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<executions>
<execution>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
springboot配置 application.properties
server.port=8090
spring.application.name=test-shardingSphere
mybatis.mapper-locations=classpath:mapper/*Mapper.xml
spring.profiles.active=xml_config
#spring.profiles.active=class_config
springboot配置 application-class_config.properties
###如果使用这种方式,需要在启动类中加上exclude={DataSourceAutoConfiguration.class},如果使用配置文件方式,这个可加可不加
###奇怪的是我在MyConfig类上加@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})还是不行
spring.shardingsphere.enabled=false
spring.shardingsphere.tables=t_user,t_order_item,t_order
spring.shardingsphere.dataSourcesUrls=AG=jdbc:mysql://127.0.0.1:3306/order_db0?useUnicode=true&characterEncoding=utf-8&useSSL=false,ZW=jdbc:mysql://127.0.0.1:3306/order_db1?useUnicode=true&characterEncoding=utf-8&useSSL=false,QZ=jdbc:mysql://127.0.0.1:3306/order_db2?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.dataSourcesUserNames=root,root,root
spring.shardingsphere.dataSourcesPasswords=123456,123456,123456
springboot配置 application-xml_config.properties
# 数据源名称 是逻辑名称,不一定和数据库名一样
spring.shardingsphere.datasource.names=AG, ZW, QZ
# 第一个数据源
spring.shardingsphere.datasource.AG.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.AG.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.AG.url=jdbc:mysql://127.0.0.1:3306/dsmsxf?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.AG.username=root
spring.shardingsphere.datasource.AG.password=123456
# 第二个数据源
spring.shardingsphere.datasource.ZW.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ZW.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ZW.url=jdbc:mysql://127.0.0.1:3306/dst001?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.ZW.username=root
spring.shardingsphere.datasource.ZW.password=123456
# 第三个数据源
spring.shardingsphere.datasource.QZ.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.QZ.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.QZ.url=jdbc:mysql://127.0.0.1:3306/dst002?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.QZ.username=root
spring.shardingsphere.datasource.QZ.password=123456
# 默认数据源,不涉及分表的表会使用这个数据源
spring.shardingsphere.sharding.default-data-source-name=AG
# 需要分库的表,如果不配置这些,所有数据将会直接路由到默认数据源
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=AG.t_user,ZW.t_user,QZ.t_user
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=AG.t_order_item,ZW.t_order_item,QZ.t_order_item
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=AG.t_order,ZW.t_order,QZ.t_order
spring.shardingsphere.sharding.default-database-strategy.hint.algorithm-class-name=com.config.MyPreciseShardingAlgorithm2
# 开启sql显示
spring.shardingsphere.props.sql.show=true
init.sql
CREATE TABLE `t_order` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`config_id` bigint(20) unsigned NOT NULL COMMENT '',
`order_name` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
CREATE TABLE `t_order_item` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`order_id` bigint(20) unsigned NOT NULL COMMENT '',
`dict_id` bigint(20) unsigned NOT NULL COMMENT '',
`item_name` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
CREATE TABLE `t_config` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`config_name` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
CREATE TABLE `t_dict` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`dict_name` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
CREATE TABLE `t_user` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`username` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
insert into t_config values (1,'配置1');
insert into t_config values (2,'配置2');
insert into t_dict values (1,'字典1');
insert into t_dict values (2,'字典2');
insert into t_user values (1,'用户1');
insert into t_user values (2,'用户2');
MyConfig
@Configuration
@ConditionalOnProperty(prefix = "spring.shardingsphere", name = "enabled", havingValue = "false")
public class MyConfig {
@Value("${spring.shardingsphere.tables}")
private String tables;
@Value("${spring.shardingsphere.dataSourcesUrls}")
private String dataSourcesUrls;
@Value("${spring.shardingsphere.dataSourcesUserNames}")
private String dataSourcesUserNames;
@Value("${spring.shardingsphere.dataSourcesPasswords}")
private String dataSourcesPasswords;
@Bean("dataSource")
public DataSource shardingDataSource() throws SQLException {
// 配置真实数据源
String []urls= dataSourcesUrls.split(",");
String[] dbUserNames = dataSourcesUserNames.split(",");
String[] dbPasswords = dataSourcesPasswords.split(",");
Map<String, DataSource> dataSourceMap = new HashMap<>(urls.length);
String defaultDs = "";
for(int i=0;i<urls.length;i++){
DruidDataSource ds = new DruidDataSource();
String tid=urls[i].split("=")[0];
String url=urls[i].split("=")[1];
ds.setUrl(url);
ds.setUsername(dbUserNames[i]);
ds.setPassword(dbPasswords[i]);
ds.setDriverClassName("com.mysql.jdbc.Driver");
dataSourceMap.put(tid, ds);
if(i==0){
defaultDs = tid;
}
}
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.setDefaultDataSourceName(defaultDs);
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new HintShardingStrategyConfiguration(new MyPreciseShardingAlgorithm2()));
String []tbs= tables.split(",");
for(String tb:tbs) {
StringBuilder stringBuilder=new StringBuilder();
dataSourceMap.forEach((k,v)->{
stringBuilder.append(k);
stringBuilder.append(".");
stringBuilder.append(tb);
stringBuilder.append(",");
});
TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration(tb,stringBuilder.substring(0, stringBuilder.length()-1));
shardingRuleConfig.getTableRuleConfigs().add(tableRuleConfig);
}
Properties p = new Properties();
p.setProperty("sql.show",Boolean.TRUE.toString());
// 获取数据源对象
DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, p);
return dataSource;
}
}
MyPreciseShardingAlgorithm2
//强制路由,需要实现HintShardingAlgorithm接口
public class MyPreciseShardingAlgorithm2 implements HintShardingAlgorithm<String> {
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<String> shardingValue) {
Collection<String> values = shardingValue.getValues();
return values;
}
}
SwaggerConfig
@Configuration
@EnableSwagger2
public class SwaggerConfig {
@Bean
public Docket createRestApi() {
return new Docket(DocumentationType.SWAGGER_2)
.groupName("分库分表").apiInfo(apiInfo())
.protocols(Sets.newHashSet("http"))
.select().apis(RequestHandlerSelectors.basePackage("com..controller"))
.build().enable(true);
}
private ApiInfo apiInfo() {
return new ApiInfoBuilder().title("分库分表 - API").description("").termsOfServiceUrl("").version("5.0.0").build();
}
}
ShardingJDBCAop
@Aspect
@Component
@EnableAspectJAutoProxy
public class ShardingJDBCAop {
@Pointcut("execution(* com.mapper.*Mapper.*(..))")
public void pc(){}
/**
* 注意:这里不能使用before搭档after这种
* @param point
* @return
*/
@Around("pc()")
public Object round(ProceedingJoinPoint point){
HintManager hintManager = HintManager.getInstance();
try{
//这里可以根据登录session中租户标识进行动态设置值
hintManager.setDatabaseShardingValue("ZW");
return point.proceed();
} catch (Throwable e) {
throw new RuntimeException("异常了",e);
} finally {
hintManager.close();
}
}
}