一雕拼、建數(shù)據(jù)庫和表

1.數(shù)據(jù)庫demo1放一張user表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'aa');
INSERT INTO `user` VALUES ('2', 'bb');

2.數(shù)據(jù)庫demo2放一張role表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1', 'CC');
INSERT INTO `role` VALUES ('2', 'DD');

二恋腕、pom.xml引入包

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>

        <!-- aop -->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
        </dependency>
        <!-- alibaba druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!-- dynamic-->
        <dependency>
            <groupId>com.typesafe.dynamicdatasource</groupId>
            <artifactId>dynamic-data-source_2.11</artifactId>
        </dependency>

三唧领、用generator插件生成user、role兩張表的實體類孽拷、mapper.java吨掌、mapper.xml

User.java
Role.java
UserMapper.java
RoleMapper.java
UserMapper.xml
RoleMapper.xml

四、配置application.yml

server:
  port: 8088

mybatis:
  mapper-locations: classpath:mapper/*.xml

spring:
  datasource:
      db1:
        url: jdbc:mysql://localhost:3306/demo1?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
        #驅(qū)動包
        driver-class-name: com.mysql.cj.jdbc.Driver
        #初始連接數(shù)
        initial-size: 5
        #最小空閑數(shù)
        min-idle: 5
        #最大活動數(shù)
        max-active: 20
        #等待超時時間
        max-wait: 60000
        #配置間隔多久才進行一次檢測脓恕，檢測需要關(guān)閉的空閑連接膜宋，單位是毫秒
        time-between-eviction-runs-millis: 60000
        # 配置一個連接在池中最小生存的時間，單位是毫秒
        min-evictable-idle-time-millis: 300000
        #驗證數(shù)據(jù)庫連接的查詢語句,MYSQL是select 1
        validation-query: SELECT 1 FROM DUAL
        #空閑時測試炼幔，testOnBorrow和testOnReturn在生產(chǎn)環(huán)境一般是不開啟的秋茫，主要是性能考慮。失效連接主要通過testWhileIdle保證
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        #打開PSCache乃秀，并指定每個鏈接上的PSCache大小
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        #配置監(jiān)控統(tǒng)計攔截的filters,去掉后監(jiān)控界面sql無法統(tǒng)計肛著，‘wall’用于防火墻，此處是filter修改的地方
        filters: stat,wall
        #通過connectproperties屬性來打開mergesql功能：慢sql記錄
        connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        #合并多個DruidDataSource
        useGlobalDataSourceStat: true
      db2:
        url: jdbc:mysql://localhost:3306/demo2?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
        #驅(qū)動包
        driver-class-name: com.mysql.cj.jdbc.Driver
        #初始連接數(shù)
        initial-size: 5
        #最小空閑數(shù)
        min-idle: 5
        #最大活動數(shù)
        max-active: 20
        #等待超時時間
        max-wait: 60000
        #配置間隔多久才進行一次檢測跺讯，檢測需要關(guān)閉的空閑連接枢贿，單位是毫秒
        time-between-eviction-runs-millis: 60000
        # 配置一個連接在池中最小生存的時間，單位是毫秒
        min-evictable-idle-time-millis: 300000
        #驗證數(shù)據(jù)庫連接的查詢語句,MYSQL是select 1
        validation-query: SELECT 1 FROM DUAL
        #空閑時測試刀脏，testOnBorrow和testOnReturn在生產(chǎn)環(huán)境一般是不開啟的局荚，主要是性能考慮。失效連接主要通過testWhileIdle保證
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        #打開PSCache愈污，并指定每個鏈接上的PSCache大小
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        #配置監(jiān)控統(tǒng)計攔截的filters,去掉后監(jiān)控界面sql無法統(tǒng)計危队，‘wall’用于防火墻，此處是filter修改的地方
        filters: stat,wall
        #通過connectproperties屬性來打開mergesql功能：慢sql記錄
        connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        #合并多個DruidDataSource
        useGlobalDataSourceStat: true

五钙畔、啟動類掃描mapper.java文件

@SpringBootApplication
@MapperScan("com.example.demo.dao")
public class DemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

六、定義DataSourceConfig金麸， 將application.yml中的配置導(dǎo)入DataSource中擎析，并注入到bean

@Configuration
public class DataSourceConfig {
    //從配置文件配置數(shù)據(jù)源
    @Primary
    @Bean(name="datasource1")
    @ConfigurationProperties("spring.datasource.db1")
    public DataSource dataSource1(){
        return new DruidDataSource();
    }

    //從配置文件配置數(shù)據(jù)源
    @Bean(name="datasource2")
    @ConfigurationProperties("spring.datasource.db2")
    public DataSource dataSource2(){
        return new DruidDataSource();
    }

    //動態(tài)數(shù)據(jù)源 進行數(shù)據(jù)源切換
    @Bean(name="dynamicDataSource")
    public DataSource dynamicDataSource(){
        DynamicDataSource dynamicDatasource=new DynamicDataSource();
        //設(shè)置默認(rèn)數(shù)據(jù)源
        dynamicDatasource.setDefaultTargetDataSource(dataSource1());
        //配置多數(shù)據(jù)源
        Map<Object,Object> dsMap=new HashMap<>();
        dsMap.put("datasource1",dataSource1());
        dsMap.put("datasource2",dataSource2());
        //將多數(shù)據(jù)源放到數(shù)據(jù)源池中
        dynamicDatasource.setTargetDataSources(dsMap);
        return dynamicDatasource;
    }
}

七、定義動態(tài)數(shù)據(jù)源切換類DynamicDataSourceContextHolder

public class DynamicDataSourceContextHolder {
    private static final ThreadLocal<String> contextHolder=new ThreadLocal<>();

    //設(shè)置數(shù)據(jù)源名稱
    public static void setDB(String dbType){
        contextHolder.set(dbType);
    }

    //獲取數(shù)據(jù)源名稱
    public static String getDB(){
        return contextHolder.get();
    }

    //清除數(shù)據(jù)源名
    public static void clearDB(){
        contextHolder.remove();
    }
}

八、定義獲取動態(tài)數(shù)據(jù)源類DynamicDataSource

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDB();
    }
}

九揍魂、定義mybatis配置類桨醋，將DynamicDataSource放入SqlSessionFactoryBean中

@EnableTransactionManagement
@Configuration
public class MyBatisConfig {
    @Resource(name = "dynamicDataSource")
    private DataSource dynamicDataSource;

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource);//將動態(tài)數(shù)據(jù)源bean配置到sqlsessionfactory
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager platformTransactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource);
    }
}

十、定義用于切換數(shù)據(jù)源的注解TargetDataSource

@Target({ElementType.METHOD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
    String value() default "datasource1";
}

十一现斋、定義切面DynamicDataSourceAspect喜最，用于攔截注解，并執(zhí)行數(shù)據(jù)源切換功能

@Aspect
@Component
public class DynamicDataSourceAspect {
    @Before("@annotation(targetDataSource)")
    public void beforeSwitchDS(JoinPoint point,TargetDataSource targetDataSource){
        DynamicDataSourceContextHolder.setDB(targetDataSource.value());
    }

    @After("@annotation(targetDataSource)")
    public void afterSwitchDS(JoinPoint point,TargetDataSource targetDataSource){
        DynamicDataSourceContextHolder.clearDB();
    }
}

十二庄蹋、測試類Test

@RestController
public class Test {
    @Autowired
    private RoleMapper roleMapper;
    @Autowired
    private UserMapper userMapper;

    //未使用TargetDataSource注解瞬内，則使用默認(rèn)數(shù)據(jù)源，即datasource1
    @RequestMapping("/ds1")
    public String selectDataSource1(){
        return userMapper.selectByPrimaryKey(1).toString();
    }

    //使用了注解限书，則數(shù)據(jù)源為注解中指定的datasource2
    @RequestMapping("/ds2")
    @TargetDataSource("datasource2")
    public String selectDataSource2(){
        return roleMapper.selectByPrimaryKey(1).toString();
    }
}

測試

1.輸入

http://localhost:8088/ds1

返回
↓

DynamicDataSourceRegister_百度搜索.png

2.輸入

http://localhost:8088/ds2

返回
↓

123545646.png

結(jié)論：兩次請求分別從不同的數(shù)據(jù)庫獲取到了數(shù)據(jù)虫蝶，多數(shù)據(jù)源配置成功！