spring boot + sharding jdbc +druid +mybatis 按照年份分库，按照月份分表实现

一，maven引用相关jar包配置

<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
      <version>4.1.1</version>
    </dependency>

<dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.1.1</version>
    </dependency>
 <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.21</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.23</version>
    </dependency>

二，创建数据库ds2020,ds2021 表user01-user12，入下图所示

三，prpperties配置

spring.shardingsphere.datasource.names=ds2020,ds2021
spring.shardingsphere.datasource.ds2020.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2020.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2020.url=jdbc:mysql://localhost:3306/ds2020?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
spring.shardingsphere.datasource.ds2020.username=root
spring.shardingsphere.datasource.ds2020.password=123123

spring.shardingsphere.datasource.ds2021.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2021.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2021.url=jdbc:mysql://localhost:3306/ds2021?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
spring.shardingsphere.datasource.ds2021.username=root
spring.shardingsphere.datasource.ds2021.password=123123

spring.shardingsphere.sharding.tables.account_info.actual-data-nodes=ds$->{2020..2021}.account_info$->{0..1}
spring.shardingsphere.sharding.tables.account_info.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.account_info.table-strategy.inline.algorithm-expression=account_info$->{id % 2}
spring.shardingsphere.sharding.tables.account_info.key-generator.column=id
spring.shardingsphere.sharding.tables.account_info.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds$->{2020..2021}.user0$->{1..9},ds$->{2020..2021}.user1$->{0..2}
spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.tp.user_server2.config.sharding.PreciseModuloTableShardingAlgorithm
spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=create_time
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.binding-tables=account_info
#spring.shardingsphere.sharding.broadcast-tables=t_config
spring.shardingsphere.props.sql.show=true

spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.tp.user_server2.config.sharding.PreciseModuloDatabaseShardingAlgorithm
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=create_time

说明：从上面properties配置中我们可以发现，user和account_info的分表算法配置是不同的，account_info使用的分片表达式即根据id的奇偶情况，分别将数据存储在不同的表里。user使用的是标准精确分类算法

四，数据库分片算法

public class PreciseModuloDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        //对于库的分片collection存放的是所有的库的列表，这里代表ds2020~ds2021
        //配置的分片的sharding-column对应的值
        String timeValue =  TimeUtil.getTimeStr(preciseShardingValue.getValue());
        //分库时配置的sharding-column
        String time = preciseShardingValue.getColumnName();
        //需要分库的逻辑表
        String table = preciseShardingValue.getLogicTableName();
        if(StringUtils.isBlank(timeValue)){
            throw new UnsupportedOperationException("preciseShardingValue is null");
        }
        //按年路由
        for (String each : collection) {
            String value = StringUtils.substring(timeValue,0,4); //获取到年份
            if(each.endsWith(value)){
                // //这里返回回去的就是最终需要查询的库名
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }
}

五，分表分片算法

public class PreciseModuloTableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        //对于库的分片collection存放的是所有的库的列表，这里代表user01~user12
        //配置的分片的sharding-column对应的值
        String timeValue =  TimeUtil.getTimeStr(preciseShardingValue.getValue());
        //分库时配置的sharding-column
        String time = preciseShardingValue.getColumnName();
        //需要分库的逻辑表
        String table = preciseShardingValue.getLogicTableName();
        if(StringUtils.isBlank(timeValue)){
            throw new UnsupportedOperationException("preciseShardingValue is null");
        }
        //按月路由
        for (String each : collection) {
            String value = StringUtils.substring(timeValue,5,7); //获取到月份
            System.out.println("------------------------------------------------"+value);
            if(each.endsWith(value)){
                //这里返回回去的就是最终需要查询的表名
                return each;
            }
        }
        return null;
    }
}

六：测试

@RestController
@RequestMapping("/user0")
public class UserController {
    @Autowired
    private IUserService userService;

    @ResponseBody
    @GetMapping(value = "/saveUser")
    public String saveAccount(){

        User user = new User();
        user.setAccountName("张三");
        user.setCreateTime(new Date());
        userService.save(user);

        return "1";
    }
}

七：测试结果

git代码存放位置：https://gitee.com/taopeng98/spring_cloud_distributed   分库分表实现在user_server2模块中