介绍
基于sharding-datasource, jpa进行分库操作, 基于AbstractDataSourceBasedMultiTenantConnectionProviderImpl, CurrentTenantIdentifierResolver动态切换数据源
SaaS服务多租户介绍，每个租户的资源都是独立的，从入口到应用部署到数据，都是完全隔离的。每个租户相当于“托管”在提供服务的公司里面，公司做的其实是统一运维。好处在于，这个隔离非常彻底，基本不太会有相互影响；如果有特殊客户要求的定制化，也没啥处理难度。缺陷在于，很容易因为隔离和定制，导致版本不统一，资源也浪费比较大，因为是“代运维”的模式，因此服务成本也比较高。这个时候通常采用的数据库方式是分库
1. maven项目依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
</dependencies>
2.application.yml配置
spring:
application:
name: jdbc-db-jpa-tenant
profiles:
include: jdbc
jpa:
show-sql: true
hibernate:
ddl-auto: none
naming:
implicit-strategy: org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
database-platform: org.hibernate.dialect.MySQL8Dialect
properties:
hibernate.enable_lazy_load_no_trans: true
hibernate.format_sql: true
hibernate.show_sql: true
logging:
file:
name: logs/${spring.application.name}.log
level:
org.springframework: info
com.lance.sharding.tenant: debug
3.application-jdbc.yml配置
com:
multi:
db:
bbs_1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/bbs_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
password: li123456
username: root
bbs_2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/bbs_2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
password: li123456
username: root
bbs_3:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/bbs_3?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
password: li123456
username: root
4.测试Sql脚本
CREATE TABLE `t_order`
(
`order_id` bigint NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`address_id` bigint NOT NULL,
`status` tinyint NULL DEFAULT NULL,
`creator` varchar(32) NULL DEFAULT NULL,
`create_time` datetime NULL DEFAULT NULL,
`updater` varchar(32) NULL DEFAULT NULL,
`update_time` datetime NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
5.配置多数据源
public class DbMultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {
private final Map<String, DataSource> dataSourceMap;
@Override
protected DataSource selectAnyDataSource() {
return this.dataSourceMap.values().iterator().next();
}
@Override
protected DataSource selectDataSource(String tenantIdentifier) {
return this.dataSourceMap.get(tenantIdentifier);
}
}
public class CustomCurrentTenantIdentifierResolver implements CurrentTenantIdentifierResolver {
private final static String DEFAULT_TENANT_ID = "bbs_1";
@Override
public String resolveCurrentTenantIdentifier() {
String currentTenantId = DbContextHolder.get();
return StringUtils.hasText(currentTenantId) ? currentTenantId : DEFAULT_TENANT_ID;
}
@Override
public boolean validateExistingCurrentSessions() {
return false;
}
}
@Configuration
@AllArgsConstructor
@EnableConfigurationProperties(CustomDbProperties.class)
@EnableJpaRepositories(basePackages = {"com.lance.sharding.tenant.repository"}, transactionManagerRef = "txManager")
public class MultiDbConfig {
private final CustomDbProperties customDbProperties;
private final JpaProperties jpaProperties;
@Bean
public MultiTenantConnectionProvider multiTenantConnectionProvider() {
Map<String, DataSource> map = new HashMap<>(8);
customDbProperties.getDb().forEach((k, cfg) -> map.put(k, new HikariDataSource(cfg)));
return new DbMultiTenantConnectionProviderImpl(map);
}
@Bean
public CurrentTenantIdentifierResolver currentTenantIdentifierResolver() {
return new CustomCurrentTenantIdentifierResolver();
}
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(
MultiTenantConnectionProvider multiTenantConnectionProvider, CurrentTenantIdentifierResolver currentTenantIdentifierResolver) {
Map<String, Object> hibernateProps = new LinkedHashMap<>(jpaProperties.getProperties());
hibernateProps.put(Environment.MULTI_TENANT, MultiTenancyStrategy.DATABASE);
hibernateProps.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProvider);
hibernateProps.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolver);
hibernateProps.put(Environment.PHYSICAL_NAMING_STRATEGY, CamelCaseToUnderscoresNamingStrategy.class.getName());
hibernateProps.put(Environment.IMPLICIT_NAMING_STRATEGY, SpringImplicitNamingStrategy.class.getName());
// No dataSource is set to resulting entityManagerFactoryBean
LocalContainerEntityManagerFactoryBean result = new LocalContainerEntityManagerFactoryBean();
result.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
result.setJpaPropertyMap(hibernateProps);
result.setPackagesToScan("com.lance.sharding.tenant.entity");
return result;
}
@Bean
public EntityManagerFactory entityManagerFactory(LocalContainerEntityManagerFactoryBean entityManagerFactoryBean) {
return entityManagerFactoryBean.getObject();
}
@Bean
public PlatformTransactionManager txManager(EntityManagerFactory entityManagerFactory) {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(entityManagerFactory);
return transactionManager;
}
}
6.单元测试Test
class OrderRepositoryTests {
@Autowired
private OrderRepository orderRepository;
@Test
@Disabled
void save() {
try {
//DbContextHolder.set("bbs_1");
//DbContextHolder.set("bbs_2");
DbContextHolder.set("bbs_3");
ThreadLocalRandom random = ThreadLocalRandom.current();
IntStream.range(0, 20).forEach(i -> {
OrderEntity order = new OrderEntity();
order.setOrderId(System.nanoTime() + i);
order.setAddressId(i);
order.setUserId(Math.abs(random.nextInt()));
order.setCreator("user.0" + i);
order.setCreateTime(new Date());
order.setUpdater(order.getCreator());
order.setUpdateTime(order.getCreateTime());
orderRepository.save(order);
});
} finally {
DbContextHolder.clear();
}
}
@Test
@Disabled
void findAll() {
try {
//DbContextHolder.set("bbs_1");
DbContextHolder.set("bbs_2");
//DbContextHolder.set("bbs_1");
List<OrderEntity> list = orderRepository.findAll();
log.info("===>{}", list);
} finally {
DbContextHolder.clear();
}
}
}
