springboot动态数据源用shardingjdbc按时间分表

2024-01-28 14:13:22

1、背景：

原有项目架构

springboot
mybatis-plus
dynamic-datasource（动态数据源、mybatis-plus拓展)
druid 连接池

根据业务数据不断增加，需要进行对业务量大的数据表行分表，因为以前的业务都是动态数据源管理datasource，改造可以顺势而为，把shardingjdbc的数据源也交给

dynamic-datasource 来管理，默认走以前不分表的逻辑，并且不由sharding管理，兼容以前逻辑，这里shardingjdbc有不兼容的sql情况，不分表的部分可以规避此风险。

2、必要的pom依赖：

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.3.RELEASE</version>
    <relativePath/>
</parent>


<properties>
      <mybatis-plus.version>3.1.2</mybatis-plus.version>
      <druid.version>1.1.17</druid.version>
  </properties>

<!-- mybatis-plus -->
<dependency>
       <groupId>com.baomidou</groupId>
       <artifactId>mybatis-plus-boot-starter</artifactId>
       <version>${mybatis-plus.version}</version>
   </dependency>
<!-- druid -->
     <dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
   <version>${druid.version}</version>
</dependency>
<!-- 动态数据源 -->
<dependency>
   <groupId>com.baomidou</groupId>
   <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
   <version>3.1.1</version>
</dependency>
<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
   <version>4.1.0</version>
</dependency>

3、yml相关配置

spring:
  shardingsphere:
    datasource:
      names: ds0
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://xx:3306/xx?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
        username: xx
        password: xx
    sharding:
      tables:
        table1:
          actualDataNodes: ds0.table1_${202107..202112}
          tableStrategy:
            standard:
              shardingColumn: create_time
              #精确分片算法类名称，用于=和IN。。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
              preciseAlgorithmClassName: org.jeecg.config.sharding.DatabaseShardingAlgorithm
              #范围分片算法类名称，用于BETWEEN，可选。。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
          keyGenerator:
            type: SNOWFLAKE
            column: id
  datasource:
      druid:
    stat-view-servlet:
      enabled: true
      loginUsername: admin
      loginPassword: 123456
      allow:
    web-stat-filter:
      enabled: true
  dynamic:
    druid: # 全局druid参数，绝大部分值和默认保持一致。(现已支持的参数如下,不清楚含义不要乱设置)
      # 连接池的配置信息
      # 初始化大小，最小，最大
      initial-size: 5
      min-idle: 5
      maxActive: 20
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      # 打开PSCache，并且指定每个连接上PSCache的大小
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,slf4j
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
    datasource:
      master:
        url: jdbc:mysql://xx:3306/xx?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
        username: xx
        password: xx
        driver-class-name: com.mysql.jdbc.Driver
      # 多数据源配置
      multi-datasource1:
        url: jdbc:mysql://xx:3306/xx1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true
        username: xx
        password: xx
        driver-class-name: com.mysql.jdbc.Driver

格式需要自己调整一下对齐，以上配置是原有dynamic 相关、sharding相关是拓展添加

4、分表算法逻辑（org.jeecg.config.sharding.DatabaseShardingAlgorithm）

package org.jeecg.config.sharding;


import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;


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




public class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
       String db_name=preciseShardingValue.getLogicTableName();
       try {
         Date date = preciseShardingValue.getValue();
         String year = String.format("%tY", date);
         String mon  = String.format("%tm",date);
         db_name=db_name+"_"+year+mon;
         System.out.println("db_name:" + db_name);
   } catch (Exception e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
   }
       
       for (String each : collection) {
           System.out.println("db:" + each);
           if (each.equals(db_name)) {
                return each;
            }
        }
       
       throw new IllegalArgumentException();
    }
}

5、sharding数据源加入到动态数据源里

package org.jeecg.common.config;


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.adapter.AbstractDataSourceAdapter;
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.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;


/**
* 动态数据源配置：
*
* 使用{@link com.baomidou.dynamic.datasource.annotation.DS}注解，切换数据源
*
* <code>@DS(DataSourceConfiguration.SHARDING_DATA_SOURCE_NAME)</code>
*
* @author lixiaoyi
*/
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
        SpringBootConfiguration.class})
public class DataSourceConfiguration {
    /**
     * 分表数据源名称
     */
    private static final String SHARDING_DATA_SOURCE_NAME = "gits_sharding";
    /**
     * 动态数据源配置项
     */
    @Autowired
    private DynamicDataSourceProperties properties;


    /**
     * shardingjdbc有四种数据源，需要根据业务注入不同的数据源
     *
     * <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
     * <p>2. 主从数据源: masterSlaveDataSource;
     * <p>3. 脱敏数据源：encryptDataSource;
     * <p>4. 影子数据源：shadowDataSource
     *
     */
    @Lazy
    @Resource(name = "shardingDataSource")
    AbstractDataSourceAdapter shardingDataSource;


    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                // 将 shardingjdbc 管理的数据源也交给动态数据源管理
                dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
                return dataSourceMap;
            }
        };
    }


    /**
     * 将动态数据源设置为首选的 （此处可以不注入 此对象 因为配置里已经表明了primary数据源）
     * 当spring存在多个数据源时, 自动注入的是首选的对象
     * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
     *
     * @return
     */
    @Primary
    @Bean
    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setPrimary(properties.getPrimary());
        dataSource.setStrict(properties.getStrict());
        dataSource.setStrategy(properties.getStrategy());
        dataSource.setProvider(dynamicDataSourceProvider);
        dataSource.setP6spy(properties.getP6spy());
        dataSource.setSeata(properties.getSeata());
        return dataSource;
    }
}

6、使用

@Service
@DS("gits_sharding")
public class TestServiceImpl{
}

可以在相关service使用@DS 去改变数据源，@DS是mybatis-plus 拓展的 dynamic-datasource 相关用法这里就不展开说明了

码农公寓

相关文章