本文是在springboot整合分库分表的基础上添加了多数据源,建议先看上一篇shardingsphere5.x整合springboot分库分表实战_任人人人呢的博客-CSDN博客
pom.xml配置:
<!--shardingsphere分库分表依赖-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.1</version>
</dependency>
<!--mybatis-plus依赖-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
<!-- dynamic多数据源 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.1.1</version>
</dependency>
yml配置:
spring:
datasource:
# 动态数据源配置
dynamic:
datasource:
tour:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/tour_business?serverTimezone=GMT%2b8:00&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true&useSSL=false
username: root
password: root
avalon:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost/avalon_ads?serverTimezone=GMT%2b8:00&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true&useSSL=false
username: root
password: root
# 指定默认数据源名称
primary: tour
shardingsphere:
datasource:
common:
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
names: db0,db1
db0:
url: jdbc:mysql://localhost:3306/test
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
db1:
url: jdbc:mysql://localhost:3306/zkq_oms_test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
#默认数据源,未分片的表默认执行库
sharding:
default-data-source-name: db1
rules:
sharding:
key-generators:
#此处必须要配置,否则会导致报错,因为shardingsphere-jdbc-core-spring-boot-starter需要加载此项配置,官网的demo例子有错
#分布式序列算法:https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/keygen/
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
sharding-algorithms:
#分片算法:https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/sharding/
table-inline:
type: MOD
props:
sharding-count: 10
tables:
orders:
# 配置orders表的分表的规则
actual-data-nodes: db0.orders_$->{0..9}
table-strategy:
standard:
sharding-column: order_no
sharding-algorithm-name: table-inline
enabled: true
# 展示修改以后的sql语句
props:
sql-show: true
mybatis:
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
type-aliases-package: com.example.demo.entity
mapper-locations: classpath:mapper/*.xml
添加多数据源配置类:
package com.example.demo;
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.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.sql.DataSource;
import java.util.Map;
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
SpringBootConfiguration.class})
public class DataSourceConfiguration {
/**
* 动态数据源配置项
* 这里会根据yml文件的配置自动加载配置,将多个数据源信息放到datasourceMap中
*/
@Autowired
private DynamicDataSourceProperties properties;
/**
* 使用shardingSphereDataSource 自动装载的 DataSource
* 5.1.1版本自动装载的shardingSphereDataSource beanName="shardingSphereDataSource"
* 要加@Lazy
*/
@Lazy
@Autowired
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);
// 将 shardingjdbc 管理的数据源也交给动态数据源管理
dataSourceMap.put(DBConstants.SHARDING, shardingSphereDataSource);
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;
}
}
添加多数据源常量类:
package com.example.demo;
public class DBConstants {
/**
* 数据源分组 - 巡店库
* 这里的tour是yml中的 spring.datasource.dynamic.datasource.tour
*/
public static final String TOUR = "tour";
/**
* 数据源分组 - 阿瓦隆库
* 这里的avalon是yml中的 spring.datasource.dynamic.datasource.avalon
*/
public static final String AVALON = "avalon";
/**
* 数据源分组 - 分库分表
*/
public static final String SHARDING = "sharding";
}
Mapper文件
@Repository
public interface CommonDealerMapper {
//使用多数据源默认的tour库
CommonDealerEntity getCommonDealer(@Param("clientCode") String clientCode);
//使用多数据源的avalon库
@DS(value = DBConstants.AVALON)
AvalonCommonDealer getShuCangCommonDealer(@Param("clientCode") String clientCode);
}
@Repository
public interface OrdersMapper extends BaseMapper<Orders> {
//使用shardingsphere中分表数据源
@DS(value = DBConstants.SHARDING)
Orders getOrderByNo(String orderNo);
@DS(value = DBConstants.SHARDING)
List<Orders> getOrderList();
}
@Repository
public interface ZkqClientMapper {
//使用shardingsphere中默认数据源,未分片的表默认执行库
@DS(value = DBConstants.SHARDING)
Map<String, Object> selectByPrimaryKey(Long id);
}