Sharding-jdbc

pom引入

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

Application.yml

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      names: master
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:3306/share_sharpe?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: 123456

    sharding:
      default-data-source-name: master
      tables:
        user:
          actual-data-nodes: master.user_$->{1..12}
          table-strategy:
            standard:
              sharding-column: create_time
              precise-algorithm-class-name: com.space.springbootsharesharpe.sharding.TablePreciseAlgorithm
              range-algorithm-class-name: com.space.springbootsharesharpe.sharding.TableRangeAlgorithm

            key-generator:
              column: id
              type: SNOWFLAKE

    props:
      sql:
        show: true
    enabled: true

sql脚本

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `user_1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 1 ... 12

SET FOREIGN_KEY_CHECKS = 1;

TablePreciseAlgorithm.java

package com.space.springbootsharesharpe.sharding;

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.springframework.stereotype.Component;

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


@Component
@Slf4j
public class TablePreciseAlgorithm implements PreciseShardingAlgorithm<Date> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> preciseShardingValue) {
        // 根据当前日期 来 分库分表
        Date date = preciseShardingValue.getValue();
        // 去掉前缀0
        String mon = String.valueOf(Integer.parseInt(String.format("%tm", date)));
        // 选择表
        String tableName = "user_" + mon;
        for (String each : availableTargetNames) {
            if (each.equals(tableName)) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }

}

TableRangeAlgorithm.java

package com.space.springbootsharesharpe.sharding;

import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.springframework.stereotype.Component;

import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;


@Component
@Slf4j
public class TableRangeAlgorithm implements RangeShardingAlgorithm<Date> {

    @SneakyThrows
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Date> rangeShardingValue) {
        log.info("collection:{},rangeShardingValue:{}", collection, rangeShardingValue);
        Date lowerEndpoint = rangeShardingValue.getValueRange().lowerEndpoint();
        Date upperEndpoint = rangeShardingValue.getValueRange().upperEndpoint();
        int mon = Integer.parseInt(String.format("%tm", lowerEndpoint));
        int monE = Integer.parseInt(String.format("%tm", upperEndpoint));
        // 选择表
        HashSet<String> names = new HashSet<>(collection);
        String regEx = "[^0-9]";
        Pattern p = Pattern.compile(regEx);
        return names.stream().filter(e -> {
            Matcher m = p.matcher(e);
            String trim = m.replaceAll("").trim();
            int parseInt = Integer.parseInt(trim);
            return parseInt >= mon && parseInt <= monE;
        }).collect(Collectors.toSet());
    }


}

基于配置类实现

在启动类上排除shardingjdbc自带的配置类

import org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;

@SpringBootApplication(exclude={DataSourceAutoConfiguration.class, SpringBootConfiguration.class})

创建ShardingJdbcConfig

package com.space.springbootsharesharpe.config;

import com.space.springbootsharesharpe.sharding.TablePreciseAlgorithm;
import com.space.springbootsharesharpe.sharding.TableRangeAlgorithm;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.NoneShardingStrategyConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.ShardingStrategyConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.transaction.core.TransactionType;
import org.apache.shardingsphere.transaction.core.TransactionTypeHolder;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;


@Configuration
public class ShardingJdbcConfig {

    @Bean("defaultDataSource")
    public DataSource defaultDataSource(){
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/share_sharpe?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true");
        config.setPassword("123456");
        config.setUsername("root");
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        return new HikariDataSource(config);
    }

    @Primary
    @Bean(name = "shardingDataSource")
    public DataSource dataSource(@Qualifier("defaultDataSource") DataSource defaultDataSource) throws SQLException {
        TransactionTypeHolder.set(TransactionType.LOCAL);
        // 指定需要分库分表的数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("master", defaultDataSource);
        // 分库分表配置
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // 配置默认自增主键生成器
        shardingRuleConfig.setDefaultKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE","id"));
        // 配置各个表的分库分表策略，这里只配了一张表的就是user
        shardingRuleConfig.getTableRuleConfigs().add(getTableRuleConfiguration());
        // 配置默认分表规则
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(new NoneShardingStrategyConfiguration());
        // 配置默认分库规则(不配置分库规则,则只采用分表规则)
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new NoneShardingStrategyConfiguration());
        // 配置默认数据源
        shardingRuleConfig.setDefaultDataSourceName("master");
        // 属性配置项，可以为以下属性
        Properties properties = new Properties();
        // 是否打印SQL解析和改写日志
        properties.setProperty("sql.show", Boolean.TRUE.toString());
        return ShardingDataSourceFactory.createDataSource(dataSourceMap,shardingRuleConfig, properties);
    }



    /**
     *      Sharding提供了5种分片策略：
     *      StandardShardingStrategyConfiguration:标准分片策略, 提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持
     *      ComplexShardingStrategyConfiguration:复合分片策略, 提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。
     *      InlineShardingStrategyConfiguration:Inline表达式分片策略, 使用Groovy的Inline表达式，提供对SQL语句中的=和IN的分片操作支持
     *      HintShardingStrategyConfiguration:通过Hint而非SQL解析的方式分片的策略
     *      NoneShardingStrategyConfiguration:不分片的策略
     *      Sharding提供了以下4种算法接口：
     *      PreciseShardingAlgorithm
     *      RangeShardingAlgorithm
     *      HintShardingAlgorithm
     *      ComplexKeysShardingAlgorithm
     *
     */
    public TableRuleConfiguration getTableRuleConfiguration() {
        TableRuleConfiguration result = new TableRuleConfiguration("user","master.user_$->{1..12}");
        ShardingStrategyConfiguration tableShardingStrategyConfig = new StandardShardingStrategyConfiguration("create_time",new TablePreciseAlgorithm(),new TableRangeAlgorithm());
        result.setTableShardingStrategyConfig(tableShardingStrategyConfig);
        result.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE","id"));
        return result;
    }

}