寫(xiě)在前面

上一篇文章（http://www.reibang.com/p/ed7bc1e49b61）出現(xiàn)的問(wèn)題，就是sharing-jdbc無(wú)法根據(jù)一個(gè)不固定的字段（停車(chē)場(chǎng)id）進(jìn)行動(dòng)態(tài)分表，因?yàn)閍ctual-data-nodes是在項(xiàng)目啟動(dòng)的時(shí)候就加載好的离熏，不支持動(dòng)態(tài)修改。
還好這一切都是可以解決的戴涝。
那么這一篇文章滋戳，就是解決了actual-data-nodes動(dòng)態(tài)修改問(wèn)題。解決方案大致說(shuō)明一下就是基于sharding-jdbc + sharding的服務(wù)編排治理+redis啥刻，實(shí)現(xiàn)了訂單表根據(jù)停車(chē)場(chǎng)id動(dòng)態(tài)分表奸鸯，每增刪停車(chē)場(chǎng)，在不重啟項(xiàng)目的情況下動(dòng)態(tài)的改變actual-data-nodes

思路

首先參考了這篇老哥的博文：https://blog.csdn.net/qq_32588349/article/details/99440985 給了我很大的啟發(fā)
根據(jù)官方文檔描述可帽，shardingsphere提供了配置中心娄涩、注冊(cè)中心的服務(wù)治理功能。并且有這句描述：

image.png

大概的描述一下本demo的業(yè)務(wù)：

1. sharing-jdbc ： 引入基于java的配置包（不用starter包）
2. 需要分的表為：訂單表（t_order）
3. 分表的依據(jù)字段：停車(chē)場(chǎng)id(car_park_id)
4. 分庫(kù)字段：不需要分庫(kù)
5. 初始化數(shù)據(jù)庫(kù)要有一個(gè)默認(rèn)表：t_order_defalut,這個(gè)表只是為了第一次啟動(dòng)項(xiàng)目，還沒(méi)有停車(chē)場(chǎng)信息的時(shí)候荸镊，用來(lái)默認(rèn)的咽斧，里面不會(huì)存任何數(shù)據(jù)
6. redis hash存放<停車(chē)場(chǎng)id，停車(chē)場(chǎng)名稱(chēng)>
7. redis zset存放 訂單actual-data-nodes贷洲，score為當(dāng)前時(shí)間的時(shí)間戳晋柱，方便獲取最新的用來(lái)替換

配置步驟

sql腳本

/*
Navicat MySQL Data Transfer

Source Server         : 開(kāi)發(fā)數(shù)據(jù)庫(kù) 4.71
Source Server Version : 50730
Source Host           : 192.168.4.71:3307
Source Database       : sharding_carpark

Target Server Type    : MYSQL
Target Server Version : 50730
File Encoding         : 65001

Date: 2020-05-11 18:25:00
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_car_park
-- ----------------------------
DROP TABLE IF EXISTS `t_car_park`;
CREATE TABLE `t_car_park` (
  `id` varchar(64) NOT NULL,
  `name` varchar(100) DEFAULT NULL COMMENT '名稱(chēng)',
  `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='停車(chē)場(chǎng)表';

-- ----------------------------
-- Table structure for t_order_default
-- ----------------------------
DROP TABLE IF EXISTS `t_order_default`;
CREATE TABLE `t_order_default` (
  `id` varchar(64) NOT NULL,
  `name` varchar(100) DEFAULT NULL COMMENT '名稱(chēng)',
  `car_park_id` varchar(64) DEFAULT NULL COMMENT '停車(chē)場(chǎng)id',
  `no` varchar(100) DEFAULT NULL COMMENT '訂單號(hào)',
  `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='測(cè)試分表';

pom文件

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</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>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>

        <!--mybatisplus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1.tmp</version>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>

        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.60</version>
        </dependency>

        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.44</version>
        </dependency>

        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.0.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-orchestration</artifactId>
            <version>4.0.0</version>
        </dependency>

重點(diǎn)關(guān)注：sharding-jdbc-core雁竞、sharding-jdbc-orchestration這兩個(gè)包，這兩個(gè)包是必備的碑诉。

application.yml

server:
  port: 8086
  tomcat:
    max-threads: 100
spring:
  druid:
    datasource:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://192.168.4.71:3307/sharding_carPark?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: 123456
      maxActive: 20
      initialSize: 5
      maxWait: 60000
      minIdle: 5
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      #是否緩存preparedStatement，也就是PSCache进栽。在mysql下建議關(guān)閉德挣。 PSCache對(duì)支持游標(biāo)的數(shù)據(jù)庫(kù)性能提升巨大，比如說(shuō)oracle快毛。
      poolPreparedStatements: false
      #要啟用PSCache格嗅，-1為關(guān)閉 必須配置大于0番挺，當(dāng)大于0時(shí)，poolPreparedStatements自動(dòng)觸發(fā)修改為true  可以把這個(gè)數(shù)值配置大一些屯掖，比如說(shuō)100
      maxOpenPreparedStatements: -1
      #配置監(jiān)控統(tǒng)計(jì)攔截的filters玄柏，去掉后監(jiān)控界面sql無(wú)法統(tǒng)計(jì)，'wall'用于防火墻
      filters: stat,wall,log4j2
      #通過(guò)connectProperties屬性來(lái)打開(kāi)mergeSql功能贴铜；慢SQL記錄
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      #合并多個(gè)DruidDataSource的監(jiān)控?cái)?shù)據(jù)
      useGlobalDataSourceStat: true
      loginUsername: druid
      loginPassword: druid
  redis:
    database: 1
    host: 192.168.4.71
    port: 6379
    password: 123456
    jedis:
      pool:
        max-active: 8
        max-wait: -1
        max-idle: 8
        min-idle: 0
logging:
  level:
    com.example.demo: debug

這里的配置文件比較常規(guī)

LocalRegistryCenter 本地注冊(cè)中心

package com.example.demo.config.shardingconfig;

import org.apache.shardingsphere.orchestration.reg.api.RegistryCenter;
import org.apache.shardingsphere.orchestration.reg.api.RegistryCenterConfiguration;
import org.apache.shardingsphere.orchestration.reg.listener.DataChangedEventListener;

import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

public class LocalRegistryCenter implements RegistryCenter {
    public static Map<String, DataChangedEventListener> listeners = new ConcurrentHashMap<>();
    
    private RegistryCenterConfiguration config;
    
    private Properties properties;
    /**
     * public 是為了在重置節(jié)點(diǎn)的時(shí)候減少去重新讀配置
     */
    public static Map<String, String> values = new ConcurrentHashMap<>();
    
    @Override
    public void init(RegistryCenterConfiguration config) {
        this.config = config;
    }
    
    @Override
    public String get(String key) {
        return values.get(key);
    }
    
    @Override
    public String getDirectly(String key) {
        return values.get(key);
    }
    
    @Override
    public boolean isExisted(String key) {
        return values.containsKey(key);
    }
    
    @Override
    public List<String> getChildrenKeys(String key) {
        return null;
    }
    
    @Override
    public void persist(String key, String value) {
        values.put(key, value);
    }
    
    @Override
    public void update(String key, String value) {
        values.put(key, value);
    }
    
    @Override
    public void persistEphemeral(String key, String value) {
        values.put(key, value);
    }
    
    @Override
    public void watch(String key, DataChangedEventListener dataChangedEventListener) {
        if (null != dataChangedEventListener) {
            // 將數(shù)據(jù)改變的事件監(jiān)聽(tīng)器緩存下來(lái)
            listeners.put(key, dataChangedEventListener);
        }
    }
    
    @Override
    public void close() {
        config = null;
    }
    
    @Override
    public void initLock(String key) {
        
    }
    
    @Override
    public boolean tryLock() {
        return false;
    }
    
    @Override
    public void tryRelease() {
        
    }
    
    @Override
    public String getType() {
        // 【關(guān)鍵點(diǎn)1】粪摘，留著文章后續(xù)引用
        return "shardingLocalRegisterCenter";
    }
    
    @Override
    public Properties getProperties() {
        return properties;
    }
    
    @Override
    public void setProperties(Properties properties) {
        this.properties = properties;
    }
}

下面這步很重要
在本地文件中添加注冊(cè)中心

1. 在resources文件夾下面新建Directory，名稱(chēng)為：META-INF
2. 在META-INF繼續(xù)創(chuàng)建名為service的Directory

3. 添加file绍坝，名為org.apache.shardingsphere.orchestration.reg.api.RegistryCenter（注意這個(gè)是固定的）徘意，里面的內(nèi)容是：com.example.demo.config.shardingconfig.LocalRegistryCenter（你本地注冊(cè)中心類(lèi)存放的全路徑）
   像這樣

   
   
   image.png

基于java的配置類(lèi)

package com.example.demo.config.shardingconfig;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.logging.Slf4jLogFilter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.util.StringUtils;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import com.example.demo.config.datasource.DataSourceProperties;
import com.example.demo.config.redis.RedisConfig;
import com.example.demo.config.redis.RedisPrefixEnum;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.orchestration.config.OrchestrationConfiguration;
import org.apache.shardingsphere.orchestration.reg.api.RegistryCenterConfiguration;
import org.apache.shardingsphere.shardingjdbc.orchestration.api.OrchestrationShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.redis.core.RedisTemplate;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

/**
 * @title: ShardingRuleConfig
 * @projectName shardingJavaDemo
 * @description: TODO
 * @author zhy
 * @date 2020/5/910:23
 */
@Configuration
@AutoConfigureAfter({DataSourceProperties.class, RedisConfig.class})
public class ShardingRuleConfig {

    private String defaultDataSource = DatasourceEnum.DEFAULT.getValue();

    @Autowired
    private RedisTemplate<String,Object> redisTemplate;

    @Autowired
    private DataSourceProperties properties;

    /**
      * shardingjdbc數(shù)據(jù)源
      * @param
      * @throws
      * @return javax.sql.DataSource
      * @author zhy
      * @date 2020/5/9 10:33
      */
    @Bean
    public DataSource dataSource() throws SQLException {
        // 配置真實(shí)數(shù)據(jù)源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        //多數(shù)據(jù)源配置
        //數(shù)據(jù)源1
        DruidDataSource dataSource0 = druidDataSource();
        dataSourceMap.put(defaultDataSource, dataSource0);
        //數(shù)據(jù)源2
//        DruidDataSource dataSource1 = createDb1();
//        dataSourceMap.put("ds1", dataSource1);

        // 配置分片規(guī)則
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        //訂單表分片規(guī)則
        TableRuleConfiguration orderRuleConfig = orderRuleConfig();
        shardingRuleConfig.getTableRuleConfigs().add(orderRuleConfig);
        //可以繼續(xù)用add添加分片規(guī)則
        //shardingRuleConfig.getTableRuleConfigs().add(orderRuleConfig);
        //多數(shù)據(jù)源一定要指定默認(rèn)數(shù)據(jù)源，只有一個(gè)數(shù)據(jù)源就不需要
        //shardingRuleConfig.setDefaultDataSourceName("ds0");
        Properties p = new Properties();
        //打印sql語(yǔ)句轩褐，生產(chǎn)環(huán)境關(guān)閉
        p.setProperty("sql.show",Boolean.TRUE.toString());
        OrchestrationConfiguration orchestrationConfig = new OrchestrationConfiguration(
                "orchestration-sharding-data-source", new RegistryCenterConfiguration("shardingLocalRegisterCenter"),
                false);
        return OrchestrationShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, p,
                orchestrationConfig);
    }

    /**
     * 訂單分片規(guī)則
     * @param
     * @throws
     * @return io.shardingjdbc.core.api.config.TableRuleConfiguration
     * @author zhy
     * @date 2020/5/7 10:28
     */
    private TableRuleConfiguration orderRuleConfig(){
        String logicTable = ShardingTableEnum.ORDER.getValue();
        String orderNodesByRedisCarPark = getActualDataNodesByCatalog(ShardingTableEnum.ORDER);
        //t_order_default 這張表是默認(rèn)表映砖，需要事先建好，防止首次啟動(dòng)報(bào)錯(cuò)
        String actualDataNodes = StringUtils.isEmpty(orderNodesByRedisCarPark) ? "ds0.t_order_default" : orderNodesByRedisCarPark;
        TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration(logicTable,actualDataNodes);
        //設(shè)置分表策略
        tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("car_park_id",new CarParkShardingTableAlgorithm()));
        //根據(jù)時(shí)間將策略放進(jìn)redis中,方便讀取替換
        redisTemplate.opsForZSet().add(RedisPrefixEnum.SHARDING_RULE_ORDER.getValue(),actualDataNodes,new Date().getTime());
        return tableRuleConfig;
    }


    /**
      * 根據(jù)分表類(lèi)型獲取初始化actualDataNodes
      * @param logicTable
      * @throws
      * @return java.lang.String
      * @author zhy
      * @date 2020/5/11 14:52
      */
    public String getActualDataNodesByCatalog(ShardingTableEnum logicTable){
        String redisKey = RedisPrefixEnum.CAR_PARK_ID_CATALOG.getValue();
        //獲取所有的停車(chē)場(chǎng)
        Set<Object> keys = redisTemplate.opsForHash().keys(redisKey);
        if (keys.isEmpty()){
            return null;
        }
        StringBuilder sb = new StringBuilder();
        keys.forEach(obj -> {
            sb.append(defaultDataSource).append(".").append(logicTable.getValue()).append("_").append(obj.toString()).append(",");
        });
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }

    /**
     * 獲取druid數(shù)據(jù)庫(kù)鏈接
     * @param
     * @throws
     * @return com.alibaba.druid.pool.DruidDataSource
     * @author zhy
     * @date 2020/5/7 10:29
     */
    private DruidDataSource druidDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(properties.getDriverClassName());
        dataSource.setUrl(properties.getUrl());
        dataSource.setUsername(properties.getUsername());
        dataSource.setPassword(properties.getPassword());
        dataSource.setInitialSize(properties.getInitialSize());
        dataSource.setMinIdle(properties.getMinIdle());
        dataSource.setMaxActive(properties.getMaxActive());
        dataSource.setMaxWait(properties.getMaxWait());
        dataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis());
        dataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis());
        String validationQuery = properties.getValidationQuery();
        if (validationQuery != null && !"".equals(validationQuery)) {
            dataSource.setValidationQuery(validationQuery);
        }
        dataSource.setTestWhileIdle(properties.isTestWhileIdle());
        dataSource.setTestOnBorrow(properties.isTestOnBorrow());
        dataSource.setTestOnReturn(properties.isTestOnReturn());
        if (properties.isPoolPreparedStatements()) {
            dataSource.setMaxPoolPreparedStatementPerConnectionSize(properties.getMaxPoolPreparedStatementPerConnectionSize());
        }
        String connectionPropertiesStr = properties.getConnectionProperties();
        if (connectionPropertiesStr != null && !"".equals(connectionPropertiesStr)) {
            Properties connectProperties = new Properties();
            String[] propertiesList = connectionPropertiesStr.split(";");
            for (String propertiesTmp : propertiesList) {
                String[] obj = propertiesTmp.split("=");
                String key = obj[0];
                String value = obj[1];
                connectProperties.put(key, value);
            }
            dataSource.setConnectProperties(connectProperties);
        }
        dataSource.setUseGlobalDataSourceStat(properties.isUseGlobalDataSourceStat());
        WallConfig wallConfig = new WallConfig();
        wallConfig.setMultiStatementAllow(true);
        WallFilter wallFilter = new WallFilter();
        wallFilter.setConfig(wallConfig);
        //打開(kāi)日志記錄過(guò)濾器灾挨，可通過(guò)log4j2,記錄sql   application.yml中配置【logging:config: classpath:logConfig/log4j2.xml】
        Slf4jLogFilter slf4jLogFilter = new Slf4jLogFilter();
        slf4jLogFilter.setStatementCreateAfterLogEnabled(false);
        slf4jLogFilter.setStatementCloseAfterLogEnabled(false);
        slf4jLogFilter.setResultSetOpenAfterLogEnabled(false);
        slf4jLogFilter.setResultSetCloseAfterLogEnabled(false);
        List<Filter> filters = new ArrayList<>();
        filters.add(wallFilter);
        filters.add(new StatFilter());
        filters.add(slf4jLogFilter);

        dataSource.setProxyFilters(filters);
        return dataSource;
    }
}

動(dòng)態(tài)替換的shardingService

package com.example.demo.config.shardingconfig;

import com.example.demo.config.datasource.DataSourceProperties;
import com.example.demo.config.redis.RedisConfig;
import com.example.demo.config.redis.RedisPrefixEnum;
import org.apache.shardingsphere.orchestration.reg.listener.DataChangedEvent;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.Set;

/**
 * @title: ShardingService
 * @projectName shardingJavaDemo
 * @description: TODO
 * @author zhy
 * @date 2020/5/1115:08
 */
@Component
@AutoConfigureAfter({RedisConfig.class})
public class ShardingService {

    @Autowired
    private RedisTemplate<String,Object> redisTemplate;

    @Autowired
    private ShardingRuleConfig shardingRuleConfig;

    /**
      * 替換sharding里的分表規(guī)則ActualDataNodes的值
      * @param oldRule
      * @param newRule
      * @throws
      * @return void
      * @author zhy
      * @date 2020/5/11 15:12
      */
    public void replaceActualDataNodes(String oldRule,String newRule){
        // 獲取已有的配置
        String rules = LocalRegistryCenter.values
                .get("/orchestration-sharding-data-source/config/schema/logic_db/rule");
        // 修改規(guī)則
        String rule = rules.replace(oldRule, newRule);
        LocalRegistryCenter.listeners.get("/orchestration-sharding-data-source/config/schema")
                .onChange(new DataChangedEvent(
                        "/orchestration-sharding-data-source/config/schema/logic_db/rule",
                        rule, DataChangedEvent.ChangedType.UPDATED));
        LocalRegistryCenter.values.put("/orchestration-sharding-data-source/config/schema/logic_db/rule",rule);
    }

    /**
      * 獲取當(dāng)前的分表規(guī)則
      * @param shardingTableEnum
      * @throws
      * @return java.lang.String
      * @author zhy
      * @date 2020/5/11 15:56
      */
    public String getActualDataNodesInRedis(ShardingTableEnum shardingTableEnum){
        String redisKey = RedisPrefixEnum.SHARDING_RULE_ORDER.getValue();
        //倒序獲取一條最新的紀(jì)錄
        Set<Object> objects = redisTemplate.opsForZSet().reverseRange(redisKey, 0, 1);
        return new ArrayList<>(objects).get(0).toString();
    }

    /**
      * 根據(jù)redis中存儲(chǔ)的停車(chē)場(chǎng)id獲取分表規(guī)則
      * @param shardingTableEnum
      * @throws
      * @return java.lang.String
      * @author zhy
      * @date 2020/5/11 16:09
      */
    public String getActualDataNodesByCatalog(ShardingTableEnum shardingTableEnum){
        return shardingRuleConfig.getActualDataNodesByCatalog(shardingTableEnum);
    }
}

最后貼上源碼

https://github.com/zhyhuayong/shardingJavaDemo
大家可以參考源碼邑退，要測(cè)試的同學(xué)記得修改自己的數(shù)據(jù)庫(kù)配置哦

本人為（weixin）恭（gong）祝（zhong）號(hào)：一吱小確幸。歡迎大家關(guān)注