Recently, we are working on SaaS applications. The database adopts a single instance multi-schema architecture (see Reference 1 for details). Each tenant has an independent schema, and the entire data source has a shared schema, so it is necessary to solve the problem of dynamic addition and deletion and switching data sources.
After searching for many articles online, many of them talk about the master-slave data source configuration, or they have determined the data source configuration before the application is started, and rarely talk about how to dynamically load the data source without shutting down, so I wrote this article for reference.
Techniques used
Ideas
When a request comes in, determine the tenant to which the current user belongs, and switch to the corresponding data source based on the tenant information, and then perform subsequent business 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 username**/ String dbUser; /** * Database password**/ String dbPassword; /** * Database public_key **/ String dbPublicKey;}DataSourceUtil (Aidant Tool Class, Non-essential) 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="; /** * Spring bean key for splicing data sources */ public static String getDataSourceBeanKey(String tenantKey) { if (!StringUtils.hasText(tenantKey)) { return null; } return tenantKey + DATA_SOURCE_BEAN_KEY_SUFFIX; } /** * Splicing complete JDBC URL */ public static String getJDBCUrl(String baseUrl) { if (!StringUtils.hasText(baseUrl)) { return null; } return baseUrl + JDBC_URL_ARGS; } /** * Spliced complete Druid connection properties*/ public static String getConnectionProperties(String publicKey) { if (!StringUtils.hasText(publicKey)) { return null; } return CONNECTION_PROPERTIES + publicKey; }}DataSourceContextHolder
Use ThreadLocal to save the data source key name of the current thread and implement the set, get, and 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() { return dataSourceKey.get(); } public static void clearDataSourceKey() { dataSourceKey.remove(); }}DynamicDataSource (key point)
Inherit AbstractRoutingDataSource (it is recommended to read its source code to understand the process of dynamically switching data sources) and realize dynamic selection of data sources;
public class DynamicDataSource extends AbstractRoutingDataSource { @Autowired private ApplicationContext applicationContext; @Lazy @Autowired private DynamicDataSourceSummoner summoner; @Lazy @Autowired private TenantConfigDAO tenantConfigDAO; @Override protected String determineCurrentLookupKey() { String tenantKey = DataSourceContextHolder.getDataSourceKey(); return DataSourceUtil.getDataSourceBeanKey(tenantKey); } @Override protected DataSource determineTargetDataSource() { 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(); }}DynamicDataSourceSummoner (key point of focus)
Load data source information from the database and dynamically assemble and register spring beans.
@Slf4j@Componentpublic class DynamicDataSourceSummoner implements ApplicationListener<ContextRefreshedEvent> { // Consistent with 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 loading is completed*/ @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); } } } } /** * Read the tenant's DB configuration from the database and dynamically inject the Spring container*/ public void registerDynamicDataSources() { // Get the DB configuration for all tenants List<TenantConfigEntity> tenantConfigEntities = tenantConfigDAO.listAll(); if (CollectionUtils.isEmpty(tenantConfigEntities)) { throw new IllegalStateException("Application initialization failed, please configure the data source first"); } // Register the data source bean in the container addDataSourceBeans(tenantConfigEntities); } /** * Create beans based on DataSource and register in the 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 has been registered in spring, do not re-register if (applicationContext.containsBean(beanKey)) { DruidDataSource existsDataSource = applicationContext.getBean(beanKey, DruidDataSource.class); if (isSameDataSource(existsDataSource, entity)) { continue; } } // Assemble bean AbstractBeanDefinition beanDefinition = getBeanDefinition(entity, beanKey); // Register bean beanFactory.registerBeanDefinition(beanKey, beanDefinition); // Put it in the map, note that the bean object was created just now targetDataSources.put(beanKey, applicationContext.getBean(beanKey)); } // Set the created map object to targetDataSources; dynamicDataSource.setTargetDataSources(targetDataSources); // This operation must be performed before the AbstractRoutingDataSource will be reinitialized resolvedDataSources in this way, only dynamic switching will take effect dynamicDataSource.afterPropertiesSet(); } /** * Assemble data source 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(); } /** * Determine whether the DataSource in the Spring container is consistent with the DataSource information of the database* Note: There is no judgment here on public_key, because the other three information can basically be determined to be unique*/ 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; } }}spring-data-source.xml
<!-- Introduce jdbc configuration file --> <context:property-placeholder location="classpath:data.properties" ignore-unresolvable="true"/> <!-- Public (default) data source --> <bean id="defaultDataSource" 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 initialization size, minimum, and maximum --> <property name="initialSize" value="5" /> <property name="minIdle" value="2" /> <property name="maxActive" value="10" /> <!-- Configure the time to wait for connection to timeout in milliseconds --> <property name="maxWait" value="1000" /> <!-- Configure how long it takes to detect the idle connection that needs to be closed in milliseconds --> <property name="timeBetweenEvictionRunsMillis" value="5000" /> <!-- Configure how long it takes to detect once, detect the idle connection that needs to be closed in milliseconds --> <property name="timeBetweenEvictionRunsMillis" value="5000" /> <!-- Configure how long it takes to detect once, detect the idle connection that needs to be closed in milliseconds --> <property name="timeBetweenEvictionRunsMillis" value="5000" /> <!-- Configure the minimum time for a connection to survive in the pool, in milliseconds --> <property name="minEvictableIdleTimeMillis" value="240000" /> <property name="validationQuery" value="SELECT 1" /> <!--Unit: seconds, timeout time to detect whether the connection is valid --> <property name="validationQueryTimeout" value="60" /> <!--Configure to true, which does not affect performance and ensures security. When applying for a connection, if the idle time is greater than timeBetweenEvictionRunsMillis, perform validationQuery to detect whether the connection is valid-> <property name="testWhileIdle" value="true" /> <!--Execute validationQuery to detect whether the connection is valid when applying for a connection. This configuration will reduce performance. --> <property name="testOnBorrow" value="true" /> <!--Execute validationQuery when returning the connection to check whether the connection is valid. Doing this configuration will reduce 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"> <property name="dataSource" ref="multipleDataSource"/> </bean> <!-- Multi-Data Source--> <bean id="multipleDataSource"> <property name="defaultTargetDataSource" ref="defaultDataSource"/> <property name="targetDataSources"> <map> <entry key="defaultDataSource" value-ref="defaultDataSource"/> </map> </property> </bean> <!-- Annotation Transaction Manager--> <!-- The order value here must be greater than the order value of DynamicDataSourceAspectAdvice--> <tx:annotation-driven transaction-manager="txManager" order="2"/> <!-- Create SqlSessionFactory and specify the data source--> <bean id="mainSqlSessionFactory"> <property name="dataSource" ref="multipleDataSource"/> </bean> <!-- The package name where the DAO interface is located, Spring will automatically find the DAO under it --> <bean id="mainSqlMapper"> <property name="sqlSessionFactoryBeanName" value="mainSqlSessionFactory"/> <property name="basePackage" value="abc*.dao"/> </bean> <bean id="defaultSqlSessionFactory"> <property name="dataSource" ref="defaultDataSource"/> </bean> <bean id="defaultSqlMapper"> <property name="sqlSessionFactoryBeanName" value="defaultSqlSessionFactory"/> <property name="basePackage" value="abcbase.dal.dao"/> </bean> <!-- Other configuration omitted-->DynamicDataSourceAspectAdvice
Automatically switch data sources using AOP for reference only;
@Slf4j@Aspect@Component@Order(1) // Please note: the order here must be less than the order of tx:annotation-driven, that is, first execute the DynamicDataSourceAspectAdvice section, and then execute the transaction section to obtain the final data source @EnableAspectJAutoProxy(proxyTargetClass = true)public class DynamicDataSourceAspectAdvice { @Around("execution(* abc*.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 into the tenant header, otherwise 400 will be 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(); return result; }}Summarize
The above is the implementation method of Spring dynamic registration of multiple data sources introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to everyone in time. Thank you very much for your support to Wulin.com website!