Springboot如何設置多數(shù)據(jù)源,隨時切換
需求
接到一個任務,把一個數(shù)據(jù)庫里面的數(shù)據(jù)定時導入到另外的數(shù)據(jù)庫中
但是又不允許我們通過binlog+canal同步,所以考慮起一個微服務充當同步腳本的作用
且配置多數(shù)據(jù)庫,并且支持隨時切換
環(huán)境
- 1、mysql多個庫
- 2、mysql+postgresql
思路
spring框架本身支持多數(shù)據(jù)源,我們查看他的定義
Spring的多數(shù)據(jù)源支持—AbstractRoutingDataSource,AbstractRoutingDataSource定義了抽象的determineCurrentLookupKey方法,子類實現(xiàn)此方法,來確定要使用的數(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; } // 確定當前要使用的數(shù)據(jù)源 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 #數(shù)據(jù)庫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ù)庫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ù)庫配置類:
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ù)庫配置 * @date 2022/5/19 */ @Configuration public class DataSourceConfig { /** * 數(shù)據(jù)源1 * spring.datasource.db1:application.properteis中對應屬性的前綴 * @return */ @Bean(name = "db1") @ConfigurationProperties(prefix = "spring.datasource.db1") public DataSource dataSourceOne() { return DataSourceBuilder.create().build(); } /** * 數(shù)據(jù)源2 * spring.datasource.db2:application.properteis中對應屬性的前綴 * @return */ @Bean(name = "db2") @ConfigurationProperties(prefix = "spring.datasource.db2") public DataSource dataSourceTwo() { return DataSourceBuilder.create().build(); } /** * 動態(tài)數(shù)據(jù)源: 通過AOP在不同數(shù)據(jù)源之間動態(tài)切換 * @return */ @Primary @Bean(name = "dynamicDataSource") public DataSource dynamicDataSource() { DynamicDataSource dynamicDataSource = new DynamicDataSource(); // 默認數(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中存在多個數(shù)據(jù)源了,事務管理器需要重新配置,不然器不知道選擇哪個數(shù)據(jù)源 * 事務管理器此時管理的數(shù)據(jù)源將是動態(tài)數(shù)據(jù)源dynamicDataSource * 配置@Transactional注解 * @return */ @Bean public PlatformTransactionManager transactionManager() { return new DataSourceTransactionManager(dynamicDataSource()); } }
2) DynamicDataSource 動態(tài)數(shù)據(jù)源類:
import com.example.demo.utils.DataSourceUtil; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; /** * 動態(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 { /** * 默認數(shù)據(jù)源 */ public static final String DEFAULT_DS = "db1"; /** * 數(shù)據(jù)源屬于一個公共的資源 * 采用ThreadLocal可以保證在多線程情況下線程隔離 */ private static final ThreadLocal<String> contextHolder = new ThreadLocal<>(); /** * 設置數(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、啟動
(1)啟動類中配置移除默認的數(shù)據(jù)庫配置類
import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; //移除默認數(shù)據(jù)庫配置類 @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: #數(shù)據(jù)庫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ù)庫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的庫,所以驅動在上面
現(xiàn)在因為數(shù)據(jù)庫的產品不一樣,所以驅動類名稱放在下面單獨配置(有些人真完全不會變通,哎)
3、測試
插入:pg數(shù)據(jù)庫的主鍵自增mybatis還有點難搞,我們直接配置id插入
- mysql:
- pg:
- 查詢:
總結
以上為個人經驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關文章
Spring boot2X Consul如何使用Feign實現(xiàn)服務調用
這篇文章主要介紹了spring boot2X Consul如何使用Feign實現(xiàn)服務調用,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2019-12-12在SpringBoot項目中實現(xiàn)讀寫分離的流程步驟
SpringBoot作為一種快速開發(fā)框架,廣泛應用于Java項目中,在一些大型應用中,數(shù)據(jù)庫的讀寫分離是提升性能和擴展性的一種重要手段,本文將介紹如何在SpringBoot項目中優(yōu)雅地實現(xiàn)讀寫分離,并通過適當?shù)拇a插入,詳細展開實現(xiàn)步驟,同時進行拓展和分析2023-11-11mybatis配置mapper-locations位置的三種方式小結
這篇文章主要給大家介紹了關于mybatis配置mapper-locations位置的三種方式,Mybatis-Plus的初衷是為了簡化開發(fā),而不建議開發(fā)者自己寫SQL語句的,但是有時客戶需求比較復雜,需要的朋友可以參考下2023-08-08