SpringBoot整合ShardingJDBC按时间分库分表及查询

参考文档：

SpringBoot整合ShardingJDBC按时间分表及自动建表_sharding jdbc 自动创建表-优快云博客

本文基于上述文档，做了分库处理。

1、项目背景

公司项目由于数据增长过多，考虑到要进行相关数据分库分表存储。

仅对于当年数据，存储在默认数据库中（ds0），对于往年数据，存储在历史数据库中（ds1）.

2、yml文档的修改

spring:
  shardingsphere:
    datasource:
      # 定义数据源
      names: ds0, ds1
      # 默认数据源
      ds0:
        type: ${spring.datasource.type}
        driver-class-name: ${spring.datasource.driver-class-name}
        url: ${spring.datasource.druid.master.url}
        username: ${spring.datasource.druid.master.username}
        password: ${spring.datasource.druid.master.password}
      ds1:
        type: ${spring.datasource.type}
        driver-class-name: ${spring.datasource.driver-class-name}
        url: ${spring.datasource.druid.history.url}
        username: ${spring.datasource.druid.history.username}
        password: ${spring.datasource.druid.history.password}
    sharding:
      # 默认数据源，未分片的表默认执行库
      default-data-source-name: ds0
      # 分库分表
      tables:
        # 其中一个要分表的表名，逻辑表名
        record_day:
          actual-data-nodes: ds${0..1}.record_day
          # 分表
          table-strategy:
            standard:
              # 以表中的哪个字段
              sharding-column: saveDate
              precise-algorithm-class-name: com.dojustek.framework.sharding.PreciseAlgorithmCustomer
              range-algorithm-class-name: com.dojustek.framework.sharding.RangeAlgorithmCustomer
          database-strategy:
            standard:
              sharding-column: saveDate
              precise-algorithm-class-name: com.dojustek.framework.sharding.PreciseAlgorithmDatabase

3、分片算法实现

3.1精准算法实现

import com.dojustek.common.utils.DateUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.springframework.stereotype.Component;

import java.util.Calendar;
import java.util.Collection;
import java.util.Date;

@Component
public class PreciseAlgorithmDatabase implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        Date shardingValue = preciseShardingValue.getValue() == null ? new Date() : preciseShardingValue.getValue();
        String shardingYear = DateUtils.parseDateToStr("yyyy", shardingValue);
        String currentYear = Calendar.getInstance().get(Calendar.YEAR) + "";

        return shardingYear.equals(currentYear) ? "ds0" : "ds1";
    }
}

范围算法，可参考精准算法进行修改。

4、节点重载及自动建表修改

4.1  ShardingAlgorithmReload.tableNameCacheReloadAll 修改

修改为，遍历每个节点，进行该节点的关联数据表的加载

 public void tableNameCacheReloadAll() {
        logger.info("[{}]加载分表信息 开始", DateUtils.getTime());
        ShardingRuntimeContext runtimeContext = getRuntimeContext();
        List<TableRule> tableRuleList = (List<TableRule>) runtimeContext.getRule().getTableRules();
        for (TableRule tableRule : tableRuleList) {
            logger.info("[{}] 逻辑表[{}]", DateUtils.getTime(), tableRule.getLogicTable());
            Collection<String> actualDatasourceNames = tableRule.getActualDatasourceNames();
            if (!CollectionUtils.isEmpty(actualDatasourceNames)) {
                //遍历所有节点，获取其对应的表号列表
                for (String nodeName : actualDatasourceNames) {
                    logger.info("[{}] 当前节点[{}]", DateUtils.getTime(), nodeName);
                    Set<String> tablesInDBSet = queryTables(nodeName, tableRule.getLogicTable());
                    logger.info("[{}] 实际表[{}]", DateUtils.getTime(), tablesInDBSet);
                    refreshTableRule(tableRule, nodeName, tablesInDBSet);
                }
            }
        }
        logger.info("[{}]加载分表信息 结束", DateUtils.getTime());
    }

4.2 ShardingAlgorithmReload.queryTables修改

修改为，根据不同的节点，获取其对应节点的connection，以及其对应的数据库名称，查询相关比表号信息

    protected Set<String> queryTables(String nodeName, String tableName) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        Set<String> tables = null;
        try {
            //根据节点，获取对应的connection
            conn = shardingDataSource.getDataSourceMap().get(nodeName).getConnection();
           //获取该节点使用的数据库名称
            String catalog = conn.getCatalog();
            statement = conn.createStatement();
            rs = statement.executeQuery("select table_name from information_schema.tables where table_schema ='" + catalog + "' and table_name LIKE '" + (prefix + tableName) + "_%'");
            tables = new LinkedHashSet<>();
            while (rs.next()) {
                tables.add(rs.getString(1));
            }
        } catch (SQLException e) {
            logger.error("获取数据库连接失败！", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                logger.error("关闭数据连接失败", e);
            }
        }
        return tables;
    }

基于上述改动，基本能实现分表以及分库动态创建，查询时的动态切换