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

小一的日常 2022-08-02

4815

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:
    props:
      sql.show: true
    datasource:
      names: ds0
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xx:3306/xx?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
        username: x
        password: x
    sharding:
      tables:
        xf_rel_police:
          actualDataNodes: ds0.police_${202101..202112}
          tableStrategy:
            standard:
              shardingColumn: equipment_sys_time
              #精确分片算法类名称，用于=和IN。。该类需实现 PreciseShardingAlgorithm 接口并提供无参数的构造器
              preciseAlgorithmClassName: org.jeecg.config.sharding.DatabaseShardingAlgorithm
              #范围分片算法类名称，用于BETWEEN，可选。。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
              rangeAlgorithmClassName: org.jeecg.config.sharding.DatabaseShardingRangeAlgorithm
          keyGenerator:
            type: SNOWFLAKE
            column: id
  resource:
    static-locations: classpath:/static/,classpath:/public/
  autoconfigure:
    exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
  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://x1:3306/x1?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
          username: x
          password: x
          driver-class-name: com.mysql.cj.jdbc.Driver

4、分表算法逻辑(精确分片算法类)

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;
    } catch (Exception e) {
        e.printStackTrace();
    }
      for (String each : collection) {
          if (each.equals(db_name)) {
                return each;
            }
        }
      throw new IllegalArgumentException();
    }
}

5、范围分片算法类

package org.jeecg.config.sharding;


import cn.hutool.core.date.DateUtil;
import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;


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




public class DatabaseShardingRangeAlgorithm implements RangeShardingAlgorithm<Date> {


  @Override
  public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Date> rangeShardingValue) {


    Collection<String> result = new LinkedHashSet<>(collection.size());


    String db_name=rangeShardingValue.getLogicTableName();
    Range<Date> range = rangeShardingValue.getValueRange();
    Date lowerDate = range.lowerEndpoint();
    Date upperDate = range.upperEndpoint();
    Integer low = Integer.valueOf(DateUtil.format(lowerDate, "yyyyMM"));
    Integer upper = Integer.valueOf(DateUtil.format(upperDate, "yyyyMM"));
    for (int i = low; i <= upper; i++) {
      for (String each : collection) {
        if (each.endsWith(i + "")) {
          result.add(each);
        }
      }
    }
    return result;
  }
}

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

package org.jeecg.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.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;
            }
        };
    }


    /**
     * 将动态数据源设置为首选的
     * 当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;
    }
}

7、使用

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

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

文章转载自小一的日常，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

评论