关键词:Spring Boot、多数据源配置、MySQL、SQL Server、Oracle、动态切换
✅ 摘要
在实际企业级开发中,一个 Spring Boot 项目可能需要连接多个数据库,比如 MySQL、SQL Server 和 Oracle。不同的业务模块可能依赖不同的数据源,这就要求我们掌握 如何在 Spring Boot 中灵活配置和管理多个数据源。
本文将围绕以下内容进行详细讲解:
- Spring Boot 默认数据源配置方式
- 配置单个数据库(MySQL、SQL Server、Oracle)
- 多数据源配置与使用(MySQL + SQL Server + Oracle)
- 使用
AbstractRoutingDataSource
实现动态数据源切换 - 常见问题与解决方案(驱动类、URL格式、连接失败)
每部分都配有 完整的 application.yml 配置文件和 Java 配置类代码示例。
📌 一、Spring Boot 数据源配置基础
🔹 1. 默认数据源配置(以 MySQL 为例)
spring:
datasource:
url: jdbc:mysql://localhost:3306/test_db?useSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
⚠️ 注意:
url
要注意时区配置(serverTimezone)- 确保引入了正确的 JDBC 驱动包
📌 二、单个数据库的配置方式
🔹 1. MySQL 数据源配置
Maven 依赖:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
application.yml:
spring:
datasource:
mysql:
url: jdbc:mysql://localhost:3306/mysql_db?useSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
🔹 2. SQL Server 数据源配置
Maven 依赖:
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>12.4.0.jre8</version>
</dependency>
application.yml:
spring:
datasource:
sqlserver:
url: jdbc:sqlserver://localhost:1433;databaseName=SqlServerDB;encrypt=true;trustServerCertificate=false;loginTimeout=30;
username: sa
password: yourStrongPassword
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
🔹 3. Oracle 数据源配置
Maven 依赖(需手动下载 ojdbc jar 并安装到本地仓库):
mvn install:install-file -Dfile=ojdbc8.jar -DgroupId=com.oracle.database.jdbc -DartifactId=ojdbc8 -Dversion=21.10.0.0 -Dpackaging=jar
pom.xml 添加依赖:
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>21.10.0.0</version>
</dependency>
application.yml:
spring:
datasource:
oracle:
url: jdbc:oracle:thin:@//localhost:1521/ORCLCDB
username: system
password: oracle
driver-class-name: oracle.jdbc.OracleDriver
📌 三、多数据源配置(MySQL + SQL Server + Oracle)
🔹 1. application.yml 多数据源配置
spring:
datasource:
mysql:
url: jdbc:mysql://localhost:3306/mysql_db?useSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
sqlserver:
url: jdbc:sqlserver://localhost:1433;databaseName=SqlServerDB;encrypt=true;trustServerCertificate=false;loginTimeout=30;
username: sa
password: yourStrongPassword
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
oracle:
url: jdbc:oracle:thin:@//localhost:1521/ORCLCDB
username: system
password: oracle
driver-class-name: oracle.jdbc.OracleDriver
🔹 2. Java 配置类实现多数据源注入
第一步:定义配置属性类
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
@Data
public class DataSourceProperties {
private Map<String, DataSourceConfig> datasource;
@Data
public static class DataSourceConfig {
private String url;
private String username;
private String password;
private String driverClassName;
}
}
第二步:创建多个数据源 Bean
@Configuration
@RequiredArgsConstructor
public class DataSourceConfig {
private final DataSourceProperties dataSourceProperties;
@Bean("mysqlDataSource")
@ConfigurationProperties(prefix = "spring.datasource.mysql")
public DataSource mysqlDataSource() {
return DataSourceBuilder.create()
.url(dataSourceProperties.getDatasource().get("mysql").getUrl())
.username(dataSourceProperties.getDatasource().get("mysql").getUsername())
.password(dataSourceProperties.getDatasource().get("mysql").getPassword())
.driverClassName(dataSourceProperties.getDatasource().get("mysql").getDriverClassName())
.build();
}
@Bean("sqlServerDataSource")
public DataSource sqlServerDataSource() {
return DataSourceBuilder.create()
.url(dataSourceProperties.getDatasource().get("sqlserver").getUrl())
.username(dataSourceProperties.getDatasource().get("sqlserver").getUsername())
.password(dataSourceProperties.getDatasource().get("sqlserver").getPassword())
.driverClassName(dataSourceProperties.getDatasource().get("sqlserver").getDriverClassName())
.build();
}
@Bean("oracleDataSource")
public DataSource oracleDataSource() {
return DataSourceBuilder.create()
.url(dataSourceProperties.getDatasource().get("oracle").getUrl())
.username(dataSourceProperties.getDatasource().get("oracle").getUsername())
.password(dataSourceProperties.getDatasource().get("oracle").getPassword())
.driverClassName(dataSourceProperties.getDatasource().get("oracle").getDriverClassName())
.build();
}
}
📌 四、动态切换数据源(基于 AbstractRoutingDataSource)
🔹 1. 定义当前线程使用的数据源标识
public class DynamicDataSourceContextHolder {
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
public static void setDataSourceKey(String key) {
CONTEXT_HOLDER.set(key);
}
public static String getDataSourceKey() {
return CONTEXT_HOLDER.get();
}
public static void clearDataSourceKey() {
CONTEXT_HOLDER.remove();
}
}
🔹 2. 自定义 AbstractRoutingDataSource
@Component
@RequiredArgsConstructor
public class DynamicDataSource extends AbstractRoutingDataSource {
private final DataSource mysqlDataSource;
private final DataSource sqlServerDataSource;
private final DataSource oracleDataSource;
@PostConstruct
public void init() {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put("mysql", mysqlDataSource);
targetDataSources.put("sqlserver", sqlServerDataSource);
targetDataSources.put("oracle", oracleDataSource);
this.setTargetDataSources(targetDataSources);
this.setDefaultTargetDataSource(mysqlDataSource); // 设置默认数据源
this.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.getDataSourceKey();
}
}
🔹 3. 配置为事务管理器的数据源
@Bean
public PlatformTransactionManager transactionManager(DynamicDataSource dynamicDataSource) {
return new DataSourceTransactionManager(dynamicDataSource);
}
🔹 4. 在 Service 层使用动态数据源
@Service
@RequiredArgsConstructor
public class UserService {
private final JdbcTemplate jdbcTemplate;
public void queryFromMysql() {
DynamicDataSourceContextHolder.setDataSourceKey("mysql");
List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM user");
System.out.println("MySQL 查询结果:" + result);
}
public void queryFromSqlServer() {
DynamicDataSourceContextHolder.setDataSourceKey("sqlserver");
List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM Users");
System.out.println("SQL Server 查询结果:" + result);
}
public void queryFromOracle() {
DynamicDataSourceContextHolder.setDataSourceKey("oracle");
List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM employees");
System.out.println("Oracle 查询结果:" + result);
}
}
✅ 总结
以下几点为本文重点:
模块 | 技能点 |
---|---|
单数据源配置 | MySQL、SQL Server、Oracle 的基本配置方法 |
多数据源配置 | 如何在一个 Spring Boot 项目中配置多个数据源 |
动态数据源切换 | 使用 AbstractRoutingDataSource 实现运行时切换 |
实战能力 | 结合 JdbcTemplate、事务管理器使用多数据源 |
这些技能是你构建复杂微服务系统、支持多数据库架构的重要基础。