ShardingProxy(ShardingJDBC)自定义分表规则

╰半橙微兮° 2022-09-01 09:09 356阅读 0赞

### 文章目录 ###

*  一、场景
 *  二、代码编写
 *   *  1.创建maven工程，引入pom依赖。
     *  2.范围分片算法.
     *  3、精确分片算法。
     *  4、工具类
 *  三、ShardingProxy配置。
 *  四、额外的话

# 一、场景 #

在使用ShardingJDBC或ShardingProxy时,有些表按照取模/hash无法满足分库分表规则,这个时候可以自己实现分库分表的策略.

下面按照时间分表,orgId分库.

# 二、代码编写 #

## 1.创建maven工程，引入pom依赖。 ##

<dependencies>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-core-api</artifactId>
            <version>4.1.1</version> //自己引入的对应版本
        </dependency>
    </dependencies>

## 2.范围分片算法. ##

*  实现RangeShardingAlgorithm接口
 *  重写doSharding方法

/**
     * @BelongsProject: DemoCode
     * @BelongsPackage: 
     * @Author: hef
     * @CreateTime: 2021-07-09 17:22
     * @Description: 
     */
    public class CustomTableRangeShardingAlgorithm implements RangeShardingAlgorithm<String> {
        
        public CustomTableRangeShardingAlgorithm() {
        
        }
    
    
        @Override
        public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        
            Range<String> ranges = rangeShardingValue.getValueRange();
            LocalDateTime start = null;
            Integer startYM = null;
            if (ranges.hasLowerBound()) {
        
                String lower = ranges.lowerEndpoint();
                start = LocalDatetimeUtil.parse(lower);
                startYM = Integer.valueOf(start.format(DateTimeFormatter.ofPattern("yyyyMM")));
            }
            LocalDateTime end = null;
            Integer endYM = null;
            if (ranges.hasUpperBound()) {
        
                String upper = ranges.upperEndpoint();
                end = LocalDatetimeUtil.parse(upper);
                endYM = Integer.valueOf(end.format(DateTimeFormatter.ofPattern("yyyyMM")));
            }
            Collection<String> tables = new LinkedHashSet<>();
            if (judgmentTime(start, end)) {
        
                for (String c : collection) {
        
                    String substring = c.substring(c.length() - ShardingConstant.DATE_SHARDING_TABLE_FORMAT.length());
                    Integer cMonth = Integer.valueOf(LocalDatetimeUtil.parse(substring).format(DateTimeFormatter.ofPattern("yyyyMM")));
                    if (start != null && end != null) {
        
                        if (cMonth >= startYM && cMonth <= endYM) {
        
                            tables.add(c);
                        }
                    } else if ((start != null && cMonth >= startYM) || (end != null && cMonth <= endYM)) {
        
                        tables.add(c);
                    }
                }
            }
            return tables;
        }
    
        public boolean judgmentTime(LocalDateTime start, LocalDateTime end) {
        
            if (start == null && end == null) {
        
                throw new IllegalArgumentException("no valid time");
            }
            if (start != null && end != null) {
        
                return start.getNano() <= end.getNano();
            }
            return true;
        }
    }

## 3、精确分片算法。 ##

*  实现PreciseShardingAlgorithm接口
 *  重写doSharding方法

/**
     * @BelongsProject: DemoCode
     * @BelongsPackage: 
     * @Author: hef
     * @CreateTime: 2021-07-09 17:27
     * @Description: 
     */
    public class CustomTableShardingAlgorithm implements PreciseShardingAlgorithm<String> {
        
    
        public CustomTableShardingAlgorithm() {
        
        }
    
        @Override
        public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        
            String value = preciseShardingValue.getValue();
            LocalDateTime createTime = LocalDatetimeUtil.parse(value);
            String timeValue = createTime.format(DateTimeFormatter.ofPattern(ShardingConstant.DATE_SHARDING_TABLE_FORMAT));
    
            String columnName = preciseShardingValue.getColumnName();
            // 需要分库的逻辑表
            String table = preciseShardingValue.getLogicTableName();
            if (timeValue == null || timeValue.length() == 0) {
        
                throw new UnsupportedOperationException(columnName + ":列，分表精确分片值为NULL;");
            }
            for (String each : collection) {
        
                if (each.startsWith(table)) {
        
                    return table + "_" + timeValue;
                }
            }
            return table;
        }
    }

## 4、工具类 ##

/**
     * @BelongsProject: DemoCode
     * @BelongsPackage: 
     * @Author: hef
     * @CreateTime: 2021-07-12 10:50
     * @Description: 
     */
    public class LocalDatetimeUtil {
        
    
        private static List<DateTimeFormatter> formatList = null;
    
        static {
        
            formatList = new ArrayList<>();
            formatList.add(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            formatList.add(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.S"));
            formatList.add(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SS"));
            formatList.add(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS"));
            formatList.add(DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss"));
            formatList.add(DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.S"));
            formatList.add(DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SS"));
            formatList.add(DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSS"));
            formatList.add(
                    new DateTimeFormatterBuilder()
                            .appendPattern("yyyy-MM-dd")
                            .parseDefaulting(ChronoField.MONTH_OF_YEAR, 1)
                            .parseDefaulting(ChronoField.DAY_OF_MONTH, 1)
                            .parseDefaulting(ChronoField.HOUR_OF_DAY, 0)
                            .toFormatter());
            formatList.add(
                    new DateTimeFormatterBuilder()
                            .appendPattern("yyyy-MM")
                            .parseDefaulting(ChronoField.MONTH_OF_YEAR, 1)
                            .parseDefaulting(ChronoField.DAY_OF_MONTH, 1)
                            .parseDefaulting(ChronoField.HOUR_OF_DAY, 0)
                            .toFormatter());
            formatList.add(
                    new DateTimeFormatterBuilder()
                            .appendPattern("MM-dd")
                            .parseDefaulting(ChronoField.MONTH_OF_YEAR, 1)
                            .parseDefaulting(ChronoField.DAY_OF_MONTH, 1)
                            .parseDefaulting(ChronoField.HOUR_OF_DAY, 0)
                            .toFormatter());
            formatList.add(DateTimeFormatter.ofPattern("yyyy/MM/dd"));
            formatList.add(
                    new DateTimeFormatterBuilder()
                            .appendPattern("yyyy/MM/dd")
                            .parseDefaulting(ChronoField.MONTH_OF_YEAR, 1)
                            .parseDefaulting(ChronoField.DAY_OF_MONTH, 1)
                            .parseDefaulting(ChronoField.HOUR_OF_DAY, 0)
                            .toFormatter());
            formatList.add(DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss"));
            formatList.add(
                    new DateTimeFormatterBuilder()
                            .appendPattern("yyyyMMdd")
                            .parseDefaulting(ChronoField.MONTH_OF_YEAR, 1)
                            .parseDefaulting(ChronoField.DAY_OF_MONTH, 1)
                            .parseDefaulting(ChronoField.HOUR_OF_DAY, 0)
                            .toFormatter());
            formatList.add(DateTimeFormatter.ofPattern("yyyyMMdd HH:mm:ss"));
            formatList.add(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
            formatList.add(DateTimeFormatter.ofPattern("yyyyMMddHHmmssSSS"));
            formatList.add(
                    new DateTimeFormatterBuilder()
                            .appendPattern(ShardingConstant.DATE_SHARDING_TABLE_FORMAT)
                            .parseDefaulting(ChronoField.MONTH_OF_YEAR, 1)
                            .parseDefaulting(ChronoField.DAY_OF_MONTH, 1)
                            .parseDefaulting(ChronoField.HOUR_OF_DAY, 0)
                            .toFormatter());
        }
    
        /**
         * 格式化时间
         *
         * @param dateStr
         * @return
         */
        public static LocalDateTime parse(String dateStr) {
        
            LocalDateTime time = null;
            for (DateTimeFormatter formatter : formatList) {
        
                try {
        
                    time = LocalDateTime.parse(dateStr, formatter);
                    break;
                } catch (Exception e) {
        
                    continue;
                }
            }
            if (time == null) {
        
                throw new IllegalArgumentException("wrong time format!:" + dateStr);
            }
            return time;
        }
    }

/**
     * @BelongsProject: DemoCode
     * @BelongsPackage: 
     * @Author: hef
     * @CreateTime: 2021-07-12 11:40
     * @Description: 
     */
    public class ShardingConstant {
        
    
        /**
         * 日期分表格式
         */
        public static final String DATE_SHARDING_TABLE_FORMAT = "yyyy_MM";
    }

# 三、ShardingProxy配置。 #

注意要把刚刚写好的maven工程打成jar包导入到proxy的lib目录下面.

shardingRule:
      tables:
        #记录表
        capture_info:
          actualDataNodes: face_${
        1..16}.capture_info_${
        2021..2022}_0$->{
        1..9},face_${
        1..16}.capture_info_${
        2021..2022}_$->{
        10..12}
          tableStrategy:
            #注意这一段配置
            standard:
              #分表键
              shardingColumn: capture_time
              #范围分表规则--->指定刚刚编写的类路径
              rangeAlgorithmClassName: com.rock.sharding.config.CustomTableRangeShardingAlgorithm
              #精确分表规则--->指定刚刚编写的类路径
              preciseAlgorithmClassName: com.rock.sharding.config.CustomTableShardingAlgorithm
          databaseStrategy:
            inline:
              shardingColumn: org_id
              algorithmExpression: face_${
        org_id%16+1}

配置完成后启动proxy,然后连接到proxy后执行建表语句,则会自动创建对应格式的表;

# 四、额外的话 #

*  使用ShardingProxy启动的代理数据库无法通过Navicat和SQLYog无法连接的时候可以试试idea自带的数据库连接工具或者使用DataGrip和命令行模式试试。
 *  如果担心proxy服务不稳定可以使用Nginx做个负载均衡。
 *  此文章仅代表博主在使用ShardingProxy过程中遇到的需求然后记录下，不保证观点完全准确。

参考文章：https://www.cnblogs.com/liran123/p/14191240.html