Springboot如何设置多数据源,随时切换_Java

需求

接到一个任务，把一个数据库里面的数据定时导入到另外的数据库中

但是又不允许我们通过binlog+canal同步，所以考虑起一个微服务充当同步脚本的作用

且配置多数据库，并且支持随时切换

环境

1、mysql多个库
2、mysql+postgresql

思路

spring框架本身支持多数据源，我们查看他的定义

spring的多数据源支持—abstractroutingdatasource，abstractroutingdatasource定义了抽象的determinecurrentlookupkey方法，子类实现此方法，来确定要使用的数据源

看下下面它的源码：

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;
	}

        // 确定当前要使用的数据源
        protected abstract object determinecurrentlookupkey();
}

所以我们只要写一个自定义类去继承上面这个abstractroutingdatasource类，并重写determinecurrentlookupkey 方法即可

操作

包如下：

一、多个库都是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
  #数据库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
  #数据库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 数据库配置类：

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;
/**
* 数据库配置
* @date 2022/5/19
*/
@configuration
public class datasourceconfig {

    /**
     * 数据源1
     * spring.datasource.db1：application.properteis中对应属性的前缀
     * @return
     */
    @bean(name = "db1")
    @configurationproperties(prefix = "spring.datasource.db1")
    public datasource datasourceone() {
        return datasourcebuilder.create().build();
    }

    /**
     * 数据源2
     * spring.datasource.db2：application.properteis中对应属性的前缀
     * @return
     */
    @bean(name = "db2")
    @configurationproperties(prefix = "spring.datasource.db2")
    public datasource datasourcetwo() {
        return datasourcebuilder.create().build();
    }

    /**
     * 动态数据源: 通过aop在不同数据源之间动态切换
     * @return
     */
    @primary
    @bean(name = "dynamicdatasource")
    public datasource dynamicdatasource() {
        dynamicdatasource dynamicdatasource = new dynamicdatasource();
        // 默认数据源
        dynamicdatasource.setdefaulttargetdatasource(datasourceone());
        // 配置多数据源
        map<object, object> dsmap = new hashmap<>();
        dsmap.put("db1", datasourceone());
        dsmap.put("db2", datasourcetwo());

        dynamicdatasource.settargetdatasources(dsmap);
        return dynamicdatasource;
    }

    /**
     * 配置多数据源后ioc中存在多个数据源了，事务管理器需要重新配置，不然器不知道选择哪个数据源
     * 事务管理器此时管理的数据源将是动态数据源dynamicdatasource
     * 配置@transactional注解
     * @return
     */
    @bean
    public platformtransactionmanager transactionmanager() {
        return new datasourcetransactionmanager(dynamicdatasource());
    }
}

2） dynamicdatasource 动态数据源类：

import com.example.demo.utils.datasourceutil;
import org.springframework.jdbc.datasource.lookup.abstractroutingdatasource;
/**
 * 动态数据源类
 * @date 2022/2/11
 */
public class dynamicdatasource extends abstractroutingdatasource {
    @override
    protected object determinecurrentlookupkey() {
        return datasourceutil.getdb();
    }
}

3、切换工具类：datasourceutil

/**
* 数据源切换工具
 * @date 2022/5/19
*/
public class datasourceutil {
    /**
     * 默认数据源
     */
    public static final string default_ds = "db1";
    /**
    *  数据源属于一个公共的资源
    *  采用threadlocal可以保证在多线程情况下线程隔离
    */
    private static final threadlocal<string> contextholder = new threadlocal<>();

    /**
     * 设置数据源名
     * @param dbtype
     */
    public static void setdb(string dbtype) {
        contextholder.set(dbtype);
    }

    /**
     * 获取数据源名
     * @return
     */
    public static string getdb() {
        return (contextholder.get());
    }

    /**
     * 清除数据源名
     */
    public static void cleardb() {
        contextholder.remove();
    }
}

4、启动

（1）启动类中配置移除默认的数据库配置类

import org.springframework.boot.springapplication;
import org.springframework.boot.autoconfigure.springbootapplication;
import org.springframework.boot.autoconfigure.jdbc.datasourceautoconfiguration;

//移除默认数据库配置类
@springbootapplication(exclude = {datasourceautoconfiguration.class})
public class dbdemoapplication {

    public static void main(string[] args) {
        springapplication.run(dbdemoapplication.class, args);
    }

}

（2）测试

结果

db1库

db2库

二、一个是mysql一个是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:
  #数据库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
  #数据库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的库，所以驱动在上面