springboot dynamic multi data source configuration and usage (read data source configuration from database)

1, Review the dynamic multi data source configuration in the previous article

Part I: Spring boot dynamic multi data source configuration and use (2)

1. Inherit AbstractRoutingDataSource and override the abstract method determineCurrentLookupKey()

/** * Multiple data sources * */ public class DynamicDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DynamicContextHolder.peek(); } }

1. Inject spring container

@Bean public DynamicDataSource dynamicDataSource(DataSourceProperties dataSourceProperties) { DynamicDataSource dynamicDataSource = new DynamicDataSource(); //Set map s for multiple data sources dynamicDataSource.setTargetDataSources(getDynamicDataSource()) //Default data source DruidDataSource defaultDataSource = DynamicDataSourceFactory.buildDruidDataSource(dataSourceProperties); dynamicDataSource.setDefaultTargetDataSource(defaultDataSource); return dynamicDataSource; } private Map<Object, Object> getDynamicDataSource(){ Map<String, DataSourceProperties> dataSourcePropertiesMap = properties.getDatasource(); Map<Object, Object> targetDataSources = new HashMap<>(dataSourcePropertiesMap.size()); dataSourcePropertiesMap.forEach((k, v) -> { DruidDataSource druidDataSource = DynamicDataSourceFactory.buildDruidDataSource(v); targetDataSources.put(k, druidDataSource); }); return targetDataSources; }

1. You can see that the multi data source configuration is read from application.yml and set to the targetDataSources property in the DynamicDataSource object
   dynamicDataSource.setTargetDataSources();

2, Analysis

From the above analysis, we can see that the important thing is the map attribute of targetDataSources, which stores multiple data sources.
Then we just need to get the targetDataSources map from the configuration file, create a dataSource, put it into the map, rewrite it into the configuration taken out by data reading, create a dataSource, and then put it into the targetDataSources map variable to achieve the functions we want.

3, Get configuration from database and create data source

This step is neither difficult nor difficult. Save the database configuration in the database table, read the database configuration when the cutting class switches the data source, then create the data source, put the created data source into the targetDataSources map through the put method, and finally change the data source in the cutting class DynamicContextHolder.push (key)

However, it is inefficient to read the configuration from the database every time, and then create a dataSource data source. Therefore, in fact, we are lazy loading mode, and then use a data source cache pool to save the dataSource. If the cache has a dataSource, it will no longer be read from the database, and the data source will be obtained directly from the pool of the data source cache pool.

Concrete implementation

1. Create table

CREATE TABLE `oa_quick_knife_datasource` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `datasource_name` varchar(20) DEFAULT '' COMMENT 'Data source name', `datasource_url` varchar(200) DEFAULT '' COMMENT 'data source url', `datasource_account` varchar(50) DEFAULT '' COMMENT 'Data source account', `datasource_password` varchar(64) DEFAULT '' COMMENT 'Data source password', `remark` varchar(200) DEFAULT NULL COMMENT 'remarks', `is_show_type` tinyint(1) DEFAULT NULL COMMENT 'Data source visible type (1)-Visible to all, 2-(visible to some people)', `datasource_type` tinyint(1) DEFAULT NULL COMMENT 'default mysql，Only supported temporarily mysql', `update_name` varchar(20) DEFAULT '' COMMENT 'Updater', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time', `create_code` int(11) unsigned DEFAULT '0' COMMENT 'Create labor number', `create_name` varchar(20) DEFAULT '' COMMENT 'Creator', `update_code` int(11) unsigned DEFAULT '0' COMMENT 'Update labor number', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time', `deleted_flag` tinyint(1) DEFAULT '0' COMMENT 'Delete', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='data source ';

Note: datasource_ In the password field, we don't want to save the database password in clear text. We encrypt it and put it into this field

2. Data source cache pool

Class code of data source cache pool

/** * Data source cache pool */ public class DataSourceCachePool { /** Data source connection pool cache [local class cache - distributed is not supported] */ private static Map<String, DruidDataSource> dbSources = new HashMap<>(); private static RedisTemplate<String, Object> redisTemplate; private static RedisTemplate<String, Object> getRedisTemplate() { if (redisTemplate == null) { redisTemplate = (RedisTemplate<String, Object>) SpringContextUtils.getBean("redisTemplate"); } return redisTemplate; } /** * Get multi data source cache * * @param dbKey * @return */ public static DynamicDataSourceModel getCacheDynamicDataSourceModel(String dbKey) { String redisCacheKey = ConfigConstant.SYS_DYNAMICDB_CACHE + dbKey; if (getRedisTemplate().hasKey(redisCacheKey)) { String model = (String)getRedisTemplate().opsForValue().get(redisCacheKey); return JSON.parseObject(model,DynamicDataSourceModel.class); } DatasourceDao datasourceDao = (DatasourceDao)SpringContextUtils.getBean("datasourceDao"); DynamicDataSourceModel dbSource = datasourceDao.getDynamicDbSourceByCode(dbKey); try{ dbSource.setDbPassword(AesUtil.decryptBySalt(dbSource.getDbPassword(),dbSource.getId())); }catch (Exception e){ throw new RRException("Dynamic data source key decryption failed, dbKey: "+dbKey); } if (dbSource != null) { getRedisTemplate().opsForValue().set(redisCacheKey, JSONObject.toJSONString(dbSource)); } return dbSource; } public static DruidDataSource getCacheBasicDataSource(String dbKey) { return dbSources.get(dbKey); } /** * put Data source cache * * @param dbKey * @param db */ public static void putCacheBasicDataSource(String dbKey, DruidDataSource db) { dbSources.put(dbKey, db); } /** * Clear data source cache */ public static void cleanAllCache() { //Close data source connection for(Map.Entry<String, DruidDataSource> entry : dbSources.entrySet()){ String dbkey = entry.getKey(); DruidDataSource druidDataSource = entry.getValue(); if(druidDataSource!=null && druidDataSource.isEnable()){ druidDataSource.close(); } //Clear redis cache getRedisTemplate().delete(ConfigConstant.SYS_DYNAMICDB_CACHE + dbkey); } //wipe cache dbSources.clear(); } public static void removeCache(String dbKey) { //Close data source connection DruidDataSource druidDataSource = dbSources.get(dbKey); if(druidDataSource!=null && druidDataSource.isEnable()){ druidDataSource.close(); } //Clear redis cache getRedisTemplate().delete(ConfigConstant.SYS_DYNAMICDB_CACHE + dbKey); //wipe cache dbSources.remove(dbKey); } }

The main code of the above data source cache pool is the following section of the getCacheDynamicDataSourceModel method

The logic of this method is to configure the data source from the redis cache first. If redis does not, it will be obtained from the database, and the obtained configured database password is encrypted, so it needs to be decrypted here

/** * Get multi data source cache configuration * * @param dbKey * @return */ public static DynamicDataSourceModel getCacheDynamicDataSourceModel(String dbKey) { String redisCacheKey = ConfigConstant.SYS_DYNAMICDB_CACHE + dbKey; if (getRedisTemplate().hasKey(redisCacheKey)) { String model = (String)getRedisTemplate().opsForValue().get(redisCacheKey); return JSON.parseObject(model,DynamicDataSourceModel.class); } DatasourceDao datasourceDao = (DatasourceDao)SpringContextUtils.getBean("datasourceDao"); DynamicDataSourceModel dbSource = datasourceDao.getDynamicDbSourceByCode(dbKey); try{ dbSource.setDbPassword(AesUtil.decryptBySalt(dbSource.getDbPassword(),dbSource.getId())); }catch (Exception e){ throw new RRException("Dynamic data source key decryption failed, dbKey: "+dbKey); } if (dbSource != null) { getRedisTemplate().opsForValue().set(redisCacheKey, JSONObject.toJSONString(dbSource)); } return dbSource; }

Another important method is to put the data source into the map attribute dbSource of the cache pool

/** * put Data source cache * * @param dbKey * @param db */ public static void putCacheBasicDataSource(String dbKey, DruidDataSource db) { dbSources.put(dbKey, db); }

3. Write another tool class DynamicDBUtil to operate the data source cache pool

The core method of this class is getDbSourceByDbKey(). First judge whether the cache pool has a data source corresponding to the key. If not, read the data source configuration (read the configuration from redis first, and then read the configuration from the database). Create a DruidDataSource data source according to the configuration, and then put the data source into the cache pool

The dbKey of getDbSourceByDbKey method means that the corresponding record of the database can be found according to the key. Here, it refers to the id of the table

/** * Spring JDBC Real time database access * */ @Slf4j public class DynamicDBUtil { /** * Obtain the data source through dbKey * * @param dbKey * @return */ public static DruidDataSource getDbSourceByDbKey(final String dbKey) { //First determine whether there are database links in the cache DruidDataSource cacheDbSource = DataSourceCachePool.getCacheBasicDataSource(dbKey); if (cacheDbSource != null && !cacheDbSource.isClosed()) { log.debug("--------getDbSourceBydbKey------------------Get from cache DB connect-------------------"); return cacheDbSource; } else { //Get multi data source configuration DynamicDataSourceModel dbSource = DataSourceCachePool.getCacheDynamicDataSourceModel(dbKey); DruidDataSource dataSource = getJdbcDataSource(dbSource); if(dataSource!=null && dataSource.isEnable()){ DataSourceCachePool.putCacheBasicDataSource(dbKey, dataSource); }else{ throw new RRException("Dynamic data source connection failed, dbKey: "+dbKey); } log.info("--------getDbSourceBydbKey------------------establish DB Database connection-------------------"); return dataSource; } } /** * Get the data source [the lowest level method, do not call it casually] * * @param dbSource * @return */ private static DruidDataSource getJdbcDataSource(final DynamicDataSourceModel dbSource) { DruidDataSource dataSource = new DruidDataSource(); String driverClassName = dbSource.getDbDriver(); String url = dbSource.getDbUrl(); String dbUser = dbSource.getDbUsername(); String dbPassword = dbSource.getDbPassword(); dataSource.setDriverClassName(driverClassName); dataSource.setUrl(url); //dataSource.setValidationQuery("SELECT 1 FROM DUAL"); dataSource.setTestWhileIdle(true); dataSource.setTestOnBorrow(false); dataSource.setTestOnReturn(false); dataSource.setBreakAfterAcquireFailure(true); dataSource.setConnectionErrorRetryAttempts(0); dataSource.setUsername(dbUser); dataSource.setMaxWait(60000); dataSource.setPassword(dbPassword); log.info("******************************************"); log.info("* *"); log.info("*====["+dbSource.getCode()+"]=====Druid Connection pool enabled ====*"); log.info("* *"); log.info("******************************************"); return dataSource; } /** * Close database connection pool * * @param dbKey * @return */ public static void closeDbKey(final String dbKey) { DruidDataSource dataSource = getDbSourceByDbKey(dbKey); try { if (dataSource != null && !dataSource.isClosed()) { dataSource.getConnection().commit(); dataSource.getConnection().close(); dataSource.close(); } } catch (SQLException e) { e.printStackTrace(); } } private static JdbcTemplate getJdbcTemplate(String dbKey) { DruidDataSource dataSource = getDbSourceByDbKey(dbKey); return new JdbcTemplate(dataSource); } /** * Get connection * @param url * @param username * @param password * @param driverName * @return */ public static Connection getConn(String url,String username,String password,String driverName) { Connection conn = null; try { Class.forName(driverName); conn = DriverManager.getConnection(url, username, password); } catch (Exception e) { throw new RRException("Unable to connect, problem:"+e.getMessage(), e); } return conn; } /** * Close database connection * @param */ public static void closeConnection(Connection conn) { try { if(conn!=null){ conn.close(); } } catch (SQLException e) { throw new RRException("close connection failure", e); } } }

4. Inherit AbstractRoutingDataSource and override the abstract method determineCurrentLookupKey()

The targetDataSources static variable and setDataSource() static method are added here compared with the DynamicDataSource in the previous article

targetDataSources are used to point to

/** * Multiple data sources * */ public class DynamicDataSource extends AbstractRoutingDataSource { public static Map<Object, Object> targetDataSources = new ConcurrentHashMap<>(10); @Override protected Object determineCurrentLookupKey() { return DynamicContextHolder.peek(); } public static void setDataSource(String dbKey) throws Exception{ if(!DynamicDataSource.targetDataSources.containsKey(dbKey)){ DruidDataSource dataSource = DynamicDBUtil.getDbSourceByDbKey(dbKey); DynamicDataSource.targetDataSources.put(dbKey,dataSource); } //Switch dbKey of dynamic multi data source DynamicContextHolder.push(dbKey); DynamicDataSource dynamicDataSource = (DynamicDataSource) SpringContextUtils.getBean("dynamicDataSource"); dynamicDataSource.afterPropertiesSet(); } }

5. Data source configuration class

@Configuration @EnableConfigurationProperties(DynamicDataSourceProperties.class) public class DynamicDataSourceConfig { @Autowired private DynamicDataSourceProperties properties; @Bean @ConfigurationProperties(prefix = "spring.datasource.druid") public DataSourceProperties dataSourceProperties() { return new DataSourceProperties(); } @Bean public DruidDataSource defaultDataSource(DataSourceProperties dataSourceProperties) { //Default data source, obtained and created through configuration DruidDataSource defaultDataSource = DynamicDataSourceFactory.buildDruidDataSource(dataSourceProperties); return defaultDataSource; } @Bean @Primary @DependsOn({"defaultDataSource"}) public DynamicDataSource dynamicDataSource(DruidDataSource defaultDataSource) { DynamicDataSource dynamicDataSource = new DynamicDataSource(); //Set targetdatasources (obtained through database configuration, no data source is created for the first time) dynamicDataSource.setTargetDataSources(DynamicDataSource.targetDataSources); //Default data source dynamicDataSource.setDefaultTargetDataSource(defaultDataSource); return dynamicDataSource; } }

6. Just write another annotation and the facet class that implements the annotation

/** * Multi data source annotation * */ @Target() @Retention(RetentionPolicy.RUNTIME) @Documented @Inherited public @interface DataSource { String value() default ""; }

/** * Multiple data sources, facet processing class * */ @Aspect @Component @Order(Ordered.HIGHEST_PRECEDENCE) public class DataSourceAspect { protected Logger logger = LoggerFactory.getLogger(getClass()); @Pointcut("@annotation(io.renren.datasource.annotation.DataSource) " + "|| @within(io.renren.datasource.annotation.DataSource)") public void dataSourcePointCut() { } @Around("dataSourcePointCut()") public Object around(ProceedingJoinPoint point) throws Throwable { MethodSignature signature = (MethodSignature) point.getSignature(); Class targetClass = point.getTarget().getClass(); Method method = signature.getMethod(); DataSource targetDataSource = (DataSource)targetClass.getAnnotation(DataSource.class); DataSource methodDataSource = method.getAnnotation(DataSource.class); if(targetDataSource != null || methodDataSource != null){ String value; if(methodDataSource != null){ value = methodDataSource.value(); }else { value = targetDataSource.value(); } //Dynamically set the data source according to dbKey DynamicDataSource.setDataSource(dbKey); logger.debug("set datasource is {}", value); } try { return point.proceed(); } finally { DynamicContextHolder.poll(); logger.debug("clean datasource"); } } }

This step is completed, and then add the DataSource annotation to the class or method of the service to realize the multi data source specified by the operation.

4, Specify the data source through the input parameters of the interface

The value of the above annotation DataSource is written in the code, but we have such a requirement that the front end operates the data of the specified data source according to the interface input parameters.

So on the basis of the above, we will transform it again
Write two more notes

/** * Multi data source annotation - annotate the dbKey of the data source * * @author ZhangXinLin */ @Target() @Retention(RetentionPolicy.RUNTIME) @Documented @Inherited public @interface DbKey { }

The custom annotation dbkey is used to mark the parameter, which is used to specify the dbkey of the data source

/** * Multi data source annotation * * @author ZhangXinLin */ @Target() @Retention(RetentionPolicy.RUNTIME) @Documented @Inherited public @interface DynamicDataSource { }

The custom annotation of DynamicDataSource is used on the method of controller

Facet class of DynamicDataSource annotation

This aspect class dynamically switches the data source according to the input parameter dbKey of the method. The core code is to call this line of code
//Dynamically set the data source according to dbKey
DynamicDataSource.setDataSource(dbKey);

/** * @Description: Dynamically load multiple data sources (load after startup) **/ @Aspect @Component @Order(Ordered.HIGHEST_PRECEDENCE) public class DynamicDataSourceAspect { protected Logger logger = LoggerFactory.getLogger(getClass()); @Pointcut("@annotation(io.renren.datasource.annotation.DynamicDataSource) " + "|| @within(io.renren.datasource.annotation.DynamicDataSource)") public void dynamicdataSourcePointCut() { } @Around("dynamicdataSourcePointCut()") public Object around(ProceedingJoinPoint point) throws Throwable { //Obtain parameters, and obtain data sources according to parameters String dbKey = getDbKey(point); if( dbKey != null){ //Dynamically set the data source according to dbKey DynamicDataSource.setDataSource(dbKey); } try { return point.proceed(); } finally { DynamicContextHolder.poll(); logger.debug("clean datasource"); } } /** * Obtain the dbKey of the data source according to the @ dbKey annotation * @param point * @return */ private String getDbKey(ProceedingJoinPoint point) { MethodSignature signature = (MethodSignature) point.getSignature(); Method method = signature.getMethod(); Object[] args = point.getArgs(); String value = null; //Parameter annotation: dimension 1 is parameter and dimension 2 is annotation Annotation[][] annotations = method.getParameterAnnotations(); for (int i = 0; i < annotations.length; i++) { Object param = args[i]; Annotation[] paramAnn = annotations[i]; //If the parameter is empty, the next parameter will be directly if (param == null || paramAnn.length == 0) { continue; } for (Annotation pAnnotation : paramAnn) { if (pAnnotation.annotationType().equals(DbKey.class)) { value = param.toString(); break; } } } return value; } }

Then add the annotation @ DynamicDataSource on the controller's method, and add the annotation @ Dbkey

/** * View a list of all tables for the data source * @param id * @return */ @DynamicDataSource @RequestMapping("/getTableList/") public R getTableList(@PathVariable("id") @DbKey Integer id){ List<Map<String, Object>> list = datasourceService.queryTableList(id); return R.ok().put("list", list); }

Look at the actual effect

1. First, we rewrite a page by adding or deleting the data source of the table
   

1. Call the getTableList interface on the page to switch the data of the data source

It can be seen that when different databases are selected on the front-end page, the back-end interface will dynamically switch data sources according to the input parameters of dbKey, so as to query the table name list of different data sources

Source code

The source code is in the project of a rapid development platform that has not been written yet (the function can write templates online, configure data sources online, write development templates without changing the code, and generate the basic code of different systems);
This project has not been completed, and will be open-source after it is completed, so the source code here is not available for the time being

Add new comment

For adding a comment, please log in

Login or create account

0 comments