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#sit123spring.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.HashModShardingAlgorithmspring.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);
}}