spring boot 八、 sharding-jdbc 分库分表 按月分表

在项目resources目录下新建com.jianmu.config.sharding.DateShardingAlgorithm 文件

新增yaml配置 数据源

spring:
  shardingsphere:
    props:
      sql:
        #是否在日志中打印 SQL
        show: true
        #打印简单风格的 SQL
        simple: true
    datasource:
      names: pingxuanlog
      pingxuanlog:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db_jianmu_pingxuan_log?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: root
        #最大连接池数量
        max-active: 10
        #最小连接池数量
        min-idle: 5
        #初始化时建立物理连接的个数
        initial-size: 5
        #获取连接时最大等待时间，单位毫秒
        max-wait: 3000
        #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        time-between-eviction-runs-millis: 60000
        #配置一个连接在池中最小生存的时间，单位是毫秒
        min-evictable-idle-time-millis: 100000
        #用来检测连接是否有效的sql，要求是一个查询语句
        validation-query: SELECT 1 FROM DUAL
        #建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
        test-while-idle: true

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

新增yaml配置 sharding 分表规则

spring:
  shardingsphere:
    sharding:
      tables:
        t_act_vt_log:
          #配置数据节点，这里是按月分表，时间范围设置在202201 ~ 210012
          actual-data-nodes: pingxuanlog.t_act_vt_log_$->{
   202201..203012}
          table-strategy:
            standard:
              #使用标准分片策略，配置分片字段
              sharding-column: add_time
              # 精确匹配规则(自定义类)
              precise-algorithm-class-name: com.jianmu.config.sharding.DateShardingAlgorithm
              # 范围匹配规则(自定义类)
              range-algorithm-class-name: com.jianmu.config.sharding.DateShardingAlgorithm
        t_act_access_log:
          #配置数据节点，这里是按月分表，时间范围设置在202201 ~ 210012
          actual-data-nodes: pingxuanlog.t_act_access_log_$->{
   202201..203012}
          table-strategy:
            standard:
              #使用标准分片策略，配置分片字段
              sharding-column: add_time
              # 精确匹配规则(自定义类)
              precise-algorithm-class-name: com.jianmu.config.sharding.DateShardingAlgorithm
              # 范围匹配规则(自定义类)
              range-algorithm-class-name: com.jianmu.config.sharding.DateShardingAlgorithm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

DataSourceConfiguration

package com.jianmu.config.sharding;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.util.Map;

/**
 * 动态数据源配置：
 * 

 * 使用{@link com.baomidou.dynamic.datasource.annotation.DS}注解，切换数据源
 *
 * @DS(DataSourceConfiguration.SHARDING_DATA_SOURCE_NAME)
 *
 */
@Configuration
@AutoConfigureBefore({
   DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class DataSourceConfiguration {
   
    /**
     * 分表数据源名称
     */
    public static final String SHARDING_DATA_SOURCE_NAME = "sharding";
    /**
     * 动态数据源配置项
     */
    private final DynamicDataSourceProperties dynamicDataSourceProperties;
    private final ShardingDataSource shardingDataSource;

    @Autowired
    public DataSourceConfiguration(DynamicDataSourceProperties dynamicDataSourceProperties, @Lazy ShardingDataSource shardingDataSource) {
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45