多数据源动态切换 自定义注解方式

1、通过切面的方式进行数据源的切换；切的是 注解@DataSoure；@Pointcut("@annotation(com.manage.common.annotation.DataSource)"             + "|| @within(com.manage.common.annotation.DataSource)")

2、然后切面获取当前的方法的注解中所要切换的数据库

3、设置数据源的变量ThreadLocal<String> CONTEXT_HOLDER.set(dsType); （

什么是ThreadLocal　　

ThreadLocal是java中的线程本地变量，用于每个线程保存自己的变量，使线程间的变量互不干扰。

使用ThreadLocal维护变量，ThreadLocal为每个使用该变量的线程提供独立的变量副本，

所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。

）

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

1.定义DynamicDataSource类继承抽象类AbstractRoutingDataSource，并实现了determineCurrentLookupKey()方法。

2.把配置的多个数据源会放在AbstractRoutingDataSource的 targetDataSources和defaultTargetDataSource中，然后通过afterPropertiesSet()方法将数据源分别进行复制到resolvedDataSources和resolvedDefaultDataSource中。

3.调用AbstractRoutingDataSource的getConnection()的方法的时候，先调用determineTargetDataSource()方法返回DataSource在进行getConnection()。  

数据源配置 这里用的是postgresql

# 数据源配置 spring: datasource: type: com.alibaba.druid.pool.DruidDataSource driverClassName: org.postgresql.Driver druid: # 主库数据源 system: url: jdbc:postgresql://192....207:12004/db_system?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT+8 username: postgres password: DB$zs#H9KhA$t.....z%THI%q4^PYS # 从库数据源 template: # 从数据源开关/默认关闭/captchaImage enabled: true url: jdbc:postgresql://192....207:12004/db_work_template?autoReconnect=true&&useSSL=true username: postgres password: DB$zs#H9KhA$t.....z%THI%q4^PYS # 从库数据源 log: # 从数据源开关/默认关闭/captchaImage enabled: true url: jdbc:postgresql://192....207:12004/db_work_log?autoReconnect=true&&useSSL=true username: postgres password: DB$zs#H9KhA$t.....z%THI%q4^PYS # 从库数据源 event: # 从数据源开关/默认关闭/captchaImage enabled: true url: jdbc:postgresql://192....207:12004/db_work_event?autoReconnect=true&&useSSL=true username: postgres password: DB$zs#H9KhA$t.....z%THI%q4^PYS # 从库数据源 statis: # 从数据源开关/默认关闭/captchaImage enabled: true url: jdbc:postgresql://192....207:12004/db_work_statis?autoReconnect=true&&useSSL=true username: postgres password: DB$zs#H9KhA$t.....z%THI%q4^PYS # 从库数据源 allelism: # 从数据源开关/默认关闭/captchaImage enabled: true url: jdbc:postgresql://192....207:12004/db_allelism?autoReconnect=true&&useSSL=true username: postgres password: DB$zs#H9KhA$t.....z%THI%q4^PYS # 从库数据源 activiti: # 从数据源开关/默认关闭/captchaImage enabled: true url: jdbc:postgresql://192....207:12004/db_activiti?autoReconnect=true&&useSSL=true username: postgres password: DB$zs#H9KhA$t.....z%THI%q4^PYS # 初始连接数 initialSize: 5 # 最小连接池数量 minIdle: 10 # 最大连接池数量 maxActive: 20 # 配置获取连接等待超时的时间 maxWait: 60000 # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 timeBetweenEvictionRunsMillis: 60000 # 配置一个连接在池中最小生存的时间，单位是毫秒 minEvictableIdleTimeMillis: 300000 # 配置一个连接在池中最大生存的时间，单位是毫秒 maxEvictableIdleTimeMillis: 900000 # 配置检测连接是否有效 validationQuery: SELECT 1 testWhileIdle: true testOnBorrow: false testOnReturn: false webStatFilter: enabled: true statViewServlet: enabled: true # 设置白名单，不填则允许所有访问 allow: url-pattern: /druid/* # 控制台管理用户名和密码 login-username: login-password: filter: stat: enabled: true # 慢SQL记录 log-slow-sql: true slow-sql-millis: 1000 merge-sql: true wall: config: multi-statement-allow: true

配置多数据源

import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder; import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties; import com.alibaba.druid.util.Utils; import com.manage.common.enums.DataSourceType; import com.manage.framework.config.properties.DruidProperties; import com.manage.common.datasource.DynamicDataSource; import com.manage.util.spring.SpringUtils; import org.activiti.spring.SpringAsyncExecutor; import org.activiti.spring.SpringProcessEngineConfiguration; import org.activiti.spring.boot.AbstractProcessEngineAutoConfiguration; import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.web.servlet.FilterRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.transaction.PlatformTransactionManager; import javax.servlet.*; import javax.sql.DataSource; import java.io.IOException; import java.util.HashMap; import java.util.Map; /** * druid 配置多数据源 * * @author xxx */ @Configuration public class DruidConfig extends AbstractProcessEngineAutoConfiguration { @Bean @ConfigurationProperties("spring.datasource.druid.system") public DataSource systemDataSource(DruidProperties druidProperties) { DruidDataSource dataSource = DruidDataSourceBuilder.create().build(); return druidProperties.dataSource(dataSource); } @Bean @ConfigurationProperties("spring.datasource.druid.activiti") @ConditionalOnProperty(prefix = "spring.datasource.druid.activiti", name = "enabled", havingValue = "true") public DataSource activitiDataSource(DruidProperties druidProperties) { DruidDataSource dataSource = DruidDataSourceBuilder.create().build(); return druidProperties.dataSource(dataSource); } @Bean @ConfigurationProperties("spring.datasource.druid.allelism") @ConditionalOnProperty(prefix = "spring.datasource.druid.allelism", name = "enabled", havingValue = "true") public DataSource allelismDataSource(DruidProperties druidProperties) { DruidDataSource dataSource = DruidDataSourceBuilder.create().build(); return druidProperties.dataSource(dataSource); } @Bean @ConfigurationProperties("spring.datasource.druid.template") @ConditionalOnProperty(prefix = "spring.datasource.druid.template", name = "enabled", havingValue = "true") public DataSource templateDataSource(DruidProperties druidProperties) { DruidDataSource dataSource = DruidDataSourceBuilder.create().build(); return druidProperties.dataSource(dataSource); } @Bean @ConfigurationProperties("spring.datasource.druid.log") @ConditionalOnProperty(prefix = "spring.datasource.druid.log", name = "enabled", havingValue = "true") public DataSource logDataSource(DruidProperties druidProperties) { DruidDataSource dataSource = DruidDataSourceBuilder.create().build(); return druidProperties.dataSource(dataSource); } @Bean @ConfigurationProperties("spring.datasource.druid.event") @ConditionalOnProperty(prefix = "spring.datasource.druid.event", name = "enabled", havingValue = "true") public DataSource eventDataSource(DruidProperties druidProperties) { DruidDataSource dataSource = DruidDataSourceBuilder.create().build(); return druidProperties.dataSource(dataSource); } @Bean @ConfigurationProperties("spring.datasource.druid.statis") @ConditionalOnProperty(prefix = "spring.datasource.druid.statis", name = "enabled", havingValue = "true") public DataSource statisDataSource(DruidProperties druidProperties) { DruidDataSource dataSource = DruidDataSourceBuilder.create().build(); return druidProperties.dataSource(dataSource); } @Bean(name = "dynamicDataSource") @Primary public DynamicDataSource dataSource(DataSource systemDataSource) { Map<Object, Object> targetDataSources = new HashMap<>(); targetDataSources.put(DataSourceType.SYSTEM.name(), systemDataSource); setDataSource(targetDataSources, DataSourceType.ACTIVITI.name(), "activitiDataSource"); setDataSource(targetDataSources, DataSourceType.ALLELISM.name(), "allelismDataSource"); setDataSource(targetDataSources, DataSourceType.TEMPLATE.name(), "templateDataSource"); setDataSource(targetDataSources, DataSourceType.LOG.name(), "logDataSource"); setDataSource(targetDataSources, DataSourceType.EVENT.name(), "eventDataSource"); setDataSource(targetDataSources, DataSourceType.STATIS.name(), "statisDataSource"); return new DynamicDataSource(systemDataSource, targetDataSources); } /** * 设置数据源 * * @param targetDataSources 备选数据源集合 * @param sourceName 数据源名称 * @param beanName bean名称 */ public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName) { try { DataSource dataSource = SpringUtils.getBean(beanName); targetDataSources.put(sourceName, dataSource); } catch (Exception e) { } } @Bean public SpringProcessEngineConfiguration springProcessEngineConfiguration( PlatformTransactionManager transactionManager, SpringAsyncExecutor springAsyncExecutor) throws IOException { return baseSpringProcessEngineConfiguration( SpringUtils.getBean("activitiDataSource"), transactionManager, springAsyncExecutor); } /** * 去除监控页面底部的广告 */ @SuppressWarnings({"rawtypes", "unchecked"}) @Bean @ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true") public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties) { // 获取web监控页面的参数 DruidStatProperties.StatViewServlet config = properties.getStatViewServlet(); // 提取common.js的配置路径 String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*"; String commonJsPattern = pattern.replaceAll("\\*", "js/common.js"); final String filePath = "support/http/resources/js/common.js"; // 创建filter进行过滤 Filter filter = new Filter() { public void init(FilterConfig filterConfig) throws ServletException { } @Override public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException { chain.doFilter(request, response); // 重置缓冲区，响应头不会被重置 response.resetBuffer(); // 获取common.js String text = Utils.readFromResource(filePath); // 正则替换banner, 除去底部的广告信息 text = text.replaceAll("<a.*?banner\"></a><br/>", ""); text = text.replaceAll("powered.*?shrek.wang</a>", ""); response.getWriter().write(text); } @Override public void destroy() { } }; FilterRegistrationBean registrationBean = new FilterRegistrationBean(); registrationBean.setFilter(filter); registrationBean.addUrlPatterns(commonJsPattern); return registrationBean; } }

自定义注解

import java.lang.annotation.*; /** * 自定义多数据源切换注解 * * @author xxx */ @Target({ ElementType.METHOD, ElementType.TYPE }) @Retention(RetentionPolicy.RUNTIME) @Documented @Inherited public @interface DataSource { /** * 切换数据源名称 */ public DataSourceType value() default DataSourceType.SYSTEM; }

数据源 枚举

/** * 数据源 * * @author xxx */ public enum DataSourceType { /** * 主库 */ SYSTEM, /** * 从库 */ ACTIVITI, /** * 从库 */ ALLELISM, /** * 从库 */ TEMPLATE, /** * 从库 */ LOG, /** * 从库 * */ EVENT, /** * 从库 * */ STATIS, }

多数据源处理 AOP面向切面

import com.manage.common.annotation.DataSource; import com.manage.common.datasource.DynamicDataSourceContextHolder; import com.manage.util.StringUtils; 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.aspectj.lang.reflect.MethodSignature; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.core.annotation.Order; import org.springframework.stereotype.Component; import java.lang.reflect.Method; /** * 多数据源处理 * * @author xxx */ @Aspect @Order(1) @Component public class DataSourceAspect { protected Logger logger = LoggerFactory.getLogger(getClass()); //PointCut是指哪些方法需要被执行"AOP" @Pointcut("@annotation(com.manage.common.annotation.DataSource)" + "|| @within(com.manage.common.annotation.DataSource)") public void dsPointCut() { } @Around("dsPointCut()") public Object around(ProceedingJoinPoint point) throws Throwable { DataSource dataSource = getDataSource(point); if (StringUtils.isNotNull(dataSource)) { DynamicDataSourceContextHolder.setDataSourceType(dataSource.value().name()); } try { return point.proceed(); } finally { // 销毁数据源 在执行方法之后 //---------------------------------务必注意--------------------------------- // 因为执行完之后就需要销毁数据源。所以在一个service中执行多次数据库操作。需要手 // 动进行数据库切换 // 例如 这种情况就需要进行手动切换数据库 // List<User> userList = UserService.selectUserList(User); //DynamicDataSourceContextHolder.setDataSourceType(DataSourceType.ACTIVITI.name()); //List<ActIdUser> idUserList = actIdUserMapper.selectActIdUserList(null); DynamicDataSourceContextHolder.clearDataSourceType(); } } /** * 获取需要切换的数据源 */ public DataSource getDataSource(ProceedingJoinPoint point) { MethodSignature signature = (MethodSignature) point.getSignature(); Class<? extends Object> targetClass = point.getTarget().getClass(); DataSource targetDataSource = targetClass.getAnnotation(DataSource.class); if (StringUtils.isNotNull(targetDataSource)) { return targetDataSource; } else { Method method = signature.getMethod(); DataSource dataSource = method.getAnnotation(DataSource.class); return dataSource; } } }

讲解：

获取当前方法

ProceedingJoinPoint point.getTarget().getClass();

targetClass.getAnnotation(DataSource.class)

获取当前注解

动态数据源

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import javax.sql.DataSource; import java.util.Map; /** * 动态数据源 * * @author xxx */ public class DynamicDataSource extends AbstractRoutingDataSource { public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) { super.setDefaultTargetDataSource(defaultTargetDataSource); super.setTargetDataSources(targetDataSources); super.afterPropertiesSet(); } @Override protected Object determineCurrentLookupKey() { return DynamicDataSourceContextHolder.getDataSourceType(); } }

数据源切换处理

手动切换数据源 DynamicDataSourceContextHolder.setDataSourceType(DataSourceType.ACTIVITI.name());

import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * 数据源切换处理 * * @author xxx */ public class DynamicDataSourceContextHolder { public static final Logger log = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class); /** * 使用ThreadLocal维护变量，ThreadLocal为每个使用该变量的线程提供独立的变量副本， * 所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。 */ private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>(); /** * 设置数据源的变量 */ public static void setDataSourceType(String dsType) { log.info("切换到{}数据源", dsType); CONTEXT_HOLDER.set(dsType); } /** * 获得数据源的变量 */ public static String getDataSourceType() { return CONTEXT_HOLDER.get(); } /** * 清空数据源变量 */ public static void clearDataSourceType() { CONTEXT_HOLDER.remove(); } }