DynamicDataSource遇见Sharding-JDBC实现多租户分库和业务分库分表

前言

Sharding-JDBC 常用来做分库分表，其可以配置灵活的分库表策略，满足大多数业务场景需求，此外还比较轻量级，客户端引入相应的jar即可，提供springboot properties 配置策略，上手容易。

但Sharding-JDBC作为分库分表中间件，不支持租户功能，如果想要实现租户数据隔离（表字段级别或者数据库级别）需要将所有表都进行sharding管理，虽然能实现但不够优雅而且sql受限sharing规则要求，对程序的维护性和编码难度造成影响。

由于项目中需要按照租户进行数据库级别隔离，而且还要实现部分表的分库分表（例如L:order表按照user_id分库分表），调研sharding-jdbc后发现无法完美的满足要求。

多租户隔离无非通过包装数据源DataSource进行数据连接的路由，在选择上初期也考虑自己实现一套基于spring的 AbstractRoutingDataSource 数据源 + shardingjdbc，进行租户路由。后来在mybatis-plus官网上发现一个快速集成多数据源的中间件 dynamic-datasource-spring-boot-starter 刚好满足项目需求。

dynamic-datasource 只做 切换数据源 这件核心的事情，并不限制你的具体操作，切换了数据源可以做任何CRUD。

整合后的数据源架构

自上而下分为三层：容器/业务层：通过spring的数据源以及事务管理，为上层获取数据库连接、数据源路由层、数据库存储层。

• 容器/业务层：通过spring的数据源以及事务管理，为上层获取数据库连接(mybatis、jdbctempl等)
• 数据源路由层：通过配置的租户规则和分库分表规则，选择具体的底层数据源，并返回连接
• 数据库存储层：按照租户建立的数据库和按照sharding分好的表，存储数据信息

数据源路由层实现细节

引入依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.1</version>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.3.6</version>
</dependency>

移除shardingjdbc默认starter配置

因为shardingjdbc默认starter配置，会自动创建一个shardingDataSource，这里我们直接禁用掉org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration，下面两种方式可以禁用自动配置。

• 注解@SpringBootApplication中exclude

@SpringBootApplication(exclude = SpringBootConfiguration.class)

• 配置文件中exclude

spring:
  autoconfigure:
    exclude:
      - org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration

新建一个dynamic-datasource和sharding的整合配置类

DynamicDatasourceShardingConfiguration 参考SpringBootConfiguration配置类，主要解析数据源配置信息，创建DynamicDataSourceProvider 和 ShardingDataSource 进行整合。

import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.google.common.base.Optional;
import com.google.common.base.Preconditions;
import lombok.RequiredArgsConstructor;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.core.config.inline.InlineExpressionParser;
import org.apache.shardingsphere.core.exception.ShardingException;
import org.apache.shardingsphere.core.yaml.swapper.impl.ShardingRuleConfigurationYamlSwapper;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.shardingjdbc.spring.boot.common.SpringBootPropertiesConfigurationProperties;
import org.apache.shardingsphere.shardingjdbc.spring.boot.encrypt.SpringBootEncryptRuleConfigurationProperties;
import org.apache.shardingsphere.shardingjdbc.spring.boot.masterslave.SpringBootMasterSlaveRuleConfigurationProperties;
import org.apache.shardingsphere.shardingjdbc.spring.boot.sharding.SpringBootShardingRuleConfigurationProperties;
import org.apache.shardingsphere.spring.boot.datasource.DataSourcePropertiesSetter;
import org.apache.shardingsphere.spring.boot.datasource.DataSourcePropertiesSetterHolder;
import org.apache.shardingsphere.spring.boot.util.DataSourceUtil;
import org.apache.shardingsphere.spring.boot.util.PropertyUtil;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.core.env.StandardEnvironment;
import org.springframework.jndi.JndiObjectFactoryBean;

import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

@Configuration
@ComponentScan("org.apache.shardingsphere.spring.boot.converter")
@EnableConfigurationProperties({
        SpringBootShardingRuleConfigurationProperties.class,
        SpringBootMasterSlaveRuleConfigurationProperties.class, SpringBootEncryptRuleConfigurationProperties.class,
        SpringBootPropertiesConfigurationProperties.class})
@RequiredArgsConstructor
public class ShardingConfiguration implements EnvironmentAware {

    private final SpringBootShardingRuleConfigurationProperties shardingRule;

    private final SpringBootPropertiesConfigurationProperties props;

    private final String jndiName = "jndi-name";

    private final Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();


    @Bean
    public ShardingDataSourceMap shardingDataSourceMap() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfigurationYamlSwapper().swap(shardingRule);
        Map<String, DataSource> shardingDataSourceMap = new LinkedHashMap<>();
        Collection<TableRuleConfiguration> tableRuleConfigs = shardingRuleConfiguration.getTableRuleConfigs();
        for (String dataSourceKey : dataSourceMap.keySet()) {
            Map<String, DataSource> map = new HashMap<>();
            map.put(dataSourceKey, dataSourceMap.get(dataSourceKey));
            List<TableRuleConfiguration> newShardingRuleConfigurationList = new LinkedList<>();
            for (TableRuleConfiguration tableRuleConfig : tableRuleConfigs) {
                TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration(tableRuleConfig.getLogicTable(), dataSourceKey + "." + tableRuleConfig.getActualDataNodes());
                tableRuleConfiguration.setDatabaseShardingStrategyConfig(tableRuleConfig.getDatabaseShardingStrategyConfig());
                tableRuleConfiguration.setTableShardingStrategyConfig(tableRuleConfig.getTableShardingStrategyConfig());
                tableRuleConfiguration.setKeyGeneratorConfig(tableRuleConfig.getKeyGeneratorConfig());
                newShardingRuleConfigurationList.add(tableRuleConfiguration);
            }
            shardingRuleConfiguration.setTableRuleConfigs(newShardingRuleConfigurationList);
            DataSource shardingDataSource = ShardingDataSourceFactory.createDataSource(map,
                    shardingRuleConfiguration,
                    props.getProps());
            shardingDataSourceMap.put(dataSourceKey, shardingDataSource);
        }
        return new ShardingDataSourceMap(shardingDataSourceMap);
    }

    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider(ShardingDataSourceMap shardingDataSourceMap) {
        return new DynamicDataSourceProvider() {

            @Override
            public Map<String, DataSource> loadDataSources() {

                return shardingDataSourceMap.getDataSourceMap();
            }
        };
    }

    @Override
    public final void setEnvironment(final Environment environment) {
        String prefix = "spring.shardingsphere.datasource.";
        for (String each : getDataSourceNames(environment, prefix)) {
            try {
                dataSourceMap.put(each, getDataSource(environment, prefix, each));
            } catch (final ReflectiveOperationException ex) {
                throw new ShardingException("Can't find datasource type!", ex);
            } catch (final NamingException namingEx) {
                throw new ShardingException("Can't find JNDI datasource!", namingEx);
            }
        }
    }

    private List<String> getDataSourceNames(final Environment environment, final String prefix) {
        StandardEnvironment standardEnv = (StandardEnvironment) environment;
        standardEnv.setIgnoreUnresolvableNestedPlaceholders(true);
        return null == standardEnv.getProperty(prefix + "name")
                ? new InlineExpressionParser(standardEnv.getProperty(prefix + "names")).splitAndEvaluate() : Collections.singletonList(standardEnv.getProperty(prefix + "name"));
    }

    @SuppressWarnings("unchecked")
    private DataSource getDataSource(final Environment environment, final String prefix, final String dataSourceName) throws ReflectiveOperationException, NamingException {
        Map<String, Object> dataSourceProps = PropertyUtil.handle(environment, prefix + dataSourceName.trim(), Map.class);
        Preconditions.checkState(!dataSourceProps.isEmpty(), "Wrong datasource properties!");
        if (dataSourceProps.containsKey(jndiName)) {
            return getJndiDataSource(dataSourceProps.get(jndiName).toString());
        }
        DataSource result = DataSourceUtil.getDataSource(dataSourceProps.get("type").toString(), dataSourceProps);
        Optional<DataSourcePropertiesSetter> dataSourcePropertiesSetter = DataSourcePropertiesSetterHolder.getDataSourcePropertiesSetterByType(dataSourceProps.get("type").toString());
        if (dataSourcePropertiesSetter.isPresent()) {
            dataSourcePropertiesSetter.get().propertiesSet(environment, prefix, dataSourceName, result);
        }
        return result;


    }

    private DataSource getJndiDataSource(final String jndiName) throws NamingException {
        JndiObjectFactoryBean bean = new JndiObjectFactoryBean();
        bean.setResourceRef(true);
        bean.setJndiName(jndiName);
        bean.setProxyInterface(DataSource.class);
        bean.afterPropertiesSet();
        return (DataSource) bean.getObject();
    }
}

@Data
@AllArgsConstructor
public class ShardingDataSourceMap {
    private Map<String, DataSource> dataSourceMap;
}

properties 规则配置

spring:
  datasource:
    dynamic:
      primary: tenant-1
  shardingsphere:
    datasource:
      names: tenant-1,tenant-2
      tenant-1:
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/db1?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
        password: 112233
        type: com.zaxxer.hikari.HikariDataSource
        username: root
      tenant-2:
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/db2?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
        password: 112233
        type: com.zaxxer.hikari.HikariDataSource
        username: root
    sharding:
      tables:
        t_order:
          actualDataNodes: t_order_${1..255}
          tableStrategy:
            inline:
              algorithmExpression: t_order_${tenant_id}
              shardingColumn: tenant_id
        t_order_detail:
          actualDataNodes: t_order_detail_${1..255}
          tableStrategy:
            inline:
              algorithmExpression: t_order_detail_${tenant_id}
              shardingColumn: tenant_id

业务切换租户

调用DynamicDataSourceContextHolder对应的方法即可切换

DynamicDataSourceContextHolder.push(CommonConstant.DATASOURCE_PREFIX  + tenantId);