<!--引入多數(shù)據(jù)源需要的依賴包-->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
  <version>3.1.1</version>
</dependency>

spring:
  application:
    name: zhangjiadayuan-server
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    # Druid的其他屬性配置
    druid:
      # 初始化時(shí)建立物理連接的個(gè)數(shù)
      initial-size: 10
      # 連接池的最小空閑數(shù)量
      min-idle: 5
      # 連接池最大連接數(shù)量
      max-active: 20
      # 獲取連接時(shí)最大等待時(shí)間，單位毫秒
      max-wait: 60000
      # 申請(qǐng)連接的時(shí)候檢測(cè)，如果空閑時(shí)間大于timeBetweenEvictionRunsMillis，執(zhí)行validationQuery檢測(cè)連接是否有效。
      test-while-idle: true
      # 既作為檢測(cè)的間隔時(shí)間又作為testWhileIdel執(zhí)行的依據(jù)
      time-between-eviction-runs-millis: 60000
      # 銷毀線程時(shí)檢測(cè)當(dāng)前連接的最后活動(dòng)時(shí)間和當(dāng)前時(shí)間差大于該值時(shí)，關(guān)閉當(dāng)前連接(配置連接在池中的最小生存時(shí)間)
      min-evictable-idle-time-millis: 30000
      # 用來檢測(cè)數(shù)據(jù)庫連接是否有效的sql 必須是一個(gè)查詢語句(oracle中為 select 1 from dual)
      validation-query: SELECT 1 FROM DUAL
      # 申請(qǐng)連接時(shí)會(huì)執(zhí)行validationQuery檢測(cè)連接是否有效,開啟會(huì)降低性能,默認(rèn)為true
      test-on-borrow: false
      # 歸還連接時(shí)會(huì)執(zhí)行validationQuery檢測(cè)連接是否有效,開啟會(huì)降低性能,默認(rèn)為true
      test-on-return: false
      # 是否緩存preparedStatement, 也就是PSCache,PSCache對(duì)支持游標(biāo)的數(shù)據(jù)庫性能提升巨大，比如說oracle,在mysql下建議關(guān)閉。
      pool-prepared-statements: false
      # 置監(jiān)控統(tǒng)計(jì)攔截的filters，去掉后監(jiān)控界面sql無法統(tǒng)計(jì)，stat: 監(jiān)控統(tǒng)計(jì)、Slf4j:日志記錄、waLL: 防御sqL注入
      filters: stat,wall,slf4j
      # 要啟用PSCache，必須配置大于0，當(dāng)大于0時(shí)，poolPreparedStatements自動(dòng)觸發(fā)修改為true。在Druid中，不會(huì)存在Oracle下PSCache占用內(nèi)存過多的問題，可以把這個(gè)數(shù)值配置大一些，比如說100
      max-pool-prepared-statement-per-connection-size: -1
      # 合并多個(gè)DruidDataSource的監(jiān)控?cái)?shù)據(jù)
      use-global-data-source-stat: true
      # 通過connectProperties屬性來打開mergeSql功能；慢SQL記錄
      connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      web-stat-filter:
        # 是否啟用StatFilter默認(rèn)值true
        enabled: true
        # 添加過濾規(guī)則
        url-pattern: /*
        # 忽略過濾的格式
        exclusions: /druid/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico

      stat-view-servlet:
        # 是否啟用StatViewServlet默認(rèn)值true
        enabled: true
        # 訪問路徑為/druid時(shí)，跳轉(zhuǎn)到StatViewServlet
        url-pattern: /druid/*
        # 是否能夠重置數(shù)據(jù)
        reset-enable: false
        # 需要賬號(hào)密碼才能訪問控制臺(tái)，默認(rèn)為root  http://localhost:8585/druid/
        login-username: root
        login-password: 123456
        # IP白名單
        allow: 127.0.0.1
        # IP黑名單（共同存在時(shí)，deny優(yōu)先于allow）
        deny:
    dynamic:
      primary: master #設(shè)置默認(rèn)的數(shù)據(jù)源或者數(shù)據(jù)源組
      strict: false #嚴(yán)格匹配數(shù)據(jù)源，默認(rèn)為false，true未匹配到數(shù)據(jù)源時(shí)會(huì)拋出異常，false使用默認(rèn)數(shù)據(jù)源
      datasource:
        master:
          # 數(shù)據(jù)源基本配置
          username: root
          password: root123
          url: jdbc:mysql://127.0.0.1:3306/zhangjiadayuan?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
          # driver-class需要注意mysql驅(qū)動(dòng)的版本(com.mysql.cj.jdbc.Driver 或 com.mysql.jdbc.Driver)
          driver-class-name: com.mysql.cj.jdbc.Driver
        slave:
          # 數(shù)據(jù)源基本配置
          username: root
          password: root123
          url: jdbc:mysql://127.0.0.1:3306/zhangjiadayuan2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
          # driver-class需要注意mysql驅(qū)動(dòng)的版本(com.mysql.cj.jdbc.Driver 或 com.mysql.jdbc.Driver)
          driver-class-name: com.mysql.cj.jdbc.Driver

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;


@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, DruidDataSourceAutoConfigure.class})
@ComponentScan("cn.zhang.*")
@MapperScan("cn.zhang.mapper")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class,args);
    }
}

spring:
  datasource:
    druid:
      db1:
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/db1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true
        username: root
        password: 123456
      db2:
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/db2?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true
        username: root
        password: 123456
      test-on-borrow: true

@Configuration(basePackages = MyBatisConfig.BASE_PACKAGE, sqlSessionTemplateRef = "sqlSessionTemplate")
public class DataSourceConfigurer {
    //mapper模式下的接口層
    static final String BASE_PACKAGE = "cn.zhang.mapper";
	public static final String CLASSPATH_MAPPING_XML = "classpath*:mapper/*.xml";
    /**
     * 配置數(shù)據(jù)源
     *
     * @return
     */
    @Bean(name = "db1")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.druid.db1")
    public DataSource db1() {
        return DruidDataSourceBuilder.create().build();
    }
    /**
     * 配置數(shù)據(jù)源
     *
     * @return
     */
    @Bean(name = "db2")
    @ConfigurationProperties(prefix = "spring.datasource.druid.db2")
    public DataSource db2() {
        return DruidDataSourceBuilder.create().build();
    }
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
        Map<Object, Object> dataSourceMap = new HashMap<Object, Object>(2);
        dataSourceMap.put(DataSourceKey.Master.getName(), db1());
        dataSourceMap.put(DataSourceKey.SLAVE.getName(), db2());
        dynamicRoutingDataSource.setDefaultTargetDataSource(dcs());
        dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
        DynamicDataSourceContextHolder.dataSourceKeys.addAll(dataSourceMap.keySet());
        return dynamicRoutingDataSource;
    }
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        //MybatisPlus使用的是MybatisSqlSessionFactory
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource());
        //此處設(shè)置為了解決找不到mapper文件的問題
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(CLASSPATH_MAPPING_XML));
        return sqlSessionFactoryBean.getObject();
    }
    @Bean
    public SqlSessionTemplate sqlSessionTemplate() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory());
    }
    /**
     * 事務(wù)
     *
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }

/**
 * 該類繼承自 AbstractRoutingDataSource 類，
 * 在訪問數(shù)據(jù)庫時(shí)會(huì)調(diào)用該類的 determineCurrentLookupKey() 方法獲取數(shù)據(jù)庫實(shí)例的 key
 */
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        logger.info("current datasource is : {}", DynamicDataSourceContextHolder.getDataSourceKey());
        return DynamicDataSourceContextHolder.getDataSourceKey();
    }
}

public class DynamicDataSourceContextHolder {
    private static ThreadLocal<Object> CONTEXT_HOLDER = ThreadLocal.withInitial(() -> DataSourceKey.DCS.getName());
    public static List<Object> dataSourceKeys = new ArrayList<Object>();
    public static void setDataSourceKey(String key){
        CONTEXT_HOLDER.set(key);
    }
    public static Object getDataSourceKey(){
        return CONTEXT_HOLDER.get();
    }
    public static void clearDataSourceKey(){
        CONTEXT_HOLDER.remove();
    }
    public static Boolean containDataSourceKey(String key){
        return dataSourceKeys.contains(key);
    }
}

public enum DataSourceKey {
	MASTER("db1"),
	SLAVE("db2");
	
	private String name;
 
	public String getName() {
		return name;
	}
 
	public void setName(String name) {
		this.name = name;
	}
 
	private DataSourceKey(String name) {
		this.name = name;
	}
}

//該注解只是作用在方法上，這里默認(rèn)的數(shù)據(jù)源是DB1
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
    String value() default "db1";
}

@Aspect
@Component
public class DynamicDataSourceAspect {
    private Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);
    @Before("@annotation(targetDataSource)")
    public void switchDataSource(JoinPoint joinPoint, TargetDataSource targetDataSource) {
        if (!DynamicDataSourceContextHolder.containDataSourceKey(targetDataSource.value().getName())) {
            logger.error("DataSource [{}] doesn't exist, use default DataSource [{}]", targetDataSource.value());
        } else {
            DynamicDataSourceContextHolder.setDataSourceKey(targetDataSource.value().getName());
            logger.info("Switch DataSource to [{}] in Method [{}]", DynamicDataSourceContextHolder.getDataSourceKey(), joinPoint.getSignature());
        }
    }
    @After("@annotation(targetDataSource)")
    public void restoreDataSource(JoinPoint joinPoint, TargetDataSource targetDataSource) {
        DynamicDataSourceContextHolder.clearDataSourceKey();
        logger.info("Restore DataSource to [{}] in Method [{}]", DynamicDataSourceContextHolder.getDataSourceKey(), joinPoint.getSignature());
    }
}

//也可用與Service方法或類上
@TargetDataSource("db2")
String getClassStudent(@Param("open_id") String openId);