ShardingSphere stand分片策略实现

这段时间在做业务开发时候，需要对数据进行按天分表存储，这里采取了shardingSphere中间件来管理sql路由。

业务需求：

• 支持按照esb消息中的时间戳，进行分表插入。
• 支持按照时间戳和其他参数进行rang查询

鉴于上面的业务需求，这边采用shardingSphere的StandardShardingStrategyConfiguration作为分片实现算法

具体的实现代码(shardingsphere 3.x版本)如下

    @Bean("Datasource")
    public DataSource getDataSource1() throws SQLException {
        //todo 修改db配置
        // 配置真实数据库
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        DataSource dataSource1 = DruidConfigBuilder.buildDatasource("combo_plus", 1, 1, 16);
        dataSourceMap.put("ds0", dataSource1);
        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getBindTableRule());
        // 创建dataSource
        return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap<>(), new Properties());
    }

    /**
     * 设置分片键
     *
     * @return
     */
    private TableRuleConfiguration getBindTableRule() {
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
        orderTableRuleConfig.setLogicTable("l_house_record");
        orderTableRuleConfig.setTableShardingStrategyConfig(
                new StandardShardingStrategyConfiguration("msg_update_time",
                        // 精确查询
                        (tableNames, preciseShardingValue) -> {
                            String columnName = preciseShardingValue.getColumnName();
                            String logicTableName = preciseShardingValue.getLogicTableName();
                            Comparable updateTime = preciseShardingValue.getValue();
                            if ("msg_update_time".equals(columnName)) {
                                return logicTableName + "_" + getTableSuffix((Long) updateTime);
                            }
                            return null;
                        },
                        // 范围查询
                        (tableNames, rangeShardingValue) -> {
                            String columnName = rangeShardingValue.getColumnName();
                            String logicTableName = rangeShardingValue.getLogicTableName();
                            List<String> tableNameResult = new ArrayList<>();
                            if ("msg_update_time".equals(columnName)) {
                                Range valueRange = rangeShardingValue.getValueRange();

                                if (valueRange.hasLowerBound() && valueRange.hasUpperBound()) {
                                    Long lowTime = (Long) valueRange.lowerEndpoint();
                                    Long upperTime = (Long) valueRange.upperEndpoint();
                                    List<String> tableSuffixs = getTableSuffixs(lowTime, upperTime);
                                    for (String tableSuffix : tableSuffixs) {
                                        tableNameResult.add(logicTableName + "_" + tableSuffix);
                                    }
                                }
                            }
                            return tableNameResult;
                        }
                )
        );
        return orderTableRuleConfig;
    }

    private String getTableSuffix(Long updateTime) {
        Instant instant = Instant.ofEpochMilli(updateTime);
        LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
        return localDateTime.toLocalDate().format(DateTimeFormatter.BASIC_ISO_DATE);
    }

    private List<String> getTableSuffixs(Long startTime, Long endTime) {
        Instant startInstant = Instant.ofEpochMilli(startTime);
        LocalDateTime startLocalDateTime = LocalDateTime.ofInstant(startInstant, ZoneId.systemDefault());
        LocalDate startLocalDate = startLocalDateTime.toLocalDate();

        Instant endInstant = Instant.ofEpochMilli(endTime);
        LocalDateTime endLocalDateTime = LocalDateTime.ofInstant(endInstant, ZoneId.systemDefault());
        LocalDate endLocalDate = endLocalDateTime.toLocalDate();
        List<String> result = new ArrayList<>();
        while (endLocalDate.isAfter(startLocalDate)) {
            result.add(endLocalDate.format(DateTimeFormatter.BASIC_ISO_DATE));
            endLocalDate = endLocalDate.minusDays(1);
        }
        result.add(endLocalDate.format(DateTimeFormatter.BASIC_ISO_DATE));
        return result;
    }