Springboot如何設(shè)置多數(shù)據(jù)源,隨時(shí)切換

更新時(shí)間：2024年04月01日 15:57:56 作者：快樂(lè)敲代碼

這篇文章主要介紹了Springboot如何設(shè)置多數(shù)據(jù)源,隨時(shí)切換方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

需求

接到一個(gè)任務(wù)，把一個(gè)數(shù)據(jù)庫(kù)里面的數(shù)據(jù)定時(shí)導(dǎo)入到另外的數(shù)據(jù)庫(kù)中

但是又不允許我們通過(guò)binlog+canal同步，所以考慮起一個(gè)微服務(wù)充當(dāng)同步腳本的作用

且配置多數(shù)據(jù)庫(kù)，并且支持隨時(shí)切換

環(huán)境

1、mysql多個(gè)庫(kù)
2、mysql+postgresql

思路

spring框架本身支持多數(shù)據(jù)源，我們查看他的定義

Spring的多數(shù)據(jù)源支持—AbstractRoutingDataSource，AbstractRoutingDataSource定義了抽象的determineCurrentLookupKey方法，子類實(shí)現(xiàn)此方法，來(lái)確定要使用的數(shù)據(jù)源

看下下面它的源碼：

public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
	 protected DataSource determineTargetDataSource() {
		Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
		Object lookupKey = determineCurrentLookupKey();
		DataSource dataSource = this.resolvedDataSources.get(lookupKey);
		if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
			dataSource = this.resolvedDefaultDataSource;
		}
		if (dataSource == null) {
			throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
		}
		return dataSource;
	}

        // 確定當(dāng)前要使用的數(shù)據(jù)源
        protected abstract Object determineCurrentLookupKey();
}

所以我們只要寫(xiě)一個(gè)自定義類去繼承上面這個(gè)AbstractRoutingDataSource類，并重寫(xiě)determineCurrentLookupKey 方法即可

操作

包如下：

一、多個(gè)庫(kù)都是mysql類型

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 https://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.6.7</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>db-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>db-demo</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-web</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.2.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-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

1、配置文件application.yml

#端口
server.port: 7788
spring.application.name: bddemo

# mysql
spring.datasource:
  driver-class-name: com.mysql.cj.jdbc.Driver
  #數(shù)據(jù)庫(kù)1
  db1:
    jdbc-url: jdbc:mysql://127.0.0.1:3306/db1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false
    username: root
    password: 123456
  #數(shù)據(jù)庫(kù)2
  db2:
    jdbc-url: jdbc:mysql://127.0.0.1:3306/db2?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false
    username: root
    password: 123456

# mybatis
mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: ccom.example.demo.*.entity

2、配置類

1） DataSourceConfig 數(shù)據(jù)庫(kù)配置類：

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;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* 數(shù)據(jù)庫(kù)配置
* @date 2022/5/19
*/
@Configuration
public class DataSourceConfig {

    /**
     * 數(shù)據(jù)源1
     * spring.datasource.db1：application.properteis中對(duì)應(yīng)屬性的前綴
     * @return
     */
    @Bean(name = "db1")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource dataSourceOne() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 數(shù)據(jù)源2
     * spring.datasource.db2：application.properteis中對(duì)應(yīng)屬性的前綴
     * @return
     */
    @Bean(name = "db2")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource dataSourceTwo() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 動(dòng)態(tài)數(shù)據(jù)源: 通過(guò)AOP在不同數(shù)據(jù)源之間動(dòng)態(tài)切換
     * @return
     */
    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        // 默認(rèn)數(shù)據(jù)源
        dynamicDataSource.setDefaultTargetDataSource(dataSourceOne());
        // 配置多數(shù)據(jù)源
        Map<Object, Object> dsMap = new HashMap<>();
        dsMap.put("db1", dataSourceOne());
        dsMap.put("db2", dataSourceTwo());

        dynamicDataSource.setTargetDataSources(dsMap);
        return dynamicDataSource;
    }

    /**
     * 配置多數(shù)據(jù)源后IOC中存在多個(gè)數(shù)據(jù)源了，事務(wù)管理器需要重新配置，不然器不知道選擇哪個(gè)數(shù)據(jù)源
     * 事務(wù)管理器此時(shí)管理的數(shù)據(jù)源將是動(dòng)態(tài)數(shù)據(jù)源dynamicDataSource
     * 配置@Transactional注解
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }
}

2） DynamicDataSource 動(dòng)態(tài)數(shù)據(jù)源類：

import com.example.demo.utils.DataSourceUtil;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
 * 動(dòng)態(tài)數(shù)據(jù)源類
 * @date 2022/2/11
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceUtil.getDB();
    }
}

3、切換工具類：DataSourceUtil

/**
* 數(shù)據(jù)源切換工具
 * @date 2022/5/19
*/
public class DataSourceUtil {
    /**
     * 默認(rèn)數(shù)據(jù)源
     */
    public static final String DEFAULT_DS = "db1";
    /**
    *  數(shù)據(jù)源屬于一個(gè)公共的資源
    *  采用ThreadLocal可以保證在多線程情況下線程隔離
    */
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

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

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

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

4、啟動(dòng)

（1）啟動(dòng)類中配置移除默認(rèn)的數(shù)據(jù)庫(kù)配置類

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

//移除默認(rèn)數(shù)據(jù)庫(kù)配置類
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class DbDemoApplication {

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

}

（2）測(cè)試

結(jié)果

db1庫(kù)

db2庫(kù)

二、一個(gè)是mysql一個(gè)是postgresql

1、pom依賴新增

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>

2、配置文件application.yml

#端口
server.port: 7788
spring.application.name: bddemo

# mysql
spring.datasource:
  #數(shù)據(jù)庫(kù)1
  db1:
    driver-class-name: com.mysql.cj.jdbc.Driver
    jdbc-url: jdbc:mysql://127.0.0.1:3306/db1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false
    username: root
    password: 123456
  #數(shù)據(jù)庫(kù)2
  db2:
#    driver-class-name: com.mysql.cj.jdbc.Driver
#    jdbc-url: jdbc:mysql://127.0.0.1:3306/db2?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false
#    username: root
#    password: 123456
    driver-class-name: org.postgresql.Driver
    jdbc-url: jdbc:postgresql://127.0.0.1:5432/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false
    username: postgres
    password: 123456

# mybatis
mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: ccom.example.demo.*.entity

注意：

之前都是mysql的庫(kù)，所以驅(qū)動(dòng)在上面