shardingsphere-jdbc 按照租户（强制路由）分库+ springboot +MySQL

最新推荐文章于 2025-06-01 09:17:55 发布

cuit_618

最新推荐文章于 2025-06-01 09:17:55 发布

阅读量1.9k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： shardingsphere

本文链接：https://blog.youkuaiyun.com/zhaocuit/article/details/120913024

shardingsphere 专栏收录该内容

2 篇文章

订阅专栏

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();
        }
    }
}