dynamic + shardingsphere(4.1.1) 实现动态分库分表

1. 主要依赖：
    <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>

2. 数据库配置如下：

spring.datasource.dynamic.primary=redisDB
spring.datasource.dynamic.datasource.redisDB.driver-class-name = com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.redisDB.url=jdbc:mysql://10.95.35.103:3306/csc_siebel_api_call?characterEncoding=UTF-8&useUnicode=true&useSSL=false&serverTimezone=GMT
spring.datasource.dynamic.datasource.redisDB.username=cscsitprd
spring.datasource.dynamic.datasource.redisDB.password=Cscs23tpr#d
spring.datasource.dynamic.datasource.campaignDB.driver-class-name = com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.campaignDB.url=jdbc:mysql://10.95.35.226:3306/CSC_MBHK_CAMPAIGN?characterEncoding=UTF-8&useUnicode=true&useSSL=false&serverTimezone=GMT
spring.datasource.dynamic.datasource.campaignDB.username=cdcmbsit
spring.datasource.dynamic.datasource.campaignDB.password=Cdcmb#sit123
spring.datasource.dynamic.datasource.memberDB.driver-class-name = com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.memberDB.url=jdbc:mysql://10.95.35.226:3306/CSC_MBHK_MEMBER?characterEncoding=UTF-8&useUnicode=true&useSSL=false&serverTimezone=GMT
spring.datasource.dynamic.datasource.memberDB.username=cdcmbsit
spring.datasource.dynamic.datasource.memberDB.password=Cdcmb#sit123

spring.shardingsphere.datasource.names=campaign-db
spring.shardingsphere.datasource.campaign-db.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.campaign-db.driverClassName=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.campaign-db.url=${spring.datasource.dynamic.datasource.campaignDB.url}
spring.shardingsphere.datasource.campaign-db.username=${spring.datasource.dynamic.datasource.campaignDB.username}
spring.shardingsphere.datasource.campaign-db.password=${spring.datasource.dynamic.datasource.campaignDB.password}

refreshCampaignContact.filterSwitch = true
# 开启打印分表sql,生产要关闭false
spring.shardingsphere.props.sql.show=true
#分表
spring.shardingsphere.sharding.tables.CUSTOMER_CAMPAIGN_CONTACT.actualDataNodes=campaign-db.CUSTOMER_CAMPAIGN_CONTACT_$->{0..63}
# 分表字段
spring.shardingsphere.sharding.tables.CUSTOMER_CAMPAIGN_CONTACT.table-strategy.standard.sharding-column=CON_PER_ID
# 实现分表规则类：-- HashModShardingAlgorithm
spring.shardingsphere.sharding.tables.CUSTOMER_CAMPAIGN_CONTACT.table-strategy.standard.precise-algorithm-class-name=com.aswatson.conf.HashModShardingAlgorithm
# 注释的是，表达式方法分表，但是我们要自己增加逻辑，所以做成实现分表规则类。
#spring.shardingsphere.sharding.tables.CUSTOMER_CAMPAIGN_CONTACT.tableStrategy.inline.shardingColumn=CON_PER_ID
#spring.shardingsphere.sharding.tables.CUSTOMER_CAMPAIGN_CONTACT.tableStrategy.inline.algorithmExpression=CUSTOMER_CAMPAIGN_CONTACT_$->{(CON_PER_ID.hashCode()& Integer.MAX_VALUE) % 64}

spring.shardingsphere.sharding.tables.CUSTOMER_LOY_CAM_SEG.actualDataNodes=campaign-db.CUSTOMER_LOY_CAM_SEG_$->{0..63}
spring.shardingsphere.sharding.tables.CUSTOMER_LOY_CAM_SEG.table-strategy.standard.sharding-column=CONTACT_ID
spring.shardingsphere.sharding.tables.CUSTOMER_LOY_CAM_SEG.table-strategy.standard.precise-algorithm-class-name=com.aswatson.conf.HashModShardingAlgorithm


spring.datasource.dynamic.druid.initial-size = 5
spring.datasource.dynamic.druid.min-idle = 5
spring.datasource.dynamic.druid.max-active = 30
spring.datasource.dynamic.druid.max-wait = 6000
spring.datasource.dynamic.druid.time-between-eviction-runs-millis = 60000
spring.datasource.dynamic.druid.min-evictable-idle-time-millis = 300000
spring.datasource.dynamic.druid.validation-query = SELECT 1 FROM DUAL
spring.datasource.dynamic.druid.test-while-idle = true
spring.datasource.dynamic.druid.test-on-borrow = false
spring.datasource.dynamic.druid.test-on-return = false

3. 配置 ShardingDataSourceConfig类：

package com.aswatson.conf;

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.boot.autoconfigure.AutoConfigureAfter;
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;
 
@Configuration
@AutoConfigureAfter({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class ShardingDataSourceConfig {

    @Resource
    private DynamicDataSourceProperties properties;

    //@Lazy
    @Resource(name = "shardingDataSource")
    private DataSource shardingSphereDataSource;

　　
    @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);
　　　　　　　　　　// @DS(campaign-db-sharding) 就是注入的数据源，分库分表的时候，在mapper层注入.
                dataSourceMap.put("campaign-db-sharding", shardingSphereDataSource);
                return dataSourceMap;
            }
        };
    }

    /**
     * 将动态数据源设置为首选的,当spring存在多个数据源时, 自动注入的是首选的对象设置为主要的数据源之后，就支持shardingjdbc原生的配置方式
     */
    @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;
    }

}

4. 配置分表的规则（简单精准分表策略）：

package com.aswatson.conf;

import com.ctrip.framework.apollo.Config;
import com.ctrip.framework.apollo.ConfigService;
import java.util.Collection;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

/**
 * @Author Tim
 * @Date 2022/6/30 11:52
 */
@Slf4j
public class HashModShardingAlgorithm implements PreciseShardingAlgorithm {

    // 通过Apollo client获取是否需要分表开关，不需要分表，通过availableTargetNames 截取 "_num"前的表
    public static String shardingTableSwitch = "sharding.table.switch";
    public static String nameSpace = "cdc-common";

    @Override
    public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
        Comparable value = shardingValue.getValue();
        int hashValue = value.hashCode();
        int availableTargetNamesLen = availableTargetNames.size();
        int targetNamesIdx = Math.abs(hashValue) % availableTargetNamesLen;
        Object[] availableTargetNamesArr = availableTargetNames.toArray(new Object[0]);
        String tableName = String.valueOf(availableTargetNamesArr[targetNamesIdx]);
        log.info("redisService-doSharding-tableName: {}", tableName);
        return getActualTable(tableName);
    }

    private String getActualTable(String tableName) {
        Config config = ConfigService.getConfig(nameSpace);
        boolean tableShardingSwitch = Boolean.parseBoolean(config.getProperty(shardingTableSwitch, "false"));
        if (!tableShardingSwitch) {
            String actualTable = tableName.replaceAll("\\d+","");
            return actualTable.substring(0, actualTable.length()-1);
        }
        log.info("redisService-getActualTable: tableShardingSwitch={}, tableName={}", tableShardingSwitch, tableName);
        return tableName;
    }

}

5.  分库分表使用，在mapper上增加注入的数据源名

package com.aswatson.infrastructure.dao.campaignDao.mapper;

import com.aswatson.infrastructure.dao.campaignDao.model.CustomerCampaignContactPO;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
@DS("campaign-db-sharding")
public interface CustomerCampaignContactMapper extends BaseMapper<CustomerCampaignContactPO> {

    @Select("SELECT DISTINCT SEG_NUM FROM CUSTOMER_LOY_CAM_SEG WHERE CONTACT_ID = #{contactId}")
    List<String> getCustomerLoyCampaignSegments(@Param("contactId") String contactId);

    List<CustomerCampaignContactPO> getCustomerContactLoyByConPerIdAndFilter(@Param("contactId") String contactId);

}

6. 其他的数据源：

package com.aswatson.infrastructure.dao.memberDao.mapper;

import com.aswatson.infrastructure.dao.memberDao.model.CustomerLoyCardPO;
import com.aswatson.infrastructure.dao.memberDao.model.CustomerLoyContactSplit1PO;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author albert.yang
 * @since 2020-04-09
 */
@Mapper
@DS("memberDB")
public interface CustomerLoyContactSplit1Mapper extends BaseMapper<CustomerLoyContactSplit1PO> {

    List<CustomerLoyCardPO> selectMobile(@Param("buId") String buId, @Param("mobile") String mobile,
        @Param("mobile2") String mobile2);
}

7. 注意的是，项目启动时候会报错：

package com.aswatson;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import io.micrometer.core.instrument.MeterRegistry;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.actuate.autoconfigure.metrics.MeterRegistryCustomizer;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.ImportResource;
import org.springframework.scheduling.annotation.EnableScheduling;

@Configuration
//启动类要注意排除自动注入的数据源
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, DruidDataSourceAutoConfigure.class}, scanBasePackages = {"com.aswatson.*"})
@ComponentScan({"com.aswatson.*"})
@MapperScan("com.aswatson.infrastructure.dao.*")
//@EnableDiscoveryClient
@EnableScheduling
public class RedisServerApplication {

    public static void main(String[] args) {
        SpringApplication.run(RedisServerApplication.class, args);
    }

    @Bean MeterRegistryCustomizer<MeterRegistry> configurer(@Value("${spring.application.name}") String applicationName) {
        return registry -> registry.config().commonTags("application", applicationName);
    }

}

 

 

8. 写个测试接口就可以测试了：