springboot实现多数据源动态切换(Druid连接池)
1:引入依赖
连接池
切面
2:配置yml
spring:
datasource:
druid:
db1:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
username: root
password: root
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall’用于防火墙,此处是filter修改的地方
filters:
commons-log.connection-logger-name: stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
useGlobalDataSourceStat: true
db2:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/dcmonitor?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
username: root
password: root
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall’用于防火墙,此处是filter修改的地方
filters:
commons-log.connection-logger-name: stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
useGlobalDataSourceStat: true
3:配置数据源
/**
• 多数据源配置类 配置了两个数据源
/
@Configuration
public class DataSourceConfig {
/*• 从配置文件配置数据源
• @return
*/
@Bean(name = “datasource1”)
@ConfigurationProperties(prefix = “spring.datasource.druid.db1”) // application.properteis中对应属性的前缀
public DataSource dataSource1() {
// return DataSourceBuilder.create().build();
return new DruidDataSource();
}/**
• 从配置文件配置数据源
• @return
*/
@Bean(name = “datasource2”)
@ConfigurationProperties(prefix = “spring.datasource.druid.db2”) // application.properteis中对应属性的前缀
public DataSource dataSource2() {
// return DataSourceBuilder.create().build();
return new DruidDataSource();
}
@Bean(name = “datasource3”)
@ConfigurationProperties(prefix = “spring.datasource.druid.db3”) // application.properteis中对应属性的前缀
public DataSource dataSource3() {
// return DataSourceBuilder.create().build();
return new DruidDataSource();
}/**
• 动态数据源 进行数据源的切换
• @return
*/
@Primary
@Bean(name = “dynamicDataSource”)
public DataSource dynamicDataSource() {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
// 默认数据源
dynamicDataSource.setDefaultTargetDataSource(dataSource1());
// 配置多数据源
Map<Object, Object> dsMap = new HashMap();
dsMap.put(“datasource1”, dataSource1());
dsMap.put(“datasource2”, dataSource2());
dsMap.put(“datasource3”, dataSource3());
//将多数据源添加到数据源池中
dynamicDataSource.setTargetDataSources(dsMap);
return dynamicDataSource;
}/**
• 通过动态数据源配置sqlsessionfactory
• @param dynamicDataSource
• @return
• @throws Exception
/
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier(“dynamicDataSource”) DataSource dynamicDataSource) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dynamicDataSource);
factoryBean.setTypeAliasesPackage(“com.trs.bigscreen.mapper”);
factoryBean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:mapping/.xml"));
return factoryBean.getObject();
}/**
• 通过动态数据源配置sqlsessionTemplate
• @param sqlSessionFactory
• @return
• @throws Exception
*/
@Bean
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}/**
• 配置事务管理器
• @param dynamicDataSource
• @return
*/
@Bean
public DataSourceTransactionManager transactionManager(@Qualifier(“dynamicDataSource”) DataSource dynamicDataSource){
return new DataSourceTransactionManager(dynamicDataSource);}
}
4:创建动态数据源获取类
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
System.out.println(“数据源为”+DataSourceContextHolder.getDB());
return DataSourceContextHolder.getDB();
}
}
5:创建数据源管理切换类
public class DataSourceContextHolder {
/**
• 默认数据源
*/
public static final String DEFAULT_DS = “datasource1”;private static final ThreadLocal contextHolder = new ThreadLocal<>();
// 设置数据源名
public static void setDB(String dbType) {
System.out.println(“切换到{”+dbType+"}数据源");
contextHolder.set(dbType);
}// 获取数据源名
public static String getDB() {
return (contextHolder.get());
}// 清除数据源名
public static void clearDB() {
contextHolder.remove();
}
}
6:Druid配置监控
//Druid监控配置
@Configuration
public class DruidMoniterConfig {
//1、配置一个管理后台的Servlet
@Bean
public ServletRegistrationBean statViewServlet() {
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), “/druid/*”);
Map<String, String> initParams = new HashMap<>();
initParams.put(“loginUsername”, “admin”);
initParams.put(“loginPassword”, “admin”);
initParams.put(“allow”, “”);//默认就是允许所有访问
// initParams.put(“deny”, “192.168.15.21”);//黑名单的IPbean.setInitParameters(initParams);
return bean;}
//2、配置一个web监控的filter
@Bean
public FilterRegistrationBean webStatFilter() {
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());Map<String, String> initParams = new HashMap<>();
initParams.put("exclusions", "*.js,*.css,/druid/*");
bean.setInitParameters(initParams);
bean.setUrlPatterns(Arrays.asList("/*"));
return bean;}
}
7:定义注解用于指定数据源
/**
• 数据源切换注解
/
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD,ElementType.TYPE})
public @interface DS {
String value() default “datasource1”;
}
8:定义切面进行动态切换
/*- 动态数据源切面 */ @Order(0) // Spring的事务与数据源是绑定的。也就说,如果你开启了事务,那么数据源已经绑定了。那么这个时候,你在去切换数据源就无效了。也就是说要想有效,那么久要在事务开启之前就把数据源切换好。
@Aspect
@Component
public class DynamicDataSourceAspect {
@Before("@annotation(ds)")
public void beforeSwitchDS(JoinPoint point,DS ds){
System.out.println(“进入切面了…”);
//获得当前访问的class
Class<?> className = point.getTarget().getClass();
//获得访问的方法名
String methodName = point.getSignature().getName();
//得到方法的参数的类型
Class[] argClass = ((MethodSignature)point.getSignature()).getParameterTypes();
String dataSource = DataSourceContextHolder.DEFAULT_DS;
try {
// 得到访问的方法对象
Method method = className.getMethod(methodName, argClass);
// 判断是否存在@DS注解
if (method.isAnnotationPresent(DS.class)) {
DS annotation = method.getAnnotation(DS.class);
// 取出注解中的数据源名
dataSource = annotation.value();
}
} catch (Exception e) {
e.printStackTrace();
}
// 切换数据源
DataSourceContextHolder.setDB(dataSource);
}
@After("@annotation(com.trs.bigscreen.annotation.DS)")
public void afterSwitchDS(JoinPoint point){
DataSourceContextHolder.clearDB();
}
}
9:用法