Recently, in the SaaS application, the database adopts the single-instance multi-schema architecture (see Reference 1 for details). Each tenant has an independent schema, and the whole data source has a shared schema. Therefore, the problem of dynamic addition, deletion and data source switching needs to be solved.
After searching a lot of articles on the Internet, most of them are about the configuration of master and slave data sources, or the configuration of data sources has been determined before the application starts, and very few of them are about how to load data sources dynamically without stopping the machine, so I write this article for reference.
The technology used
- Java8
- Spring + SpringMVC + MyBatis
- Druid connection pool
- Lombok
- (The above techniques do not affect the implementation of the idea, just for the convenience of browsing the following code snippet)
Train of thought
When a request is received, the system determines the tenant of the current user and switches to the corresponding data source based on the tenant information, and then performs subsequent service operations.
Code implementation
TenantConfigEntity (Tenant information)
@EqualsAndHashCode(callSuper = false)
@Data
@FieldDefaults(level = AccessLevel.PRIVATE)
public class TenantConfigEntity {
/** * Tenant ID **/
Integer tenantId;
/** * Tenant name **/
String tenantName;
/** * Tenant name key **/
String tenantKey;
/** * database url **/
String dbUrl;
/** * Database user name **/
String dbUser;
/** * Database password **/
String dbPassword;
/** * database public_key **/
String dbPublicKey;
}
Copy the codeDataSourceUtil (Assistive tools class, not necessary)
public class DataSourceUtil {
private static final String DATA_SOURCE_BEAN_KEY_SUFFIX = "_data_source";
private static final String JDBC_URL_ARGS = "? useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true&zeroDateTimeBehavior=convertToNull";
private static final String CONNECTION_PROPERTIES = "config.decrypt=true; config.decrypt.key=";
/** * Concatenate the spring bean key of the data source */
public static String getDataSourceBeanKey(String tenantKey) {
if(! StringUtils.hasText(tenantKey)) {return null;
}
return tenantKey + DATA_SOURCE_BEAN_KEY_SUFFIX;
}
/** * concatenate the full JDBC URL */
public static String getJDBCUrl(String baseUrl) {
if(! StringUtils.hasText(baseUrl)) {return null;
}
return baseUrl + JDBC_URL_ARGS;
}
/** * Concatenate the full Druid connection properties */
public static String getConnectionProperties(String publicKey) {
if(! StringUtils.hasText(publicKey)) {return null;
}
returnCONNECTION_PROPERTIES + publicKey; }}Copy the codeDataSourceContextHolder
Use ThreadLocal to save the data source key name of the current thread, and implement set, get, clear methods.
public class DataSourceContextHolder {
private static final ThreadLocal<String> dataSourceKey = new InheritableThreadLocal<>();
public static void setDataSourceKey(String tenantKey) {
dataSourceKey.set(tenantKey);
}
public static String getDataSourceKey(a) {
return dataSourceKey.get();
}
public static void clearDataSourceKey(a) { dataSourceKey.remove(); }}Copy the codeDynamicDataSource
Inheritance AbstractRoutingDataSource (suggest reading the source code, to understand the process of dynamic data source switch), realize the dynamic selection data source;
public class DynamicDataSource extends AbstractRoutingDataSource {
@Autowired
private ApplicationContext applicationContext;
@Lazy
@Autowired
private DynamicDataSourceSummoner summoner;
@Lazy
@Autowired
private TenantConfigDAO tenantConfigDAO;
@Override
protected String determineCurrentLookupKey(a) {
String tenantKey = DataSourceContextHolder.getDataSourceKey();
return DataSourceUtil.getDataSourceBeanKey(tenantKey);
}
@Override
protected DataSource determineTargetDataSource(a) {
String tenantKey = DataSourceContextHolder.getDataSourceKey();
String beanKey = DataSourceUtil.getDataSourceBeanKey(tenantKey);
if(! StringUtils.hasText(tenantKey) || applicationContext.containsBean(beanKey)) {return super.determineTargetDataSource();
}
if (tenantConfigDAO.exist(tenantKey)) {
summoner.registerDynamicDataSources();
}
return super.determineTargetDataSource(); }}Copy the codeDynamicDataSourceSummoner (key in a key)
Load data source information from the database, and dynamically assemble and register Spring beans,
@Slf4j
@Component
public class DynamicDataSourceSummoner implements ApplicationListener<ContextRefreshedEvent> {
// Same as the default data source ID of spring-data-source.xml
private static final String DEFAULT_DATA_SOURCE_BEAN_KEY = "defaultDataSource";
@Autowired
private ConfigurableApplicationContext applicationContext;
@Autowired
private DynamicDataSource dynamicDataSource;
@Autowired
private TenantConfigDAO tenantConfigDAO;
private static boolean loaded = false;
/** * Execute */ after Spring is loaded
@Override
public void onApplicationEvent(ContextRefreshedEvent event) {
// Prevent repeated execution
if(! loaded) { loaded =true;
try {
registerDynamicDataSources();
} catch (Exception e) {
log.error("Data source initialization failed, Exception:", e); }}}/** * reads the tenant's DB configuration from the database and dynamically injects the Spring container */
public void registerDynamicDataSources(a) {
// Get the DB configuration of all tenants
List<TenantConfigEntity> tenantConfigEntities = tenantConfigDAO.listAll();
if (CollectionUtils.isEmpty(tenantConfigEntities)) {
throw new IllegalStateException("Application initialization failed, please configure data source first");
}
// Register the data source bean with the container
addDataSourceBeans(tenantConfigEntities);
}
/** * Create the bean according to DataSource and register it with container */
private void addDataSourceBeans(List<TenantConfigEntity> tenantConfigEntities) {
Map<Object, Object> targetDataSources = Maps.newLinkedHashMap();
DefaultListableBeanFactory beanFactory = (DefaultListableBeanFactory) applicationContext.getAutowireCapableBeanFactory();
for (TenantConfigEntity entity : tenantConfigEntities) {
String beanKey = DataSourceUtil.getDataSourceBeanKey(entity.getTenantKey());
// If the data source is already registered in Spring, it will not be registered again
if (applicationContext.containsBean(beanKey)) {
DruidDataSource existsDataSource = applicationContext.getBean(beanKey, DruidDataSource.class);
if (isSameDataSource(existsDataSource, entity)) {
continue; }}/ / assembly bean
AbstractBeanDefinition beanDefinition = getBeanDefinition(entity, beanKey);
/ / registered bean
beanFactory.registerBeanDefinition(beanKey, beanDefinition);
// Add the bean object to the map
targetDataSources.put(beanKey, applicationContext.getBean(beanKey));
}
// Set the map object to targetDataSources;
dynamicDataSource.setTargetDataSources(targetDataSources);
/ / have to do this, will the re-initialization AbstractRoutingDataSource resolvedDataSources, also only in this way, the dynamic switch will work
dynamicDataSource.afterPropertiesSet();
}
/** * Assemble the datasource Spring bean */
private AbstractBeanDefinition getBeanDefinition(TenantConfigEntity entity, String beanKey) {
BeanDefinitionBuilder builder = BeanDefinitionBuilder.genericBeanDefinition(DruidDataSource.class);
builder.getBeanDefinition().setAttribute("id", beanKey);
// Other configurations inherit defaultDataSource
builder.setParentName(DEFAULT_DATA_SOURCE_BEAN_KEY);
builder.setInitMethodName("init");
builder.setDestroyMethodName("close");
builder.addPropertyValue("name", beanKey);
builder.addPropertyValue("url", DataSourceUtil.getJDBCUrl(entity.getDbUrl()));
builder.addPropertyValue("username", entity.getDbUser());
builder.addPropertyValue("password", entity.getDbPassword());
builder.addPropertyValue("connectionProperties", DataSourceUtil.getConnectionProperties(entity.getDbPublicKey()));
return builder.getBeanDefinition();
}
/** * Check whether the DataSource in the Spring container is the same as the DataSource in the database */
private boolean isSameDataSource(DruidDataSource existsDataSource, TenantConfigEntity entity) {
boolean sameUrl = Objects.equals(existsDataSource.getUrl(), DataSourceUtil.getJDBCUrl(entity.getDbUrl()));
if(! sameUrl) {return false;
}
boolean sameUser = Objects.equals(existsDataSource.getUsername(), entity.getDbUser());
if(! sameUser) {return false;
}
try {
String decryptPassword = ConfigTools.decrypt(entity.getDbPublicKey(), entity.getDbPassword());
return Objects.equals(existsDataSource.getPassword(), decryptPassword);
} catch (Exception e) {
log.error("Data source password verification failed,Exception:{}", e);
return false; }}}Copy the codespring-data-source.xml
<! JDBC configuration file -->
<context:property-placeholder location="classpath:data.properties" ignore-unresolvable="true"/>
<! -- Public (default) data source -->
<bean id="defaultDataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<! -- Basic properties URL, user, password -->
<property name="url" value="${ds.jdbcUrl}" />
<property name="username" value="${ds.user}" />
<property name="password" value="${ds.password}" />
<! -- Configure initial size, min, Max -->
<property name="initialSize" value="5" />
<property name="minIdle" value="2" />
<property name="maxActive" value="10" />
<! -- Set the timeout for waiting to get connections in milliseconds -->
<property name="maxWait" value="1000" />
<! -- How often is it configured to detect idle connections that need to be closed in milliseconds -->
<property name="timeBetweenEvictionRunsMillis" value="5000" />
<! Set the minimum time for a connection to live in the pool, in milliseconds.
<property name="minEvictableIdleTimeMillis" value="240000" />
<property name="validationQuery" value="SELECT 1" />
<! -- Unit: second, the timeout time to check whether the connection is valid -->
<property name="validationQueryTimeout" value="60" />
<! -- You are advised to set this parameter to true to ensure performance and security. Application connection testing, if free time is more than timeBetweenEvictionRunsMillis, performing validationQuery test connection is valid - >
<property name="testWhileIdle" value="true" />
<! Execute validationQuery when applying for a connection to check whether the connection is valid. This configuration can degrade performance. -->
<property name="testOnBorrow" value="true" />
<! Execute validationQuery when returning a connection to check whether the connection is valid. This configuration can degrade performance. -->
<property name="testOnReturn" value="false" />
<! --Config Filter-->
<property name="filters" value="config" />
<property name="connectionProperties" value="config.decrypt=true; config.decrypt.key=${ds.publickey}" />
</bean>
<! -- Transaction manager -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="multipleDataSource"/>
</bean>
<! -- Multiple data sources -->
<bean id="multipleDataSource" class="a.b.c.DynamicDataSource">
<property name="defaultTargetDataSource" ref="defaultDataSource"/>
<property name="targetDataSources">
<map>
<entry key="defaultDataSource" value-ref="defaultDataSource"/>
</map>
</property>
</bean>
<! -- Annotation transaction manager -->
<! - the order value must be greater than DynamicDataSourceAspectAdvice order here - >
<tx:annotation-driven transaction-manager="txManager" order="2"/>
<! Create SqlSessionFactory and specify data source -->
<bean id="mainSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="multipleDataSource"/>
</bean>
<! Spring will automatically find the DAO under the package name of the DAO interface.
<bean id="mainSqlMapper" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="sqlSessionFactoryBeanName" value="mainSqlSessionFactory"/>
<property name="basePackage" value="a.b.c.*.dao"/>
</bean>
<bean id="defaultSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="defaultDataSource"/>
</bean>
<bean id="defaultSqlMapper" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="sqlSessionFactoryBeanName" value="defaultSqlSessionFactory"/>
<property name="basePackage" value="a.b.c.base.dal.dao"/>
</bean>
<! -- Other configuration omitted -->
Copy the codeDynamicDataSourceAspectAdvice
Use AOP to automatically switch data sources, for reference only;
@Slf4j
@Aspect
@Component
@Order(1) / / please note that this order must be smaller than the tx: annotation - driven order, namely the first execution DynamicDataSourceAspectAdvice aspect, to perform transactions section, to get to the final data source
@EnableAspectJAutoProxy(proxyTargetClass = true)
public class DynamicDataSourceAspectAdvice {
@Around("execution(* a.b.c.*.controller.*.*(..) )")
public Object doAround(ProceedingJoinPoint jp) throws Throwable {
ServletRequestAttributes sra = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletRequest request = sra.getRequest();
HttpServletResponse response = sra.getResponse();
String tenantKey = request.getHeader("tenant");
// The front-end must pass in the Tenant Header, otherwise 400 is returned
if(! StringUtils.hasText(tenantKey)) { WebUtils.toHttp(response).sendError(HttpServletResponse.SC_BAD_REQUEST);return null;
}
log.info("Current tenant key:{}", tenantKey);
DataSourceContextHolder.setDataSourceKey(tenantKey);
Object result = jp.proceed();
DataSourceContextHolder.clearDataSourceKey();
returnresult; }}Copy the codeother
In addition, some information (e.g. Tenant configuration, county, and other shared information) is stored in a common schema, which means that a request may include querying the current tenant data source and the public data source. It is recommended to create a separate sqlSessionFactory and sqlMapper for the public data source (corresponding to defaultSqlSessionFactory and defaultSqlMapper in XML configuration, respectively) or to make the public data source a separate microservice. Later horizontal expansion is also convenient.
The resources
-
Three options for SaaS multi-tenant data isolation
-
Spring MVC+Mybatis multi-data source configuration
-
Spring creates beans on the fly
-
Saas Spring loads and edits data sources dynamically