1. 什么是多數據源

1.1 基本概念

多數據源（Multiple DataSources）是指在一個SpringBoot應用中同時連接和使用多個數據庫的技術。這些數據庫可以是：

• 不同類型的數據庫：MySQL、PostgreSQL、Oracle等
• 相同類型的不同實例：主庫、從庫、不同業務的數據庫
• 不同環境的數據庫：開發、測試、生產環境

1.2 傳統單數據源 vs 多數據源

單數據源架構

SpringBoot應用 → 單個DataSource → 單個數據庫

多數據源架構

SpringBoot應用 → DataSource1 → 數據庫1（用戶數據）→ DataSource2 → 數據庫2（訂單數據）→ DataSource3 → 數據庫3（日志數據）

---

2. 為什么需要多數據源

2.1 業務場景需求

1. 讀寫分離：主庫寫入，從庫讀取，提高性能
2. 業務隔離：不同業務模塊使用獨立數據庫
3. 數據遷移：新老系統數據庫并存
4. 分布式架構：微服務架構中的數據分離

2.2 技術優勢

• 性能優化：分散數據庫壓力
• 數據安全：重要數據隔離存儲
• 擴展性好：易于水平擴展
• 故障隔離：一個數據庫故障不影響其他業務

---

3. 多數據源的實現方式

3.1 靜態多數據源

• 編譯時確定：在代碼中明確指定使用哪個數據源
• 配置簡單：通過注解或配置類實現
• 性能較好：沒有動態切換的開銷

3.2 動態多數據源

• 運行時決定：根據業務邏輯動態選擇數據源
• 靈活性高：可以根據條件動態切換
• 實現復雜：需要自定義路由邏輯

---

4. 環境準備

4.1 創建SpringBoot項目

pom.xml依賴配置

<?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.7.8</version><relativePath/></parent><groupId>com.example</groupId><artifactId>multi-datasource-demo</artifactId><version>1.0.0</version><name>multi-datasource-demo</name><description>SpringBoot多數據源配置示例</description><properties><java.version>1.8</java.version></properties><dependencies><!-- SpringBoot Web Starter --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- SpringBoot JDBC Starter --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency><!-- MyBatis Starter --><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.3.0</version></dependency><!-- MySQL驅動 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><!-- 連接池 --><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.16</version></dependency><!-- Lombok --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!-- Test --><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>

4.2 準備測試數據庫

創建兩個測試數據庫

-- 創建用戶數據庫
CREATE DATABASE user_db CHARACTER SET utf8mb4;-- 創建訂單數據庫
CREATE DATABASE order_db CHARACTER SET utf8mb4;-- 用戶表
USE user_db;
CREATE TABLE user (id BIGINT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL,email VARCHAR(100),age INT,create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);INSERT INTO user (username, email, age) VALUES 
('張三', 'zhangsan@example.com', 25),
('李四', 'lisi@example.com', 30),
('王五', 'wangwu@example.com', 28);-- 訂單表
USE order_db;
CREATE TABLE orders (id BIGINT PRIMARY KEY AUTO_INCREMENT,user_id BIGINT NOT NULL,product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2),quantity INT,create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);INSERT INTO orders (user_id, product_name, price, quantity) VALUES 
(1, 'iPhone 14', 8999.00, 1),
(2, 'MacBook Pro', 15999.00, 1),
(1, 'AirPods', 1999.00, 2);

---

5. 方法一：使用@Primary和@Qualifier注解

5.1 配置文件

application.yml

# SpringBoot多數據源配置
spring:# 主數據源配置（用戶數據庫）datasource:primary:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/user_db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghaiusername: rootpassword: 123456type: com.alibaba.druid.pool.DruidDataSource# Druid連接池配置druid:initial-size: 5min-idle: 5max-active: 20max-wait: 60000time-between-eviction-runs-millis: 60000min-evictable-idle-time-millis: 300000validation-query: SELECT 1test-while-idle: truetest-on-borrow: falsetest-on-return: false# 從數據源配置（訂單數據庫）secondary:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/order_db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghaiusername: rootpassword: 123456type: com.alibaba.druid.pool.DruidDataSourcedruid:initial-size: 5min-idle: 5max-active: 20max-wait: 60000time-between-eviction-runs-millis: 60000min-evictable-idle-time-millis: 300000validation-query: SELECT 1test-while-idle: truetest-on-borrow: falsetest-on-return: false# MyBatis配置
mybatis:mapper-locations: classpath:mapper/**/*.xmltype-aliases-package: com.example.entityconfiguration:map-underscore-to-camel-case: truelog-impl: org.apache.ibatis.logging.stdout.StdOutImpl# 日志配置
logging:level:com.example.mapper: debugorg.springframework.jdbc: debug

5.2 數據源配置類

DataSourceConfig.java

package com.example.config;import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;/*** 多數據源配置類* 使用@Primary和@Qualifier注解方式實現多數據源*/
@Configuration
public class DataSourceConfig {/*** 創建主數據源（用戶數據庫）* @Primary 注解表示這是主要的數據源，當有多個同類型Bean時優先使用此Bean* @ConfigurationProperties 自動綁定配置文件中的屬性*/@Primary@Bean(name = "primaryDataSource")@ConfigurationProperties(prefix = "spring.datasource.primary")public DataSource primaryDataSource() {return new DruidDataSource();}/*** 創建從數據源（訂單數據庫）*/@Bean(name = "secondaryDataSource")@ConfigurationProperties(prefix = "spring.datasource.secondary")public DataSource secondaryDataSource() {return new DruidDataSource();}/*** 主數據源的JdbcTemplate*/@Primary@Bean(name = "primaryJdbcTemplate")public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource dataSource) {return new JdbcTemplate(dataSource);}/*** 從數據源的JdbcTemplate*/@Bean(name = "secondaryJdbcTemplate")public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource dataSource) {return new JdbcTemplate(dataSource);}/*** 主數據源的事務管理器*/@Primary@Bean(name = "primaryTransactionManager")public DataSourceTransactionManager primaryTransactionManager(@Qualifier("primaryDataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}/*** 從數據源的事務管理器*/@Bean(name = "secondaryTransactionManager")public DataSourceTransactionManager secondaryTransactionManager(@Qualifier("secondaryDataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}
}

5.3 MyBatis配置類

PrimaryMyBatisConfig.java

package com.example.config;import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;import javax.sql.DataSource;/*** 主數據源MyBatis配置* 掃描用戶相關的Mapper接口*/
@Configuration
@MapperScan(basePackages = "com.example.mapper.user", sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class PrimaryMyBatisConfig {/*** 主數據源的SqlSessionFactory*/@Primary@Bean(name = "primarySqlSessionFactory")public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);// 設置MyBatis配置文件位置bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/user/*.xml"));// 設置實體類別名包路徑bean.setTypeAliasesPackage("com.example.entity");return bean.getObject();}/*** 主數據源的SqlSessionTemplate*/@Primary@Bean(name = "primarySqlSessionTemplate")public SqlSessionTemplate primarySqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {return new SqlSessionTemplate(sqlSessionFactory);}
}

SecondaryMyBatisConfig.java

package com.example.config;import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;import javax.sql.DataSource;/*** 從數據源MyBatis配置* 掃描訂單相關的Mapper接口*/
@Configuration
@MapperScan(basePackages = "com.example.mapper.order", sqlSessionTemplateRef = "secondarySqlSessionTemplate")
public class SecondaryMyBatisConfig {/*** 從數據源的SqlSessionFactory*/@Bean(name = "secondarySqlSessionFactory")public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);// 設置MyBatis配置文件位置bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/order/*.xml"));// 設置實體類別名包路徑bean.setTypeAliasesPackage("com.example.entity");return bean.getObject();}/*** 從數據源的SqlSessionTemplate*/@Bean(name = "secondarySqlSessionTemplate")public SqlSessionTemplate secondarySqlSessionTemplate(@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {return new SqlSessionTemplate(sqlSessionFactory);}
}

5.4 實體類

User.java

package com.example.entity;import lombok.Data;
import java.time.LocalDateTime;/*** 用戶實體類*/
@Data
public class User {private Long id;private String username;private String email;private Integer age;private LocalDateTime createTime;
}

Order.java

package com.example.entity;import lombok.Data;
import java.math.BigDecimal;
import java.time.LocalDateTime;/*** 訂單實體類*/
@Data
public class Order {private Long id;private Long userId;private String productName;private BigDecimal price;private Integer quantity;private LocalDateTime createTime;
}

5.5 Mapper接口

UserMapper.java

package com.example.mapper.user;import com.example.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import java.util.List;/*** 用戶Mapper接口* 此接口會使用主數據源（用戶數據庫）*/
@Mapper
public interface UserMapper {/*** 查詢所有用戶*/List<User> findAll();/*** 根據ID查詢用戶*/User findById(@Param("id") Long id);/*** 根據用戶名查詢用戶*/User findByUsername(@Param("username") String username);/*** 插入用戶*/int insert(User user);/*** 更新用戶*/int update(User user);/*** 刪除用戶*/int deleteById(@Param("id") Long id);
}

OrderMapper.java

package com.example.mapper.order;import com.example.entity.Order;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import java.util.List;/*** 訂單Mapper接口* 此接口會使用從數據源（訂單數據庫）*/
@Mapper