springboot2 + mybatis + mysql + oracle + sqlserver多数据源的配置

• 相信很多朋友在开发的时候，可能会碰到需要一个项目，配置多个数据源的需求，可能是同一种数据库，也可能是不同种类的数据库。
• 这种情况，我们就需要配置多数据源对程序的支持了。
• （本例理论上支持很多种数据库，或者同种数据库配置多个库分别作数据源也许。拓展新强）

环境介绍

• web框架：SpringBoot2
• orm框架：Mybatis
• 数据库连接池：Druid
• 主数据源：Mysql
• 从数据源1：Oracle
• 从数据源2：SqlServer
• 运行平台：Jdk8
• 接口文档：Swagger-ui （提供伪Restful接口）
• 日志配置：Logback

基本思路

• 自定义多个数据源，并指定切换规则
• 引入ThreadLocal来保存和管理数据源上下文标识
• 使用AOP切面编程，根据某些自定义条件，动态切换数据源(反射)
• 访问接口测试效果

大致步骤

1. 创建一个拥有ThreadLocal变量的类，用来存取数据源名称

   public class JdbcContextHolder {
   
       /** 本地线程共享对象（保证在同一线程下切换后不要被其他线程修改） */
       private final static ThreadLocal local = new ThreadLocal();
   
       public static void putDataSource(String name){
           local.set(name);
       }
   
       public static String getDataSource(){
           return local.get();
       }
   
       public static void removeDataSource(){
           local.remove();
       }
   
   }
   

2. 创建一个枚举类，用来存放每个数据源的标识符（标识符是自定义的）

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

3. 在启动类上，禁用springboot自动配置的数据源：(exclude = DataSourceAutoConfiguration.class)

   @SpringBootApplication(exclude = DataSourceAutoConfiguration.class) 
   @ComponentScan("com.dcm.*.**.**")
   public class MoreDsApplication extends SpringBootServletInitializer {
   
       public static void main(String[] args) {
   
           SpringApplication.run(MoreDsApplication.class, args);
           System.out.println("[---------------more_ds项目: started......]");
   
       }
   
       /** 创建一个SpringApplicationBuilder交付给springboot框架来完成初始化运行配置 */
       @Override
       protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
           return application.sources(MoreDsApplication.class);
       }
   }
   

4. 创建动态数据源类，接管springboot的数据源配置

   public class DynamicDataSource extends AbstractRoutingDataSource {
       
           /** 数据源路由，此方法用于产生要选取的数据源逻辑名称 */
           @Override
           protected Object determineCurrentLookupKey() {
               //从共享线程中获取数据源名称
               return JdbcContextHolder.getDataSource();
           }
       }
   

5. 在application.yml文件中加入自定义的多数据源的配置

   datasource:
     druid:
       type: com.alibaba.druid.pool.DruidDataSource
       initialSize: 1
       minIdle: 3
       maxActive: 20
       maxWait: 60000
       timeBetweenEvictionRunsMillis: 60000
       minEvictableIdleTimeMillis: 30000
       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
     mysql:
       url: jdbc:mysql://192.168.0.241:3306/pmpmain?useUnicode=true&useSSL=false&characterEncoding=utf8
       username: sdcm
       password: Sdcm_123456
       driverClassName: com.mysql.jdbc.Driver
       validationQuery: select ‘x‘
     oracle:
       url: jdbc:oracle:thin:@192.168.0.200:1522:sdcm
       username: sdcm
       password: Sdcm123456
       driverClassName: oracle.jdbc.OracleDriver
       validationQuery: select 1 from dual
   

6. 根据application.yml的多数据源配置，初始化各数据源并指定默认数据源

   @Configuration
   public class DataSourceConfig {
   
       private Logger logger = LoggerFactory.getLogger(this.getClass());
   
       // -----------------------------------------mysql config-------------------------------------
   
       @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.validationQuery}")
       private String validationQuery;
   
       @Bean(name="dataSourceMysql")
       public DataSource dataSourceMysql(){
           System.out.println("----------------主配" + dbUrl);
   
           DruidDataSource datasource = new DruidDataSource();
           datasource.setUrl(dbUrl);
           datasource.setUsername(username);
           datasource.setPassword(password);
           datasource.setDriverClassName(driverClassName);
           datasource.setValidationQuery(validationQuery);
           setDruidOptions(datasource); // 设置druid数据源的属性
   
           return datasource;
       }
   
       // -----------------------------------------oracle config-------------------------------------
   
       @Value("${datasource.oracle.url}")
       private String oracleUrl;
   
       @Value("${datasource.oracle.username}")
       private String oracleUsername;
   
       @Value("${datasource.oracle.password}")
       private String oraclePassword;
   
       @Value("${datasource.oracle.driverClassName}")
       private String oracleDriverClassName;
   
       @Value("${datasource.oracle.validationQuery}")
       private String oracleValidationQuery;
    
       @Bean(name="dataSourceOracle")
       public DataSource dataSourceOracle(){
           System.out.println("----------------次配" + oracleUrl);
   
           DruidDataSource datasource = new DruidDataSource();
           datasource.setUrl(oracleUrl);
           datasource.setUsername(oracleUsername);
           datasource.setPassword(oraclePassword);
           datasource.setDriverClassName(oracleDriverClassName);
           datasource.setValidationQuery(oracleValidationQuery);
           setDruidOptions(datasource); // 设置druid数据源的属性
   
           return datasource;
       }
   
       // -----------------------------------------druid config-------------------------------------
   
       @Value("${datasource.druid.initialSize}")
       private int initialSize;
   
       @Value("${datasource.druid.minIdle}")
       private int minIdle;
   
       @Value("${datasource.druid.maxActive}")
       private int maxActive;
   
       @Value("${datasource.druid.maxWait}")
       private int maxWait;
   
       @Value("${datasource.druid.timeBetweenEvictionRunsMillis}")
       private int timeBetweenEvictionRunsMillis;
   
       @Value("${datasource.druid.minEvictableIdleTimeMillis}")
       private int minEvictableIdleTimeMillis;
   
       @Value("${datasource.druid.testWhileIdle}")
       private boolean testWhileIdle;
   
       @Value("${datasource.druid.testOnBorrow}")
       private boolean testOnBorrow;
   
       @Value("${datasource.druid.testOnReturn}")
       private boolean testOnReturn;
   
       @Value("${datasource.druid.poolPreparedStatements}")
       private boolean poolPreparedStatements;
   
       @Value("${datasource.druid.maxPoolPreparedStatementPerConnectionSize}")
       private int maxPoolPreparedStatementPerConnectionSize;
   
       @Value("${datasource.druid.filters}")
       private String filters;
   
       @Value("{datasource.druid.connectionProperties}")
       private String connectionProperties;
   
       private void setDruidOptions(DruidDataSource datasource){
           datasource.setInitialSize(initialSize);
           datasource.setMinIdle(minIdle);
           datasource.setMaxActive(maxActive);
           datasource.setMaxWait(maxWait);
           datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
           datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
           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 Exception", e);
           }
           datasource.setConnectionProperties(connectionProperties);
       }
   
   
       @Bean(name = "dynamicDataSource")
       @Primary  // 优先使用，多数据源
       public DataSource dataSource(){
   
           DynamicDataSource dynamicDataSource = new DynamicDataSource();
           DataSource mysql = dataSourceMysql();
           DataSource oracle = dataSourceOracle();
   
           //设置默认数据源
           dynamicDataSource.setDefaultTargetDataSource(mysql);
   
           //配置多个数据源
           Map

7. 自定义注解，作为AOP切面范围的一个条件（这样更加灵活）

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

8. 创建AOP切换，动态切换数据源

   @Aspect
   @Order(2)
   @Component
   public class DataSourceAspect {
   
       private Logger logger = LoggerFactory.getLogger(this.getClass());
   
       // 切入点：service类的方法上(这个包的子包及所有包的里面的以Service结尾的类的任意方法名任意参数的方法，都讲被切到)
       @Pointcut("execution(* com.dcm.more_ds..*Service..*(..))")
       public void dataSourcePointCut(){
           System.out.println("dataSourcePointCut service");
       }
   
       @Before("dataSourcePointCut()")
       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);
   
               // 如果 m 上存在切换数据源的注解，则根据注解内容进行数据源切换
               if (m != null && m.isAnnotationPresent(TargetDataSource.class)){
                   TargetDataSource data = m.getAnnotation(TargetDataSource.class);
                   JdbcContextHolder.putDataSource(data.value().getName());
                   logger.info("》》》》》》》 current thread " + Thread.currentThread().getName() + " add 【 " + data.value().getName() + " 】 to ThreadLocal");
               } else { // 如果不存在，则使用默认数据源
                   logger.info("》》》》》》》 use default datasource");
               }
           }catch (Exception e){
               e.printStackTrace();
           }
       }
   
       // 执行完切面后，将线程共享中的数据源名称清空
       @After("dataSourcePointCut()")
       public void after(JoinPoint joinPoint){
           JdbcContextHolder.removeDataSource();
       }
   
   }
   

9. 在需要切换数据源的dao interface或者service interface上(具体看你切面切的范围)，加上自定义的注解（这里随便列举两个）

   @Transactional(readOnly = true)
       @TargetDataSource(DataSourceType.Oracle)
       public Listlist(Integer startNum, Integer limit) {
           setDao();
           return baseDao.selectWithPage(Arrays.asList(startNum, limit));
       }
   
       @TargetDataSource(DataSourceType.Oracle)
       public ListselectByRequestBodyIds(@RequestBody List
    ids) {
           return selectByPrimaryKeys(ids);
       }
       // .......more
   

10. 配置mybatis

    @Configuration
    // 扫描指定包下的dao，这样就不用每个dao interface上面写@Mapper了
    @MapperScan(basePackages = "com.dcm.more_ds.dao.*.**")
    public class MyBatisConf {
    
        @Autowired
        @Qualifier("dynamicDataSource")
        private DataSource dataSource;
    
        @Bean
        public SqlSessionFactory sqlSessionFactory() {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
    
            // 分页插件
            PageHelper pageHelper = new PageHelper();
            Properties properties = new Properties();
            properties.setProperty("reasonable", "true");
            properties.setProperty("supportMethodsArguments", "true");
            properties.setProperty("returnPageInfo", "check");
            properties.setProperty("params", "count=countSql");
            properties.setProperty("autoRuntimeDialect","true"); // 运行时根据数据源自动选择方言 （这句很重要）
            pageHelper.setProperties(properties);
    
            // 添加插件
            bean.setPlugins(new Interceptor[] { pageHelper });
    
            // 添加XML目录
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            try {
                bean.setMapperLocations(resolver.getResources("classpath:com/dcm/more_ds/dao/*/*.xml"));
                bean.setConfigLocation(resolver.getResource("classpath:mybatis-conf.xml"));
                return bean.getObject();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
    
        @Bean(name = "sqlSessionTemplate")
        public SqlSessionTemplate sqlSessionTemplate() {
            return new SqlSessionTemplate(sqlSessionFactory());
        }
    
    }
    

源码地址

springboot2 + mybatis + mysql + oracle多数据源的配置

• 源码中用到了AOP的其它应用：统一入参校验，统一异常处理。接口是伪Restful的。
• 源码中的mapper.xml，dao，service，serviceImpl，controller都是代码生成器生成的。(本贴破10w阅读量就分享代码生成器)

文末寄语：

• 转载请注明出处
• 感谢