dynamic + shardingSphere整合

背景

按查询时间来走分表，分表根据年份

依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.3.2</version>
</dependency>

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

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

application.yml

#######################################dynamic配置###########################################
spring:
  datasource:
    dynamic:
      primary: db1 #设置默认的数据源,默认值为master
      datasource:
        db1:  #数据源db1
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://localhost:3306/dev?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=CTT
          username: root
          password: root
      type: com.alibaba.druid.pool.DruidDataSource
      druid:
        initial-size: 10
        max-active: 100
        min-idle: 10
        max-wait: 60000
        
#######################################ShardingSphere配置###########################################
  shardingsphere:
    datasource:
      names: campaign-db
      campaign-db:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: ${spring.datasource.dynamic.datasource.db1.url}
        username: ${spring.datasource.dynamic.datasource.db1.username}
        password: ${spring.datasource.dynamic.datasource.db1.password}
    props:
      sql:
        show: true
    sharding:
      props:
        sql.show: true
      tables:
        tdata_business_monitor_indicator_summary:  #必须要写表名
          actual-data-nodes: campaign-db.tdata_business_monitor_indicator_summary_$->{2022..2023} #分表后缀
          table-strategy:
            standard:
              shardingColumn: date_time #分表字段
              preciseAlgorithmClassName: com.wf.dev.config.DatePreciseShardingAlgorithm # 自定义in和=的精准匹配算法
              rangeAlgorithmClassName: com.wf.dev.config.DatePreciseShardingAlgorithm # 自定义between and、>=、<=、>、<等范围匹配算法

自定义算法

自定义in和=的精准匹配算法
自定义between and、>=、<=、>、<等范围匹配算法

package com.wf.dev.config;

import com.google.common.collect.Sets;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.springframework.util.CollectionUtils;

import java.util.Collection;

/**
 * @author wf
 * @date 2022年12月01日 15:07
 * @description
 */
@Slf4j
public class DatePreciseShardingAlgorithm implements PreciseShardingAlgorithm<String>, RangeShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> preciseShardingValue) {
        String dateTime = preciseShardingValue.getValue();
        log.info("Sharding input:" + preciseShardingValue.getValue());
        String suffix = getSuffixByYearMonthDay(dateTime);
        for (String tableName : availableTargetNames) {
            log.info("suffix:" + suffix + ", 表明:{}" + tableName);
            if (tableName.endsWith(suffix)) {
                return tableName;
            }
        }
        throw new IllegalArgumentException("未找到匹配的数据表");
    }

    private static String getSuffixByYearMonthDay(String dateTime) {
        return StringUtils.substring(dateTime, 0, 4);
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
        String startTime = rangeShardingValue.getValueRange().lowerEndpoint();
        String endTime = rangeShardingValue.getValueRange().upperEndpoint();
        String startTimeSuffix = getSuffixByYearMonthDay(startTime);
        String endTimeSuffix = getSuffixByYearMonthDay(endTime);
        Collection<String> collect = Sets.newHashSet();
        for (String tableName : collection) {
            if (tableName.endsWith(startTimeSuffix)) {
                collect.add(tableName);
                continue;
            }
            if (tableName.endsWith(endTimeSuffix)) {
                collect.add(tableName);
                continue;
            }
        }
        if (CollectionUtils.isEmpty(collect)) {
            throw new IllegalArgumentException("未找到匹配的数据表");
        }
        return collect;
    }
}

数据源合并

将ShardingSphere数据源加入到dynamic数据源中

package com.wf.dev.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.spring.boot.SpringBootConfiguration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
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;


/**
 * @author wf
 * @date 2022年12月01日 13:54
 * @description
 */
@Configuration
@AutoConfigureAfter({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class DataSourceConfiguration {

    /**
     * 分表数据源名称
     */
    public static final String SHARDING_DATA_SOURCE_NAME = "sharding";
    /**
     * 动态数据源配置项
     */
    @Autowired
    private DynamicDataSourceProperties dynamicDataSourceProperties;

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

    /**
     * 将shardingDataSource放到了多数据源（dataSourceMap）中
     * 注意有个版本的bug，3.1.1版本 不会进入loadDataSources 方法，这样就一直造成数据源注册失败
     */
    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = dynamicDataSourceProperties.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(dynamicDataSourceProperties.getPrimary());
        dataSource.setStrict(dynamicDataSourceProperties.getStrict());
        dataSource.setStrategy(dynamicDataSourceProperties.getStrategy());
        dataSource.setProvider(dynamicDataSourceProvider);
        dataSource.setP6spy(dynamicDataSourceProperties.getP6spy());
        dataSource.setSeata(dynamicDataSourceProperties.getSeata());
        return dataSource;
    }
}

Mapper

使用注解@DS("sharding")，选择ShardingSphere数据源

package com.wf.dev.mapper.monitor.business;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.wf.dev.model.monitor.business.BusinessMonitorReq;
import com.wf.dev.model.monitor.business.BusinessMonitorVO;

import java.util.List;

/**
 *
 * @author wf
 * @date 2022年11月28日
 */
@DS("sharding")
public interface BusinessMonitorMapper {

    List<BusinessMonitorVO> list(BusinessMonitorReq req);
}