springboot+druid+mybatis注解方式实现多数据源数据监控实践

一、新建springboot(2.2.2.RELEASE)的maven项目，在pom中引入druid和mybatis的相关依赖包

        <!--alibaba-->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.22</version>
		</dependency>

		<!--mysql-->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.44</version>
		</dependency>


		<!--mybatis-->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.1</version>
		</dependency>

二、添加application.yml中的配置信息

#mybatis配置
mybatis:
  type-aliases-package: com.xxx.xxx.model.log.message.po
  mapper-locations: classpath*:com/xxx/xxx/orm/mybatis/xml/*/*.xml,classpath*:com/xxx/xxx/orm/mybatis/xml/*/*/*.xml
  configLocation: classpath:mybatis/mybatis-config.xml #mybatis配置文件的位置
#数据源及druid配置
spring:
  datasource:
    db1:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.1.xxx:3306/xxx?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&tinyInt1isBit=false&useOldAliasMetadataBehavior=true&testOnBorrow=true&allowMultiQueries=true
      username: xxx
      password: xxx
      defaultAutoCommit: false
      # 防止 emoji 表情无法保存
      connectionInitSqls: set names utf8mb4;
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 300000
      minEvictableIdleTimeMillis: 600000
      # 防止mysql 8小时空闲关闭
      testWhileIdle: true
      validationQuery: SELECT 1 FROM DUAL
      # 初始化大小，最小，最大·
      initialSize: 3
      maxActive: 18
      minIdle: 3
      autoReconnect: true
      filters: stat,wall,slf4j
    db2:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.1.xxx:3306/xxx?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&tinyInt1isBit=false&useOldAliasMetadataBehavior=true&testOnBorrow=true&allowMultiQueries=true
      username: xxx
      password: xxx
      defaultAutoCommit: false
      # 防止 emoji 表情无法保存
      connectionInitSqls: set names utf8mb4;
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 300000
      minEvictableIdleTimeMillis: 600000
      # 防止mysql 8小时空闲关闭
      testWhileIdle: true
      validationQuery: SELECT 1 FROM DUAL
      # 初始化大小，最小，最大·
      initialSize: 3
      maxActive: 18
      minIdle: 3
      autoReconnect: true
      filters: stat,wall,slf4j

三、在springboot入口类中禁止掉DataSourceAutoConfiguration.class

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)

四、增加druid多数据源的相关配置——多套数据源（还有动态数据源和参数化变更数据源）

1、新增注解类

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
    String name() default "";
}

2、配置druid数据源信息

@Configuration
public class DruidConfig {

    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource mysqlDataSource() {
        return new DruidDataSource();
    }
    @Bean(name = "db2lDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource db2lDataSource() {
        return new DruidDataSource();
    }

    @Bean
    @Primary
    public DynamicDataSource dataSource(DataSource mysqlDataSource, DataSource db2lDataSource) {
        Map<Object, Object> targetDataSource = new HashMap<>();
        targetDataSource.put(DataSourceConstants.DB1, mysqlDataSource);
        targetDataSource.put(DataSourceConstants.DB2, db2lDataSource);
        return new DynamicDataSource(mysqlDataSource, targetDataSource);
    }
}

3、使用本地线程获取数据源

public class DynamicDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return getDataSource();
    }

    public static void setDataSource(String dataSource){
        contextHolder.set(dataSource);
    }

    public static String getDataSource(){
        return  contextHolder.get();
    }

    public static void clearDataSource(){
        contextHolder.remove();
    }
}

4、配置切面实现数据源切换

@Slf4j
@Aspect
@Component
public class DataSourceAspect implements Ordered {


    @Pointcut("@annotation(com.xxx.xxx.core.annotation.DataSource)")
    public void dataSourcePointCut(){

    }

    @Around("dataSourcePointCut()")
    public Object around(ProceedingJoinPoint point)throws  Throwable{
        MethodSignature signature = (MethodSignature)point.getSignature();
        Method method = signature.getMethod();
        DataSource ds = method.getAnnotation(DataSource.class);
        if(ds == null){
            DynamicDataSource.setDataSource(DataSourceConstants.DB1);
            log.info("set mysql");
        }else{
            DynamicDataSource.setDataSource(ds.name());
            log.info("set datasource {}",ds.name());
        }

        try{
            return point.proceed();
        }finally {
            DynamicDataSource.clearDataSource();
            log.info("clear datasource {}",ds.name());
        }
    }

    @Override
    public int getOrder() {
        return 1;
    }
}

5、在DAO接口层切换数据源

加上我们自己编写的注解，并指定数据源名称即可，若未指定，则使用我们在切面中定义好的默认数据源，小伙伴们可以根据自己的需求来修改。

    @DataSource(name = DB2)
    SysOrgUser selectByPrimaryKey(Long id);

五、配置druid数据源监控页面

1、配置druid过滤器

@WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",
        initParams={
                @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")// 忽略资源
        }
)
public class DruidStatFilter extends WebStatFilter {
}

2、配置druid监控页面

@SuppressWarnings("serial")
@WebServlet(urlPatterns = "/druid/*",
    initParams={
//            @WebInitParam(name="allow",value="192.168.1.113,127.0.0.1"),// IP白名单 (没有配置或者为空，则允许所有访问)
            @WebInitParam(name="deny",value="192.168.1.112"),// IP黑名单 (存在共同时，deny优先于allow)
            @WebInitParam(name="loginUsername",value="admin"),// 用户名
            @WebInitParam(name="loginPassword",value="admin"),// 密码
            @WebInitParam(name="resetEnable",value="false")// 禁用HTML页面上的“Reset All”功能
    })
public class DruidStatViewServlet extends StatViewServlet {

}

3、将过滤器与监控页面添加进springboot组件

给springboot启动类添加servletComponentScan注解，用以将5.1,5.2添加至扫描列表

@ServletComponentScan

4、登录druid监控页面

在浏览器中输入ip地址:应用端口号/项目名/druid/index.html即可访问监控页面，输入自己配置的账号密码（我这里配置的是admin，admin）后，即可查看监控啦