使用 AbstractRoutingDataSource 实现动态切换数据源

╰+哭是因爲堅強的太久メ 2022-10-26 14:18 128阅读 0赞

#### [源码][Link 1] ####

#### [参考文献][Link 2] ####

思路： 使用AbstractRoutingDataSource数据源（路由数据源）,为多数据源切换存在。

AbstractRoutingDataSource 会管理一个map \{路由键: 数据源\}。调用determineCurrentLookupKey方法。根据路由键切换数据源。

1.  创建一个静态Map来保存 所有的数据源key为租户id val为数据源

/** * 租户id, 数据源 */
 public static final Map<Object, Object> dataSourceMap = new ConcurrentHashMap<>();

1.  创建一个静态map 存放当前登录用户信息

/** * token, user 用户信息中包含租户id */
 public static final Map<String, User> userMap = new ConcurrentHashMap<>();

1.  重写AbstractRoutingDataSource

package com.dynamic.data.source.config;
 
 import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
 
 import javax.sql.DataSource;
 import java.util.Map;
 
 /** * 开发公司：联信 * 版权：联信 * * Annotation * * @author 刘志强 * @created Create Time: 2021/2/2 */
 public class DynamicDataSource extends AbstractRoutingDataSource { 
 /** * 确定目标数据源 * 如果不希望数据源在启动配置时就加载好，可以定制这个方法，从任何你希望的地方读取并返回数据源 * 比如从数据库、文件、外部接口等读取数据源信息，并最终返回一个DataSource实现类对象即可 */
 @Override
 protected DataSource determineTargetDataSource() { 
 return super.determineTargetDataSource();
 }
 /** * 确定当前路由键，会根据路由键查找对应的数据源 * 这里通过设置数据源Key值来切换数据，定制这个方法 */
 @Override
 protected Object determineCurrentLookupKey() { 
 // 从线程副本中获取当前数据源路由键
 return DynamicDataSourceContextHolder.getDataSourceKey();
 }
 /** * 设置默认数据源 * @param defaultDataSource */
 public void setDefaultDataSource(Object defaultDataSource) { 
 super.setDefaultTargetDataSource(defaultDataSource);
 }
 /** * 设置数据源 * @param dataSources */
 public void setDataSources(Map<Object, Object> dataSources) { 
 super.setTargetDataSources(dataSources);
 // 将数据源的 key 放到数据源上下文的 key 集合中，用于切换时判断数据源是否有效
 DynamicDataSourceContextHolder.addDataSourceKeys(dataSources.keySet());
 }
 }

1.  创建一个ThreadLocal contextHolder 存放当前线程路由

package com.dynamic.data.source.config;
 
 import java.util.ArrayList;
 import java.util.Collection;
 import java.util.List;
 
 /** * 开发公司：联信 * 版权：联信 * * Annotation * * @author 刘志强 * @created Create Time: 2021/2/2 */
 public class DynamicDataSourceContextHolder { 
 private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>() { 
 /** * 将 master 数据源的 key作为默认数据源的 key */
 @Override
 protected String initialValue() { 
 return "default";
 }
 };
 /** * 数据源的 key集合，用于切换时判断数据源是否存在 */
 public static List<Object> dataSourceKeys = new ArrayList<>();
 /** * 切换数据源 * @param key */
 public static void setDataSourceKey(String key) { 
 contextHolder.set(key);
 }
 /** * 获取数据源 * @return */
 public static String getDataSourceKey() { 
 return contextHolder.get();
 }
 /** * 重置数据源 */
 public static void clearDataSourceKey() { 
 contextHolder.remove();
 }
 /** * 判断是否包含数据源 * @param key 数据源key * @return */
 public static boolean containDataSourceKey(String key) { 
 return dataSourceKeys.contains(key);
 }
 /** * 添加数据源keys * @param keys * @return */
 public static boolean addDataSourceKeys(Collection<? extends Object> keys) { 
 return dataSourceKeys.addAll(keys);
 }
 
 }

1.  初始化默认数据源

package com.dynamic.data.source.config;
 
 import com.alibaba.druid.pool.DruidDataSource;
 import org.apache.ibatis.session.SqlSessionFactory;
 import org.mybatis.spring.SqlSessionFactoryBean;
 import org.mybatis.spring.SqlSessionTemplate;
 import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.beans.factory.annotation.Qualifier;
 import org.springframework.beans.factory.annotation.Value;
 import org.springframework.boot.context.properties.ConfigurationProperties;
 import org.springframework.boot.jdbc.DataSourceBuilder;
 import org.springframework.context.annotation.Bean;
 import org.springframework.context.annotation.Configuration;
 import org.springframework.context.annotation.Primary;
 import org.springframework.core.env.Environment;
 import org.springframework.core.io.ResourceLoader;
 import org.springframework.jdbc.datasource.DataSourceTransactionManager;
 
 import javax.sql.DataSource;
 import java.sql.SQLException;
 import java.util.Properties;
 
 /** * 开发公司：联信 * 版权：联信 * * Annotation * * @author 刘志强 * @created Create Time: 2021/2/2 */
 @Configuration
 public class MybatisConfig { 
 
 @Autowired
 private Environment env;
 
 @Value("${spring.datasource.url}")
 private String url;
 @Value("${spring.datasource.username}")
 private String username;
 @Value("${spring.datasource.password}")
 private String password;
 
 @Bean(name = "defaultDataSource")
 @Primary
 public DataSource defaultDataSource(@Qualifier("poolProperties") Properties poolProperties) throws SQLException { 
 DruidDataSource dataSource = DataSourceBuilder.create()
 // 数据库 连接池类型 如果不设置类型默认类型为 com.zaxxer.hikari.HikariDataSource
 .type(DruidDataSource.class)
 // 驱动
 .driverClassName("com.mysql.cj.jdbc.Driver")
 // 链接
 .url(url)
 // 账号
 .username(username)
 // 密码
 .password(password)
 .build();
 // 加载连接池配置信息
 dataSource.configFromPropety(poolProperties);
 // 初始化
 dataSource.init();
 // 初始化
 DataSourceAdmin.dataSourceMap.put("default",dataSource);
 return dataSource;
 }
 
 /** * 创建数据源路由 * * @return */
 @Bean("dynamicDataSource")
 public DataSource dynamicDataSource() throws SQLException { 
 DynamicDataSource dynamicDataSource = new DynamicDataSource();
 dynamicDataSource.setDefaultDataSource(defaultDataSource(poolProperties()));
 dynamicDataSource.setDefaultTargetDataSource(defaultDataSource(poolProperties()));
 dynamicDataSource.setDataSources(DataSourceAdmin.dataSourceMap);
 return dynamicDataSource;
 }
 
 
 @Bean(name = "sqlSessionFactory")
 @Primary
 public SqlSessionFactory sqlSessionFactory(
 @Qualifier("dynamicDataSource") DataSource dynamicDataSource,
 @Qualifier("mybatisData") MybatisProperties properties,
 ResourceLoader resourceLoader) throws Exception { 
 SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
 bean.setDataSource(dynamicDataSource);
 bean.setTypeAliasesPackage(properties.getTypeAliasesPackage());
 bean.setConfigLocation(resourceLoader.getResource(properties.getConfigLocation()));
 bean.setMapperLocations(properties.resolveMapperLocations());
 return bean.getObject();
 }
 
 @Bean(name = "sqlSessionTemplate")
 public SqlSessionTemplate sqlSessionTemplate(
 @Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory
 ) { 
 return new SqlSessionTemplate(sqlSessionFactory);
 }
 
 @Bean(name = "mybatisData")
 @ConfigurationProperties(prefix = "mybatis")
 @Primary
 public MybatisProperties mybatisProperties() { 
 MybatisProperties mybatisProperties = new MybatisProperties();
 return mybatisProperties;
 }
 
 @Bean(name = "dataSourceTransactionManager")
 public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dynamicDataSource") DataSource dynamicDataSource) { 
 return new DataSourceTransactionManager(dynamicDataSource);
 }
 
 @Bean("poolProperties")
 public Properties poolProperties() { 
 String prefixPool = "spring.datasource.pool.";
 Properties prop = new Properties();
 prop.put("druid.initialSize", env.getProperty(prefixPool + "initialSize", String.class));
 prop.put("druid.maxActive", env.getProperty(prefixPool + "maxActive", String.class));
 prop.put("druid.minIdle", env.getProperty(prefixPool + "minIdle", String.class));
 prop.put("druid.maxWait", env.getProperty(prefixPool + "maxWait", String.class));
 prop.put("druid.poolPreparedStatements", env.getProperty(prefixPool + "poolPreparedStatements", String.class));
 prop.put("druid.maxPoolPreparedStatementPerConnectionSize", env.getProperty(prefixPool + "maxPoolPreparedStatementPerConnectionSize", String.class));
 prop.put("druid.validationQuery", env.getProperty(prefixPool + "validationQuery", String.class));
 prop.put("druid.validationQueryTimeout", env.getProperty(prefixPool + "validationQueryTimeout", String.class));
 prop.put("druid.testOnBorrow", env.getProperty(prefixPool + "testOnBorrow", String.class));
 prop.put("druid.testOnReturn", env.getProperty(prefixPool + "testOnReturn", String.class));
 prop.put("druid.testWhileIdle", env.getProperty(prefixPool + "testWhileIdle", String.class));
 prop.put("druid.timeBetweenEvictionRunsMillis", env.getProperty(prefixPool + "timeBetweenEvictionRunsMillis", String.class));
 prop.put("druid.minEvictableIdleTimeMillis", env.getProperty(prefixPool + "minEvictableIdleTimeMillis", String.class));
 prop.put("druid.filters", env.getProperty(prefixPool + "filters", String.class));
 return prop;
 }
 }

1.  项目初始化 加载租户数据源

package com.dynamic.data.source.service.impl;
 
 import com.alibaba.druid.pool.DruidDataSource;
 import com.dynamic.data.source.config.DataSourceAdmin;
 import com.dynamic.data.source.config.DataSourceInfo;
 import com.dynamic.data.source.config.DynamicDataSource;
 import com.dynamic.data.source.domain.User;
 import com.dynamic.data.source.mapper.UserMapper;
 import com.dynamic.data.source.service.InitService;
 import lombok.extern.slf4j.Slf4j;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.beans.factory.annotation.Qualifier;
 import org.springframework.boot.jdbc.DataSourceBuilder;
 import org.springframework.stereotype.Service;
 
 import javax.sql.DataSource;
 import java.sql.SQLException;
 import java.util.List;
 import java.util.Properties;
 
 /**
 * 开发公司：联信
 * 版权：联信
 * 
 * Annotation
 *
 * @author 刘志强
 * @created Create Time: 2021/1/26
 */
 @Service
 @Slf4j
 public class InitServiceImpl implements InitService {
 
 @Autowired
 private UserMapper userMapper;
 @Autowired
 @Qualifier("poolProperties")
 private Properties poolProperties;
 @Autowired
 @Qualifier("dynamicDataSource")
 private DataSource dynamicDataSource;
 
 @Override
 public void initDataSource() {
 List<User> list = userMapper.getUserAll();
 list.forEach(user -> {
 DataSourceInfo dataSourceInfo = new DataSourceInfo();
 dataSourceInfo.setUserId(user.getId());
 
 StringBuilder url = new StringBuilder();
 url.append("jdbc:mysql://").append(user.getDatabaseIp())
 .append(":3306/")
 .append(user.getDatabaseName())
 .append("?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=round&useAffectedRows=true&useSSL=false");
 // 创建数据源
 DruidDataSource dataSource = DataSourceBuilder.create()
 // 数据库 连接池类型 如果不设置类型默认类型为 com.zaxxer.hikari.HikariDataSource
 .type(DruidDataSource.class)
 // 驱动
 .driverClassName("com.mysql.cj.jdbc.Driver")
 // 链接
 .url(url.toString())
 // 账号
 .username(user.getDatabaseAccount())
 // 密码
 .password(user.getDatabasePassword())
 .build();
 
 // 加载连接池配置信息
 dataSource.configFromPropety(poolProperties);
 try {
 log.info("租户数据源初始化{}",user.getUserName());
 dataSource.init();
 } catch (SQLException throwables) {
 throwables.printStackTrace();
 }
 DataSourceAdmin.dataSourceMap.put(String.valueOf(user.getId()),dataSource);
 });
 // 初始化数据源
 DynamicDataSource dataSource = (DynamicDataSource) dynamicDataSource;
 dataSource.setDataSources(DataSourceAdmin.dataSourceMap);
 dataSource.afterPropertiesSet();
 }
 }

1.  aop 拦截根据登录信息切换数据源

package com.dynamic.data.source.config.aop;
 
 import com.dynamic.data.source.config.DataSourceAdmin;
 import com.dynamic.data.source.config.DynamicDataSourceContextHolder;
 import com.dynamic.data.source.domain.User;
 import lombok.extern.slf4j.Slf4j;
 import org.aspectj.lang.ProceedingJoinPoint;
 import org.aspectj.lang.annotation.Around;
 import org.aspectj.lang.annotation.Aspect;
 import org.aspectj.lang.annotation.Pointcut;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.core.annotation.Order;
 import org.springframework.stereotype.Component;
 import org.springframework.util.StringUtils;
 
 import javax.servlet.http.HttpServletRequest;
 
 /**
 * 开发公司：联信
 * 版权：联信
 * 
 * Annotation
 *
 * @author 刘志强
 * @created Create Time: 2021/2/2
 */
 @Aspect
 @Component
 @Slf4j
 @Order(-1)
 public class DynamicDataSourceAspect {
 @Autowired
 private HttpServletRequest httpServletRequest;
 
 /**
 * 定义切点Pointcut
 */
 @Pointcut("execution(* com.dynamic.data.source.controller.*.*(..))")
 public void excudeService() {
 }
 
 /**
 * 环绕通知
 * @param pjp
 * @return
 * @throws Throwable
 */
 @Around("excudeService()")
 public Object doAround(ProceedingJoinPoint pjp) throws Throwable {
 log.info("----环绕通知----");
 
 String token = httpServletRequest.getHeader("Authorization");
 if (!StringUtils.isEmpty(token)) {
 User user = DataSourceAdmin.userMap.get(token);
 log.info("当前租户Id:{}", user.toString());
 DynamicDataSourceContextHolder.setDataSourceKey(String.valueOf(user.getId()));
 Object result = pjp.proceed();
 DynamicDataSourceContextHolder.clearDataSourceKey();
 return result;
 } else {
 DynamicDataSourceContextHolder.setDataSourceKey("default");
 Object result = pjp.proceed();
 DynamicDataSourceContextHolder.clearDataSourceKey();
 return result;
 }
 }
 
 }

1.  登录 保存token和user绑定信息

@Override
        public String login(String userName, String password) {
            User user = userMapper.getUserByUserNameAndPassword(userName,password);
            if (user != null) {
                String token = UUID.randomUUID().toString();
                DataSourceAdmin.userMap.put(token,user);
                return token;
            } else {
                return "无此用户";
            }
        }

[Link 1]: https://gitee.com/lzq199528/dynamic-data-source-two
[Link 2]: https://blog.csdn.net/qq_36521507/article/details/103452961