Spring Boot + Mybatis 整合Mysql ，SQLServer数据源以及整合druid，动态调整数据源切换。_sql server 动态数据源

pom.xml依赖

<dependencies>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-web</artifactId>
	</dependency>
	<dependency>
		<groupId>org.mybatis.spring.boot</groupId>
		<artifactId>mybatis-spring-boot-starter</artifactId>
		<version>1.3.2</version>
	</dependency>
 
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<scope>runtime</scope>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-test</artifactId>
		<scope>test</scope>
	</dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-aop</artifactId>
	</dependency>
</dependencies>

配置文件application.yml

datasource:
  mysql:
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&useSSL=false&characterEncoding=utf8
      username: root
      password: root
      initialSize: 1
      minIdle: 3
      maxActive: 20
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 30000
      validationQuery: select 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,slf4j
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      # 合并多个DruidDataSource的监控数据
      #useGlobalDataSourceStat: true
  sqlserver:
      url: jdbc:sqlserver://127.0.0.1:1433;databasename=test
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
      username: lc0019999
      password: Xtgly2018

创建一个数据源类型枚举类

public enum DataSourceType {
	Mysql("mysql"),
	SQLServer("sqlserver");
 
	private String name;
 
	DataSourceType(String name) {
		this.name = name;
	}
 
	public String getName() {
		return name;
	}
 
	public void setName(String name) {
		this.name = name;
	}
}

数据上下文

public class JdbcContextHolder {
 
	private final static ThreadLocal<String> local = new ThreadLocal<>();
 
	public static void putDataSource(String name){
		local.set(name);
	}
 
	public static String getDataSource(){
		return local.get();
	}
}

AOP数据源切换

@Aspect
@Order(2)
@Component
public class DataSourceAspect {
	private Logger logger = LoggerFactory.getLogger(this.getClass());
 
	//切点
	@Pointcut("execution(* com.test.*.service..*(..)))")
	public void aspect(){
		System.out.println("aspect");
	}
	@Before("aspect()")
	private void before(JoinPoint joinPoint){
		Object target = joinPoint.getTarget();
		String method = joinPoint.getSignature().getName();
		Class<?> classz = target.getClass();
		Class<?>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes();
		try {
			Method m = classz.getMethod(method,parameterTypes);
			if (m != null && m.isAnnotationPresent(MyDataSource.class)){
				MyDataSource data = m.getAnnotation(MyDataSource.class);
				JdbcContextHolder.putDataSource(data.value().getName());
				logger.info("===============上下文赋值完成:{}"+data.value().getName());
			}
		}catch (Exception e){
			e.printStackTrace();
		}
	}
}

自定义注解mydatasource

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface MyDataSource {
	DataSourceType value() default DataSourceType.Mysql;
}

数据库配置

@SuppressWarnings("AlibabaRemoveCommentedCode")
@Configuration
public class DataSourceConfig {
 
 
	private Logger logger = LoggerFactory.getLogger(this.getClass());
 
 
	@Value("${datasource.mysql.url}")
	private String dbUrl;
 
	@Value("${datasource.mysql.username}")
	private String username;
 
	@Value("${datasource.mysql.password}")
	private String password;
 
	@Value("${datasource.mysql.driverClassName}")
	private String driverClassName;
 
	@Value("${datasource.mysql.initialSize}")
	private int initialSize;
 
	@Value("${datasource.mysql.minIdle}")
	private int minIdle;
 
	@Value("${datasource.mysql.maxActive}")
	private int maxActive;
 
	@Value("${datasource.mysql.maxWait}")
	private int maxWait;
 
	@Value("${datasource.mysql.timeBetweenEvictionRunsMillis}")
	private int timeBetweenEvictionRunsMillis;
 
	@Value("${datasource.mysql.minEvictableIdleTimeMillis}")
	private int minEvictableIdleTimeMillis;
 
	@Value("${datasource.mysql.validationQuery}")
	private String validationQuery;
 
	@Value("${datasource.mysql.testWhileIdle}")
	private boolean testWhileIdle;
 
	@Value("${datasource.mysql.testOnBorrow}")
	private boolean testOnBorrow;
 
	@Value("${datasource.mysql.testOnReturn}")
	private boolean testOnReturn;
 
	@Value("${datasource.mysql.poolPreparedStatements}")
	private boolean poolPreparedStatements;
 
	@Value("${datasource.mysql.maxPoolPreparedStatementPerConnectionSize}")
	private int maxPoolPreparedStatementPerConnectionSize;
 
	@Value("${datasource.mysql.filters}")
	private String filters;
 
	@Value("{datasource.mysql.connectionProperties}")
	private String connectionProperties;
 
	@Bean(name="mysql")
	@ConfigurationProperties(prefix = "datasource.mysql")
	public DataSource dataSourceMysql(){
		System.out.println("主配"+dbUrl);
		DruidDataSource datasource = new DruidDataSource();
		datasource.setUrl(dbUrl);
		datasource.setUsername(username);
		datasource.setPassword(password);
		datasource.setDriverClassName(driverClassName);
		datasource.setInitialSize(initialSize);
		datasource.setMinIdle(minIdle);
		datasource.setMaxActive(maxActive);
		datasource.setMaxWait(maxWait);
		datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
		datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
		datasource.setValidationQuery(validationQuery);
		datasource.setTestWhileIdle(testWhileIdle);
		datasource.setTestOnBorrow(testOnBorrow);
		datasource.setTestOnReturn(testOnReturn);
		datasource.setPoolPreparedStatements(poolPreparedStatements);
		datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
		try {
			datasource.setFilters(filters);
		} catch (SQLException e) {
			logger.error("druid configuration initialization filter", e);
		}
		datasource.setConnectionProperties(connectionProperties);
		return datasource;
	}
 
	@Value("${datasource.sqlserver.url}")
	private String sqlServerUrl;
 
	@Value("${datasource.sqlserver.username}")
	private String sqlServerUsername;
 
	@Value("${datasource.sqlserver.password}")
	private String sqlServerPassword;
 
	@Value("${datasource.sqlserver.driverClassName}")
	private String sqlServerDriverClassName;
 
	@Bean(name="sqlserver")
	@ConfigurationProperties(prefix = "datasource.sqlserver")
	public DataSource dataSourceSQLServer(){
		System.out.println("次配"+sqlServerUrl);
		DruidDataSource datasource = new DruidDataSource();
		datasource.setUrl(sqlServerUrl);
		datasource.setUsername(sqlServerUsername);
		datasource.setPassword(sqlServerPassword);
		datasource.setDriverClassName(sqlServerDriverClassName);
		datasource.setInitialSize(initialSize);
		return datasource;
	}
 
 
	@Bean(name = "dynamicDataSource")
	@Primary  //优先使用，多数据源
	public DataSource dataSource(){
		DynamicDataSource dynamicDataSource = new DynamicDataSource();
		DataSource mysql = dataSourceMysql();
		DataSource sqlServer = dataSourceSQLServer();
		//设置默认数据源
		dynamicDataSource.setDefaultTargetDataSource(mysql);
		//配置多个数据源
		Map<Object,Object> map = new HashMap<>();
		map.put(DataSourceType.Mysql.getName(),mysql);
		map.put(DataSourceType.SQLServer.getName(),sqlServer);
		dynamicDataSource.setTargetDataSources(map);
		return dynamicDataSource;
	}
 
	@Bean(name="druidServlet")
	public ServletRegistrationBean druidServlet() {
		ServletRegistrationBean reg = new ServletRegistrationBean();
		reg.setServlet(new StatViewServlet());
		reg.addUrlMappings("/druid/*");
		reg.addInitParameter("allow", ""); //白名单
		return reg;
	}
 
	@Bean(name = "filterRegistrationBean")
	public FilterRegistrationBean filterRegistrationBean() {
		FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
		filterRegistrationBean.setFilter(new WebStatFilter());
		filterRegistrationBean.addUrlPatterns("/*");
		filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
		filterRegistrationBean.addInitParameter("profileEnable", "true");
		filterRegistrationBean.addInitParameter("principalCookieName","USER_COOKIE");
		filterRegistrationBean.addInitParameter("principalSessionName","USER_SESSION");
		filterRegistrationBean.addInitParameter("DruidWebStatFilter","/*");
		return filterRegistrationBean;
	}
}