springboot2 多数据源配置

一、前言

多数据源的应用场景大多为数据迁移和读写分离，这里的业务场景是数据的采集与再入库

比较重要的一点就是AbstractRoutingDataSource，下边是简单源码说明

image.png

二、示例

pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>multi-data-source</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>multi-data-source</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</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-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>

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

        <!-- https://mvnrepository.com/artifact/com.aliyun/aliyun-java-sdk-core -->
        <dependency>
            <groupId>com.aliyun</groupId>
            <artifactId>aliyun-java-sdk-core</artifactId>
            <version>3.2.4</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.github.penggle/kaptcha -->
        <dependency>
            <groupId>com.github.penggle</groupId>
            <artifactId>kaptcha</artifactId>
            <version>2.3.2</version>
        </dependency>
        <!-- alibaba的druid数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.2</version>
            <scope>provided</scope>
        </dependency>
        <!-- 支持 @ConfigurationProperties 注解 -->
        <!--<dependency>-->
        <!--<groupId>org.springframework.boot</groupId>-->
        <!--<artifactId>spring-boot-configuration-processor</artifactId>-->
        <!--<optional>true</optional>-->
        <!--</dependency>-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.httpcomponents/httpclient -->
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient</artifactId>
            <version>4.5.6</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.4</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.shiro/shiro-spring -->
        <!--<dependency>-->
        <!--<groupId>org.apache.shiro</groupId>-->
        <!--<artifactId>shiro-spring</artifactId>-->
        <!--<version>1.4.0</version>-->
        <!--</dependency>-->

        <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.10</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/com.squareup.okhttp3/okhttp -->
        <dependency>
            <groupId>com.squareup.okhttp3</groupId>
            <artifactId>okhttp</artifactId>
            <version>3.10.0</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger2 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger-ui -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/commons-codec/commons-codec -->
        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
            <version>1.11</version>
        </dependency>

        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.9.9</version>
        </dependency>

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

        <dependency>
            <groupId>org.bouncycastle</groupId>
            <artifactId>bcprov-jdk15on</artifactId>
            <version>1.57</version>
        </dependency>

        <dependency>
            <groupId>com.google.zxing</groupId>
            <artifactId>core</artifactId>
            <version>3.3.1</version>
        </dependency>

        <dependency>
            <groupId>com.google.zxing</groupId>
            <artifactId>javase</artifactId>
            <version>3.3.1</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.snmp4j/snmp4j -->
        <dependency>
            <groupId>org.snmp4j</groupId>
            <artifactId>snmp4j</artifactId>
            <version>2.7.0</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <!--指定编译时的jdk版本1.8 -->
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.5</version>
                <configuration>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

application.yml

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.multidatasource.model

pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql

logging:
  level:
    com.example.multidatasource.dao: DEBUG
  file: log/log.log

spring:
  profiles:
    active: test

application-test.yml

server:
  port: 8083
  tomcat:
    uri-encoding: UTF-8
  servlet:
    context-path: /

spring:
  application:
    name: data-default
  datasource:
    kanyun:  #数据源1
      name: kanyun
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3307/kanyun??useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
      username: root
      password: admin
      max-pool-size: 20
      max-active: 10
      max-idle: 5
      min-idle: 2
      initial-size: 2
      validation-query: select 1
      test-on-borrow: true
      test-on-return: false
      test-while-idle: false
      time-between-eviction-runs-millis: 3000
      min-evictable-idle-time-millis: 3000
      max-wait: 3000
      jmx-enabled: true
    sgdata:  #数据源2
      name: sgdata
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://127.0.0.1:3307/sgdatabase??useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
      username: root
      password: admin
      max-pool-size: 20
      max-active: 10
      max-idle: 5
      min-idle: 2
      initial-size: 2
      validation-query: select 1
      test-on-borrow: true
      test-on-return: false
      test-while-idle: false
      time-between-eviction-runs-millis: 3000
      min-evictable-idle-time-millis: 3000
      max-wait: 3000
      jmx-enabled: true
  mvc:
    static-path-pattern: /
    view:
      prefix: /views/
      suffix: .html
    resources:
      static-locations: classpath:/static/
  http:
    encoding:
      charset: utf-8
      force: true
      enabled: true

DataSourceContextHolder

package com.example.multidatasource.bean;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class DataSourceContextHolder {

    /** 本地线程共享对象（保证在同一线程下切换后不要被其他线程修改） */
    private final static ThreadLocal<String> 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();
    }
}

MultipleDataSource

package com.example.multidatasource.bean;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class MultipleDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        //从共享线程中获取数据源名称
        return DataSourceContextHolder.getDataSource();
    }

}

DataSource 注解

package com.example.multidatasource.bean;

import java.lang.annotation.*;

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {

    String value() default "";
    String kanyun = "kanyun";
    String sgdata = "sgdata";
}

注解切面

package com.example.multidatasource.bean;

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

@Component
@Slf4j
@Aspect
@Order(-1)
public class DataSourceAspect {

    @Pointcut("@annotation(com.example.multidatasource.bean.DataSource)")
    public void pointCut(){
    }

    @Before("pointCut() && @annotation(dataSource)")
    public void doBefore(DataSource dataSource){
        log.info("选择数据源---"+dataSource.value());
        DataSourceContextHolder.putDataSource(dataSource.value());
    }

    @After("pointCut()")
    public void doAfter(){
        DataSourceContextHolder.removeDataSource();
    }

}

配置动态数据源

package com.example.multidatasource.config;


import com.example.multidatasource.bean.MultipleDataSource;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
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 javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
@MapperScan("com.example.multidatasource.dao")
public class DataSourceConfig {

    @Bean(name = "kanyunDataSource")
    @Qualifier("kanyunDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.kanyun")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "sgdataDataSource")
    @Qualifier("sgdataDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.sgdata")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 动态数据源配置
     * @return
     */
    @Bean
    @Primary
    public DataSource multipleDataSource(@Qualifier("kanyunDataSource") DataSource db1,
                                         @Qualifier("sgdataDataSource") DataSource db2) {
        MultipleDataSource multipleDataSource = new MultipleDataSource();
        Map< Object, Object > targetDataSources = new HashMap<>();
        targetDataSources.put(com.example.multidatasource.bean.DataSource.kanyun, db1);
        targetDataSources.put(com.example.multidatasource.bean.DataSource.sgdata, db2);
        //添加数据源
        multipleDataSource.setTargetDataSources(targetDataSources);
        //设置默认数据源
        multipleDataSource.setDefaultTargetDataSource(db1);
        return multipleDataSource;
    }

}

定时测试

package com.example.multidatasource.service;

import com.example.multidatasource.dao.ActivesignalMapper;
import com.example.multidatasource.dao.App_accesslogMapper;
import com.example.multidatasource.model.Activesignal;
import com.example.multidatasource.model.App_accesslog;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
@Slf4j
public class TestService {

    @Autowired
    ActivesignalMapper activesignalMapper;
    @Autowired
    App_accesslogMapper app_accesslogMapper;

    @Scheduled(initialDelay = 1000*5,fixedRate = 1000*60*60*2400)
    public void exec(){
        List<App_accesslog> list1 = app_accesslogMapper.queryForList();
        list1.forEach(activesignal -> log.info("获取数据1：{}",activesignal));

        List<Activesignal> list2 = activesignalMapper.queryForList();
        list2.forEach(activesignal -> log.info("获取数据2：{}",activesignal));
    }

}

下边是启动类

package com.example.multidatasource;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.scheduling.TaskScheduler;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.concurrent.ThreadPoolTaskScheduler;

@ComponentScan(basePackages = {"com.example"})
@ServletComponentScan(basePackages = {"com.example"})
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
@EnableScheduling
public class MultiDataSourceApplication {

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

    @Bean
    public TaskScheduler taskScheduler(){
        ThreadPoolTaskScheduler threadPoolTaskScheduler = new ThreadPoolTaskScheduler();
        threadPoolTaskScheduler.setPoolSize(10);
        threadPoolTaskScheduler.setThreadNamePrefix("task");
        return threadPoolTaskScheduler;
    }

}

测试

image.png

结语

这里没有使用事务，如果采用事务需要分别配置每个数据源的事务，并采用事务性注解进行统一管理

-end-