SpringBoot mybatis基于注解的方式实现多数据源配置

当项目需要使用到两个或多个数据源时，就需要采用多数据源配置了

第一步 修改配置文件

spring:
  datasource:
    dbcp1: #第一个数据源
      driver_class_name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3306/shop
      username: root
      password: root
      dialect: mysql
    dbcp2: #第二个数据源
      driver_class_name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3306/shorp1
      username: root
      password: root
      dialect: mysql
    dbcp3: #第三个数据源
      driver_class_name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3306/shop2
      username: root
      password: root
      dialect: mysql
    druid: #druid配置
    # 连接池的配置信息
    # 初始化大小，最小，最大
    initial-size: 5
    min-idle: 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'用于防火墙
    filters: stat,wall,log4j
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000

配置了三个数据源 建议从两个开始（虽然三个也没差别）

第二步：实现基于注解数据源动态切换

DataSourceConfig负责加载配置文件中数据源的配置信息

package com.shop.config;

import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
//@RefreshScope
public class DataSourceConfig {

    //数据源1
    @Bean(name = "dbcp1")
    @ConfigurationProperties(prefix = "spring.datasource.dbcp1") // application.properteis中对应属性的前缀
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }

    //数据源2
    @Bean(name = "dbcp2")
    @ConfigurationProperties(prefix = "spring.datasource.dbcp2") // application.properteis中对应属性的前缀
    public DataSource dataSource2() {
        return DataSourceBuilder.create().build();
    }

    //数据源2
    @Bean(name = "dbcp3")
    @ConfigurationProperties(prefix = "spring.datasource.dbcp3") // application.properteis中对应属性的前缀
    public DataSource dataSource3() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 动态数据源: 通过AOP在不同数据源之间动态切换
     * @return
     */
    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicDS dynamicDS = new DynamicDS();
        // 默认数据源
//        dynamicDataSource.setDefaultTargetDataSource(dataSource1());
        // 配置多数据源
        Map<Object, Object> dsMap = new HashMap();
        dsMap.put("dbcp1", dataSource1());
        dsMap.put("dbcp2", dataSource2());
        dsMap.put("dbcp3", dataSource3());

        dynamicDS.setTargetDataSources(dsMap);
        return dynamicDS;
    }

    /**
     * 配置@Transactional注解事物
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }

}

DataSourceContextHolder用来获取及设置当前数据源

package com.shop.config;


import com.shop.codes.DSEnum;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *用来获取及设置当前数据源
 */

public class DataSourceContextHolder {
    static Logger logger = LoggerFactory.getLogger("DataSourceContextHolder");
    /**
     * 默认数据源
     */
    public static final String DEFAULT_DS = DSEnum.DB1.getValue();

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

    // 设置数据源名
    public static void setDB(String dbType) {
        contextHolder.set(dbType);
    }

    // 获取数据源名
    public static String getDB() {
        String dbsource = contextHolder.get();
        if(dbsource == null ){
            dbsource = "dbcp1";
            logger.info("数据源为NULL 返回默认数据源"+ dbsource);
        }
        return  dbsource;
    }

    // 清除数据源名
    public static void clearDB() {
        contextHolder.remove();
    }

}

枚举类

package com.shop.codes;

public enum DSEnum {
    DB1("dbcp1"),
    DB2("dbcp2"),
    DB3("dbcp3");

    private String value;

    DSEnum(String value){
        this.value=value;
    }

    public String getValue() {
        return value;
    }
}

自定义DSAnno注解

package com.shop.annos;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface DSAnno {
    String value() default "dbcp1";
}

定义切面类，根据接口上的注解动态切换数据源

package com.shop.config;

import com.shop.annos.DSAnno;
import com.shop.util.GetPrivateFieldUtil;
import java.lang.reflect.Method;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

/**
 * 自定义注解 + AOP的方式实现数据源动态切换。
 */
@Aspect
@Component
public class AspectDynamicDS {

    private Logger logger = LoggerFactory.getLogger(AspectDynamicDS.class);

    @Before("@annotation(com.shop.annos.DSAnno)")
    public void beforeSwitchDS(JoinPoint point) {
        try {
            Object methodInvocation = GetPrivateFieldUtil.getPrivateField(point, "methodInvocation", point.getClass());
            Method method = (Method) GetPrivateFieldUtil.getPrivateField(methodInvocation, "method", methodInvocation.getClass());
            DSAnno annotation = method.getAnnotation(DSAnno.class);
            DataSourceContextHolder.setDB(annotation.value());
        } catch (Exception e) {
            e.printStackTrace();
        }
        logger.info("当前数据源为" + DataSourceContextHolder.getDB());
    }

    @After("@annotation(com.shop.annos.DSAnno)")
    public void afterSwitchDS(JoinPoint point) {
        DataSourceContextHolder.clearDB();
    }
}

动态数据源，重载函数用于返回当前数据源

package com.shop.config;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDS extends AbstractRoutingDataSource {
    private Logger logger = LoggerFactory.getLogger(getClass().getName());
    @Override
    protected Object determineCurrentLookupKey() {
        String dbsource = DataSourceContextHolder.getDB();
        if(dbsource == null ){
            dbsource = "dbcp1";
            logger.info("数据源为NULL 返回数据源"+DataSourceContextHolder.getDB());
        }
        return dbsource;
    }

}

被切面引用的工具类

package com.shop.util;

import java.lang.reflect.Field;

public class GetPrivateFieldUtil {
    public static Object getPrivateField(Object obj, String fieldName, Class classs) {
        try {
            Field field = classs.getDeclaredField(fieldName);
            field.setAccessible(true);
            return field.get(obj);
        } catch (Exception e) {
            try {
                classs = classs.getSuperclass();
                Field field = classs.getDeclaredField(fieldName);
                field.setAccessible(true);
                return field.get(obj);
            } catch (Exception ex) {
                return null;
            }
        }
    }
}

第三步：禁止springboot自动注入数据源配置

package com.shop;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@MapperScan("com.shop.mapper")
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}

使用方法
Mapper接口文件定义3个接口,分别对应3个数据源的查询操作(为了省事儿，用了注解)

分别对应 第一个数据源 第二个数据源 第三个数据源
对了三个数据源里面的数据都是一样的 可以自己修改用户名密码去区别

//第一个数据源
 	@DSAnno("dbcp1")
    @Select("SELECT * from oc_current_day_revenue where polylineId=#{polylineId} GROUP BY date ")
    List<NowDayRevenue> getAllDataByTypeId(@Param("polylineId") int polylineId);
    //第二个数据源
    @DSAnno("dbcp2")
    @Select("SELECT * FROM oc_polylinedata")
    List<OcPolylineData> selectAllType();
    //三个数据源	
    @DSAnno("dbcp3")
    @Select("SELECT * FROM oc_users WHERE name = #{name} AND password = #{password}")
    User getInfo(@Param("name")String name,@Param("password")String password);

controller：

 @RequestMapping(value = "/loginIn",method = RequestMethod.POST)
    public String login(String name,String password){
        User user = tidDSUserService.loginIn(name,password);
        String s = weatherController.QueryWeather();
        if(user !=null){
            return "success";
        }else {
            return "error";
        }
    }
   @PostMapping("/getAll")
    @ResponseBody
    public Map getAll(){
        Map map = firDSNowDayRevenueService.getAll();
        return map;
    }
     @PostMapping("/getAllPolyline")
    @ResponseBody
    public Map getAllPolyline(){
        return polylineService.getAllPolyline();
    }

console控制台打印：
从登陆页面登陆成功时，会自动查询所有数据分别调用三个数据源
控制台可以看到打印的数据
登录用数据源3 查询类别用数据源2 查询所有当日数据用数据源1

2021-11-01 23:04:17.498  INFO 23652 --- [nio-8090-exec-3] c.s.service.impl.TidDSUserServiceImpl    : admin1:::::1
2021-11-01 23:04:17.507  INFO 23652 --- [nio-8090-exec-3] com.shop.config.AspectDynamicDS          : 当前数据源为dbcp3
2021-11-01 23:04:17.530  INFO 23652 --- [nio-8090-exec-3] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2021-11-01 23:04:17.859  INFO 23652 --- [nio-8090-exec-3] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2021-11-01 23:04:20.804  INFO 23652 --- [nio-8090-exec-3] c.s.service.impl.TidDSUserServiceImpl    : {"tem_day":"22","update_time":"22:55","wea":"多云","win_meter":"0km/h","city":"上海","wea_img":"yun","tem_night":"16","win_speed":"0级","cityid":"101020100","air":"118","tem":"17","win":"南风"}
2021-11-01 23:04:20.804  INFO 23652 --- [nio-8090-exec-3] c.s.service.impl.TidDSUserServiceImpl    : 22
2021-11-01 23:04:26.361  INFO 23652 --- [nio-8090-exec-4] com.shop.config.AspectDynamicDS          : 当前数据源为dbcp2
2021-11-01 23:04:26.362  INFO 23652 --- [nio-8090-exec-4] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Starting...
2021-11-01 23:04:26.392  INFO 23652 --- [nio-8090-exec-4] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Start completed.
2021-11-01 23:04:26.396  INFO 23652 --- [nio-8090-exec-4] com.shop.config.AspectDynamicDS          : 当前数据源为dbcp1
2021-11-01 23:04:26.396  INFO 23652 --- [nio-8090-exec-4] com.zaxxer.hikari.HikariDataSource       : HikariPool-3 - Starting...
2021-11-01 23:04:26.427  INFO 23652 --- [nio-8090-exec-4] com.zaxxer.hikari.HikariDataSource       : HikariPool-3 - Start completed.
2021-11-01 23:04:26.434  INFO 23652 --- [nio-8090-exec-4] com.shop.config.AspectDynamicDS          : 当前数据源为dbcp1
2021-11-01 23:04:26.436  INFO 23652 --- [nio-8090-exec-4] com.shop.config.AspectDynamicDS          : 当前数据源为dbcp1

项目地址(包含数据库)：

https://github.com/DNYDYS/SpringBootStudy